Everyone needs a travel plan especially if you are working in India, it is tough to reserve your tickets as it needs. I had lot of bad experiences missing my reservations without a well in advance booking – that pays me a lot.

Here, I would like to share with you one of my SQL Server Script prepared for my travel plan.

The idea is very simple – I would love to see my leave dates and the number of days that I would get. I also wanted to look at an option with a voluntary leave to get more holidays. 🙂

The script is very much configurable and easy to understand, so that anyone can change as per the requirement.

The below screenshot is a sample output of the Script:

Script:

Declare @LeaveCalendar as Table (Calendar_Date Date,Train_Date Date,WeekName Varchar(10), Is_NationalHoliday Bit,Is_LocalHoliday Bit, Holiday_Description Varchar(100))

Declare @CalendarYear char(4) = '2015'
Declare @BookingPeriod int = 60 --No.Of days prior the ticket needs to be booked.

;With cteCalendar
as
(
 Select DATEADD(yy, DATEDIFF(yy,0,@CalendarYear + '-01-01'), 0) CalendarDate
 Union All
 Select DATEADD(day,1,Calendardate) CalendarDate From cteCalendar
 Where CalendarDate < DATEADD(yy, DATEDIFF(yy,0,@CalendarYear + '-01-01') + 1, -1)
)
Insert into @LeaveCalendar(Calendar_Date,Train_Date,WeekName)
Select CalendarDate,DATEADD(day,-(@BookingPeriod),CalendarDate),DATENAME(WEEKDAY,CalendarDate) From cteCalendar OPTION(MAXRECURSION 366)

--Saturday and Sunday List Holiday update
Update @LeaveCalendar Set Is_NationalHoliday = 1, Holiday_Description = WeekName
Where WeekName in ('Sunday','Saturday')

--National Holiday List
;with cte(sDate,sDescription) as 
(
 Select @CalendarYear+'-01-01' ,'New Year' 
 Union All
 Select @CalendarYear+'-01-26' ,'Republic Day'
 Union All
 Select @CalendarYear+'-05-01' ,'May Day'
 Union All
 Select @CalendarYear+'-10-02' ,'Gandhi Jayanthi'
 Union All
 Select @CalendarYear+'-12-25' ,'Christmas Day'
)
Update A Set Is_NationalHoliday = 1,Holiday_Description = B.sDescription 
From @LeaveCalendar A
Inner join cte B On A.Calendar_Date = B.sDate 

--LocalHoliday List(The below is a sample, you may add your local holidays in the list)
;with cte(sDate,sDescription) as 
(
 Select @CalendarYear+'-01-15' ,'Pongal' 
 Union All
 Select @CalendarYear+'-04-14' ,'Tamil New Year' 
 Union All
 Select @CalendarYear+'-08-20' ,'Onam' 
 Union All
 Select @CalendarYear+'-09-17' ,'Ganesh Chaturthi'
 Union All
 Select @CalendarYear+'-10-21' ,'Ayudha Puja'
 Union All
 Select @CalendarYear+'-11-10' ,'Diwali'

)
Update A Set Is_LocalHoliday = 1,Holiday_Description = B.sDescription 
From @LeaveCalendar A
Inner join cte B On A.Calendar_Date = B.sDate 

;With cte as
(
Select *,Coalesce(Is_NationalHoliday,Is_LocalHoliday) IsHoliday From @LeaveCalendar
)
,cteHolidayPeriod as
(
 Select *,Coalesce(IsHoliday,Case when Lead(IsHoliday,1)Over(Order by Calendar_date) = Lag(IsHoliday,1)Over(Order by Calendar_Date) 
 and Lag(IsHoliday,1)Over(Order by Calendar_Date) =1 Then 1 
 Else 0 End) as 'Holiday_Period_Voluntry',
 Case when Lead(IsHoliday,1)Over(Order by Calendar_date) = Lag(IsHoliday,1)Over(Order by Calendar_Date) 
 and Lag(IsHoliday,1)Over(Order by Calendar_Date) =1 and IsNull(IsHoliday,0) = 0 Then 1 
 Else NULL End Is_VoluntryHoliday
 From cte
)
,cteReadable as
(
 Select 
 *,datediff(day, '19000101', Calendar_Date) - ROW_NUMBER() OVER (order by Calendar_date) as GroupID 
 From cteHolidayPeriod Where Holiday_Period_Voluntry=1
)
Select *,
(Select Top 1 Holiday_Description From @LeaveCalendar Where Calendar_Date between Calendar_HolidayStartDate and Calendar_HolidayEndDate
 and 1 in (Is_LocalHoliday,Is_NationalHoliday) and Weekname not in ('Saturday','Sunday')) 'Holiday Description'
From(
Select 
 Min(Calendar_date) Calendar_HolidayStartDate,Max(Calendar_date) Calendar_HolidayEndDate,
 DateAdd(day,-1,Min(Train_Date)) Booking_HolidayStartDate,Max(Train_Date) Booking_HolidayEndDate,
 Count(Calendar_Date) 'No.Of Holidays', Count(Is_VoluntryHoliday) 'No.Of Voluntry Holiday'
From cteReadable
Group by GroupID having Count(Calendar_Date)>2)A