Retrieving historic records based on month

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 2000 Forums

Transact-SQL (2000)

Retrieving historic records based on month

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-31 : 08:21:31

Jeff writes "I am generating a cost per day based on bills for particular months. Typically, 2 bills will fall into each month. Below I am looking for Bills that occured 1 year ago in feb.

@month = 12
@dte = 02/28/2002

I use a datepart function to retrieve the 2 bills associated with the month of feb that occured -12 months ago:
DATEDIFF(MONTH, @dte,
ISNULL (SH.PERIOD_END_DATE,AH.PERIOD_END_DATE)) = -@month

DATEDIFF(MONTH, @dte,
ISNULL (SH.PERIOD_START_DATE,AH.PERIOD_START_DATE)) = - @month

Bills that will be returned are:
Bill 1: 1/15/2001 - 2/15/2001
Bill 2: 2/15/2001 - 3/15/2001

This works fine 90% of the time. However, this does not return bills that include the entire month of feb, ie (01/31/2001 - 03/01/2001)

Is there a better way to retrieve the bills, or any way to incorporate some logic that will return those bills which do not start/end in my desired month?"

izaltsman
A custom title

1139 Posts

Posted - 2002-07-31 : 11:46:27

I am guessing (haven't tested) something like this might work:


declare @monthstart datetime
declare @monthend datetime

SET @monthstart=DATEADD (mm, (-1) * @month, @dte)
SET @monthstart=CONVERT(datetime, CAST(YEAR(@monthstart) as varchar)+RIGHT('0'+CAST(MONTH(@monthstart) as varchar), 2) + '01', 112)
SET @monthend = DATEADD (s, -1, DATEADD(mm, 1, @monthstart))


SELECT 
...
WHERE 
ISNULL (SH.PERIOD_START_DATE,AH.PERIOD_START_DATE) <= @monthend 
AND 
ISNULL (SH.PERIOD_END_DATE,AH.PERIOD_END_DATE) >= @monthstart

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources