My question is simple but I can not find a solution to this - probably with window functions, I have following sample data:
ID OrderDate TotalOrder
--------------------------
1 2019-01-01 5
2 2019-01-02 3
3 2019-01-03 1
4 2019-01-04 15
5 2019-01-05 20
6 2019-01-06 18
7 2019-01-07 2
8 2019-01-08 4
9 2019-01-09 9
And required result is:
ID OrderDate TotalOrder Category
-------------------------------------
1 2019-01-01 5 1
2 2019-01-02 3 1
3 2019-01-03 1 1
4 2019-01-04 15 2
5 2019-01-05 20 2
6 2019-01-06 18 2
7 2019-01-07 2 3
8 2019-01-08 4 3
9 2019-01-09 9 3
The Category column is calculated based on the range of TotalOrder values.
And if the TotalOrder value is less than 10, the value of the Category is increased.
You could use LAG and SUM OVER():
-- Create sample data
CREATE TABLE #Tbl(
ID INT,
OrderDate DATE,
TotalOrder INT
);
INSERT INTO #Tbl VALUES
(1, '2019-01-01', 5), (2, '2019-01-02', 3), (3, '2019-01-03', 1),
(4, '2019-01-04', 15), (5, '2019-01-05', 20), (6, '2019-01-06', 18),
(7, '2019-01-07', 2), (8, '2019-01-08', 4), (9, '2019-01-09', 9);
-- Query
WITH Cte AS(
SELECT *,
LessThanTen = CASE WHEN TotalOrder < 10 THEN 1 ELSE 0 END
FROM #Tbl
),
CteLag AS (
SELECT *,
Prev = LAG(LessThanTen, 1, NULL) OVER(ORDER BY OrderDate, ID)
FROM Cte
)
SELECT
ID,
OrderDate,
TotalOrder,
Category = SUM(CASE WHEN Prev = LessThanTen THEN 0 ELSE 1 END) OVER(ORDER BY OrderDate, ID)
FROM CteLag
ORDER BY OrderDate, ID;
-- Cleanup
DROP TABLE #Tbl;