July 24, 2012 at 9:48 am
I am currently having below table and sample rows.
CREATE TABLE ORDERS (Year int, Month int, ORDER_COUNT int, ORDER_TOTAL int)
INSERT INTO ORDERS
SELECT 2010,4,8,29381 UNION
SELECT 2010, 5, 8, 28289 UNION
SELECT 2010 ,6, 7, 8890 UNION
SELECT 2010, 7, 8, 7160 UNION
SELECT 2010, 9, 7, 11340 UNION
SELECT 2010, 10, 8, 15960 UNION
SELECT 2010, 11, 7, 16590 UNION
SELECT 2010 ,12, 8, 21960 UNION
SELECT 2011 ,1, 8, 25160 UNION
SELECT 2011, 2, 7, 24640 UNION
SELECT 2011, 5, 8, 37160 UNION
SELECT 2011, 6 ,7, 22224 UNION
SELECT 2011 ,7, 8, 28401 UNION
SELECT 2011, 8, 5, 19282
I need the result set as mentioned below i.e. I need the values for missing month and also respective values as 0.
Year Month ORDER_COUNT ORDER_TOTAL
2010 1 0 0
2010 2 0 0
2010 3 0 0
2010 4 8 29381
2010 5 8 28289
2010 6 7 8890
2010 7 8 7160
2010 9 7 11340
2010 10 8 15960
2010 11 7 16590
2010 12 8 21960
2011 1 8 25160
2011 2 7 24640
2011 3 0 0
2011 4 0 0
2011 5 8 37160
2011 6 7 22224
2011 7 8 28401
2011 8 5 19282
2011 9 0 0
2011 10 0 0
2011 11 0 0
2011 12 0 0
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 24, 2012 at 9:52 am
rhythmk (7/24/2012)
I am currently having below table and sample rows.
CREATE TABLE ORDERS (Year int, Month int, ORDER_COUNT int, ORDER_TOTAL int)
INSERT INTO ORDERS
SELECT 2010,4,8,29381 UNION
SELECT 2010, 5, 8, 28289 UNION
SELECT 2010 ,6, 7, 8890 UNION
SELECT 2010, 7, 8, 7160 UNION
SELECT 2010, 9, 7, 11340 UNION
SELECT 2010, 10, 8, 15960 UNION
SELECT 2010, 11, 7, 16590 UNION
SELECT 2010 ,12, 8, 21960 UNION
SELECT 2011 ,1, 8, 25160 UNION
SELECT 2011, 2, 7, 24640 UNION
SELECT 2011, 5, 8, 37160 UNION
SELECT 2011, 6 ,7, 22224 UNION
SELECT 2011 ,7, 8, 28401 UNION
SELECT 2011, 8, 5, 19282
I need the result set as mentioned below i.e. I need the values for missing month and also respective values as 0.
Year Month ORDER_COUNT ORDER_TOTAL
2010 1 0 0
2010 2 0 0
2010 3 0 0
2010 4 8 29381
2010 5 8 28289
2010 6 7 8890
2010 7 8 7160
2010 9 7 11340
2010 10 8 15960
2010 11 7 16590
2010 12 8 21960
2011 1 8 25160
2011 2 7 24640
2011 3 0 0
2011 4 0 0
2011 5 8 37160
2011 6 7 22224
2011 7 8 28401
2011 8 5 19282
2011 9 0 0
2011 10 0 0
2011 11 0 0
2011 12 0 0
Missing something here, the code you have written in an attempt to solve your problem. I see a fairly easy solution and I am curious what you have tried before I post it.
July 24, 2012 at 9:53 am
You could use a number table to create all month/years in the required range, then join to it with your data & set the nulls to zero.
July 24, 2012 at 10:21 am
Correct Lynn,have provided the wrong input.:unsure:
The requirement is still not finalized and will post that if have any doubt.
However thanks all.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply