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