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 …

;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, DATEA…

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] [Table_Output_Required_Result_V2] [Excel Explain SQL Team]

@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 …

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

[image] yosiasz: ll id of 1? are you sure? True for sample data. In real table , there is around 2000 different ids.

@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.

hi hope this helps already solved ... question why ? answer a different way to do this ..perhaps performance tuning considerations SELECT s.id, s.entry_date, s.flag FROM sandwich s JOIN ( SELECT id, MIN(entry_date) AS start_date, MAX(entry_date) AS end_date FROM ( …

hi mateen :winking_face_with_tongue: solved it in a couple of seconds in "PYTHON" not like t-sql ... so many functions logics exists not exists ... so confusing .. only with a lots of experience or learning very very quickly .. you can do it in t-sql [image]

SQLTeam.com | Weblogs | Forums

SQLTeam.com Forums

To find sandwich holidays

Transact-SQL

Mateen May 5, 2026, 8:11am 11

Thanks

show post in topic

Home
Categories
Guidelines
Terms of Service
Privacy Policy

Powered by Discourse, best viewed with JavaScript enabled