Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hi, i am trying to do an SQL query for get a data for a line chart where i need to show the trends of the data, here is the sql query i came up with:
SQL
SELECT
    material
,   sum(CASE WHEN MONTH(date_in) = 1 AND YEAR(date_in)=2009 THEN amount ELSE 0 END) AS "JAN 2009"
,   sum(CASE WHEN MONTH(date_in) = 2 AND YEAR(date_in)=2009 THEN amount ELSE 0 END) AS "FEB 2009"
,   sum(CASE WHEN MONTH(date_in) = 3 AND YEAR(date_in)=2009 THEN amount ELSE 0 END) AS "MAR 2009"
,	sum(CASE WHEN MONTH(date_in) = 4 AND YEAR(date_in)=2009 THEN amount ELSE 0 END) AS "APR 2009"
FROM rec_stats
GROUP BY material
ORDER BY material


the table is something like this:
|  material |  amount | date_in | user |


the user is irrelevant right now,

basically, i need to get the result to be like this:
material | jan 2009 | feb 2009 | .......... | dec 2010 | jan 2011 | ..........
1        |   120    |    0     | .......... |   200    |   0      | .........
2        |   0      |   300    | ..........................................
.
.
.


but as you can see, i need to define the grouping one by one, and this isn't really possible when i don't know when is the time range of the data...

i will be using C# for the implementation, and i wonder if there is anyway to generate the string based on the existing data?
Posted
Updated 22-Apr-11 6:02am
v2

If you can ease up on the requirement of how exactly the data will look, the problem is quite simple. You'll need to rewrite your query to be something like:

SQL
SELECT
  material,
  SUM([Amount]) AS [Amount],
  RIGHT(CONVERT(varchar(50), [date_in], 106), 8) AS [Date]
FROM
  [rec_stats]
GROUP BY
  material,
  YEAR([date_in]),
  MONTH([date_in])
ORDER BY
  material,
  [date_in]

You'll get data in the following format:

material | amount | date

From there, you'll just have to pivot the data in C# to a form you can use. Aside from the above, the other options you have would be:

-Dynamic SQL (i.e. SQL that writes SQL statements and executes it)
-SQL Pivot[^] - I don't consider this much different than Dynamic SQL, since you have to define what each column is, but I just wanted to state it.
 
Share this answer
 
v2
Comments
BlackJack99 22-Apr-11 11:23am    
thanks for the interest.
do you mind explaining what "RIGHT(CONVERT(varchar(50), [date_in], 106), 8) AS [Date]" is for? i am not too familiar with this statement,
wouldnt it make some "holes" for months that dont have any entry in its result?

how does it differ from this statement?
SELECT material, YEAR(date_in) AS 'Year', MONTH(date_in) AS 'Month', SUM(amount) AS "Total"
FROM rec_stats
GROUP BY material, YEAR(date_in), MONTH(date_in)
ORDER BY material, YEAR(date_in), MONTH(date_in)
Andrew Rissing 22-Apr-11 21:27pm    
Assuming I did it correctly without executing it, it should result in a date string of the format "JAN 2009". You can break it up as you have in what you provided. It just depends on what you want it to be. I tried to get it as close as to the original output you were looking for.
BlackJack99 23-Apr-11 2:38am    
yes, it did result in what you mentioned, but it doesnt return the records for months that dont have any entry, for example, in case of there is no record with the record of MAY 2010, i need it to return 0 as the amount instead of not returning anything,

or are you suggesting to do the data in another format? if that is the case, i would really appreciate it if you would explain more about it.. thank you..
Andrew Rissing 23-Apr-11 16:42pm    
The two options you have if you want that feature is to fill in the gaps in C# or create a SQL stored procedure that will provide a table of all the dates you need as a result to join up with. You would take the list of all the dates you need and LEFT JOIN it with data provided in the query above.
BlackJack99 23-Apr-11 23:06pm    
thanks... i think i get the idea now... thank you very much!
Can you try something like this:

SQL
GROUP BY dateadd(month, datediff(month, 0, date_in), 0)


That will group the stats by each month I believe. I can't test if this works, I don't have month spanning data right now.
 
Share this answer
 
v2
Comments
BlackJack99 22-Apr-11 11:35am    
Hi, mind explaining more? i dont really understand how does it work (or even where to put it),
it calculate the difference between the month of the date and 0, and then add it with 0? how does this would group each stats by the month and fill the entry-less months with 0?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900