Outer Joins

I have a set of data which needs to be put in a table in addition to dates
(daily). There are some dates where a location could be closed and hence no
data values but i still want to see the date row in the table with location
and all other associated details but NULL for visit counts.
I have tried LEFT OUTER / RIGHT OUTER but failed. Is there an easy way to do
this ?
Thanks
AsimAsim wrote:
> I have a set of data which needs to be put in a table in addition to
> dates (daily). There are some dates where a location could be closed
> and hence no data values but i still want to see the date row in the
> table with location and all other associated details but NULL for
> visit counts.
> I have tried LEFT OUTER / RIGHT OUTER but failed. Is there an easy
> way to do this ?
> Thanks
> Asim
You need to post table DDL and sample data.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||It is very hard to debug code that you cannot see :)|||It sounds like you want the output to include information (dates)
that are nowhere to be found in your data, as if you were to query
an empty sales table and expect a result with some dates.
If you want data in your result, the data has to be in the source
of the query. A calendar table might be what you want in this
case:
select
C.theDate,
A.Location,
A.otherStuff
from Calendar as C
left outer join AsimTable as A
on A.dt >= C.theDate
and A.dt < C.theDate + 1
See http://www.aspfaq.com/show.asp?id=2519
Steve Kass
Drew University
Asim wrote:

>I have a set of data which needs to be put in a table in addition to dates
>(daily). There are some dates where a location could be closed and hence no
>data values but i still want to see the date row in the table with location
>and all other associated details but NULL for visit counts.
>I have tried LEFT OUTER / RIGHT OUTER but failed. Is there an easy way to d
o
>this ?
>Thanks
>Asim
>|||Sorry guys!
Here is the code, the first temp table gets all the cost center, procedures
and etc and then the second table gets each date (D_DateMonth) .
DECLARE @.Procs TABLE
(ProcedureCode varchar(11), CostCtr varchar(16), CcName varchar(31),
StatCode varchar(11), LocationID varchar(30),
SubGrouping varchar(30), [Grouping] varchar(30), Service varchar(50), Campus
varchar(30), SiteGroup varchar(50))
INSERT INTO @.Procs
SELECT ProcedureCode, CostCtr, CcName, StatCode, LocationID, SubGrouping,
[Grouping], Service, Campus, SiteGroup
FROM D_Procedures
GROUP BY ProcedureCode, CostCtr, CcName, StatCode, LocationID,
SubGrouping, [Grouping], Service, Campus, SiteGroup
SELECT dbo.D_Date_month.[Date] as DOM, dbo.TitleCase(a.Service) as
Service,a.Campus as Hospital,dbo.TitleCase(a.SiteGroup) as Location,
a.CostCtr as CostCtr,dbo.TitleCase(a.CcName) as CcName,
SUM(CASE WHEN OC1_Txns.TransactionCount < 0 THEN -1 ELSE 1 END) as
Visits,NULL AS BudgetVisits,
NULL AS VisitVar, NULL AS LastFYVisits, NULL AS LastFYVar, NULL AS Charges,
NULL AS LastFYCharges,
NULL AS ChargesVar,Convert(smalldatetime, GetDate()) as RowUpdatedatetime
FROM dbo.D_Date_month LEFT OUTER JOIN OC1_Txns ON OC1_Txns.BatchDateTime
= dbo.D_Date_month.[Date]
INNER JOIN @.Procs a ON OC1_Txns.TransactionProcedure = a.ProcedureCode
WHERE (Left(a.StatCode,1) = 'V'
OR a.StatCode='FAMPLAN' OR a.StatCode='MIDWIFE' OR a.StatCode='ORTHOOTH')
AND (OC1_Txns.BatchDateTime >= '04/01/2005' AND OC1_Txns.BatchDateTime <
'04/03/2005')
GROUP BY dbo.D_Date_month.[Date],a.Service, a.Campus, a.SiteGroup,
a.CostCtr, a.CcName
ORDER BY dbo.D_Date_month.[Date],a.Service, a.Campus, a.SiteGroup,
a.CostCtr, a.CcName
"--CELKO--" wrote:

> It is very hard to debug code that you cannot see :)
>|||The C table has no dates.. The information is based on visit level in that
table and I need to build the daily visits grouped by service, location and
etc and even if there was no visit on a particular date for a service or
locaiton, i want to see that service or location with a null for visits but
the date.
"Steve Kass" wrote:

> It sounds like you want the output to include information (dates)
> that are nowhere to be found in your data, as if you were to query
> an empty sales table and expect a result with some dates.
> If you want data in your result, the data has to be in the source
> of the query. A calendar table might be what you want in this
> case:
> select
> C.theDate,
> A.Location,
> A.otherStuff
> from Calendar as C
> left outer join AsimTable as A
> on A.dt >= C.theDate
> and A.dt < C.theDate + 1
> See http://www.aspfaq.com/show.asp?id=2519
> Steve Kass
> Drew University
> Asim wrote:
>
>|||Why not fold the first query into the second one instead of creating a
physical table? Why are you doiing formatting in the query instead of
enforcing this in the DDL? Why do data elements keep changing names
from table to table?
My quick guess is that it might look like this:
SELECT D.foobar_date, A.service, A.campus, A.site_group), A.cost_ctr,
A.cc_name,
SUM(CASE WHEN T.transaction_count < 0
THEN -1 ELSE 1 END) AS visits,
CURRENT_TIMESTAMP
FROM D_Date_Month AS M
LEFT OUTER JOIN
Oc1_Txns AS T
ON T.batch_datetime = D.foobar_date
INNER JOIN
(SELECT DISTINCT procedure_code, cost_ctr, cc_name, stat_code,
location_id, subgrouping, grouping, service, campus, site_group
FROM D_Procedures) AS A
ON T.procedure_code = A.procedure_code
WHERE (LEFT(A.stat_code, 1) = 'V'
OR A.stat_code IN ('FAMPLAN', 'MIDWIFE', 'ORTHOOTH'))
AND T.batch_datetime BETWEEN '2005-04-01 00:00:00' AND '2005-04-03
23:59:59.9999'
GROUP BY D.foobar_date, A.service, A.campus, A.site_group, A.cost_ctr,
A.cc_name;|||On 27 May 2005 11:34:09 -0700, --CELKO-- wrote:
(snip)
>AND T.batch_datetime BETWEEN '2005-04-01 00:00:00' AND '2005-04-03
>23:59:59.9999'
Hi Joe,
This is a bad modification from the original code, for several reasons.
First, the constant '2005-04-03 23:59:59.9999' will not convert to a
datetime value, since you specify one decimal place too much.
Second, the format you used is not safe. It could be interpreted as
yyyy-mm-dd or yyyy-dd-mm, depending on regional settings. The only safe
formats in SQL Server are:
* yyyymmdd (date only - note: no interpunction)
* yyyy-mm-ddThh:mm:ss.ttt (date plus time - note the dashes in the date
part, the colons and decimal point in the time part and the capital T
that seperates the parts. Also note that the milliseconds (the .ttt
part) is optional).
Third, if you change it to '2005-04-03T23:59:59.999', it will be
converted to the fourth of april, midnight. You should use either
'2005-04-03T23:59:59.997' (if the column has datatype datetime) or
'2005-04-03T23:59:00' (if it is smalldatetime). And you must change it
if the column's datatype changes, or if MS changes the precision of
either the datetime or the smalldatetime datatype.
Of course, just using T.batch_datetime < '20050404' (almost the same as
the original code, only the date format is changed!) is much easier and
much safer.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)