CODEWITHSUNDEEP
sqlsql-serverlag
Decrescence
Decrescence

Reputation: 11

How to aggregate data for lagging months only

I've run into a bit of a problem cleansing our order data.

The dataset includes duplicate colour codes (orders) over the years, and I need to sum the data only if it's in the same month, or rolled over from the previous month.

I have tried this query, which returns the below table.

;WITH CTE_Rollovers AS
    (
        SELECT 
            CAST(DATEADD(month, DATEDIFF (month, 0, OrderDate), 0) AS DATE) AS OrderDate, 
            ColourCode, 
            RequiredTO, 
            DispatchedTO,
            CASE WHEN COALESCE(LAG(OrderDate, 1) OVER (PARTITION BY ColourCode ORDER BY OrderDate),'') != '' THEN 1 ELSE 0 END AS IsRollover
          FROM VW_QORDER_HIS_OrderDetail (NOLOCK)
            WHERE 
                OrderIsActive = 1 AND
                IsCurrent = 1 AND
                SpecMaterialType = 'HC Product' AND 
                Vessel = 'Port Stock' AND 
                ColourCode != 'Unknown'
    )
        SELECT * FROM CTE_Rollovers 
            WHERE IsRollover = 1 AND ColourCode = 'Aqua Preto' ORDER BY ColourCode

Per below example, I need to aggregate only the ones in bold

OrderDate ColourCode RequiredTO DispatchedTO IsRollover
2014-12-01 Aqua Preto 4000.000 3566.650 1
2015-06-01 Aqua Preto 14250.000 4613.350 1
2015-07-01 Aqua Preto 9690.720 9690.720 1
2016-01-01 Aqua Preto 9991.550 9991.550 1
2016-12-01 Aqua Preto 7000.000 4197.190 1
2016-12-01 Aqua Preto 2800.000 2833.920 1
2017-03-01 Aqua Preto 17000.000 14860.520 1

Desired result:

OrderDate ColourCode RequiredTO DispatchedTO IsRollover
2014-12-01 Aqua Preto 4000.000 3566.650 1
2015-06-01 Aqua Preto 23940.72 14304.07 1
2016-01-01 Aqua Preto 9991.550 9991.550 1
2016-12-01 Aqua Preto 9800.000 4197.190 1
2017-03-01 Aqua Preto 17000.000 14860.520 1

Upvotes: 1

Views: 66

Answers (1)

DannySlor
DannySlor

Reputation: 4620

Here's a nifty solution using dense_rank in order to group consecutive months.

with t as (
          select dense_rank() over (order by year(OrderDate),month(OrderDate)) as rank
                ,OrderDate
                ,ColourCode
                ,RequiredTO
                ,DispatchedTO
                ,IsRollover
          from   VW_QORDER_HIS_OrderDetail
          )
 
select  min(OrderDate)    as OrderDate
       ,min(ColourCode)   as ColourCode
       ,sum(RequiredTO)   as RequiredTO
       ,sum(DispatchedTO) as DispatchedTO
       ,min(IsRollover)   as IsRollover
from    t
group by rank - (year(OrderDate)*12+month(OrderDate))
order by OrderDate
OrderDate ColourCode RequiredTO DispatchedTO IsRollover
2014-12-01 Aqua Preto 4000 3566.65 1
2015-06-01 Aqua Preto 23940.72 14304.07 1
2016-01-01 Aqua Preto 9991.55 9991.55 1
2016-12-01 Aqua Preto 9800 7031.11 1
2017-03-01 Aqua Preto 17000 14860.52 1

Fiddle

Upvotes: 1

Related Questions