How to group by and select corresponding row values?

To start I am using SSMS 2018. I am trying to create a Common Table Expression to use in some code later. I need to get down to a single row per applicable quarter per person, I’ve tried using Havings/Group Bys and wheres but I can’t get the correct result set. What I have Currently is a table like this:

Total Cookies(DurableKey(Int), QuarterEndDateOfStartQuarter(Date), QuarterEndDate(Date), totalCookies (Int), NumberofDays(Int))

The Durable Key represents a person overtime.

The QuarterEndDateOfStartQuarter represents the first applicable quarter per some requirements, i.e. ate at least 10 cookies in one day during the quarter

The QuarterEndDate represents the current Quarter, up to 3 quarters after the QuarterEndDateOfStartQuarter

The TotalCookies represents the cookies since the beginning of the Start Quarter that have been eaten

The NumberofDays represents the number of days since the beginning of the Start Quarter that a cookie was eaten.

The data looks similar to this:

DurableKey QuarterEndDateOfStartQuarter QuarterEndDate totalCookies NumberofDays
99999   2018-06-30  2018-06-30  1410.000    17
99999   2018-06-30  2018-09-30  6564.000    91
99999   2018-06-30  2018-12-31  8174.000    154
99999   2018-06-30  2019-03-31  9554.000    161
99999   2018-09-30  2018-09-30  5154.000    74
99999   2018-09-30  2018-12-31  6764.000    137
99999   2018-09-30  2019-03-31  8144.000    144
99999   2018-09-30  2019-06-30  10091.000   188
99999   2019-03-31  2019-03-31  1380.000    7
99999   2019-03-31  2019-06-30  3327.000    51
99999   2019-03-31  2019-09-30  3357.000    52
99999   2019-03-31  2019-12-31  3357.000    52
99999   2019-06-30  2019-06-30  1695.000    30
99999   2019-06-30  2019-09-30  1725.000    31
99999   2019-06-30  2019-12-31  1725.000    31
99999   2019-06-30  2020-03-31  2917.500    74

And what I want to end up with is a CTE that has person – quarter granularity rather than person – Start Quarter – quarter. So the table would have the columns :

Cookies(DurableKey(Int), QuarterEndDate(Date), totalCookies (Int), NumberofDays(Int))

Where if there are overlapping rows, the data would be pulled from the row corresponding to the earlier QuarterEndDateOfStartQuarter :

DurableKey QuarterEndDate totalCookies NumberofDays
99999       2018-06-30  1410.000    17
99999       2018-09-30  6564.000    91
99999       2018-12-31  8174.000    154
99999       2019-03-31  9554.000    161
99999       2019-06-30  10091.000   188
99999       2019-09-30  3357.000    52
99999       2019-12-31  3357.000    52
99999       2020-03-31  2917.500    74

I feel like this should be straightforward but have been not been able to accomplish it.

Answer

Use row_number to order by the QuarterEndDateOfStartQuarter column partitioned by the key and the quarter. Then select the first ordered for each partition.

select  DurableKey, QuarterEndDateDate, totalCookies, NumberofDays
from    (
            select  *,
                    ord = row_number() over(
                        partition by durableKey, quarterEndDateDate 
                        order by QuarterEndDateOfStartQuarter
                    )
            from    @totalCookies
        ) tk
where   ord = 1

Advertisement

Answer