Sql-server – Better way to get months contained in a period for querying sales tables

sql servert-sql

I'm trying to automate some sales processing that is currently largely manual. I get the sales by month expressed as
201701, 201702 etc.

Part of what I'm trying to accomplish is better re-use of aggregation queries for historical data, currently there are just a lot of queries saved in the cloud. I need to able to pass in a year and quarter, and get a pattern to match against the sales data. I'd like to accomplish this using SQL's built in datetime functions rather than a case statement and casts.

What's a better way to do the following, that doesn't rely on string manipulation?

-- the @year and @quarter will be passed into a SP    
declare @year int = 2017
declare @quarter int = 2

declare @name_timePeriod nvarchar(20) = 'Qtr'+ ' ' + cast(@quarter as nvarchar(2)) + ' ' + cast(@year as nvarchar(4))

declare @contained_periods nvarchar(max) = 

case @quarter
    when 1 then cast(@year as nvarchar(4)) + '0[1-3]'
    when 2 then cast(@year as nvarchar(4)) + '0[4-6]'
    when 3 then cast(@year as nvarchar(4)) + '0[7-9]'
    when 4 then cast(@year as nvarchar(4)) + '1[0-2]'
end

Best Answer

I noticed that you have already decided on your solution, but wanted to offer another suggestion which utilizes Aaron Bertrand's Date Dimension table.

There are just so many situations where a Date Dimension table comes in handy.

Assuming you had created and loaded his Date Dimension table, it would be a simple as: 

DECLARE @year INT = 2017
    ,@quarter INT = 3

SELECT DISTINCT convert(VARCHAR(4), [Year]) + right(REPLICATE('0', 2) + convert(VARCHAR(2), [Month]), 2) as Period
FROM DateDimension
WHERE [Year] = @year
    AND [Quarter] = @quarter

--OR

SELECT DISTINCT substring(MMYYYY,3,4) + substring(MMYYYY,1,2) as Period
FROM DateDimension
WHERE [Year] = @year
    AND [Quarter] = @quarter

giving

| Period |
|--------|
| 201707 |
| 201708 |
| 201709 |
Related Question