HI All Please Help me to get 2 Reports..
1. Week Wise Applied Leave Count Report for Calendar Year. (This should be count of leaves applied weekly wise from Mon to Sun
EmpCode, Empname, LeaveType, WK 06/Jan, Wk 13/Jan, Wk 03/ Feb ...... Wk 22/Dec, Wk 29 /Dec
2222 Mark Sick Lea 1 0 2 2 1
2222 Mark Cas Lea 2 1 0 4 2
2222 Mark LOP 0 0 0 15 20
2. Month Wise Applied Leave Count Report For Calendar wise
EmpCode, Empname, LeaveType, January, February, March ...... November , December
2222 Mark SL 1 2 0 5 2
2222 Mark CL 2 1 0 4 2
2222 Mark LOP 0 0 0 15 20
Some points to be noted.
1. Some leave fall in current month end in next month.
2. For SL and CL Sat and Sun is weekoff. some employee apply from Friday to Tuesday etc.,
3. Holiday in between the leave days not counted. System calculates the days and store in NO of Days Column.
4. End of the code i pasted some query which retrieves availed leaves for the entire year. Now i need split of leave availed weekly wise and monthly wise.
5. In Leave status updates table if employee applies leave the status id insert as 1, if he withdrawn statusid is 2, if rejected status id is 5, is approved statusid is 4.. Weneed to take the Maxstatus id in leave statusupdate table and mark as Applied or Approved.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EmployeeDetails](
[EmployeeLoginName] [varchar](200) NOT NULL,
[EmployeeName] [varchar](200) NOT NULL,
[ReportingManager] [varchar](200) NULL,
CONSTRAINT [PK_EmployeeMasterD] PRIMARY KEY CLUSTERED
(
[EmployeeLoginName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
=============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LeaveStatusMaster](
[LeaveStatusID] [int] IDENTITY(1,1) NOT NULL,
[LeaveStatusName] [varchar](200) NOT NULL,
[LeaveStatusDescription] [varchar](2000) NULL,
CONSTRAINT [PK_LeaveStatusMaster] PRIMARY KEY CLUSTERED
(
[LeaveStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
====================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LeaveTypes](
[LeaveTypeID] [int] IDENTITY(1,1) NOT NULL,
[TypeName] [varchar](200) NOT NULL,
[ApplicableGenders] [int] NOT NULL,
[Abbrivation] [varchar](5) NOT NULL,
CONSTRAINT [PK_Leave_Types] PRIMARY KEY CLUSTERED
(
[LeaveTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Leave_Types] UNIQUE NONCLUSTERED
(
[TypeName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Leave_Types_1] UNIQUE NONCLUSTERED
(
[Abbrivation] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0-Both, 1-Male, 2-Female' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LeaveTypes', @level2type=N'COLUMN',@level2name=N'ApplicableGenders'
GO
ALTER TABLE [dbo].[LeaveTypes] ADD CONSTRAINT [DF_Leave_Types_Applicable_Genders] DEFAULT ((0)) FOR [ApplicableGenders]
GO
=================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LeaveDetails](
[LeaveDetailsID] [bigint] IDENTITY(1,1) NOT NULL,
[EmployeeLoginName] [varchar](200) NOT NULL,
[LeaveRuleID] [int] NOT NULL,
[FromDate] [datetime] NOT NULL,
[FromSession] [int] NOT NULL,
[ToDate] [datetime] NOT NULL,
[ToSession] [int] NOT NULL,
[IncludedHolidays] [decimal](5, 2) NOT NULL,
[IncludedWeeklyOffs] [decimal](5, 2) NOT NULL,
[NoOfDays] [decimal](5, 2) NOT NULL,
[Purpose] [varchar](200) NOT NULL,
[ContactInfo] [varchar](2000) NULL,
[eMailCC] [varchar](4096) NULL,
[ValidAgainstDate] [datetime] NULL,
[IsCompulsoryLeave] [bit] NOT NULL,
[IsMedicalCertificateProvided] [bit] NULL,
[isFromAttendance] [bit] NULL,
[LeaveStatusID] [int] NULL,
CONSTRAINT [PK_Leave_Transactions] PRIMARY KEY CLUSTERED
(
[LeaveDetailsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
========================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LeaveStatusUpdates](
[LeaveStatusUpdateID] [bigint] IDENTITY(1,1) NOT NULL,
[LeaveDetailsID] [bigint] NOT NULL,
[LeaveStatusID] [int] NOT NULL,
[CreatedBy] [varchar](200) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[Remarks] [varchar](200) NULL,
CONSTRAINT [PK_LeaveStatusUpdates] PRIMARY KEY CLUSTERED
(
[LeaveStatusUpdateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[LeaveStatusUpdates] WITH CHECK ADD CONSTRAINT [FK_LeaveStatusUpdates_LeaveDetails] FOREIGN KEY([LeaveDetailsID])
REFERENCES [dbo].[LeaveDetails] ([LeaveDetailsID])
GO
ALTER TABLE [dbo].[LeaveStatusUpdates] CHECK CONSTRAINT [FK_LeaveStatusUpdates_LeaveDetails]
GO
ALTER TABLE [dbo].[LeaveStatusUpdates] WITH CHECK ADD CONSTRAINT [FK_LeaveStatusUpdates_LeaveStatusMaster] FOREIGN KEY([LeaveStatusID])
REFERENCES [dbo].[LeaveStatusMaster] ([LeaveStatusID])
GO
ALTER TABLE [dbo].[LeaveStatusUpdates] CHECK CONSTRAINT [FK_LeaveStatusUpdates_LeaveStatusMaster]
GO
====================================
insert into Employeedetails values ('1111','Mark','1111')
insert into Employeedetails values ('2222','Steve','1111')
insert into Employeedetails values ('3333','Messi','1111')
insert into Employeedetails values ('4444','Ronaldo','1111')
insert into Employeedetails values ('5555','hayden','1111')
insert into Employeedetails values ('6666','Sachin','1111')
insert into Employeedetails values ('7777','Ganguly','1111')
insert into Employeedetails values ('8888','Ponting','1111')
insert into Employeedetails values ('9999','Rohit','1111')
insert into Employeedetails values ('1000','Kapil','1111')
insert into Employeedetails values ('2000','Federer','1111')
insert into Employeedetails values ('3000','Nadal','1111')
insert into Employeedetails values ('4000','Sarena','1111')
insert into Employeedetails values ('5000','Venus','1111')
insert into Employeedetails values ('6000','Sania','1111')
insert into Employeedetails values ('7000','Ali','1111')
insert into Employeedetails values ('8000','Tyson','1111')
insert into Employeedetails values ('9000','Vinoth','1111')
=======================
SET IDENTITY_INSERT leavestatusmaster ON
INSERT leavestatusmaster(LeaveStatusID,LeaveStatusName,LeaveStatusDescription) VALUES('1','Apply','Apply Leave')
INSERT leavestatusmaster(LeaveStatusID,LeaveStatusName,LeaveStatusDescription) VALUES('2','Withdrawn','Withdrawn')
INSERT leavestatusmaster(LeaveStatusID,LeaveStatusName,LeaveStatusDescription) VALUES('3','OnHold','OnHold')
INSERT leavestatusmaster(LeaveStatusID,LeaveStatusName,LeaveStatusDescription) VALUES('4','Approved','Approved')
INSERT leavestatusmaster(LeaveStatusID,LeaveStatusName,LeaveStatusDescription) VALUES('5','Rejected','Rejected')
INSERT leavestatusmaster(LeaveStatusID,LeaveStatusName,LeaveStatusDescription) VALUES('6','ApplyCancel','ApplyCancel')
==========================
INSERT leavetypes(TypeName,ApplicableGenders,Abbrivation) VALUES('Sick Leave','2','SL')
INSERT leavetypes(TypeName,ApplicableGenders,Abbrivation) VALUES('Casual Leave','2','CL')
INSERT leavetypes(TypeName,ApplicableGenders,Abbrivation) VALUES('Loss of Pay','2','LOP')
SET IDENTITY_INSERT leavetypes OFF
===========================
--select * from leavedetails where leaveruleid=1 and employeeloginname='1111'
insert into leavedetails values('1111',1,'2019/01/14','1','2019/01/14','2',0,0,'1','Going to home town','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('1111',1,'2019/01/16','1','2019/01/16','2',0,0,'1','Going to home town','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('1111',1,'2019/01/28','1','2019/01/28','2',0,0,'1','went to home town','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('1111',1,'2019/02/25','1','2019/02/25','2',0,0,'1','Fever','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('1111',1,'2019/04/29','1','2019/04/29','2',0,0,'1','went to home town','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('1111',1,'2019/05/21','1','2019/05/21','2',0,0,'1','Sick leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('1111',1,'2019/07/01','1','2019/07/01','2',0,0,'1','Sick Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('1111',1,'2019/07/22','1','2019/07/22','2',0,0,'1','Sick leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('1111',1,'2019/08/12','1','2019/08/14','2',0,0,'3','Sick Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('1111',1,'2019/10/03','1','2019/10/03','2',0,0,'1','Sick leave','',NULL,NULL,0,0,NULL,NULL)
--select * from leavestatusupdates
--Leaave Apply Status - 1 from Leave Status Master--
insert into leavestatusupdates values (1,1,'1111',getdate(),'Apply')
insert into leavestatusupdates values (2,1,'1111',getdate(),'Apply')
insert into leavestatusupdates values (3,1,'1111',getdate(),'Apply')
insert into leavestatusupdates values (4,1,'1111',getdate(),'Apply')
insert into leavestatusupdates values (5,1,'1111',getdate(),'Apply')
insert into leavestatusupdates values (6,1,'1111',getdate(),'Apply')
insert into leavestatusupdates values (7,1,'1111',getdate(),'Apply')
insert into leavestatusupdates values (8,1,'1111',getdate(),'Apply')
insert into leavestatusupdates values (9,1,'1111',getdate(),'Apply')
insert into leavestatusupdates values (10,1,'1111',getdate(),'Apply')
--Leaave Approve Status -4 from Leave Status Master--
insert into leavestatusupdates values (1,4,'1111',getdate(),'Approved')
insert into leavestatusupdates values (2,4,'1111',getdate(),'Approved')
insert into leavestatusupdates values (3,4,'1111',getdate(),'Approved')
insert into leavestatusupdates values (4,4,'1111',getdate(),'Approved')
insert into leavestatusupdates values (5,4,'1111',getdate(),'Approved')
insert into leavestatusupdates values (6,4,'1111',getdate(),'Approved')
insert into leavestatusupdates values (7,4,'1111',getdate(),'Approved')
insert into leavestatusupdates values (8,4,'1111',getdate(),'Approved')
insert into leavestatusupdates values (9,4,'1111',getdate(),'Approved')
insert into leavestatusupdates values (10,4,'1111',getdate(),'Approved')
=====================================================================
insert into leavedetails values('2222',1,'2019/01/02','1','2019/01/04','2',0,0,'3','unwell','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('2222',1,'2019/02/13','1','2019/02/14','2',0,0,'2','unwell','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('2222',1,'2019/07/11','1','2019/07/11','2',0,0,'1','personal work','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('2222',1,'2019/07/08','1','2019/07/08','2',0,0,'1','unwell','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('2222',1,'2019/07/10','1','2019/07/12','2',0,0,'3','unwell','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('2222',1,'2019/07/15','1','2019/07/15','2',0,0,'1','unwell','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('2222',1,'2019/08/14','1','2019/08/14','2',0,0,'1','unwell','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('2222',1,'2019/08/28','1','2019/08/28','2',0,0,'1','unwell','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('2222',1,'2019/09/12','1','2019/09/12','2',0,0,'1','unwell down with fever','',NULL,NULL,0,0,NULL,NULL)
--select * from leavestatusupdates where leavedetailsid >= 11
--Leaave Apply Status - 1 from Leave Status Master--
insert into leavestatusupdates values (11,1,'2222',getdate(),'Apply')
insert into leavestatusupdates values (12,1,'2222',getdate(),'Apply')
insert into leavestatusupdates values (13,1,'2222',getdate(),'Apply')
insert into leavestatusupdates values (14,1,'2222',getdate(),'Apply')
insert into leavestatusupdates values (15,1,'2222',getdate(),'Apply')
insert into leavestatusupdates values (16,1,'2222',getdate(),'Apply')
insert into leavestatusupdates values (17,1,'2222',getdate(),'Apply')
insert into leavestatusupdates values (18,1,'2222',getdate(),'Apply')
insert into leavestatusupdates values (19,1,'2222',getdate(),'Apply')
--Leaave Approve Status -4 withdrawn status 2 from Leave Status Master--
insert into leavestatusupdates values (11,4,'2222',getdate(),'Approved')
insert into leavestatusupdates values (12,4,'2222',getdate(),'Approved')
insert into leavestatusupdates values (13,4,'2222',getdate(),'Approved')
insert into leavestatusupdates values (14,4,'2222',getdate(),'Approved')
insert into leavestatusupdates values (15,4,'2222',getdate(),'Approved')
insert into leavestatusupdates values (16,4,'2222',getdate(),'Approved')
insert into leavestatusupdates values (17,4,'2222',getdate(),'Approved')
insert into leavestatusupdates values (18,2,'2222',getdate(),'Withdrawn')
insert into leavestatusupdates values (19,2,'2222',getdate(),'Withdrawn')
==========================
--LOP Leave--
insert into leavedetails values('1000',3,'2019/05/14','1','2019/06/12','2',0,0,'29','LOP','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('1111',3,'2019-09-16 00:00:00.000','1','2019-10-01 00:00:00.000','2',0,0,'16','LOP','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('3000',3,'2019-10-03 00:00:00.000','1','2019-10-07 00:00:00.000','2',0,0,'5','LOP','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('3000',3,'2019-10-09 00:00:00.000','1','2019-10-11 00:00:00.000','2',0,0,'3','LOP','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('4000',3,'2019-08-01 00:00:00.000','1','2019-08-01 00:00:00.000','2',0,0,'1','LOP','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('4000',3,'2019-08-26 00:00:00.000','1','2019-08-26 00:00:00.000','2',0,0,'1','LOP','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('4000',3,'2019-09-20 00:00:00.000','1','2019-09-20 00:00:00.000','2',0,0,'1','LOP','',NULL,NULL,0,0,NULL,NULL)
insert into leavestatusupdates values (20,1,'1000',getdate(),'Apply')
insert into leavestatusupdates values (21,1,'1111',getdate(),'Apply')
insert into leavestatusupdates values (22,1,'3000',getdate(),'Apply')
insert into leavestatusupdates values (23,1,'3000',getdate(),'Apply')
insert into leavestatusupdates values (24,1,'4000',getdate(),'Apply')
insert into leavestatusupdates values (25,1,'4000',getdate(),'Apply')
insert into leavestatusupdates values (26,1,'4000',getdate(),'Apply')
insert into leavestatusupdates values (20,4,'1000',getdate(),'Approved')
insert into leavestatusupdates values (21,4,'1111',getdate(),'Approved')
insert into leavestatusupdates values (22,4,'3000',getdate(),'Approved')
insert into leavestatusupdates values (23,4,'3000',getdate(),'Approved')
insert into leavestatusupdates values (24,4,'4000',getdate(),'Approved')
insert into leavestatusupdates values (25,4,'4000',getdate(),'Approved')
insert into leavestatusupdates values (26,4,'4000',getdate(),'Approved')
==========================
--Causl Leave--
--select * from leavestatusupdates where leavestatusupdateid >= 39
delete from leavestatusupdates where leavestatusupdateid >= 39
--select * from leavetypes
--select * from employeedetails
--select * from leavedetails where leaveruleid=2
insert into leavedetails values('5555',1,'2019/10/03','1','2019/10/03','2',0,0,'1','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('5555',1,'2019/10/07','1','2019/10/07','2',0,0,'1','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('5555',1,'2019/10/07','1','2019/10/07','2',0,0,'1','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('5555',1,'2019/10/17','1','2019/10/17','1',0,0,'0.5','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('1111',1,'2019/10/25','1','2019/10/25','2',0,0,'1','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('1111',1,'2019/10/29','1','2019/10/29','2',0,0,'1','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('1111',1,'2019/10/22','1','2019/10/22','2',0,0,'1','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('1111',1,'2019/10/09','1','2019/10/11','2',0,0,'3','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('2222',1,'2019/10/09','1','2019/10/09','2',0,0,'1','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('2222',1,'2019/10/03','1','2019/10/03','2',0,0,'1','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('2222',1,'2019/10/21','1','2019/10/22','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('2222',1,'2019/10/30','2','2019/10/30','2',0,0,'0.5','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('3333',1,'2019/10/21','1','2019/10/25','2',0,0,'5','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('3333',1,'2019/10/29','1','2019/10/30','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('3333',1,'2019/10/23','1','2019/10/25','1',0,0,'2.5','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('3333',1,'2019/10/09','1','2019/10/10','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('7777',1,'2019/10/24','1','2019/10/25','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('7777',1,'2019/10/14','2','2019/10/14','2',0,0,'0.5','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('7777',1,'2019/10/03','1','2019/10/04','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('7777',1,'2019/10/04','1','2019/10/07','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('8888',1,'2019/10/11','1','2019/10/14','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('8888',1,'2019/09/03','1','2019/09/05','2',0,0,'3','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('8888',1,'2019/10/30','1','2019/10/31','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('8888',1,'2019/10/07','1','2019/10/10','2',0,0,'3','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('9999',1,'2019/10/25','1','2019/10/30','2',0,0,'3','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('9999',1,'2019/09/30','1','2019/10/01','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('9999',1,'2019/10/09','1','2019/10/11','2',0,0,'3','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('9999',1,'2019/11/08','1','2019/11/12','1',0,0,'2.5','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('4000',1,'2019/09/03','1','2019/09/04','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('4000',1,'2019/11/06','1','2019/11/07','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('4000',1,'2019/10/18','1','2019/10/18','1',0,0,'0.5','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('4000',1,'2019/10/03','1','2019/10/04','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('5000',1,'2019/09/19','1','2019/09/20','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('5000',1,'2019/10/30','1','2019/10/31','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('5000',1,'2019/11/25','2','2019/11/25','2',0,0,'0.5','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('5000',1,'2019/11/07','2','2019/11/07','2',0,0,'0.5','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('6000',1,'2019/09/04','1','2019/09/05','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('6000',1,'2019/10/29','1','2019/10/30','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('6000',1,'2019/10/10','1','2019/10/10','1',0,0,'0.5','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
insert into leavedetails values('6000',1,'2019/10/17','1','2019/10/18','2',0,0,'2','Casual Leave','',NULL,NULL,0,0,NULL,NULL)
--select * from leavedetails and leaveruleid=7
insert into leavestatusupdates values (27,1,'5555',getdate(),'Apply')
insert into leavestatusupdates values (28,1,'5555',getdate(),'Apply')
insert into leavestatusupdates values (29,1,'5555',getdate(),'Apply')
insert into leavestatusupdates values (30,1,'5555',getdate(),'Apply')
insert into leavestatusupdates values (31,1,'1111',getdate(),'Apply')
insert into leavestatusupdates values (32,1,'1111',getdate(),'Apply')
insert into leavestatusupdates values (33,1,'1111',getdate(),'Apply')
insert into leavestatusupdates values (34,1,'1111',getdate(),'Apply')
insert into leavestatusupdates values (35,1,'2222',getdate(),'Apply')
insert into leavestatusupdates values (36,1,'2222',getdate(),'Apply')
insert into leavestatusupdates values (37,1,'2222',getdate(),'Apply')
insert into leavestatusupdates values (38,1,'2222',getdate(),'Apply')
insert into leavestatusupdates values (39,1,'3333',getdate(),'Apply')
insert into leavestatusupdates values (40,1,'3333',getdate(),'Apply')
insert into leavestatusupdates values (41,1,'3333',getdate(),'Apply')
insert into leavestatusupdates values (42,1,'3333',getdate(),'Apply')
insert into leavestatusupdates values (43,1,'7777',getdate(),'Apply')
insert into leavestatusupdates values (44,1,'7777',getdate(),'Apply')
insert into leavestatusupdates values (45,1,'7777',getdate(),'Apply')
insert into leavestatusupdates values (46,1,'7777',getdate(),'Apply')
insert into leavestatusupdates values (47,1,'8888',getdate(),'Apply')
insert into leavestatusupdates values (48,1,'8888',getdate(),'Apply')
insert into leavestatusupdates values (49,1,'8888',getdate(),'Apply')
insert into leavestatusupdates values (50,1,'8888',getdate(),'Apply')
insert into leavestatusupdates values (51,1,'9999',getdate(),'Apply')
insert into leavestatusupdates values (52,1,'9999',getdate(),'Apply')
insert into leavestatusupdates values (53,1,'9999',getdate(),'Apply')
insert into leavestatusupdates values (54,1,'9999',getdate(),'Apply')
insert into leavestatusupdates values (55,1,'4000',getdate(),'Apply')
insert into leavestatusupdates values (56,1,'4000',getdate(),'Apply')
insert into leavestatusupdates values (57,1,'4000',getdate(),'Apply')
insert into leavestatusupdates values (58,1,'4000',getdate(),'Apply')
insert into leavestatusupdates values (59,1,'5000',getdate(),'Apply')
insert into leavestatusupdates values (60,1,'5000',getdate(),'Apply')
insert into leavestatusupdates values (61,1,'5000',getdate(),'Apply')
insert into leavestatusupdates values (62,1,'5000',getdate(),'Apply')
insert into leavestatusupdates values (63,1,'6000',getdate(),'Apply')
insert into leavestatusupdates values (64,1,'6000',getdate(),'Apply')
insert into leavestatusupdates values (65,1,'6000',getdate(),'Apply')
insert into leavestatusupdates values (66,1,'6000',getdate(),'Apply')
insert into leavestatusupdates values (27,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (28,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (29,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (30,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (31,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (32,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (33,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (34,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (35,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (36,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (37,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (38,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (39,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (40,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (41,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (42,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (43,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (44,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (45,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (46,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (47,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (48,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (49,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (50,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (51,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (52,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (53,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (54,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (55,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (56,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (57,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (58,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (59,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (60,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (61,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (62,4,'HR',getdate(),'Approved')
insert into leavestatusupdates values (63,4,'HR',getdate(),'Approved')
Sample Query to get Total Availed Leaves for the Calendar Year Change Leave rule id to
view different types of leave.
SELECT EOD.EmployeeLoginName, EOD.EmployeeName,
(SELECT CAST(isnull(SUM(NoofDays),0) AS VARCHAR(10)) from LeaveDetails LD
inner join LeaveStatusupdates LSU on LD.LeaveDetailsID = LSU.LeaveDetailsID and LD.EmployeeLoginName = EOD.EmployeeLoginName
and LSU.LeaveStatusUpdateID = (select MAX(LeaveStatusUpdateID) from LeaveStatusupdates where LeaveDetailsID = LD.LeaveDetailsID
group by LeaveDetailsID)
and LSU.LeaveStatusID in (4)and leaveruleid=2
WHERE (FromDate<='31 dec 2019' and FromDate>='01 jan 2019') and (ToDate<='31 dec 2019' and ToDate>='01 jan 2019')
) as [Availed During 1 jan 2019 to 31 Dec 2019]
FROM EmployeeDetails EOD
