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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 query help

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 ts
on p.PREJEMNIK = ts.acSubject
where p.POSLDOG in ('3000','3010','3600','3X10','3X20','3X30','3X40')
and p.PREJEMNIK = 'neo-displays doo'
group by
p.PREJEMNIK,
p.DATUMRAC
and my resalt is like this
http://www.freeimagehosting.net/4zr3n
why 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 it

like

select p.prejemnik,

SUM (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 p.ZAPLACILO else 0 end) 'Fak. pre 6 meseci',
...
from PROMET p left join THE_SETSUBJ ts
on p.PREJEMNIK = ts.acSubject
where p.POSLDOG in ('3000','3010','3600','3X10','3X20','3X30','3X40')
and p.PREJEMNIK = 'neo-displays doo'
group by
p.PREJEMNIK


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-05-06 : 05:55:37
not working...
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 this

PREJEMNIK_________Faktura pre. 6 meseci________Faktura pre. 5 meseci______Faktura pre. 4 meseca___Faktura pre. 3 meseca_______Faktura pre. 2 meseca_____Faktura prosli mesec
Name of the firm_____________SUM________________________SUM____________________SUM_________________SUM__________________SUM_____________________SUM
Go to Top of Page

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...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-07 : 00:35:10
see how to post proper information for the question to help us understand your scenario

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -