Date 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)

Date query

Author

Topic

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-27 : 05:46:33

Hello people, How can I re-write this query to make it work....

select (case when datepart(mm, transdate)>=7 then
sum(amountcur)
where datename(dateadd(yyyy,1,transdate))
else
sum(amountcur)
where datename(dateadd(yyyy,-1,transdate))
end) as YTD_Total
from
ledgertrans

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-27 : 06:02:36

without any explanation what are you trying to do, i guess you want this ?


declare @fin_date datetime

select	@fin_date	= case 	when 	datepart(month, getdate()) < 7
				then 	dateadd(month, datediff(month, 0, getdate()) - 12 + 7 - datepart(month, getdate()), 0)
				else	dateadd(month, 6, dateadd(year, datediff(year, 0, getdate()), 0))
				end

select	@fin_date

select YTD_Total = sum(amountcur)
from   ledgertrans
where  transdate >= @fin_date

EDIT : in red

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

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-27 : 07:08:44

Hi Khtan,

Sorry about the missing explaination, but I am trying to total a field whose dates (for several years ) are between July and June next year.

the error I get when I run your code is:

Msg 8117, Level 16, State 1, Line 10
Operand data type datetime is invalid for sum operator.

thanks again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-27 : 07:37:06

[code]
declare @fin_date_start datetime,
@fin_date_end datetime

select @fin_date_start = dateadd(month, 6, dateadd(year, datediff(year, 0, getdate()), 0)) -- July this year
select @fin_date_end = dateadd(month, 12, @fin_date_start) -- July next year

select @fin_date_start, @fin_date_end

select YTD_Total = sum(amountcur)
from ledgertrans
where transdate >= @fin_date_start
and transdate < @fin_date_end[/code]

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

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-29 : 02:11:29

Pardon my ignorance KH, but will this query only handle half the requirements of summing for the year forward not previous.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 04:10:52

you want the current financial year YTD or previous financial year YTD ?

just change the select statement and minus 12 month will give you previous fin year


select	@fin_date_start	= dateadd(month, 6 - 12, dateadd(year, datediff(year, 0, getdate()), 0)) -- July previous year
select	@fin_date_end	= dateadd(month, 12, @fin_date_start) -- July this year

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 04:19:45

Cannot use variables in a view.
Please continue here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128417

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 04:21:14

OP never mention about view in this thread.

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

Durbslaw
Starting Member