February 13, 2023 at 11:59 pm
Hi ALL
I need help with the following query.
declare @T table (
ID int identity
, PAX_CNT INT
, PAX_GROUP INT
);
insert into @T(PAX_CNT) values (1),(null),(2),(1),(null),(null),(null),(1),(null)
;
SELECT ID,PAX_CNT,PAX_GROUP
FROM @T
Results:
ID PAX_CNT PAX_GROUP
1 1 NULL
2 NULL NULL
3 2 NULL
4 1 NULL
5 NULL NULL
6 NULL NULL
7 NULL NULL
8 1 NULL
9 NULL NULL
Every time PAX_CNT changes from any number to NULL ,on PAX_GROUP column I want to see group number.
Something like this:
ID PAX_CNT PAX_GROUP
1 1 1
2 NULL NULL
3 2 2
4 1 2
5 NULL NULL
6 NULL NULL
7 NULL NULL
8 1 3
9 NULL NULL
NULL's in PAX_GROUP column could be NULL's or 0's
ID PAX_CNT PAX_GROUP
1 1 1
2 NULL 0
3 2 2
4 1 2
5 NULL 0
6 NULL 0
7 NULL 0
8 1 3
9 NULL 0
Thank you advance
February 14, 2023 at 9:49 am
One method would be to check if the current row isn't NULL and use LAG to check the prior row was NULL and if this is true then denote it (I use a 1). Then you can use a windowed COUNT to count those values:
WITH CTE AS(
SELECT ID,
PAX_CNT,
PAX_GROUP,
CASE WHEN PAX_CNT IS NOT NULL AND LAG(PAX_CNT) OVER (ORDER BY ID) IS NULL THEN 1 END AS GrpStart
FROM @T)
SELECT ID,
PAX_CNT,
CASE WHEN PAX_CNT IS NOT NULL THEN COUNT(GrpStart) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END AS PAX_GROUP
FROM CTE;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 14, 2023 at 3:31 pm
Hello Thom
It works!!
Thank you very much
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply