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>




MS Sql Server LazyDBA home page