0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

-- create table & initial data
Create table #Test (
  	OrderID int
  , SalesID char(1)
  , OrderDT date
  , OrderQty int
) 
go
Insert into #Test Values 
(1,'A','20230101', 100), (2,'A','20230201', 200), (3,'A','20230201', 200)
, (4,'B','20230301', 300), (5,'B','20230401', 400), (6,'C','20230501', 600)
, (11,'C','20240101', 10), (12,'B','20240201', 20), (13,'A','20240301', 30)
, (14,'B','20240401', 40), (15,'C','20240401', 50), (16,'C','20240501', 60)
go
-- 原寫法
Select Convert(char(6), YMD, 112) as YM
  , m.M_QTY
  , Sum(M_QTY) over (partition by year(YMD)) as TTL_QTY
  , lag(M_QTY) 
    Over (partition by year(YMD) order by Month(YMD)) as LM_QTY  
from (
Select OrderDT as YMD
, sum(OrderQty) as M_QTY
from #Test
Group by OrderDT
) as m
go

fork query

edited jul 16

created jul 16 anonymous