June 10, 2015 at 12:40 pm
I have one table with contains number of item processed in a particular year and month and week. From this table, i need to fetch one more table that contains 5 column: L1, L2, year, week, Month. This table should contain data like:
currently system has data until may-2015, so from Jan to May, LR1 should be number of item in one each week of 2015 and then for Jun to dec, LR1 should be number of items in respective week of 2014. Similarly, LR2: Jan to may : Number of items in each week should be of 2014 and then for Jun-Dec, Number of items in each week should be of respective weeks of 2014.
Current table:
year week Month Items
2015 w5 M2 I1
2014 w18 M6 I2
2014 w18 M6 I7
2015 w6 M2 I3
2015 w9 M3 I4
2015 w17 M5 I5
2014 w17 M5 I9
2014 w17 M5 I10
2014 w2 M6 I4
2013 w18 M6 I6
desired table
Month Week LR1 LR2
M6 w18 2 (2014) 1 (2013)
M5 w17 1 (2015) 2(2014)
any help???
June 10, 2015 at 2:21 pm
Where to begin? For starters, since you're clearly new here, it would help a LOT and you would get better answers if you posted sample data like this:
SELECT 2015 AS Yr,5 AS Wk,2 AS Mo,1 AS ItemCount
UNION ALL SELECT 2014 ,18 ,6 ,2
UNION ALL SELECT 2014 ,18 ,6 ,7
UNION ALL SELECT 2015 ,6 ,2 ,3
UNION ALL SELECT 2015 ,9 ,3 ,4
UNION ALL SELECT 2015 ,17 ,5 ,5
UNION ALL SELECT 2014 ,17 ,5 ,9
UNION ALL SELECT 2014 ,17 ,5 ,10
UNION ALL SELECT 2014 ,2 ,6 ,4
UNION ALL SELECT 2013 ,18 ,6 ,6;
Then the busy folks here can easily write a query against it and you'll get a tested answer
The month part of the problem can be solved with a CASE statement.
Then you'd do a SUM on top of that, and you should be off to the races.
June 10, 2015 at 2:35 pm
is this the way to ask any query? I dont actually get the format in which it has been written
June 10, 2015 at 2:38 pm
i get the way you have posted the data.
June 10, 2015 at 3:21 pm
I should have referred you to this article:
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
It explains how to post data etc. It just makes everyone's life easier - you get better/faster answers because using the data you provide if you follow the article's advice, you can post everything necessary to answer the question.
For LR1 and LR2, I would probably use a CASE statement to split the years into two groups. You also may want to read Jeff's article on Crosstabs and Pivots[/url], since it seems that's what you're doing.
June 11, 2015 at 8:55 am
This forum etiquette link is very helpful and will change my original post in this format only. Thank You so much.
June 11, 2015 at 12:43 pm
There's a duplicate post at http://qa.sqlservercentral.com/Forums/Topic1693743-3077-1.aspx with a solution that worked.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply