SQL - get specific dates based on working week

Question

We need to determine specific dates between which data would be extracted.

For example, transactions that take place on the first of the month are posted to the accounting system on the 2nd, and transactions that take place on the last day of the month are posted to the accounting system on the 1st of the following month.

So whereas normally I could just take all records where the month is 10 for October, now I need to extract records where the date is between the 2nd of October and before the 2nd of November.

I have the below code to get the earliest date (doesn't matter if it's a weekend as in that case the records will show a posting date for the first working day after the weekend):

Declare @EarliestDate varchar(8), @SQL NVARCHAR(1000), @sDate varchar(8)

-- get data for the last 3 months
SET @EarliestDate= CAST(DATEPART(YEAR,DATEADD(m,-3, getdate())) AS VARCHAR(4)) 
    + RIGHT('00' + CAST(DATEPART(mm, DATEADD(m,-2, getdate())) AS varchar(2)), 2)+ '02' 

Now I need to check if the 1st of November is a weekend, and if it is, use the date of the next Monday.

I have started the below code but I cannot get it to work properly:

Declare @LatestDate datetime

set @LatestDate = '01/11/2014'

if datepart(dw,@LatestDate) in (1,7)
Begin
    set @LatestDate = DATEADD(d,1,@LatestDate ) 
End

Please can you show me where I am going wrong? Ideally it should work all the year round regardless of what the year is, always getting the first working day after the 1st of the month.

Answer 1

I recommend you generate the Date table from the script at Create and Populate at Date Dimension for Data Warehouse and use that to figure out when your next workday is. For my own purposes I dropped all the UK columns and Fiscal columns - though they maybe useful to you. You can also add your own Holidays too, and join directly in a query instead of creating a specific function.

Then you can just do the following:

DECLARE @LatestDate date

SET @LatestDate = '01/11/2014'

SELECT @LatestDate = MIN([Date]) FROM DimDate
WHERE [Date] >= @LatestDate
AND IsWeekday = 1 AND IsHoliday = 0

PRINT @LatestDate