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 |
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-04-30 : 02:54:39
|
select p.prejemnik, case when p.DATUMRAC between dbo.fPA_FirstDayOfTheMonth(dateAdd(month, -6, dbo.fPA_RemovingTime(getDate()))) and dbo.fPA_LastDayOfTheMonth(dateAdd(month, -6, dbo.fPA_RemovingTime(getDate()))) then SUM (p.ZAPLACILO) else 0 end 'Fak. pre 6 meseci', case when p.DATUMRAC between dbo.fPA_FirstDayOfTheMonth(dateAdd(month, -5, dbo.fPA_RemovingTime(getDate()))) and dbo.fPA_LastDayOfTheMonth(dateAdd(month, -5, dbo.fPA_RemovingTime(getDate()))) then SUM (p.ZAPLACILO) else 0 end 'Fak. pre 5 meseci', case when p.DATUMRAC between dbo.fPA_FirstDayOfTheMonth(dateAdd(month, -4, dbo.fPA_RemovingTime(getDate()))) and dbo.fPA_LastDayOfTheMonth(dateAdd(month, -4, dbo.fPA_RemovingTime(getDate()))) then SUM (p.ZAPLACILO) else 0 end 'Fak. pre 4 meseca', case when p.DATUMRAC between dbo.fPA_FirstDayOfTheMonth(dateAdd(month, -3, dbo.fPA_RemovingTime(getDate()))) and dbo.fPA_LastDayOfTheMonth(dateAdd(month, -3, dbo.fPA_RemovingTime(getDate()))) then SUM (p.ZAPLACILO) else 0 end 'Fak. pre 3 meseca', case when p.DATUMRAC between dbo.fPA_FirstDayOfTheMonth(dateAdd(month, -2, dbo.fPA_RemovingTime(getDate()))) and dbo.fPA_LastDayOfTheMonth(dateAdd(month, -2, dbo.fPA_RemovingTime(getDate()))) then SUM (p.ZAPLACILO) else 0 end 'Fak. pre 2 meseca', case when p.DATUMRAC between dbo.fPA_FirstDayOfTheMonth(dateAdd(month, -1, dbo.fPA_RemovingTime(getDate()))) and dbo.fPA_LastDayOfTheMonth(dateAdd(month, -1, dbo.fPA_RemovingTime(getDate()))) then SUM (p.ZAPLACILO) else 0 end 'Fak. prošli mesec', case when p.DATUMRAC between dbo.fPA_FirstDayOfTheMonth(dateAdd(month, 0, dbo.fPA_RemovingTime(getDate()))) and dbo.fPA_LastDayOfTheMonth(dateAdd(month, 0, dbo.fPA_RemovingTime(getDate()))) then SUM (p.ZAPLACILO) else 0 end 'Fak. tekuci mesec' from PROMET p left join THE_SETSUBJ tson p.PREJEMNIK = ts.acSubjectwhere p.POSLDOG in ('3000','3010','3600','3X10','3X20','3X30','3X40') and p.PREJEMNIK = 'neo-displays doo'group by p.PREJEMNIK, p.DATUMRACand my resalt is like thishttp://www.freeimagehosting.net/4zr3nwhy query doesn't calculate SUM? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-30 : 04:14:01
|
My guess is SUM should come outside with CASE WHEN part inside itlikeselect p.prejemnik,SUM (case when p.DATUMRAC between dbo.fPA_FirstDayOfTheMonth(dateAdd(month, -6, dbo.fPA_RemovingTime(getDate()))) anddbo.fPA_LastDayOfTheMonth(dateAdd(month, -6, dbo.fPA_RemovingTime(getDate()))) then p.ZAPLACILO else 0 end) 'Fak. pre 6 meseci',...from PROMET p left join THE_SETSUBJ tson p.PREJEMNIK = ts.acSubjectwhere p.POSLDOG in ('3000','3010','3600','3X10','3X20','3X30','3X40') and p.PREJEMNIK = 'neo-displays doo'group by p.PREJEMNIK ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-05-06 : 05:55:37
|
not working... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-06 : 06:18:23
|
please illustrate with a data sample what exactly you're expecting as output?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-05-06 : 09:41:01
|
i want in 1 row for evry firm to calculate sum somthing like thisPREJEMNIK_________Faktura pre. 6 meseci________Faktura pre. 5 meseci______Faktura pre. 4 meseca___Faktura pre. 3 meseca_______Faktura pre. 2 meseca_____Faktura prosli mesecName of the firm_____________SUM________________________SUM____________________SUM_________________SUM__________________SUM_____________________SUM |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-05-06 : 10:49:51
|
I want to calculate sum of some column by the function FrstDayOfMount and LastDayOfMonth... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|