Ordering (ranking) quarters for a date range in SQL Server

Question

I very rarely use while loops in T-SQL reporting queries. In fact, this might be the only while look in our entire reporting system. Because of this, I have decided to check if anyone has a better way to solve the following problem.

You have a report and you must query two years of transactions. One of the columns is a calendar quarter rank. I'll explain what this is in one minute.

Every calendar date has a financial quarter. I believe this is universal but from my United States perspective January 1 through March 30 is Quarter 1 of the year and April 1 through June 30 is Quarter 2 of the year and so on.

What I want to do is take all of the quarters from a given date range and rank them numerically. This means if my reports start on April 1, 2012 and run through March 30, 2014 (eight quarters) a date that falls between April 1, 2012 and June 30, 2012 would be ranked as quarter 1 (for my report).

Dates that fall on July 1, 2012, would be ranked as quarter 2.

And a transaction date of March 30, 2014, would fall into quarter 8 for my report.

To solve for this I have used a while loop to spin through the dates in the report range and assign numeric quarter ranks when ever the quarter is incremented by one (Date goes from June 30, 2012 to July 1, 2012 for example).

Here is the code...

    declare @tv_transactions table(recnum int identity(1,1) primary key, item varchar(50), amt money, txndate datetime)

insert into @tv_transactions(item, amt, txndate)values('iPod Shuffle', 130, '2012-04-03')
insert into @tv_transactions(item, amt, txndate)values('xBox One S', 299, '2012-05-12')
insert into @tv_transactions(item, amt, txndate)values('iMac Pro', 4999, '2012-07-23')
insert into @tv_transactions(item, amt, txndate)values('Flash Stick', 4.50, '2012-08-01')
insert into @tv_transactions(item, amt, txndate)values('Pencils', 3.67, '2012-10-23')
insert into @tv_transactions(item, amt, txndate)values('Markers', 4.99, '2012-11-20')
insert into @tv_transactions(item, amt, txndate)values('Windex', 1.99, '2013-01-23')
insert into @tv_transactions(item, amt, txndate)values('echo dot', 49, '2013-02-14')
insert into @tv_transactions(item, amt, txndate)values('Eggs', 3.99, '2013-04-12')
insert into @tv_transactions(item, amt, txndate)values('Strawberries', 2.99, '2013-06-23')
insert into @tv_transactions(item, amt, txndate)values('Portable Generator', 880, '2013-08-12')
insert into @tv_transactions(item, amt, txndate)values('Mattress Pad', 103.99, '2013-09-03')
insert into @tv_transactions(item, amt, txndate)values('Power Drill', 49.99, '2013-10-11')
insert into @tv_transactions(item, amt, txndate)values('GT Road GT', 14.77, '2013-11-20')
insert into @tv_transactions(item, amt, txndate)values('QFit Bluetoot Speaker', 25.99, '2014-01-31')
insert into @tv_transactions(item, amt, txndate)values('Toilet Night Light', 8.99, '2014-02-23')
insert into @tv_transactions(item, amt, txndate)values('BS-MALL Makeup Brushes', 6.29, '2014-03-14')



declare    @startdate              datetime
         , @enddate                datetime
         , @datecount              datetime
         , @year                   int
         , @quarter                int
         , @quartercount           int  

declare @tv_quarters table(quarternum int primary key, year int, quarter int)

/****************************************************************************
 * Inatialize Variables                                                     *
 ****************************************************************************/
select     @startdate               =  '2012-04-01'
        ,  @enddate                 =  '2014-03-30'
        ,  @datecount               =  null

select     @datecount               =  @startdate

select     @year                    =  year(@datecount)
        ,  @quarter                 =  datepart(quarter, @datecount)          
        ,  @quartercount            =  1


insert into @tv_quarters(quarternum, year, quarter) values(@quartercount, @year, @quarter)
select @datecount = dateadd(day, 1, @datecount)

while(@datecount <= @enddate)
begin
   select    @year     =  null
          ,  @quarter  =  null

   select    @year     = year
          ,  @quarter  = quarter
   from @tv_quarters
   where quarternum = @quartercount

   if(year(@datecount) <> @year or datepart(quarter, @datecount) <> @quarter)
   Begin
      select @quartercount = @quartercount + 1
      insert into @tv_quarters(quarternum, year, quarter) 
      select @quartercount, year(@datecount), datepart(quarter, @datecount)
    End 
    select @datecount = dateadd(day, 1, @datecount)   
end

select t.item, t.amt, t.txndate, q.quarternum
from @tv_transactions t
inner join @tv_quarters q on q.year = year(t.txndate) and q.quarter = datepart(quarter, t.txndate)
order by q.quarternum asc

Is there a better way to solve this problem? Is there a way to avoid using a while loop?

My company is currently in SQL Server 2005 (hey don't laugh at me) but we are migrating to SQL 2016 so I'm open to all suggestions.

Thank You

Answer 1

Updated answer:

If you want the quarternumto just represent the ordinal of the quarter compared to the @startdate then you can use datediff():

declare    @startdate              datetime
         , @enddate                datetime
select     @startdate               =  '2012-04-01'
        ,  @enddate                 =  '2014-04-01';
select 
    t.item
  , t.amt
  , t.txndate
  , quarternum = datediff(quarter, @startdate, t.txndate)+1
from @tv_transactions t
order by quarternum asc

rextester demo: http://rextester.com/GBO47881

returns: (with two rows removed to show the gap)

+------------------------+-----------+------------+------------+
|          item          |    amt    |  txndate   | quarternum |
+------------------------+-----------+------------+------------+
| iMac Pro               | 4999,0000 | 2012-07-23 |          2 |
| Flash Stick            | 4,5000    | 2012-08-01 |          2 |
| Pencils                | 3,6700    | 2012-10-23 |          3 |
| Markers                | 4,9900    | 2012-11-20 |          3 |
| Windex                 | 1,9900    | 2013-01-23 |          4 |
| echo dot               | 49,0000   | 2013-02-14 |          4 |
| Eggs                   | 3,9900    | 2013-04-12 |          5 |
| Strawberries           | 2,9900    | 2013-06-23 |          5 |
| Portable Generator     | 880,0000  | 2013-08-12 |          6 |
| Mattress Pad           | 103,9900  | 2013-09-03 |          6 |
| Power Drill            | 49,9900   | 2013-10-11 |          7 |
| GT Road GT             | 14,7700   | 2013-11-20 |          7 |
| QFit Bluetoot Speaker  | 25,9900   | 2014-01-31 |          8 |
| Toilet Night Light     | 8,9900    | 2014-02-23 |          8 |
| BS-MALL Makeup Brushes | 6,2900    | 2014-03-14 |          8 |
+------------------------+-----------+------------+------------+

---

You can use dense_rank() which was introduced in SQL Server 2005, and since your quarters are aligned with the calendar year, you can use year() along with datepart(quarter,...) without any modification for the ranking order:

select 
    t.item
  , t.amt
  , t.txndate
  , quarternum = dense_rank() over (order by year(t.txndate), datepart(quarter,t.txndate))
from @tv_transactions t
order by quarternum asc

rextester demo: http://rextester.com/YMPVMQ59269

returns:

+------------------------+-----------+------------+------------+
|          item          |    amt    |  txndate   | quarternum |
+------------------------+-----------+------------+------------+
| iPod Shuffle           | 130,0000  | 2012-04-03 |          1 |
| xBox One S             | 299,0000  | 2012-05-12 |          1 |
| iMac Pro               | 4999,0000 | 2012-07-23 |          2 |
| Flash Stick            | 4,5000    | 2012-08-01 |          2 |
| Pencils                | 3,6700    | 2012-10-23 |          3 |
| Markers                | 4,9900    | 2012-11-20 |          3 |
| Windex                 | 1,9900    | 2013-01-23 |          4 |
| echo dot               | 49,0000   | 2013-02-14 |          4 |
| Eggs                   | 3,9900    | 2013-04-12 |          5 |
| Strawberries           | 2,9900    | 2013-06-23 |          5 |
| Portable Generator     | 880,0000  | 2013-08-12 |          6 |
| Mattress Pad           | 103,9900  | 2013-09-03 |          6 |
| Power Drill            | 49,9900   | 2013-10-11 |          7 |
| GT Road GT             | 14,7700   | 2013-11-20 |          7 |
| QFit Bluetoot Speaker  | 25,9900   | 2014-01-31 |          8 |
| Toilet Night Light     | 8,9900    | 2014-02-23 |          8 |
| BS-MALL Makeup Brushes | 6,2900    | 2014-03-14 |          8 |
+------------------------+-----------+------------+------------+

Answer 2

You can use customizable solution with a recursive CTE (SQL Server 2005+):

DECLARE @startdate datetime = '2012-04-01',
        @enddate   datetime = '2014-04-01';

WITH dates AS (
     SELECT 1 AS quarternum, @startdate AS startDate, DATEADD(mm, 3, @startdate) AS endDate
     UNION ALL
     SELECT t.quarternum + 1, t.endDate, DATEADD(mm, 3, t.endDate) FROM dates t WHERE DATEADD(mm, 3, t.endDate) <= @enddate
)
SELECT tt.item, tt.amt, tt.txndate, d.quarternum
    FROM @tv_transactions tt
    INNER JOIN dates d ON d.startDate <= tt.txndate AND d.endDate > tt.txndate;

Output:

item                     amt       txndate     quarternum
---------------------------------- ----------- -----------
iPod Shuffle             130,00    2012-04-03  1
xBox One S               299,00    2012-05-12  1
iMac Pro                 4999,00   2012-07-23  2
Flash Stick              4,50      2012-08-01  2
Pencils                  3,67      2012-10-23  3
Markers                  4,99      2012-11-20  3
Windex                   1,99      2013-01-23  4
echo dot                 49,00     2013-02-14  4
Eggs                     3,99      2013-04-12  5
Strawberries             2,99      2013-06-23  5
Portable Generator       880,00    2013-08-12  6
Mattress Pad             103,99    2013-09-03  6
Power Drill              49,99     2013-10-11  7
GT Road GT               14,77     2013-11-20  7
QFit Bluetoot Speaker    25,99     2014-01-31  8
Toilet Night Light       8,99      2014-02-23  8
BS-MALL Makeup Brushes   6,29      2014-03-14  8

Note that @enddate changed.