ASKER
DECLARE @Order TABLE
(
MonthYear DATETIME NOT NULL,
OrderAmount INT NOT NULL
);
INSERT @Order
(
MonthYear,
OrderAmount
)
VALUES
('20210101', 200),
('20210201', 0),
('20210301', 250),
('20210401', 300),
('20210501', 100);
WITH cteOrder AS
(
SELECT MonthYear,
OrderAmount,
LAG(OrderAmount, 1, NULL) OVER(ORDER BY MonthYear) AS PrevOrderAmount,
LEAD(OrderAmount, 1, NULL) OVER(ORDER BY MonthYear) AS NextOrderAmount
FROM @Order
)
SELECT cteOrder.MonthYear,
cteOrder.OrderAmount,
CASE
WHEN cteOrder.PrevOrderAmount IS NULL OR cteOrder.NextOrderAmount IS NULL THEN 0
WHEN cteOrder.OrderAmount = 0 THEN 0
WHEN OrderAmount > 0 AND cteOrder.NextOrderAmount > cteOrder.OrderAmount THEN cteOrder.NextOrderAmount - OrderAmount
WHEN OrderAmount > 0 AND cteOrder.NextOrderAmount < cteOrder.OrderAmount THEN 0
ELSE NULL
END AS Movement
FROM cteOrder
ORDER BY cteOrder.MonthYear
DECLARE @Order TABLE
(
MonthYear varchar(50) NOT NULL,
OrderAmount INT NOT NULL
);
INSERT @Order
(
MonthYear,
OrderAmount
)
VALUES
('jan-2021', 200),
('feb-2021', 0),
('mar-2021', 250),
('Apr-2021', 300),
('May-2021', 100);
WITH cteOrder AS
(
SELECT MonthYear,
OrderAmount,
LAG(OrderAmount, 1, NULL) OVER(ORDER BY CONVERT(smalldatetime, REPLACE(MonthYear, '-', ' 1 '), 109)) AS PrevOrderAmount,
LEAD(OrderAmount, 1, NULL) OVER(ORDER BY CONVERT(smalldatetime, REPLACE(MonthYear, '-', ' 1 '), 109)) AS NextOrderAmount
FROM @Order
)
SELECT cteOrder.MonthYear,
cteOrder.OrderAmount,
CASE
WHEN cteOrder.PrevOrderAmount > 0 AND cteOrder.PrevOrderAmount < cteOrder.OrderAmount THEN cteOrder.OrderAmount - cteOrder.PrevOrderAmount
ELSE 0
END AS Movement
FROM cteOrder
ORDER BY CONVERT(smalldatetime, REPLACE(cteOrder.MonthYear, '-', ' 1 '), 109)
IF the months are all for the SAME YEAR, you wouldn't need to convert them to easily order them. If there's other years present, you'd have to pull out the year and sort by it first, obviously, but actually you still wouldn't have to convert to a date.@Scott Pletcher
Why should be the first and the last month zero? The first month is OK because we don't have data but the last month always has some data.
if given month value greater than 0 and next month less than given month should be zero
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY