Hi
If you have the products table with all products you can join that products
table with ttsfinancial using left outer join then you can get the result
that you want
Thanks
DVR
-----Original Message-----
From: Alex Borshch
[mailto:mssqldba-ezmlmshield-x55601257.[Email address protected]
Sent: Wednesday, April 26, 2006 11:41 PM
To: LazyDBA Discussion
Subject: JOINS question
Guys,
I have a table ttsfinancial containing variables
Storeid - store
Product - there are 60 different products a store sells, but some weeks
a store might not sell a type of a product
Count - # products sold
Amount $ products sold
Wedate = week ending (calendar)
I am sorting for all the products sold by a store, grouped weekly, for a
period of 1 year.
The problem is, if a store does not sell a product in any day of the
week, then the product doesn't show up.
SELECT wedate, product, sum(count)'#',sum(amount) '$'
FROM ttsfinancial
WHERE storeid=468 and wedate between '12/29/04' and '12/29/05'
GROUP BY product, wedate
Output: Product # $
1/4/2005
Product 1
12
$83.31
1/4/2005
Product 2
23
$166.64
1/4/2005
Product 3
278
$2,777.34
1/11/2005
Product 2
24
$169.65
1/11/2005
Product 3
263
$2,827.62
1/11/2005
Product 4
1
$6.93
As my final output, I need all the products to line up together, with
weeks next to each other, so in the sql query I would need product 4 to
show up with NULLS for # and $ for WE 1/4 and product 1 show up for WE
1/11, with NULLS, so when I copy and paste them next to each other, they
line up.
Final output desired in excel:
WE 1/4
WE 1/11
Product
#
$
#
$
Product 1
12
$83.31
(blank)
(blank)
Product 2
23
$166.64
24
$169.65
Product 3
278
$2777.34
263
$2827.62
Product 4
(blank)
(blank)
1
$6.93
And so on through 52 weeks
I am thinking I need to join the table on itself somehow, so I can get
those NULLS/blanks from above - otherwise I would have to line up every
week's products manually by inserting blank lines.
Any idea how to optimize the query/join to get the NULLS?
Alex Borshch
In-Store Technology Analyst
Cold Stone Creamery
Phone (480) 362-4457
Fax (480) 362-4796
Want the best birthday gift ever? Sign up here:
www.coldstonecreamery.com/birthday
<BLOCKED::http://www.coldstonecreamery.com/birthday>
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page