4

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
2

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 |
  • We have another client that uses non-standard quarters. We're a small shop and there were some issues when people realized that the date dimension table for that client hadn't been maintained, so it was recommended by a senior developer that I use a function, as it won't require maintenance. – Nate Anderson Sep 22 '17 at 15:16
  • If you go with a function, then you now need to maintain two versions of the function, depending on where the system is deployed. I think that most would agree it's better to maintain two versions of lookup data rather than two versions of code. – mathewb Sep 22 '17 at 19:39
1

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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.