Better way to get months contained in a period for querying sales tables

Question

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

Answer 1

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 |

Answer 2

I ended up creating a function:

create function [dbo].[fn_UTILITIES_Sales_Data_Periods]
(   
    @year int,
    @quarter int
)
RETURNS TABLE 
AS 
RETURN 
(
    select 
    cast(@year as nvarchar(4)) + right(('0' + cast(datepart(mm, datefromparts(@year, (@quarter * 3) - 2, 1)) as nvarchar(4))), 2) as period

    union all

    select 
        cast(@year as nvarchar(4)) + right(('0' + cast(datepart(mm, dateadd(mm, 1, datefromparts(@year, (@quarter * 3) - 2, 1))) as nvarchar(4))), 2)

    union all

    select 
        cast(@year as nvarchar(4)) + right(('0' + cast(datepart(mm, dateadd(mm, 2, datefromparts(@year, (@quarter * 3) - 2, 1))) as nvarchar(4))), 2)
)

So

@year = 2017 @quarter = 3

Yields

period
------
201707
201708
201709

Answer 3

Nate...you need to build a sproc the takes a date-range, for example: @startdate and @enddate with both being datetime.

Normalize the rows into date or datetime data-types, even if you have to make the assumption that the day part is 01

Once you have normalized your datetime value(s), you can then use where datetime_field between @startdate and @enddate

I would suggest not trying to evaluate the quarter date range in sql, that process is more procedural-based and not very friendly with set-based operations.