clear markdown help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 744928 fiddles created (10343 in the last week).

DECLARE @BuyList TABLE ( UserNo NVARCHAR(10), StoreNo NVARCHAR(10), ProductNo NVARCHAR(10), [Order] INT ) INSERT INTO @BuyList (UserNo, StoreNo, ProductNo, [Order]) VALUES ('001', 'S01', 'P01', 1), ('001', 'S01', 'P02', 2), ('001', 'S02', 'P02', 3), ('001', 'S02', 'P03', 4), ('001', 'S02', 'P04', 5), ('001', 'S01', 'P05', 6), ('001', 'S01', 'P06', 7), ('001', 'S01', 'P07', 8), ('002', 'S01', 'P08', 1), ('002', 'S02', 'P09', 2), ('003', 'S01', 'P01', 1), ('003', 'S02', 'P01', 2), ('003', 'S02', 'P02', 3) ;WITH CTE AS ( SELECT *, CASE WHEN LAG((UserNo + StoreNo)) OVER (ORDER BY UserNo, [Order]) =(UserNo + StoreNo) THEN 0 ELSE 1 END Diff FROM @BuyList ) SELECT UserNo, StoreNo, ProductNo, [Order], SUM(Diff) OVER (PARTITION BY UserNo ORDER BY UserNo, [Order]) AS StoreOrder FROM CTE ORDER BY UserNo, [Order]
UserNo StoreNo ProductNo Order StoreOrder
001 S01 P01 1 1
001 S01 P02 2 1
001 S02 P02 3 2
001 S02 P03 4 2
001 S02 P04 5 2
001 S01 P05 6 3
001 S01 P06 7 3
001 S01 P07 8 3
002 S01 P08 1 1
002 S02 P09 2 2
003 S01 P01 1 1
003 S02 P01 2 2
003 S02 P02 3 2
UserNo StoreNo ProductNo Order StoreOrder
001 S01 P01 1 1
001 S01 P02 2 1
001 S02 P02 3 2
001 S02 P03 4 2
001 S02 P04 5 2
001 S01 P05 6 3
001 S01 P06 7 3
001 S01 P07 8 3
002 S01 P08 1 1
002 S02 P09 2 2
003 S01 P01 1 1
003 S02 P01 2 2
003 S02 P02 3 2
 hidden batch(es)