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 |
|
calvinkwoo3000
Yak Posting Veteran
98 Posts |
 Posted - 2010-09-27 : 06:33:02
|
| hi Admin and all talent,I had a table as below:Id1 Year Jan Feb March .... Sept Oct Nov Dec Remark1xxx 2010 100 200 300 ...... 400 500 600 700 old Remarkxxx 2011 150 150 0 0,0,0,0.. 0 0 0 0 new RemarkData that i want is as below:Id1 Jan Feb March .... Sept Oct Nov Dec Remark1xxx 150 150 0 ............ 0 500 600 700 new RemarkMy Condition is:1)Now year=2010, Month=92)i want sum data which above this month.(year=2010 and month=Oct,Nov and Dec) and (all the month data on the following year=2011) and the remark use the latest one.Can anyone showing the select statement? thank you. |
|
|
calvinkwoo3000
Yak Posting Veteran
98 Posts |
Posted - 2010-09-27 : 23:29:28
|
| I found the solution for the query but i can't get the updated Remark1.My query without remark1 as below.SELECT Id1, SUM(CASE WHEN CAST(YEAR AS int) > 2010 THEN JAN WHEN CAST(YEAR AS int) >= 2010 AND 1 > datepart(month, getdate()) THEN JAN ELSE 0 END) AS JAN_dataSUM(CASE WHEN CAST(YEAR AS int) > 2010 THEN FEB WHEN CAST(YEAR AS int) >= 2010 AND 2 > datepart(month, getdate()) THEN FEB ELSE 0 END) AS Feb_data:::FROM tableGROUP BY id1How do i using the updated remark to replace the previous remark? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-09-30 : 12:38:42
|
| [code]...MAX(CASE WHEN CAST(YEAR AS int) > 2010 THEN Remark1 ELSE NULL END) AS Remark1...[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Select group by and .......