Checking a Date against the 25th of the previous month! 1

[SQLchris19]

Hi, i am a virtual beginner at MSSQL and have had to learn on the job as it were, I'm currently stuck on a report. This report checks a date to see whether that date was before the 25th of the previous month. The previous guy who did the query was shocking and now ive been left with a mess and i need to work out how to fix it, ive done most of it myself, but cant quite get there.

So far its checking the previous month, but not the 25th of the previous month as required. Any help would be much appreciated.

This is the bit thats the problem

[Clients].[Stage4Date] <dateadd(mm, -1, getdate())

 

[SQLDenis]

25th or 24th?
also what do you need = > or <

today do you need all the records that are greater than 2/25/2006, equal to 2/25/2006 or less than 2/25/2006

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLchris19]

Yes today it has to be all records that are less than 2/25/2006

It looks to see if the client has paid, and if that is false then it does a check to see if the client was at stage4 (which will be different for each client), before the 25th of the previous month. If the client was at stage4 before the 25th of last month then it shows all the relevant records on the report, if its not then they arent shown. I think its a pretty complicated way to do it, but thats how its got to be done.

 

[vongrunt]

To generalize problem: what if you run that code on say, 29th this month? Same rule (25th of last month) applies? Or 25th of this month?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLchris19]

Yes, the same rule applies. Its not checking todays date, its taking the date from the Stage4date and checking to see if they were at stage4date before the 25th of last month. the actual date the report is made on doesnt affect whether the stage4date is before the 25th of last month.

 

[vongrunt]

Some food for thought:

-- today is...
select getdate()
-- Number of month since 1/1/1900 is
select datediff(mm, 0, getdate())
-- Add that number of months back to 1/1/1900 and you'll get start of current month
select dateadd(mm, datediff(mm, 0, getdate()), 0)
-- subtract one month and result is start of previous month
select dateadd(mm, datediff(mm, 0, getdate())-1, 0)
-- add 24 days and... voila
select dateadd(mm, datediff(mm, 0, getdate())-1, 24)

Once you have 25h of the prev month, the rest is simple.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLchris19]

Thanks very Much, that seems to have sorted it i think. I'll you know it it's not