Billing dates vs Billing intervals

[mttorpy]

I am trying to put together an equation that will specify the correct interval based on ‘getdate()’(today’s date). For any give date you should have a maximum of two records returned one for EOM billing and one for MID(mid month) billing. Here is and example of the tables I am using:

Startdate EndDate Interval Label
5/15/2004 6/14/2004 45298 MIDJune2004
6/1/2004 6/30/2004 45467 EOMJune2004
6/15/2004 7/14/2004 45299 MIDJuly2004
7/1/2004 7/31/2004 45468 EOMJuly2004
7/15/2004 8/14/2004 45300 MIDAug2004
8/1/2004 8/31/2004 45469 EOMAug2004
8/15/2004 9/14/2004 45301 MIDSept2004
9/1/2004 9/30/2004 45470 EOMSep2004

For example on todays date the intervals chosen should be: 45299,MIDJuly2004 and 45468,EOMJuly2004.

This is the Equation I am using but it does not work correctly when you insert other possible dates.

select Dateinterval, Label, Startdate, Enddate
from Intervals
where
getdate() >= enddate
AND
getdate() between startdate and enddate + 31

Any ideas?

 

[MatthewPeters]

Would something like this work?

select Dateinterval, Label, Startdate, Enddate
from Intervals
where DATEPART(month, GETDATE()) = DATEPART(month, endDate)

Matt

 

[mttorpy]

Above solution pulls in 4 records all of which are in an end date of Aug. for today’s date we would be looking at two records from July enddate. Thanks for a new look at it though.

 

[MatthewPeters]

What about this one?

SELECT Dateinterval, Label, Startdate, Enddate
FROM Intervals
where DATEPART(month, dateadd(m, -1, GETDATE())) = DATEPART(month, endDate)

Matt

 

[mttorpy]

Not working...pulling in 6 records(two records for every year) and when I modify it to contain say '08/16/2004' it still points to July enddate