Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
see199
Starting Member
21 Posts |
 Posted - 2007-06-12 : 23:28:46
|
| how do i split the output to 12months and get from the database and sort according to the monthsoutput example:fuel for each car acording to month:car jan feb .... nov dec totalnissan 1000 1250 ... 1300 1100 17120 honda 2300 2430 ... 2340 2200 25410while inside the database, the fuel is based on every receipt: receipt(car, total, date)(nissan, 100, 01/01/05)(nissan, 110, 09/01/05)(nissan, 70, 13/01/05).... |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-06-12 : 23:31:30
|
[code]select car, jan = sum(case when month([date]) = 1 then total else 0 end), feb = sum(case when month([date]) = 2 then total else 0 end), mar = sum(case when month([date]) = 3 then total else 0 end), . . . from receiptgroup by car[/code]If you are using SQL 2005, you can try the PIVOT function
KH |
 |
|
|
see199
Starting Member
21 Posts |
Posted - 2007-06-13 : 00:10:43
|
| thx. how about if i wan to let the year be 2007?select only the year for 2007 for each month.thx |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-06-13 : 00:20:29
|
[code]declare @year intselect @year = 2007select car, jan = sum(case when month([date]) = 1 then total else 0 end), feb = sum(case when month([date]) = 2 then total else 0 end), mar = sum(case when month([date]) = 3 then total else 0 end), . . . from receiptwhere [date] >= dateadd(year, @year - 1900, 0)and [date] <= dateadd(year, @year - 1900 + 1, -1)group by car[/code]
KH |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
calling month from database