outer join - per date

[NoobieRhyme]

Hi. I have an example here for my sql question.

TABLE A:

Item
======
x
y
z


TABLE B:

ITEM DATE COUNT
===========================
x 8/17/05 34
y 8/17/05 4
x 8/18/05 76
y 8/19/05 3


I wanna have a daily inventory of the items. So I need to display all the items in Table A every day whether they have a count or not.

For this example, I'm expecting the following result:

ITEM DATE COUNT
===========================
x 8/17/05 34
y 8/17/05 4
z 8/17/05 0 (Null)
x 8/18/05 76
y 8/18/05 0 (Null)
z 8/18/05 0 (Null)
x 8/19/05 0 (Null)
y 8/19/05 3
z 8/19/05 0 (Null)


Help please? thanks!

 

[vongrunt]

Every day... for dates that exist in table B? Or for dates within certain from-to range?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[amrita418]

Try this:

select c.item, c.date, sum(c.count)
from 
(select   a.item item, 
         b.date date, 
         b.[count] [count]
from     TableA 
         inner join TableB b 
         on (a.item = b.item)
union all
select   a.item item, 
         b.date date,
         0 [count]
from     TableA,
         (select distinct date from TableB) b) c
group by c.item, c.date

PS: Code not Tested.

Regards,
AA

 

[NoobieRhyme]

Thank you very much amrita...however, it's not yet working. the two "select" works fine separately but when the two is "union-ed", the return takes too long that i didnt wait anymore...but i believe it's getting near.

vongrunt, what I need for now is for dates that exist in table B. But i'm also interested to know how to do it for dates within certain from-to range.

Thank you very much guys!

 

[vongrunt]

This should work:

-- sample data
declare @tableA table (Item char(1))
insert into @tableA values('x')
insert into @tableA values('y')
insert into @tableA values('z')

declare @tableB table(Item char(1), [Date] smalldatetime, [count] int)
insert into @tableB values ('x', '8/17/05', 34)
insert into @tableB values ('y', '8/17/05',  4)
insert into @tableB values ('x', '8/18/05', 76)
insert into @tableB values ('y', '8/19/05',  3)

-- actual query
select X.Item, X.[Date], isnull(T.[Count], 0)
from
(	select distinct A.Item, B.[Date]
	from @tableA A 
	cross join @tableB B
) X
left outer join @tableB T on X.Item=T.Item and X.[Date]=T.[Date]
order by X.[Date], X.Item

If (Item, Date) are not unique in table B (e.g. two or more rows for the same item & date) then you'll have to modify above query with GROUP BY... simple.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NoobieRhyme]

wow...it's working!...thank you very much...i wonder if i'm still allowed to make extra question..

I add a column to Table A and the Table B remains the same.

Item Expiration Date
============================
x 8/18/2005
y 9/01/2005
z 9/01/2005

Now we do not expect the expired item to display at the dates when it is expired already.

Sorry about this but i'm learning from your inputs. thanks a lot!

 

[vongrunt]

With that new column and provided sample data, what is expected output?

Also: distinct on cross join can be slow... this is sometimes faster:

select A.Item, B.[Date], isnull(T.[Count], 0)
from @tableA A
cross join ( select distinct [Date] from @tableB ) B
left outer join @tableB T on A.Item=T.Item and B.[Date]=T.[Date]
order by B.[Date], A.Item

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)