Author |
Topic |
nirene
Yak Posting Veteran
98 Posts |
Posted - 2013-04-05 : 02:24:33
|
Hai,My dataCode,Amount,Date1,2000,01/12/20121,1500,05/12/20121,3500,08/12/20121,2340,10/12/20122,3450,02/12/20122,1550,05/12/2012I want to sum the amount on weekly basis ie amount from monday to sunday to be summed up on code basisResult ExpectedCode,Sunday_Date,Summed_Amount1,02/12/2012,20001,09/12/2012,50001,16/12/2012,23402,09/12/2012,5000Thanks in advanceNirene |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-05 : 02:41:21
|
i'm guessing your output has a slight typo for code 2 as value with 02/12 cant be merged with 05/12 as they occur in different weeks as per your definitiondeclare @t table(Code int,Amount int,[Date] datetime)insert @tvalues(1,2000,'20121201'),(1,1500,'20121205'),(1,3500,'20121208'),(1,2340,'20121210'),(2,3450,'20121202'),(2,1550,'20121205')SELECT Code,DATEADD(dd,DATEDIFF(dd,0,[Date])/7 * 7 +6,0),SUM(Amount)FROM @tGROUP BY Code,DATEADD(dd,DATEDIFF(dd,0,[Date])/7 * 7 +6,0)ORDER BY Code,DATEADD(dd,DATEDIFF(dd,0,[Date])/7 * 7 +6,0)output-----------------------------------------Code WeekDate Total-----------------------------------------1 2012-12-02 00:00:00.000 20001 2012-12-09 00:00:00.000 50001 2012-12-16 00:00:00.000 23402 2012-12-02 00:00:00.000 34502 2012-12-09 00:00:00.000 1550 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
nirene
Yak Posting Veteran
98 Posts |
Posted - 2013-04-05 : 03:09:42
|
Thanks for the code and correction of result VisakhNirene |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-04-07 : 12:04:17
|
@visakh16,In your solution, the value of 2000, which occurred on 20121201 is listed for the WeekDate of 20121202. What is your WeekDate? The start of the week or the end of the week? If it's the start of the week, then the output is incorrect.--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-09 : 00:12:37
|
quote: Originally posted by Jeff Moden @visakh16,In your solution, the value of 2000, which occurred on 20121201 is listed for the WeekDate of 20121202. What is your WeekDate? The start of the week or the end of the week? If it's the start of the week, then the output is incorrect.--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it."
see the logic i applied for week date portion. its end date for the week under consideration------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|