CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Christianne Rio Ortega
Christianne Rio Ortega

Reputation: 11

Period and Quarter Sequence

I'm trying to find a way to do a sequence for date periods and quarters(not sure if this is the correct term).

Basically this will help people to navigate dates based on weeks, periods, and quarters once I join this to our sales data. For example, if I just want to know the sales from last week, I could just use WHERE WeekSequence = -1... Another example is, a manager wants to get the sales data for the past quarter, I could just use WHERE QuarterSequence = -1... something like that.

My current table:

WeekStartDate  WeekEndDate  CurrentWeek  Period  Quarter  WeekSequence
----------------------------------------------------------------------
2020-08-03     2020-08-09   0            2       1        -5
2020-08-10     2020-08-16   0            2       1        -4
2020-08-17     2020-08-23   0            2       1        -3
2020-08-24     2020-08-30   0            2       1        -2
2020-08-31     2020-09-06   0            2       1        -1
2020-09-07     2020-09-13   1            3       1        0
2020-09-14     2020-09-20   0            3       1        1
2020-09-21     2020-09-27   0            3       1        2
2020-09-28     2020-10-04   0            3       1        3
2020-10-05     2020-10-11   0            4       2        4
2020-10-12     2020-10-18   0            4       2        5

What I want it to look like(highlighted):

Goal

Upvotes: 1

Views: 322

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

If I understand correctly, just use window functions:

select t.*,
       (period -
        max(case when currentweek = 1 then period end) over ()
       ) as periodsequence,
       (quarter -
        max(case when currentweek = 1 then quarter end) over ()
       ) as quartersequence       
from t;

You can include this in a view rather than putting it in a table.

Upvotes: 2

Related Questions