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
Excellent script
LikeLike
Excellent work bro… keep it up…
LikeLike
Reblogged this on Dinesh Ram Kali..
LikeLiked by 1 person
When i try to update it with this year leave details ,I am facing the below issue,
Msg 195, Level 15, State 10, Line 65
‘Lead’ is not a recognized built-in function name.
Msg 102, Level 15, State 1, Line 70
Incorrect syntax near ‘Is_VoluntryHoliday’.
LikeLike
You must be in an older version of SQL Server. Move to SQL Server 2012 or above.
LikeLike