By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
declare @value table (r# int, value varchar(15))
insert into @value ( r#, value ) values
(1, NULL ) ,
(2, 'January'),
(3, 'February' ),
(4, NULL ),
(5, 'March' ),
(6, NULL ),
(7, Null ),
(8, 'December' ),
(9, Null ),
(10, Null ),
(11, Null ),
(12, 'November' ),
(13, Null )
select *,lead(value) over(order by r#) from @value
| r# | value | (No column name) |
|---|---|---|
| 1 | null | January |
| 2 | January | February |
| 3 | February | null |
| 4 | null | March |
| 5 | March | null |
| 6 | null | null |
| 7 | null | December |
| 8 | December | null |
| 9 | null | null |
| 10 | null | null |
| 11 | null | November |
| 12 | November | null |
| 13 | null | null |
WITH CTE (ID , Name , foodEaten , total)
AS
(
SELECT 1 , 'Sam' , 'Burger' , 3 UNION ALL
SELECT 1 , 'Sam' , 'Pizza' , 1 UNION ALL
SELECT 1 , 'Sam' , 'Kebab' , 2 UNION ALL
SELECT 1 , 'Sam' , 'Cheesecake', 3 UNION ALL
SELECT 1 , 'Sam' , 'Sandwich' , 5 UNION ALL
SELECT 2 , 'Jeff' , 'Burger' , 0 UNION ALL
SELECT 2 , 'Jeff' , 'Pizza' , 0 UNION ALL
SELECT 2 , 'Jeff' , 'Kebab' , 0 UNION ALL
SELECT 2 , 'Jeff' , 'Cheesecake', 0 UNION ALL
SELECT 2 , 'Jeff' , 'Sandwich' , 0
)
select * from cte a
where not exists (select 1 from cte b where a.id=b.id group by id,name
having sum(total)=0)
| ID | Name | foodEaten | total |
|---|---|---|---|
| 1 | Sam | Burger | 3 |
| 1 | Sam | Pizza | 1 |
| 1 | Sam | Kebab | 2 |
| 1 | Sam | Cheesecake | 3 |
| 1 | Sam | Sandwich | 5 |