Click here to Skip to main content
14,207,285 members
Rate this:
 
Please Sign up or sign in to 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:
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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:

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.
   
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!
Sandeep Mewara 22-Apr-11 12:16pm
   
My 5!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Can you try something like this:

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.
   
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)



Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190612.1 | Last Updated 22 Apr 2011
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100