Grabbing the previous 4 quarters

Question

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
                    end
            end 
    end

select @quarter as 'Quarter', @year as 'Year', @prev_quarter_1 as 'pq1', @prev_quarter_year_1 as 'pqy1', @prev_quarter_2 as 'pq2', @prev_quarter_year_2 as 'pqy2', @prev_quarter_3 as 'pq3', @prev_quarter_year_3 as 'pqy3'

Answer 1

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 <= @referenceDate
order by
     q.EndDate desc

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<T> 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 <= referenceDate))
                      .OrderByDescending(q => q.EndDate)
                      //.Skip(1) // skip current quarter?
                      .Take(4);

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

Answer 2

You could write a function to return the current quarter, and then just subtract 3 months from the date repeatedly to get the previous quarters:

public static int GetQuarter(DateTime date)
    {
        int[] quarters = new int[] { 4,4,4,1,1,1,2,2,2,3,3,3 };
        return quarters[date.Month-1];
    }

** Note, of course update this code to reflect which quarters your months apply to. Function from (https://stackoverflow.com/questions/8698303/how-do-i-discover-the-quarter-of-a-given-date-in-c-net-3-5-or-4-0)

But then it becomes trivial to get the current and previous quarters:

DateTime date = DateTime.Now;
var currentQuarter = GetQuarter(date);
date = date.AddMonths(-3);
var prevQuarter1 = GetQuarter(date);
date = date.AddMonths(-3);
var prevQuarter2 = GetQuarter(date);
date = date.AddMonths(-3);
var prevQuarter3 = GetQuarter(date);

Hopefully this is a good solution to cleaning up your code some. You can test it out here on csharppad: http://csharppad.com/gist/c0bea5365ec515a077db

Since you have access to the date object, you will have the year already too. If you need both the quarter and the year as part of the GetQuarter function, you can return a custom object that stores both values, i.e.:

public class Quarter
{
   public int Quarter { get; set; }
   public int Year { get; set;}
}

public static Quarter GetQuarter(DateTime date)
        {
            int[] quarters = new int[] { 4,4,4,1,1,1,2,2,2,3,3,3 };
            Quarter quarter = new Quarter();
            quarter.Quarter = quarters[date.Month-1];
            quarter.Year = date.Year;
            return quarter;
        }

Answer 3

Not exactly slimmed down but I do have some features added. I don't hard code getting the 4 previous quarters. Rather I have a method that allows you to get as many as you desire.

public class YearQuarter
{
    public int Year { get; private set; }
    public int Quarter { get; private set; }

    private YearQuarter() { }

    private YearQuarter(int year, int quarter) 
    {
        this.Year = year;
        this.Quarter = quarter;
    }

    public static YearQuarter FromDate(DateTime date)
    {
        int[] quarters = new int[] { 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4 };
        var q = new YearQuarter();
        q.Quarter = quarters[date.Month - 1];
        q.Year = date.Year;
        return q;
    }

    public override string ToString()
    {
        return string.Format("{0}-{1}Q", Year, Quarter);
    }

    public DateTime BegOfQuarter
    {
        get { return new DateTime(Year, (3 * Quarter) - 2, 1); }
    }

    // The beginning of the next quarter is the exclusive end for this quarter.
    // If you want the inclusive end of this quarter, this is one way depending upon your desired precision of seconds.
    public DateTime EndOfQuarter
    {
        get 
        {
            return NextQuarter.BegOfQuarter.AddMilliseconds(-1);
        }
    }

    public YearQuarter NextQuarter
    {
        get
        {
            var y = Year;
            var q = Quarter + 1;
            if (q == 5)
            {
                q = 1;
                y++;
            }
            return new YearQuarter(y, q);
        }
    }

    public YearQuarter PreviousQuarter
    {
        get
        {
            var y = Year;
            var q = Quarter - 1;
            if (q == 0)
            {
                q = 4;
                y--;
            }
            return new YearQuarter(y, q);
        }
    }

    public IList<YearQuarter> GetPreviousQuarters(int count)
    {
        var list = new List<YearQuarter>();

        var quarter = this;

        for (var i = 0; i < count; i++)
        {
            quarter = quarter.PreviousQuarter;
            list.Add(quarter);
        }

        list.Reverse();

        return list;
    }

    public IList<YearQuarter> GetNextQuarters(int count)
    {
        var list = new List<YearQuarter>();

        var quarter = this;

        for (var i = 0; i < count; i++)
        {
            quarter = quarter.NextQuarter;
            list.Add(quarter);
        }

        return list;
    }

}

I intentionally only want the YearQuarter instance set publicly using the .FromDate method. To get the 4 previous quarters, you could use:

var quarters = YearQuarter.FromDate(DateTime.Now).GetPreviousQuarters(4);