T-SQL - aggregate in all rows using aggregate from correlated sub-query

Question

store   item   datekey   onhand   salesunits
--------------------------------------------
001     A      50        65       2
001     A      51        8        4
001     A      52        0        8
--------------------------------------------

What I need to accomplish: to get the latest onhand greater than zero minus the total units sold, by store and item. So in the example above it would be 8-14=-6.

I am using a correlated sub-query to determine the latest datekey and then joining back to the main query. But obviously by doing so I lose the data related to the other rows necessary to sum the salesunits:

This is what I have and it's wrong:

select s1.Store, s1.Item, s1.OnHand, sum(salesunit)
from sales s1
  join (select top 1 store,item, max(DateKey) as datekey
        from sales
        where isnull(onhand,0) > 0
          and DateKey in (50,51,52)
        group by store, item) s2  on s2.store=s1.store and s2.item=s1.item and s2.datekey=s1.datekey
group by s1.Store, s1.Item, s1.OnHand

Thanks for your help!

Arion · Answer 1 · 2012-02-22T09:30:59.377

I would do it something like this:

First some test data:

DECLARE @tbl TABLE
        (
            store VARCHAR(4),
            item VARCHAR(2),
            datekey INT,
            onhand INT,
            salesUnits INT
        )

INSERT INTO @tbl
VALUES
    ('001','A',50,65,2),
    ('001','A',51,8,4),
    ('001','A',52,0,8)

The the query like this:

;WITH cteTotalSales AS
(
    SELECT
        SUM(tbl.salesUnits) OVER(PARTITION BY 1) AS TotalSalesUnit,
        tbl.store,
        tbl.item,
        ISNULL(tbl.onhand,0) AS onhand,
        tbl.salesUnits,
        tbl.datekey
    FROM
        @tbl AS tbl
), cteLatest AS
(
    SELECT
        RANK() OVER
            (
                PARTITION BY cteTotalSales.store,cteTotalSales.item 
                ORDER BY cteTotalSales.datekey DESC
            ) AS iRank,
        cteTotalSales.store,
        cteTotalSales.item,
        cteTotalSales.onhand,
        cteTotalSales.salesUnits,
        cteTotalSales.datekey
    FROM
        cteTotalSales
    WHERE
        (cteTotalSales.onhand-cteTotalSales.TotalSalesUnit)>0
)
SELECT
    *
FROM
    cteLatest
WHERE
    iRank=1

score 2 · Answer 2 · answered Feb 22 '12 at 09:55

;with a as
(
select rn = row_number() over (partition by store, item order by case when onhand = 0 then -1 else datekey end desc), 
Store, Item, OnHand, salesunit
from sales
)
select store, item, sum(case when rn = 1 then onhand end)-sum(salesunit) OnHand, sum(salesunit) sumsalesunit from a
group by store, item

score 2 · Accepted Answer · answered Feb 22 '12 at 10:39

2

;
WITH totals AS (
  SELECT
    *,
    totalsalesunits = SUM(salesunits) OVER (PARTITION BY store, item),
    rnk = ROW_NUMBER() OVER (PARTITION BY store, item
                                 ORDER BY SIGN(onhand) DESC, datekey DESC)
  FROM sales
)
SELECT
  store,
  item,
  onhand,
  totalsalesunits
FROM totals
WHERE rnk = 1

answered Feb 22 '12 at 10:39

Andriy M

76,112
17
94
154

1

my +1 was for the SIGN. Nice touch – t-clausen.dk Feb 22 '12 at 12:25

T-SQL - aggregate in all rows using aggregate from correlated sub-query

3 Answers3