Hello EveryOne
I am getting issue that this strored procedure is taking long time to execute. Kindly Provide me some solution to optimize this sql query. Thanks for help.
GO
/****** Object: StoredProcedure [dbo].[Usp_AgentTimeSheetReport_byStation] Script Date: 09/18/2018 18:01:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Select * from station
-- [Usp_AgentTimeSheetReport_byStation] '08/03/2018',5
--[Usp_AgentTimeSheetReport_byStation] '09/21/2018',5
ALTER Proc [dbo].[Usp_AgentTimeSheetReport_byStation]
(
--@StartDate datetime,
@PayRollEndDate datetime,
@StationID int
)
as
Begin
Declare @W1SD datetime
Declare @W1ED datetime
Declare @W2SD datetime
Declare @W2ED datetime
SET @W1SD = DATEADD(DD,-13,@PayrollEndDate)
SET @W1ED = DATEADD(DD,-7,@PayrollEndDate)
SET @W2SD = DATEADD(DD,1,@W1ED)
SET @W2ED = @PayrollEndDate
--Get ALl the employees into EmployeeList table vairab;le
Declare @EmployeeList Table
(
EmpID int,
flag bit
)
Declare @EmployeeCount int
Declare @EmpID int
Declare @AgentTimeSheet_temp Table
(
[Date] Datetime,
[Airline Name] varchar(200),
[I/B #] varchar(200),
[O/B #] varchar(200),
[Position] varchar(200),
[Agent Name] varchar(200),
[Start Time] Time,
[End Time] Time,
[Total Time] Time,
[Notes] varchar(max),
flag int
)
Declare @AgentTimeSheet_Final Table
(
[Date] Datetime,
[Airline Name] varchar(200),
[I/B #] varchar(200),
[O/B #] varchar(200),
[Position] varchar(200),
[Agent Name] varchar(200),
[Start Time] Time,
[End Time] Time,
[Total Time] Time,
[Notes] varchar(max),
flag int
)
Declare @TotalTime Table
(
EmpId int,
Week1TotalTime int,
Week2TotalTime int,
AdjustedWeek1TotalTime int,
AdjustedWeek2TotalTime int
)
Insert into @EmployeeList(EmpID,flag)
Select e.Emp_id,0 from Employee e where e.StationId = @StationID and e.Status = 'Active'
SET @EmployeeCount= (Select COUNT(*) from @EmployeeList)
--Select @EmployeeCount
--Do Follwoing for each employee in the list
While (@EmployeeCount>0)
Begin
SET @EmpID = (Select top 1 EL.EmpID from @EmployeeList EL Where el.flag = 0)
Update @EmployeeList SET flag = 1 where EmpID = @EmpID
Print '@EmpID = '+cast(@EmpID as varchar)
Insert into @AgentTimeSheet_temp
Select convert(varchar,fr.Report_Date,101) [Date],ar.Airline_Name [Airline Name],
f.FlightCode [I/B #], f.OutBoundFlightNumber [O/B #], p.Position_Title [Position],EMP_NAME [Agent Name],
cast(convert(varchar(8),START_TIME,108)as Time)[Start Time],
cast(convert(varchar(8),END_TIME,108)as Time) [End Time],
case
when fpm.Is_fourHourBilling = 1 then
Case when ISNULL(dbo.calculateTimeDiff(cast(convert(varchar(8),START_TIME,108)as Time),cast(convert(varchar(8),END_TIME,108)as Time)),0) < MinimumBillingHours then dbo.ConvertMintoTime(MinimumBillingHours)
Else
dbo.ConvertMintoTime(ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),START_TIME,108)as Time),cast(convert(varchar(8),END_TIME,108)as Time)),0))
End
else
dbo.ConvertMintoTime(ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),START_TIME,108)as Time),cast(convert(varchar(8),END_TIME,108)as Time)),0))
end
as [Total Time],
WRITE_UP [Notes],null
from dbo.Flight_Report_Info_Staffing a
Left join Flight_report fr on a.FLIGHT_REPORT_ID = fr.Flight_Report_ID
Left join Flight f on f.FlightId = fr.FlightID
Left Join Airline Ar on ar.AirlineID = f.AirlineId
Left Join Position p on p.PositionID = a.POSITION_ID
Left Join Flight_Position_Mapping FPM on FPM.FlightId = fr.FlightId and fpm.PositionID = a.POSITION_ID
where EMPID =@EmpID --and fr.Report_Date >= @W1SD and fr.Report_Date <= @W2ED and fr.Status = 'CLOSE'
and cast(convert(varchar,fr.Report_Date ,101)as datetime) >=convert(varchar,@W1SD,101)
and cast(convert(varchar,fr.Report_Date ,101) as datetime) <=convert(varchar,@W2ED,101) and fr.Status = 'CLOSE'
--order by Report_Date
Union ALL
SElect
convert(varchar,fr.Report_Date,101) [Date],ar.Airline_Name [Airline Name],
f.FlightCode [Flight Code], f.OutBoundFlightNumber [O/B #], 'Additional Service' [Position]--p.Position_Title [Position]
,EMP_NAME [Agent Name],
cast(convert(varchar(8),frads.StartTime,108)as Time)[Start Time],
cast(convert(varchar(8),frads.EndTime,108)as Time) [End Time],
dbo.ConvertMintoTime(ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),frads.StartTime,108)as Time)
,cast(convert(varchar(8),frads.EndTime,108)as Time)),0))
as [Total Time],
ads.Notes [Notes],null
from
[Flight_report_AS_Agent_mapping] frads
Inner Join Employee e on frads.AgentID = e.Emp_id
inner join Flight_Report_Additional_service ads on frads.asid = ads.asid and frads.FRID = ads.FRID and ads.RqstSrvid = frads.RqstSrvid
Inner join Flight_Report fr on ads.FRID = fr.Flight_Report_ID
Left Join Flight f on f.FlightId = fr.FlightID
Left join Airline ar on f.AirlineId = ar.AirlineID
where frads.AgentID =@EmpID -- and fr.Report_Date >= @W1SD and fr.Report_Date <= @W2ED and fr.Status = 'CLOSE'
and cast(convert(varchar,fr.Report_Date ,101)as datetime) >=convert(varchar,@W1SD,101)
and cast(convert(varchar,fr.Report_Date ,101) as datetime) <=convert(varchar,@W2ED,101) and fr.Status = 'CLOSE'
group by fr.Report_Date, ar.Airline_Name,f.FlightCode,f.OutBoundFlightNumber ,
e.EMP_NAME,frads.StartTime,frads.EndTime, ads.Notes
-- check overlapping records
Declare @RangeStartTime datetime
Declare @RangeEndTIme datetime
Declare @CurrentStartTime datetime
Declare @CurrentEndTime datetime
Declare @CurrentDate datetime
Declare @StartDate datetime
Declare @EndDate datetime
Declare @NextDayOverlappingFlag int
SET @NextDayOverlappingFlag= 0
Create table #ProcessedRecrds
(
id int identity(1,1),
Starttime datetime,
EndTime datetime
)
SET @StartDate = (Select min(date) from @AgentTimeSheet_temp where flag is null)
SET @EndDate = (Select MAX(date) from @AgentTimeSheet_temp where flag is null)
While(@StartDate<=@EndDate)
Begin
Print convert ( varchar,@startDate,101)
Declare @Count int
SET @Count = (Select COUNT(*) from @AgentTimeSheet_temp where Date = @StartDate and flag is null)
While (@Count > 0)
Begin
Print 'Count'+cast(@count as varchar)
Select Top 1 @CurrentStartTime= (cast(Convert(varchar,@StartDate,101)+' '+convert(varchar(8),[Start Time],108)as Datetime)),--[Start Time],
@CurrentEndTime = dbo.ShiftOverlapCheck(convert(varchar(8),[Start Time],108),convert(varchar(8),[End Time],108),Convert(varchar,@StartDate,101))
--[End Time]
from @AgentTimeSheet_temp a where Date = @StartDate and flag is null
order by [Start Time]
Declare @DuplicateFlag int
SET @DuplicateFlag = 0
-- Fixing the duplicate records issue=> start time and end time same
Select @DuplicateFlag=T.Count from (
Select a.[Agent Name],a.Date,a.[Start Time],a.[End Time],COUNT(*) Count
from @AgentTimeSheet_temp a
where @CurrentStartTime= (cast(Convert(varchar,@StartDate,101)+' '+convert(varchar(8),[Start Time],108)as Datetime)) and
@CurrentEndTime = dbo.ShiftOverlapCheck(convert(varchar(8),[Start Time],108),convert(varchar(8),[End Time],108),Convert(varchar,@StartDate,101))
group by a.[Agent Name],a.Date,a.[Start Time],a.[End Time]
having COUNT(*)>1
)T
If(@DuplicateFlag >1)
Begin
WITH cte AS
(
Select ROW_NUMBER() OVER(PARTITION BY a.[Agent Name],a.Date,a.[Start Time],a.[End Time],a.Flag
ORDER BY a.[Agent Name],a.Date,a.[Start Time],a.[End Time],a.flag ) AS rno,
a.[Agent Name],a.Date,a.[Start Time],a.[End Time],a.flag
from @AgentTimeSheet_temp a
where @CurrentStartTime= (cast(Convert(varchar,@StartDate,101)+' '+convert(varchar(8),[Start Time],108)as Datetime)) and
@CurrentEndTime = dbo.ShiftOverlapCheck(convert(varchar(8),[Start Time],108),convert(varchar(8),[End Time],108),Convert(varchar,@StartDate,101))
)
UPDATE cte SET flag = 1
WHERE rno=2
End
If not exists (Select 1 from #ProcessedRecrds)
Begin
Insert into #ProcessedRecrds (Starttime,EndTime)
Select @CurrentStartTime,@CurrentEndTime
If(@NextDayOverlappingFlag = 1 and @CurrentStartTime<@RangeEndTIme)
Begin
Update @AgentTimeSheet_temp
SET flag = 1
where
--@CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time]
@CurrentStartTime= (cast(Convert(varchar,@StartDate,101)+' '+convert(varchar(8),[Start Time],108)as Datetime)) AND --[Start Time],
@CurrentEndTime = dbo.ShiftOverlapCheck(convert(varchar(8),[Start Time],108),convert(varchar(8),[End Time],108),Convert(varchar,@StartDate,101))
And Date = @StartDate and flag is null
SET @NextDayOverlappingFlag = 0
End
Else
Begin
Update @AgentTimeSheet_temp
SET flag = 0
where
--@CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time]
@CurrentStartTime= (cast(Convert(varchar,@StartDate,101)+' '+convert(varchar(8),[Start Time],108)as Datetime)) AND --[Start Time],
@CurrentEndTime = dbo.ShiftOverlapCheck(convert(varchar(8),[Start Time],108),convert(varchar(8),[End Time],108),Convert(varchar,@StartDate,101))
And Date = @StartDate and flag is null
End
SET @RangeStartTime = @CurrentStartTime
SET @RangeEndTIme = @CurrentEndTime
Print '1'
End
Else
Begin
--Declare @c int
--Select @c = MAX(id) from #ProcessedRecrds
--SET @c = ISNULL(@c,0)
--Select @RangeStartTime =Starttime, @RangeEndTIme = EndTime from #ProcessedRecrds where id = @c
--While(@c>0)
--Begin
--Print '@C='+convert(varchar,@c)
--Select @RangeStartTime =Starttime, @RangeEndTIme = EndTime from #ProcessedRecrds where id = @c
if(@CurrentStartTime=@RangeStartTime and @CurrentEndTime = @RangeEndTIme) --Changed
Begin
Update @AgentTimeSheet_temp
SET flag = 1
--where @CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time] And Date = @StartDate and flag is null
where
--@CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time]
@CurrentStartTime= (cast(Convert(varchar,@StartDate,101)+' '+convert(varchar(8),[Start Time],108)as Datetime)) AND --[Start Time],
@CurrentEndTime = dbo.ShiftOverlapCheck(convert(varchar(8),[Start Time],108),convert(varchar(8),[End Time],108),Convert(varchar,@StartDate,101))
And Date = @StartDate and flag is null
--SET @c = 0
Print '2'
--Select * from @AgentTimeSheet_temp
End
Else
Begin
if(@CurrentStartTime<@RangeStartTime and @CurrentEndTime <= @RangeStartTime)
Begin
Update @AgentTimeSheet_temp
SET flag = 0
--where @CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time] And Date = @StartDate and flag is null
where
--@CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time]
@CurrentStartTime= (cast(Convert(varchar,@StartDate,101)+' '+convert(varchar(8),[Start Time],108)as Datetime)) AND --[Start Time],
@CurrentEndTime = dbo.ShiftOverlapCheck(convert(varchar(8),[Start Time],108),convert(varchar(8),[End Time],108),Convert(varchar,@StartDate,101))
And Date = @StartDate and flag is null
Print '3'
SET @RangeStartTime = @CurrentStartTime
--Select * from @AgentTimeSheet_temp
End
Else
Begin
if(@CurrentStartTime>@RangeStartTime and @CurrentStartTime >= @RangeEndTime )
Begin
Update @AgentTimeSheet_temp
SET flag = 0
--where @CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time] And Date = @StartDate and flag is null
where
--@CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time]
@CurrentStartTime= (cast(Convert(varchar,@StartDate,101)+' '+convert(varchar(8),[Start Time],108)as Datetime)) AND --[Start Time],
@CurrentEndTime = dbo.ShiftOverlapCheck(convert(varchar(8),[Start Time],108),convert(varchar(8),[End Time],108),Convert(varchar,@StartDate,101))
And Date = @StartDate and flag is null
Print '4'
--If(@CurrentStartTime = @RangeEndTIme)
SET @RangeEndTIme = @CurrentEndTime
End
Else
Begin
if(@CurrentStartTime<=@RangeStartTime and @CurrentEndTime<@RangeEndTime and @CurrentEndTime > @RangeStartTime )
Begin
Update @AgentTimeSheet_temp
SET flag = 1
--where @CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time] And Date = @StartDate and flag is null
where
--@CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time]
@CurrentStartTime= (cast(Convert(varchar,@StartDate,101)+' '+convert(varchar(8),[Start Time],108)as Datetime)) AND --[Start Time],
@CurrentEndTime = dbo.ShiftOverlapCheck(convert(varchar(8),[Start Time],108),convert(varchar(8),[End Time],108),Convert(varchar,@StartDate,101))
And Date = @StartDate and flag is null
SET @RangeStartTime = @CurrentStartTime
--SET @c = 0
Print '5'
End
Else
Begin
if(@CurrentStartTime>@RangeStartTime and @CurrentStartTime < @RangeEndTime and @CurrentEndTime <= @RangeEndTime)
Begin
Update @AgentTimeSheet_temp
SET flag = 1
--where @CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time] And Date = @StartDate and flag is null
where
--@CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time]
@CurrentStartTime= (cast(Convert(varchar,@StartDate,101)+' '+convert(varchar(8),[Start Time],108)as Datetime)) AND --[Start Time],
@CurrentEndTime = dbo.ShiftOverlapCheck(convert(varchar(8),[Start Time],108),convert(varchar(8),[End Time],108),Convert(varchar,@StartDate,101))
And Date = @StartDate and flag is null
Print '6'
--SET @c = 0
End
Else
Begin
if(@CurrentStartTime>@RangeStartTime and @CurrentStartTime <@RangeEndTime and @CurrentEndTime > @RangeEndTime)
Begin
Update @AgentTimeSheet_temp
SET flag = 1
--where @CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time] And Date = @StartDate and flag is null
where
--@CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time]
@CurrentStartTime= (cast(Convert(varchar,@StartDate,101)+' '+convert(varchar(8),[Start Time],108)as Datetime)) AND --[Start Time],
@CurrentEndTime = dbo.ShiftOverlapCheck(convert(varchar(8),[Start Time],108),convert(varchar(8),[End Time],108),Convert(varchar,@StartDate,101))
And Date = @StartDate and flag is null
--SET @c = 0
SET @RangeEndTIme = @CurrentEndTime
Print '7'
End
Else
Begin
if(@CurrentStartTime<=@RangeStartTime and @CurrentEndTime > @RangeEndTime)
Begin
Update @AgentTimeSheet_temp
SET flag = 1
--where @CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time] And Date = @StartDate and flag is null
where
--@CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time]
@CurrentStartTime= (cast(Convert(varchar,@StartDate,101)+' '+convert(varchar(8),[Start Time],108)as Datetime)) AND --[Start Time],
@CurrentEndTime = dbo.ShiftOverlapCheck(convert(varchar(8),[Start Time],108),convert(varchar(8),[End Time],108),Convert(varchar,@StartDate,101))
And Date = @StartDate and flag is null
--SET @c = 0
SET @RangeEndTIme = @CurrentEndTime
SET @RangeStartTime = @CurrentStartTime
Print '8'
End
Else
Begin
Update @AgentTimeSheet_temp
SET flag = 0
--where @CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time] And Date = @StartDate and flag is null
where
--@CurrentStartTime= [Start Time] And @CurrentEndTime = [End Time]
@CurrentStartTime= (cast(Convert(varchar,@StartDate,101)+' '+convert(varchar(8),[Start Time],108)as Datetime)) AND --[Start Time],
@CurrentEndTime = dbo.ShiftOverlapCheck(convert(varchar(8),[Start Time],108),convert(varchar(8),[End Time],108),Convert(varchar,@StartDate,101))
And Date = @StartDate and flag is null
Print '10'
End
End
End
End
End
End
End
--SET @c = @c-1
--End
Insert into #ProcessedRecrds(Starttime,EndTime)
Select @CurrentStartTime,@CurrentEndTime
If(convert(varchar,@RangeEndTime,101) = CONVERT(varchar,@RangeStartTime+1,101))
SET @NextDayOverlappingFlag = 1
End
SET @Count = (Select COUNT(*) from @AgentTimeSheet_temp where Date = @StartDate and flag is null)
End
Truncate table #ProcessedRecrds
SET @StartDate = (Select min(date) from @AgentTimeSheet_temp where flag is null)
End
Insert into @AgentTimeSheet_Final
Select * from @AgentTimeSheet_temp
Delete from @AgentTimeSheet_temp
drop table #ProcessedRecrds
--Calcaute TotalTime and AdjustedTotalTime of Employee
Declare @Weekl1TotalTime int
Declare @Weekl2TotalTime int
Declare @AdjustedWeekl1TotalTime int
Declare @AdjustedWeekl2TotalTime int
Select @Weekl1TotalTime = isnull(sum(t.Week1TotalTime),0) ,
@Weekl2TotalTime = ISNULL( SUM(t.Week2TotalTime),0)
from(
Select Report_Date,
Case when convert(varchar,fr.Report_Date,101)>=convert(varchar,@W1SD,101) and convert(varchar,fr.Report_Date,101)<=convert(varchar,@W1ED,101) then
ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),START_TIME,108)as Time),cast(convert(varchar(8),END_TIME,108)as Time)),0)End as Week1TotalTime,
Case when convert(varchar,fr.Report_Date,101)>=convert(varchar,@W2SD,101) and convert(varchar,fr.Report_Date,101)<= Convert(varchar,@W2ED,101) then
ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),START_TIME,108)as Time),cast(convert(varchar(8),END_TIME,108)as Time)),0)End as Week2TotalTime
from dbo.Flight_Report_Info_Staffing a
Left join Flight_report fr on a.FLIGHT_REPORT_ID = fr.Flight_Report_ID
Left join Flight f on f.FlightId = fr.FlightID
where EMPID = @EmpID and cast(convert(varchar,fr.Report_Date,101) as datetime) >=convert(varchar,@W1SD,101)
and cast(convert(varchar,fr.Report_Date,101) as datetime) <=convert(varchar,@W2ED,101)
and fr.Status = 'CLOSE'
Union ALL
Select Report_Date,
Case
when convert(varchar,fr.Report_Date,101)>=convert(varchar,@W1SD,101)
and convert(varchar,fr.Report_Date,101)<=convert(varchar,@W1ED,101) then
--ISNULL( ABS(DATEDIFF(mi,cast(convert(varchar(8),START_TIME,108)as Time),cast(convert(varchar(8),END_TIME,108)as Time))),0) End as Week1TotalTime,
ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),frads.StartTime,108)as Time)
,cast(convert(varchar(8),frads.EndTime,108)as Time)),0)End as Week1TotalTime,
Case
when convert(varchar,fr.Report_Date,101)>=convert(varchar,@W2SD,101)
and convert(varchar,fr.Report_Date,101)<= Convert(varchar,@W2ED,101) then
--ISNULL( ABS(DATEDIFF(mi,cast(convert(varchar(8),START_TIME,108)as Time),cast(convert(varchar(8),END_TIME,108)as Time))),0) End as Week2TotalTime
ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),frads.StartTime,108)as Time)
,cast(convert(varchar(8),frads.EndTime,108)as Time)),0)End as Week2TotalTime
from
[Flight_report_AS_Agent_mapping] frads
--Inner Join Employee e on frads.AgentID = e.Emp_id
inner join Flight_Report_Additional_service ads on frads.asid = ads.asid and frads.FRID = ads.FRID
Inner join Flight_Report fr on ads.FRID = fr.Flight_Report_ID
--Left Join Flight f on f.FlightId = fr.FlightID
--Left join Airline ar on f.AirlineId = ar.AirlineID
where frads.AgentID =@EmpID and cast(convert(varchar,fr.Report_Date ,101)as datetime) >=convert(varchar,@W1SD,101)
and cast(convert(varchar,fr.Report_Date ,101) as datetime) <=convert(varchar,@W2ED,101)and fr.Status = 'CLOSE'
group by fr.Report_date, frads.StartTime, frads.EndTime
)
t
Select @AdjustedWeekl1TotalTime = isnull(sum(t2.AdjustedWeek1TotalTime),0),
@AdjustedWeekl2TotalTime = ISNULL( SUM(t2.AdjustedWeek2TotalTime),0)
from(
Select Report_Date,
Case when cast(convert(varchar,fr.Report_Date,101) AS datetime)>=convert(varchar,@W1SD,101)
and cast(convert(varchar,fr.Report_Date,101) AS datetime) <=convert(varchar,@W1ED,101) then
case
when fpm.Is_fourHourBilling = 1 then
Case when ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),START_TIME,108)as Time),cast(convert(varchar(8),END_TIME,108)as Time)),0) < MinimumBillingHours then MinimumBillingHours
Else
ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),START_TIME,108)as Time),cast(convert(varchar(8),END_TIME,108)as Time)),0)
End
else
ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),START_TIME,108)as Time),cast(convert(varchar(8),END_TIME,108)as Time)),0)
end
end as AdjustedWeek1TotalTime,
Case when cast(convert(varchar,fr.Report_Date,101) AS datetime)>=convert(varchar,@W2SD,101)
and cast(convert(varchar,fr.Report_Date,101) AS datetime)<= Convert(varchar,@W2ED,101) then
case
when fpm.Is_fourHourBilling = 1 then
Case when ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),START_TIME,108)as Time),cast(convert(varchar(8),END_TIME,108)as Time)),0) < MinimumBillingHours then MinimumBillingHours
Else
ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),START_TIME,108)as Time),cast(convert(varchar(8),END_TIME,108)as Time)),0)
End
else
ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),START_TIME,108)as Time),cast(convert(varchar(8),END_TIME,108)as Time)),0)
end
end as AdjustedWeek2TotalTime
from dbo.Flight_Report_Info_Staffing a
Left join Flight_report fr on a.FLIGHT_REPORT_ID = fr.Flight_Report_ID
Left join Flight f on f.FlightId = fr.FlightID
LEFT Join Flight_Position_Mapping fpm on fpm.FlightId = f.FlightId
and fpm.PositionID = a.POSITION_ID
where EMPID = @EmpID and cast(convert(varchar,fr.Report_Date,101) AS datetime) >=convert(varchar,@W1SD,101)
and cast(convert(varchar,fr.Report_Date,101) AS datetime) <=convert(varchar,@W2ED,101)and fr.Status = 'CLOSE'
Union ALL
Select Report_Date,
Case when cast(convert(varchar,fr.Report_Date,101) AS datetime )>=convert(varchar,@W1SD,101)
and cast(convert(varchar,fr.Report_Date,101) AS datetime )<=convert(varchar,@W1ED,101) then
ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),frads.StartTime,108)as Time)
,cast(convert(varchar(8),frads.EndTime,108)as Time)),0)
end as AdjustedWeek1TotalTime,
Case when cast(convert(varchar,fr.Report_Date,101) AS datetime )>=convert(varchar,@W2SD,101)
and cast(convert(varchar,fr.Report_Date,101) AS datetime )<= Convert(varchar,@W2ED,101)
then
ISNULL( dbo.calculateTimeDiff(cast(convert(varchar(8),frads.StartTime,108)as Time)
,cast(convert(varchar(8),frads.EndTime,108)as Time)),0)
end as AdjustedWeek2TotalTime
from
[Flight_report_AS_Agent_mapping] frads
--Inner Join Employee e on frads.AgentID = e.Emp_id
inner join Flight_Report_Additional_service ads on frads.asid = ads.asid and frads.FRID = ads.FRID
Inner join Flight_Report fr on ads.FRID = fr.Flight_Report_ID
--Left Join Flight f on f.FlightId = fr.FlightID
--Left join Airline ar on f.AirlineId = ar.AirlineID
where frads.AgentID =@EmpID and cast(convert(varchar,fr.Report_Date,101) AS datetime ) >=convert(varchar,@W1SD,101)
and cast(convert(varchar,fr.Report_Date,101) AS datetime ) <=convert(varchar,@W2ED,101)and fr.Status = 'CLOSE'
group by fr.Report_Date, frads.StartTime,frads.EndTime
)
t2
Insert into @TotalTime(EmpId,Week1TotalTime,Week2TotalTime,AdjustedWeek1TotalTime,AdjustedWeek2TotalTime)
Select @EmpID,@Weekl1TotalTime,@Weekl2TotalTime,@AdjustedWeekl1TotalTime,@AdjustedWeekl2TotalTime
SET @EmployeeCount = @EmployeeCount-1
End
--Select @PayrollEndDate,@W1SD,@W1ED,@W2SD,@W2ED
--Overlapping ends here
Select * from @AgentTimeSheet_Final
order by [Agent Name],[Date],[Start Time],[Airline Name]
Select
dbo.ConvertMintoTime(sum(t.Week1TotalTime))as Week1TotalTime ,
dbo.ConvertMintoTime(sum(t.Week2TotalTime)) as Week2TotalTime --,
--dbo.ConvertMintoTime(sum(t.AdjustedWeek1TotalTime)) AdjustedWeek1TotalTime ,
--dbo.ConvertMintoTime(sum(t.AdjustedWeek2TotalTime)) AdjustedWeek2TotalTime
from @TotalTime t
Select
--dbo.ConvertMintoTime(sum(t.Week1TotalTime))as Week1TotalTime ,
--dbo.ConvertMintoTime(sum(t.Week2TotalTime)) as Week2TotalTime ,
dbo.ConvertMintoTime(sum(t.AdjustedWeek1TotalTime)) AdjustedWeek1TotalTime ,
dbo.ConvertMintoTime(sum(t.AdjustedWeek2TotalTime)) AdjustedWeek2TotalTime
from @TotalTime t
End
--Select *
--from dbo.Flight_Report_Info_Staffing a
--Left join Flight_report fr on a.FLIGHT_REPORT_ID = fr.Flight_Report_ID
--Left join Flight f on f.FlightId = fr.FlightID
--where EMPID = 24
Nitin SontakkePosted Sep 19, 2018, 1:51 AM