CODEWITHSUNDEEP
sql-server
Yohanes Pradono
Yohanes Pradono

Reputation: 293

How to get current record for each day

if I have following database records

DATE        ITEMNAME

2014-02-10  A
2014-02-10  B
2014-02-10  C
2014-02-11  D
2014-02-11  E
2014-02-11  F
2014-02-11  G
2014-02-11  H
2014-02-12  I
2014-02-12  J
2014-02-12  K
2014-02-12  L
2014-02-13  M
2014-02-13  N

How to return the cumulative stock balance for every day so it would return something like this:

DATE        Total
2014-02-10  3
2014-02-11  8
2014-02-12  12
2014-02-13  14

Upvotes: 0

Views: 52

Answers (2)

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

If you have SQL 2012 or SQL 2014, you can try the following. Here is the SQL Fiddle.

 SELECT 
     [DATE], 
     SUM(COUNT(ITEMNAME)) OVER(ORDER BY [DATE] 
                               ROWS BETWEEN UNBOUNDED PRECEDING 
                               AND CURRENT ROW) AS StockCount
  FROM MyTable
  group by [DATE];

Upvotes: 2

Jon Senchyna
Jon Senchyna

Reputation: 8037

This should work for you. It gets the distinct dates from your table, then counts the number of entries that fall on or before each date.

WITH
CTE_Dates AS (
    SELECT DISTINCT DATE
    FROM [TableName]
)
SELECT DATES.DATE,
       COUNT(*) AS Total
FROM CTE_Dates DATES
INNER JOIN [TableName] STOCKS
    ON DATES.DATE >= STOCKS.DATE
GROUP BY DATES.DATE

Upvotes: 2

Related Questions