Friday, August 29, 2008

Automatic SQL Run Date Range Based Off Of System Time

At work I was trying to come up with sql that could be run to determine the correct range of records to processes. We wanted to bill twice a month, and only grab the transactions that were in the previous half of the month (or prior month) I apologize upfront for having a date stored as a string, but I didn't create this, I just have to work with it. This is what I came up with:



-- This will determine the Date Time String YYYYMMDD to use based off of the SQL system date.

declare @runDate datetime -- Create a variable for the date, so we don't run into an issue starting to processes at 11:59:99, and getting two different months

declare @currentMonthYYYYMM as VarChar(6)

declare @lastMonthYYYYMM as VarChar(6)

Select @runDate = getDate()


If Month(@runDate) = 1

Begin

Select @currentMonthYYYYMM = CAST(year(@runDate) as Varchar(4)) + '01'

Select @lastMonthYYYYMM = CAST(year(@runDate) - 1 as Varchar(4)) + '12'

End


Else If Month(@runDate) < 10

Begin

Select @currentMonthYYYYMM = CAST(year(@runDate) as Varchar(4)) + '0' + CAST(Month(@runDate) as Varchar(1))

Select @lastMonthYYYYMM = CAST(year(@runDate) as Varchar(4)) + '0' + CAST(Month(@runDate)- 1 as Varchar(1))

End


Else If Month(@runDate) = 10

Begin

Select @currentMonthYYYYMM = CAST(year(@runDate) as Varchar(4)) + CAST(Month(@runDate) as Varchar(2))

Select @lastMonthYYYYMM = CAST(year(@runDate) as Varchar(4)) + '09'

End


Else

Begin

Select @currentMonthYYYYMM = CAST(year(@runDate) as Varchar(4)) + CAST(Month(@runDate) as Varchar(2))

Select @lastMonthYYYYMM = CAST(year(@runDate) as Varchar(4)) + CAST(Month(@runDate) - 1 as Varchar(2))

End



If day(@runDate) < 16 -- Then Run the Previous month, 16-End of month Since this is all string dates, YYYY0231 is valid :)

begin

SELECT *

FROM transactions

WHERE DateToBill Between @lastMonthYYYYMM + '16' AND @lastMonthYYYYMM + '31'

END


Else

BEGIN

SELECT *

FROM transaction

WHERE DateToBill Between @currentMonthYYYYMM + '01' AND @currentMonthYYYYMM + '16'

End







Can you spot any errors or think of a more elegant way? Please let me know.