calendar year to 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

Transact-SQL (2005)

calendar year to date query

Author

Topic

kbromwich
Starting Member

5 Posts

Posted - 2008-09-18 : 21:19:06

I am having a little bit of a mental block on this on :(

my problem is this. the user will pass in a date range. the sql will need to take this date range and for each year in the date range return a sum or avg of some numbers for that calendar year. that part is easy.
what i need to do though is for each . if today is 10 september for each of the years i need the query to bring back values up to that point as well.

best demonstrated with an example
date range is from 2000 - 2008. today is september 2008
the output would be
january - september 2000 [values]
january - september 2001 [values]
january - september 2002 [values]
january - september 2003 [values]
january - september 2004 [values]
january - september 2005 [values]
january - september 2006 [values]
january - september 2007 [values]
january - september 2008 [values]

i know that i could probably do some form of cursor or while loop but i was hoping that i maybe able to do this with just one query.
there will be a fiscal year version of this as well which i would imagine will be very similar and i should be able to work that out afterwards

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-18 : 22:57:53

use this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE

hey

kbromwich
Starting Member

5 Posts

Posted - 2008-09-19 : 01:02:24

thanks for that

having looked through i am not sure how i would use this to come up with my sql. i will only be passing in say 2 params that are the years in question ie 2000-2008.

i dont see how this will be able to tell the query that i need only the data in each year that goes from 1 jan to for example 13 sep.

maybe i have missed something which is quite possible

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-19 : 01:18:01

Can you explain more on your expected output ?

With input 2000 - 2008 You want it to return all the dates from 2000-01-01 till 2008-09-13 ?

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

kbromwich
Starting Member

5 Posts

Posted - 2008-09-24 : 04:23:39

the output we require is calendar year to date. easy enough for the current year. the report they want though they will specify a date range which will be a range of years.
for each year it will bring back the corresponding year to date for that year.
eg if today is the 15 september 2008. the user wants to bring back data for the years 2005,6,7,8
the data returned would be basically
1/jan/2005 - 15/sep/2005 - values of data
1/jan/2006 - 15/sep/2006 - values of data
1/jan/2007 - 15/sep/2007 value of data
1/jan/2008 - 15/sep/2008 values of data

this will allow them to be able to do comparissons on how things are tracking with say for example sales.

i was hoping to do this in one sql statement but i think i may need to use a cursor or something similar.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 04:36:51

Something like this

SELECT YEAR(datefield),
SUM(CASE MONTH(datefield) <=MONTH(GETDATE()) AND DAY(datefield) <=DAY(GETDATE()) THEN Value ELSE 0 END) AS Value1,...
FROM YourTable
WHERE YEAR(datefield) >=@Start AND YEAR(datefield)<=@End
GROUP BY YEAR(datefield)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 05:02:15

[code]DECLARE @yearFrom SMALLINT,
@yearTo SMALLINT

SELECT @yearFrom = 2000,
@YearTo = 2008

SELECT 'January - ' + DATENAME(MONTH, GETDATE()) + ' ' + CAST(x.Number + @yearFrom AS CHAR(4))
FROM (
SELECT Number
FROM master..spt_values
WHERE Type = 'P'
AND Number <= @YearTo - @YearFrom
) AS x
--LEFT JOIN ...[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources