I can't figure this one out here are my table structures:
Table period(yearMonth varchar(6), weekOfMonth varchar(1), fullDate date)
Table data(SKU varchar(6), received int, yearMonth varchar(6), fullDate date)
here's my data for period:
yearMonth weekOfMonth fullDate
------------------------------------------
201104 1 Apr 3 2011 12:00AM
201104 1 Apr 4 2011 12:00AM
201104 1 Apr 5 2011 12:00AM
201104 1 Apr 6 2011 12:00AM
201104 1 Apr 7 2011 12:00AM
201104 1 Apr 8 2011 12:00AM
201104 1 Apr 9 2011 12:00AM
201104 2 Apr 10 2011 12:00AM
201104 2 Apr 11 2011 12:00AM
201104 2 Apr 12 2011 12:00AM
201104 2 Apr 13 2011 12:00AM
201104 2 Apr 14 2011 12:00AM
201104 2 Apr 15 2011 12:00AM
201104 2 Apr 16 2011 12:00AM
201104 3 Apr 17 2011 12:00AM
201104 3 Apr 18 2011 12:00AM
201104 3 Apr 19 2011 12:00AM
201104 3 Apr 20 2011 12:00AM
201104 3 Apr 21 2011 12:00AM
201104 3 Apr 22 2011 12:00AM
201104 3 Apr 23 2011 12:00AM
201104 4 Apr 24 2011 12:00AM
201104 4 Apr 25 2011 12:00AM
201104 4 Apr 26 2011 12:00AM
201104 4 Apr 27 2011 12:00AM
201104 4 Apr 28 2011 12:00AM
201104 4 Apr 29 2011 12:00AM
201104 4 Apr 30 2011 12:00AM
201105 1 May 1 2011 12:00AM
201105 1 May 2 2011 12:00AM
201105 1 May 3 2011 12:00AM
201105 1 May 4 2011 12:00AM
201105 1 May 5 2011 12:00AM
201105 1 May 6 2011 12:00AM
201105 1 May 7 2011 12:00AM
201105 2 May 8 2011 12:00AM
201105 2 May 9 2011 12:00AM
201105 2 May 10 2011 12:00AM
201105 2 May 11 2011 12:00AM
201105 2 May 12 2011 12:00AM
201105 2 May 13 2011 12:00AM
201105 2 May 14 2011 12:00AM
201105 3 May 15 2011 12:00AM
201105 3 May 16 2011 12:00AM
201105 3 May 17 2011 12:00AM
201105 3 May 18 2011 12:00AM
201105 3 May 19 2011 12:00AM
201105 3 May 20 2011 12:00AM
201105 3 May 21 2011 12:00AM
201105 4 May 22 2011 12:00AM
201105 4 May 23 2011 12:00AM
201105 4 May 24 2011 12:00AM
201105 4 May 25 2011 12:00AM
201105 4 May 26 2011 12:00AM
201105 4 May 27 2011 12:00AM
201105 4 May 28 2011 12:00AM
201106 1 Jun 1 2011 12:00AM
201106 1 Jun 2 2011 12:00AM
201106 1 Jun 3 2011 12:00AM
201106 1 Jun 4 2011 12:00AM
201106 1 May 29 2011 12:00AM
201106 1 May 30 2011 12:00AM
201106 1 May 31 2011 12:00AM
201106 2 Jun 5 2011 12:00AM
201106 2 Jun 6 2011 12:00AM
201106 2 Jun 7 2011 12:00AM
201106 2 Jun 8 2011 12:00AM
201106 2 Jun 9 2011 12:00AM
201106 2 Jun 10 2011 12:00AM
201106 2 Jun 11 2011 12:00AM
201106 3 Jun 12 2011 12:00AM
201106 3 Jun 13 2011 12:00AM
201106 3 Jun 14 2011 12:00AM
201106 3 Jun 15 2011 12:00AM
201106 3 Jun 16 2011 12:00AM
201106 3 Jun 17 2011 12:00AM
201106 3 Jun 18 2011 12:00AM
201106 4 Jun 19 2011 12:00AM
201106 4 Jun 20 2011 12:00AM
201106 4 Jun 21 2011 12:00AM
201106 4 Jun 22 2011 12:00AM
201106 4 Jun 23 2011 12:00AM
201106 4 Jun 24 2011 12:00AM
201106 4 Jun 25 2011 12:00AM
201106 5 Jul 1 2011 12:00AM
201106 5 Jul 2 2011 12:00AM
201106 5 Jun 26 2011 12:00AM
201106 5 Jun 27 2011 12:00AM
201106 5 Jun 28 2011 12:00AM
201106 5 Jun 29 2011 12:00AM
201106 5 Jun 30 2011 12:00AM
This is the query I've tried:
SELECT DISTINCT a.SKU, SUM(a.received) AS Received
a.yearMonth , p.weekOfMonth
FROM data a
RIGHT OUTER JOIN period p
ON p.fullDate = a.fullDate
WHERE p.yearMonth >= '201104' AND p.yearMonth < '201107'
GROUP BY a.SKU, a.yearMonth , p.weekOfMonth
It doesn't return each yearMonth and week of that month if there is no data.
here's the data in table data:
sku received yearMonth fullDate
---------------------------------------
AAAA 5 201106 Jun 23 2011 12:00AM
BBBB 1 201106 Jun 10 2011 12:00AM
BBBB 1 201106 Jun 15 2011 12:00AM
here's my data when I join the 2 tables:
SKU received yearMonth weekOfMonth
-------------------------------------
AAAA 5 201106 4
BBBB 1 201106 2
BBBB 1 201106 3
I would like to display either NULL or 0 for received if there is no data so for example:
SKU received yearMonth weekOfMonth
-------------------------------------
AAAA 0 201104 1
AAAA 0 201104 2
AAAA 0 201104 3
AAAA 0 201104 4
AAAA 0 201105 1
AAAA 0 201105 2
AAAA 0 201105 3
AAAA 0 201105 4
AAAA 0 201106 1
AAAA 0 201106 2
AAAA 0 201106 3
AAAA 5 201106 4
AAAA 0 201106 5
BBBB 0 201104 1
BBBB 0 201104 2
BBBB 0 201104 3
BBBB 0 201104 4
BBBB 0 201105 1
BBBB 0 201105 2
BBBB 0 201105 3
BBBB 0 201105 4
BBBB 0 201106 1
BBBB 1 201106 2
BBBB 1 201106 3
BBBB 0 201106 4
BBBB 0 201106 5
The SUM() function is eliminating the NULL results. Execution message: Warning: Null value is eliminated by an aggregate or other SET operation.
UPDATE:
It's ugly, but it works. May need tweaking and someone with more expertise may be able to critique it.
UPDATE:
I added a DISTINCT and removed the GROUP BY on the sub query.
SELECT DISTINCT
subQ.SKU,
(
SELECT SUM(d.Received) AS NumReceived
FROM data d
JOIN period p ON d.FullDate = p.FullDate
WHERE d.YearMonth = subQ.yearMonth
AND d.SKU = subQ.SKU
AND p.weekOfMonth = subQ.weekOfMonth
) AS Received,
subQ.yearMonth,
subQ.weekOfMonth
FROM (
SELECT DISTINCT
d.SKU,
p.yearMonth,
p.weekOfMonth
FROM
period p,
data d
) subQ
ORDER BY
subQ.SKU