Grabbing the previous 4 quarters

I'm curious if there is a way to slim down this code either with SQL or with C#.

```
-- VARS
declare @iagent_id int
declare @quarter int
declare @prev_quarter_1 int
declare @prev_quarter_2 int
declare @prev_quarter_3 int
declare @year int
declare @prev_quarter_year_1 int
declare @prev_quarter_year_2 int
declare @prev_quarter_year_3 int

set @quarter = datepart(QQ, getdate()) - 1
set @year = datepart(year, getdate())
set @prev_quarter_year_1 = @year
set @prev_quarter_year_2 = @year
set @prev_quarter_year_3 = @year

if @quarter = 0
begin
set @quarter = 4
set @year = @year - 1
set @prev_quarter_1 = 3
set @prev_quarter_year_1 = @year
set @prev_quarter_2 = 2
set @prev_quarter_year_2 = @year
set @prev_quarter_3 = 1
set @prev_quarter_year_3 = @year
end
else
begin
if @quarter = 3
begin
set @prev_quarter_1 = 2
set @prev_quarter_2 = 1
set @prev_quarter_3 = 4
set @prev_quarter_year_3 = @year - 1
end
else
begin
if @quarter = 2
begin
set @prev_quarter_1 = 1
set @prev_quarter_2 = 4
set @prev_quarter_year_2 = @year - 1
set @prev_quarter_3 = 3
set @prev_quarter_year_3 = @year - 1
end
else
begin
if @quarter = 1
begin
set @prev_quarter_1 = 4
set @prev_quarter_year_1 = @year - 1
set @prev_quarter_2 = 3
set @prev_quarter_year_2 = @year - 1
set @prev_quarter_3 = 2
set @prev_quarter_year_3 = @year - 1
end

Your code is very procedural, with branching and variables and things we see in code... and bad queries. SQL likes sets/tables.

You're lucky your fiscal quarters line up with the "normal calendar" - every company I worked for had different periods for their fiscal calendar.

Since I worked with a data warehouse, one of the first things I do when I come on board in a company where I need to work with time data, is look at their databases to see if they have a table somewhere that contains their fiscal calendars. And if they don't have one, I simply create it:

create table dbo.FiscalCalendars (
_Id int not null identity(1,1)
,_DateInserted datetime not null
,_DateUpdated datetime null
,CalendarDate date not null
,CalendarDayOfWeek int not null
,CalendarDayOfMonth int not null
,CalendarDayOfYear int not null
,CalendarWeekOfYear int not null
,CalendarMonthOfYear int not null
,CalendarYear int not null
,FiscalDayOfWeek int not null
,FiscalDayOfMonth int not null
,FiscalDayOfQuarter int not null
,FiscalDayOfYear int not null
,FiscalWeekOfMonth int not null
,FiscalWeekOfQuarter int not null
,FiscalWeekOfYear int not null
,FiscalMonthOfQuarter int not null
,FiscalMonthOfYear int not null
,FiscalQuarterOfYear int not null
,FiscalYear int not null
,constraint PK_FiscalCalendars primary key clustered (_Id asc)
,constraint NK_FiscalCalendars unique (CalendarDate)
);


With a table that stores everything you've always wanted to know about every date you ever need to know anything about, selecting the last 4 quarters becomes... simple:

declare @referenceDate as date;
set @referenceDate = getdate();

with quarters as (
select
t.FiscalYear
,t.FiscalQuarterOfYear
,min(t.CalendarDate) StartDate
,max(t.CalendarDate) EndDate
from dbo.FiscalCalendars t
group by
t.FiscalYear
,t.FiscalQuarterOfYear
)
select top 4
q.FiscalYear
,q.FiscalQuarterOfYear
,q.StartDate
,q.EndDate
from quarters q
where q.StartDate

The hardest part is ...populating that time table, not querying it.

Now, to make the above return a column for each quarter, you'll need to pivot it. But if you're working (and thinking) in sets (LINQ works off IEnumerable after all), you won't need to do this.

Speaking of LINQ... assuming you have the time table mapped to FiscalCalendar entities, you could query it like this:

var referenceDate = DateTime.Today;
var quarters = context.FiscalCalendars
                      .GroupBy(t => new { t.FiscalYear, t.FiscalQuarterOfYear })
                      .Select(g => new { 
                                         FiscalYear = g.Key.FiscalYear,   
                                         FiscalQuarterOfYear = g.Key.FiscalQuarterOfYear, 
                                         StartDate = g.Min(q => q.CalendarDate),
                                         EndDate = g.Max(q => q.CalendarDate)
                                        })
                      .Where(q => q.StartDate  q.EndDate)
                      //.Skip(1) // skip current quarter?
                      .Take(4);

That would give you an IQueryable where T is an anonymous type with int FiscalYear, int FiscalQuarterOfYear, DateTime StartDate and DateTime EndDate` properties.

StackExchange Code Review Q#93332, answer score: 10