To find sandwich holidays

Transact-SQL
1

Absent ploicy:

If you are absents just before a holiday
and also absent just after a holiday
then all holidays in between will be treated as absents

How to find those holidays where one is absent
before and after holiday / holidays

I am trying

select a.*
from sandwich a
left join sandwich b  on b.entry_date = dateadd(d,1,a.entry_date)
left join sandwich c  on c.entry_date = dateadd(d,-1,a.entry_date)
where a.flag='H'
and b.flag='A'
and c.flag='A'
id entry_date flag
1 2020-10-08 H
1 2020-10-13 H

It is not bringing those holidays where they are more than 1 consecutively
the correct result should be

id entry_date flag
1 2020-10-03 H
1 2020-10-04 H
1 2020-10-08 H
1 2020-10-13 H

the schema and sample data is as below

CREATE TABLE [dbo].[sandwich](
	[id] [int] NOT NULL,
	[entry_date] [date] NOT NULL,
	[flag] [varchar](1) NULL,
	)

ALTER TABLE [dbo].[sandwich] ADD  CONSTRAINT [PK_sandwich] PRIMARY KEY CLUSTERED 
(
	[id] ASC,
	[entry_date] ASC
)


INSERT INTO [sandwich] ([id],[entry_date],[flag])
VALUES(1,'2020-10-01','P')
,(1,'2020-10-02','A')
,(1,'2020-10-03','H')
,(1,'2020-10-04','H')
,(1,'2020-10-05','A')
,(1,'2020-10-06','P')
,(1,'2020-10-07','A')
,(1,'2020-10-08','H')
,(1,'2020-10-09','A')
,(1,'2020-10-10','H')
,(1,'2020-10-11','P')
,(1,'2020-10-12','A')
,(1,'2020-10-13','H')
,(1,'2020-10-14','A')

P means present
A means absent
H means holiday

2

hi Mateen

I am not able to understand what you are saying

Doing the SQL looks very easy
But not able to understand "how you are looking at the data" and coming to your result

1
11677×139 3.76 KB
Table_Output_Required_Result_V2
Table_Output_Required_Result_V21098×486 32.2 KB
Excel Explain SQL Team
Excel Explain SQL Team1920×1080 23.7 KB

3

@harishgg1

'2020-10-02','A'
'2020-10-03','H'
'2020-10-04','H'
,'2020-10-05','A'

here row 2 and 3 are holiday [or a holiday set]
row 1: some one is absent and condition just before holiday is true
row 4 some one is absent and condition just after holiday is true
we need a sql which will give us: row 2 and row 3

after finding those holidays, we will update them as absent.

4

@harishgg1
https://www.taxreturnwala.com/sandwich-rule-labour-laws-in-india/#:~:text=The%20Sandwich%20Rule,treated%20as%20a%20leave%20day.

5

all id of 1? are you sure?

6

True for sample data.

In real table , there is around 2000 different ids.

7

@harishgg1 , @yosiasz

Please note, if i delete 4th row (1,'2020-10-04','H')
then my query works, because then there is no consecutive holiday
in the data.

8 
;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
    SELECT 0 AS number UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
)
SELECT
    s1.id,
    DATEADD(DAY, t.number, s2.entry_date) AS holiday_date,
    'H' AS flag
FROM dbo.sandwich s1
CROSS APPLY (
    SELECT s2.entry_date 
    FROM dbo.sandwich s2 
    WHERE s2.id = s1.id AND s2.entry_date = DATEADD(DAY, 1, s1.entry_date) AND s2.flag = 'H'
) AS s2
CROSS APPLY (
    SELECT s3.entry_date
    FROM dbo.sandwich s3 
    CROSS APPLY (
        SELECT TOP (1) s4.entry_date
        FROM dbo.sandwich s4
        WHERE s4.id = s1.id AND s4.entry_date > s1.entry_date AND s4.flag <> 'H'
        ORDER BY s4.entry_date
    ) AS s4
    WHERE s3.id = s1.id AND s3.entry_date = s4.entry_date AND s3.flag = 'A'
) AS s3
INNER JOIN cte_tally100 t ON t.number BETWEEN 0 AND DATEDIFF(DAY, s1.entry_date, s3.entry_date) - 2
WHERE s1.flag = 'A'
1 Like
9

Yes!
It worked.

Thanks a lot.