I had the challenge of putting together a T-SQL statement that’s going to be used in a few stored procedures. The stored procedures will build data based on what todays date is, if today is greater than second Tuesday of a sales month, then we only want this month. However, if todays date is less than or equal to the second Tuesday of the sales month then we want to build this month and last months data.
The company has its own sales calendar and it can be used to see which days fall within the current sales month. My first steps were to populate the current date, weekid and salesmonthid. I then needed to populate a small table variable with the Tuesdays that are in this month and then rank them using ROW_NUMBER() so I know what date the second Tuesday is. Table variables are great for small data sets and can be quicker than temp tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
-- ========================================================================================================= -- Declare and set our variables, using a table variable to hold our date ranks. -- ========================================================================================================= DECLARE @Today DATETIME; DECLARE @CurrentSalesMonth INT; Declare @ThisWeek INT; DECLARE @StartDate DATETIME; DECLARE @EndDate DATETIME; DECLARE @DateRanks TABLE ( date DATETIME, rn TINYINT ); SET @Today = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE() ))) SELECT @WeekID = WeekID, @CurrentSalesMonth = SalesMonthID FROM SQLRepository.dbo.t_RepositoryCalendar WHERE Date = @Today INSERT INTO @DateRanks SELECT date, ROW_NUMBER() OVER (PARTITION BY SalesMonthID ORDER BY date) rn FROM SQLRepository.dbo.t_RepositoryCalendar WHERE SalesMonthID = @CurrentSalesMonth AND Day = 'Tuesday' |
Then I build the logic to detect what dates should be given to the startdate and enddate variables based on what the current date is. The ‘if’ funtion is perfect for doing our logic test.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- ========================================================================================================= -- Logic to set the dates, if its >= 2nd tues of the month, just do this month. Else do this month and last. -- ========================================================================================================= IF @Today > (SELECT date FROM @DateRanks WHERE rn = 2) BEGIN SELECT @StartDate = MIN(date) FROM SQLRepository.dbo.t_RepositoryCalendar WHERE SalesMonthID = @CurrentSalesMonth END ELSE BEGIN SELECT @StartDate = MIN(date) FROM SQLRepository.dbo.t_RepositoryCalendar WHERE SalesMonthID = @CurrentSalesMonth - 1 END |
Leave a Comment