asked on

SQL 2008 - need help always finding certain date range

Hi,

I need SQL to run a report each month on the 25th that searches for rows that have a 'study_datetime' from the 25th of the last month to the 24th of the current month.

I was using this last month and it worked great...

select
DATEADD(month, datediff(month, 0, getdate()) -1, 24) as '24th'
, DATEADD(month, datediff(month, -1, getdate()), -7) as '25th'

Open in new window

..but this month it finds 8/25 to 9/25. I needed it to find 8/25 to 9/24, and I need this to happen each month. So, next month I will need it to find 9/25 to 10/24.

Is there anyway to make this calculate this each month without me having to make a change once a month to the job I have setup that sends an email to people with a count?

Here is the all of the code I was using in my Job.

select
(select COUNT (*)
from dbo.study
where study_custom1 like '%HOSP1%'
and study_datetime >= DATEADD(month, datediff(month, 0, getdate()) -1, 24)
and study_datetime < DATEADD(month, datediff(month, -1, getdate()), -7)) as HOSP1, 
(select COUNT (*) 
from dbo.study
where study_custom1 like '%HOSP2%'
and study_datetime >= DATEADD(month, datediff(month, 0, getdate()) -1, 24)
and study_datetime < DATEADD(month, datediff(month, -1, getdate()), -7)) as HOSP2, 
(select COUNT (*) 
from dbo.study
where study_custom1 like '%HOSP3%'
and study_datetime >= DATEADD(month, datediff(month, 0, getdate()) -1, 24)
and study_datetime < DATEADD(month, datediff(month, -1, getdate()), -7)) as HOSP3

Open in new window

thank you

Phillip Burton

select DATEFROMPARTS(year(getdate()),month(getdate())-1,25), DATEFROMPARTS(year(getdate()),month(getdate()),24)

Open in new window

doc_jay

ASKER

Phil,

is 'DATEFROMPARTS' a function of 2008 R2 or only 2012?

Phillip Burton

It appears to be only 2012.

Here's a 2008 version answer:

select dateadd(day,25,eomonth(getdate(),-2)),dateadd(day,24,eomonth(getdate(),-1))

Open in new window

doc_jay

ASKER

Phil,

Looks like 'EOMONTH' is only part of 2012 as well. :(

Phillip Burton

If at first you don't succeed, try again.

Then give up!

Or give it a third go:

select dateadd(month,-1,dateadd(day,25-day(getdate()),getdate())), dateadd(day,24-day(getdate()),getdate())

Open in new window

ASKER CERTIFIED SOLUTION

Phillip Burton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

select
sum(case when study_custom1 like '%HOSP1%' then 1 else 0 end) as HOSP1,
sum(case when study_custom1 like '%HOSP2%' then 1 else 0 end) as HOSP2,
sum(case when study_custom1 like '%HOSP3%' then 1 else 0 end) as HOSP3
from dbo.study
where
--this isn't needed if study_custom1 always includes HOSP1/2/3
(study_custom1 like '%HOSP1%' or study_custom1 like '%HOSP2%' or study_custom1 like '%HOSP3%') and
study_datetime >= DATEADD(month, datediff(month, 0, getdate()) - 1, 24) and
study_datetime < DATEADD(month, datediff(month, 0, getdate()), 24)

Note that the second comparison is "<"; therefore, the date computation should return the 25th of the current month, as < day 25 is everything through all of day 24 :-) .

PortletPaul

I'd like to stress Scott's last point immediately above. You need BOTH dates to be the 25th of their respective months

If you don't you will be ignoring 1 day of data every month

(no points pl)