Continuous SQL YTD

[Mozz]

Hi guys, i'm looking for a script that will run YTD from 1 July. that is maintenance free... So i can declare it in a report, and never touch it again.

so lets say its June 2007, and my report includes everything from 1 July 2006.
What i want the Report to do is see that when 1 July 2007 comes, that i only need 1 July 2007 - 31 July 2007...

I've made a script that i can use startdate 12 months back from the previous month end. but thats as close as i came :-S

Anyone know what i need to do?
Thanks

[viceroy]

Hi guys, i'm looking for a script that will run YTD from 1 July. that is maintenance free... So i can declare it in a report, and never touch it again.

so lets say its June 2007, and my report includes everything from 1 July 2006.
What i want the Report to do is see that when 1 July 2007 comes, that i only need 1 July 2007 - 31 July 2007...

I've made a script that i can use startdate 12 months back from the previous month end. but thats as close as i came :-S

Anyone know what i need to do?
Thanks

Huh...

How on the 1st July2007 , can you run a report from 1 July 2007 - 31 July 2007?

anyway, I think this is what you're looking for

Code: Select all

declare @startdate datetime,
	@enddate datetime
set	@startdate = 
				case 
				when month(getdate()) >= 07 
				then convert(datetime,convert(varchar(4),year(getdate())) + '-07-01 00:00.000') 
				else convert(datetime,convert(varchar(4),year(getdate())-1) + '-07-01 00:00.000')
				end
set	@enddate = 
				dateadd(day,day(cast(convert(varchar(12),getdate()) as datetime))*-1,dateadd(month,1,cast(convert(varchar(12),getdate()) as datetime)))
select @startdate,@enddate

[endev8003]

I think he means from july 2006 to june 2007, or something like that.

@Mozz
If I understand correctly, you need the current date.
You can use the GetDate() function to get the current date and time of the server.

[viceroy]

something like that is not very clear when it comes to reporting...trust me, I know.

yes getdate() will help him, but your post is not useful in the least.

[Mozz]

thanks for the replies guys...

in lamens...

I need a script for when year end is reached, it starts the new year with out supplying fixed dates...


yea, i used,
'CONVERT(char(8), dateadd(dd, - datepart(dd, @dt), @dt), 112)'
to get my end date...

now i just need something to give me the startdate. but when my end date becomes July 2007, my start date needs to become that too and stay July 07, until July 2008?

[viceroy]

see my code snippet...that will help you

[Mozz]

thanks, will give it a test drive!

[EDIT]

Soz Vice one more thing, if i want to drop the End date to previous month end?

Thanks a million! this is perfect!

[viceroy]

How do you mean previous month end?

The reason I did the month end like I did is for that first month in the ytd series..

ie 1 july 2007 - 31 july 2007

won;t work is you want prev month cos then you'll have 1 july 2007 - 30 june 2007

unless you want to only report on previous months in which case you just mod the code slightly to do it

[Mozz]

ok i ran it in Query Analyzer, and end date comes back as 2008/03/31
I'd like it to be 2008/02/29?

I've been using SQL for like 2 months now, still getting to Cast :S that's what's confusing me a bit...

[viceroy]

you studying sql?

am I doing your homework for you?

[endev8003]

yes getdate() will help him, but your post is not useful in the least.

Oops. I thought mozz already knew how to get the start date from a specified date and just needed the function. My mistake.

Try this

Code: Select all

declare @startdate datetime
declare @enddate datetime
declare @date datetime

set @date = getdate()
if month(@date) <= 7
	set @startdate = @date - (datediff(dy, '1 Jan ' + cast(year(@date) as char), @date)) - 184
else
	set @startdate = @date - (datediff(dy, '1 Jul ' + cast(year(@date) as char), @date))
set @enddate = @date - day(@date)

The 184 is the number of days from from 1st of july to the 1st of january

*EDIT* should be '<= 7' in the code

[Mozz]

haha, i'm a Network Engineer, that got dumped with the task of DB admin...

Call it Crash course 101... never read so many books in my life!

I'm thinking of turning the good ole MCSE into a MCDBA or w.e the new one is these days, It Pro or something, guess i should...



Thanks again for your help peeps...

[viceroy]

Code: Select all

declare @startdate datetime, 
   @enddate datetime 
set   @startdate = 
            case 
            when month(getdate()) >= 08 
            then convert(datetime,convert(varchar(4),year(getdate())) + '-07-01 00:00.000') 
            else convert(datetime,convert(varchar(4),year(getdate())-0) + '-07-01 00:00.000') 
            end 
set   @enddate = 
            dateadd(day,day(cast(convert(varchar(12),getdate()) as datetime))*-1,dateadd(month,1,cast(convert(varchar(12),getdate()) as datetime))) 
select @startdate,@enddate

LOL...Isn;t that always the way.

Anyway, here is the updated code to work a month in arrears