Case Query - SQL Server Forums

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 2005 Forums

SQL Server Administration (2005)

Case Query

Author

Topic

Durbslaw
Starting Member

43 Posts

Posted - 2009-05-11 : 05:49:50

Hi All,
I would greatly apppreciate help with this,
Select YTDTotal = (case when DatePart(yyyy,transdate) between DatePart(yyyy-07-01,transdate) and DatePart((yyyy-06-30, dateadd yyyy,1, transdate) then
sum(amountcur)
else
'Record not Found'
end),
From LedgeTrans
.....................................................................
In a nutshell the query should return a total for the year between July and June next year.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 06:14:15

[code]
select YTDTotal = sum(case when transdate between @date1 and @date2
then amountcur
else 0
end)
from LedgeTrans
[/code]

substitute @date1 and @date2 with your required date

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-11 : 06:14:43

[code]
select sum(amountcur) From LedgeTrans
where transdate>=dateadd(month,6,dateadd(year,datediff(year,0,getdate()),0)) and
transdate<dateadd(month,6,dateadd(year,datediff(year,0,getdate())+1,0))
[/code]

Madhivanan

Failing to plan is Planning to fail

Durbslaw
Starting Member

43 Posts

Posted - 2009-05-11 : 06:55:53

Thanks for your reply,

My silly question would be, How Do I write the date such that the year is variable whilst the Month and Date are specific?

between (yyyy-07-01) and (yyyy-06-30)

quote:

Originally posted by madhivanan


select sum(amountcur) From LedgeTrans
where transdate>=dateadd(month,6,dateadd(year,datediff(year,0,getdate()),0)) and
	  transdate<dateadd(month,6,dateadd(year,datediff(year,0,getdate())+1,0))

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 07:27:34

select @year = 2009
select dateadd(year, @year - 1900, '1900-07-01')

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-11 : 08:36:27

[code]declare @year int, @month int
select @year = 2009

SELECT....
Where transdate>=dateadd(month,6,dateadd(year, @year - 1900,0)) and
transdate<dateadd(month,6,dateadd(year, @year+1 - 1900,0))
[/code]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources