SQL Server MTD, YTD data.

sakuraime 2,321 Reputation points
2022-01-28T15:45:10.51+00:00

I have a table like a sales table .
date store staff product qty
2022-01-01 01 01 001 1
2022-01-01 01 02 001 2
2022-01-02 01 01 001 1
2022-01-02 01 02 003 2

I use sum(QTY) over partition by datemon,store,staff,product order by date asc rows between 31 preceding and current row) . .. and give a table like

date store staff product qty MTDQTY
2022-01-01 01 01 001 1 1
2022-01-01 01 02 001 2 2
2022-01-02 01 01 001 1 2
2022-01-02 01 02 003 2 2

if I want to get qty and mtdqty of the whole store , and save to the same table , is it possible ?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2022-01-28T17:09:25.007+00:00

    Use a calendar table, and simply join the date to the calendar table and group by the year/month.

    https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/

    0 comments
  2. Erland Sommarskog 101.4K Reputation points MVP
    2022-01-28T22:37:18.24+00:00

    Your question is not entirely clear, and you did not provide CREATE TABLE + INSERT, making it difficult to test. But this may be what you are looking for: 

    ; WITH CTE AS 
   SELECT MTDQTY, 
        newvalue sum(QTY) over partition by datemon,store,staff,product 
           order by date asc rows between 31 preceding and current row)
   FROM  tbl
)
UPDATE CTE
SET MTDQTY = newvalue