-- 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.