Grouping Column Without Breaking The Sequence

user_11725783 0
Viewed 48

The main objective is to group the rows following Amount Column sequentially so that, if there is any different value between the 2 same values, they will be numbered separately. This is the raw data here:

SELECT Area, DateA, DateB, Amount
FROM (VALUES
    ('ABC', '2019-08-18', '2019-08-18 00:07:47.000', 3.75),
    ('ABC','2019-08-19', '2019-08-19 00:08:47.000', 3.75),
    ('ABC','2019-08-20', '2019-08-20 00:09:47.000', 3.65),
    ('ABC','2019-08-21', '2019-08-21 00:09:57.000', 3.75))
    AS FeeCollection(Area, DateA, DateB, Amount)

I've tried this but, I don't know the real matter to number in a special way.

DENSE_RANK() OVER(ORDER BY Area, Amount)

This is the sample result I want to achieve. I'm looking for simple logic to do it. Using cursor or while looping will not be efficient for me.

enter image description here

dense-rank group-by sql-server sql
2 Answers

I believe this is what you want. I use LAG to get the value of the prior row in a CTE, and then use a windowed COUNT to reduce the value of ROW_NUMBER by 1 for each row with the same consecutive value for amount:

WITH CTE AS(
    SELECT Area,
           DateA,
           DateB,
           Amount,
           LAG(Amount) OVER (PARTITION BY Area ORDER BY DateA) AS PrevAmount
    FROM (VALUES
        ('ABC', '2019-08-18', '2019-08-18 00:07:47.000', 3.75),
        ('ABC','2019-08-19', '2019-08-19 00:08:47.000', 3.75),
        ('ABC','2019-08-20', '2019-08-20 00:09:47.000', 3.65),
        ('ABC','2019-08-21', '2019-08-21 00:09:57.000', 3.75))
        AS FeeCollection(Area, DateA, DateB, Amount))
SELECT Area,
       DateA,
       DateB,
       Amount,
       ROW_NUMBER() OVER (PARTITION BY Area ORDER BY DateA) - 
       COUNT(CASE Amount WHEN PrevAmount THEN 1 END) OVER (PARTITION BY Area ORDER BY DateA
                                                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Number
FROM CTE
ORDER BY DateA;

I did assume your PARTITION BY clause, which you may need to change/remove/move to the ORDER BY. As we had only one value for Area was impossible to know what the value should be when it changes.

user_20299830

I would do this using lag() and a cumulative sum, but looking like:

select t.*,
       sum(case when prev_amount = amount then 0 else 1 end) over
           (partition by area order by datea) as number
from (select t.*,
             lag(amount) over (partition by area order by datea) as prev_amount
      from t
     ) t;
user_11440350
Related