SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nikoz
Yak Posting Veteran

61 Posts

Posted - 04/30/2013 :  02:54:39  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 04/30/2013 :  04:14:01  Show Profile  Reply with Quote
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

61 Posts

Posted - 05/06/2013 :  05:55:37  Show Profile  Reply with Quote
not working...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/06/2013 :  06:18:23  Show Profile  Reply with Quote
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

61 Posts

Posted - 05/06/2013 :  09:41:01  Show Profile  Reply with Quote
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

61 Posts

Posted - 05/06/2013 :  10:49:51  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/07/2013 :  00:35:10  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000