I'm trying to automate some sales processing that is currently largely manual. I get the sales by month expressed as
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