Optimizing the Sql Query

Question

 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