Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 366

# SQL - Simplify calculation

Hi, I have the following calculation in my query:

TotalHours = (CAST(datediff(minute, MIN(ClockIn), MAX(ClockOut)) /  60.0 as decimal(4,2)),
(datediff(second, ClockIn,  case when datediff(hh,0,ClockIn)%24 < datepart(hour,
DefaultStart)  AND  datediff(hh,0,ClockOut)%24 >= datepart(hour, DefaultStart) THEN
dateadd(hh,(datediff(hh,0,ClockOut)/24)*  24 +  datepart(hour, DefaultStart),0) end)), 0),
108), 1,2) + '.' + Cast((CAST(substring(convert(varchar(10),  dateadd(second, sum(datediff
(second, ClockIn,  case when datediff(hh,0,ClockIn)%24 < datepart(hour, DefaultStart)
AND datediff(hh,0,ClockOut)%24 >= datepart(hour, DefaultStart)  THEN dateadd(hh,(datediff
(hh,0,ClockOut)/24)* 24 +  datepart(hour, DefaultStart),0) end)), 0), 108), 4,2) as Int) * 10)/6
as varchar(4)) as decimal(4,2)),0),
HoursDuring = ISNULL(Cast(substring(convert
(varchar(10), dateadd(second, sum(datediff(second,   case WHEN datediff(hh,0,ClockIn)%
24 < datepart(hour, DefaultBreakstart) AND datediff(hh,0,ClockOut)%24  >=  datepart(hour,
DefaultBreakstart),0)  WHEN datediff(hh,0,ClockIn)%24 = datepart(hour, DefaultBreakstart)
AND datediff(hh,0,ClockOut)%24 >=  datepart(hour, DefaultBreakstart) THEN ClockIn END,
case WHEN datediff(hh,0,ClockIn)%24 <  datepart(hour, DefaultBreakEnd)  AND  datediff
(hh,0,ClockOut)%24 > datepart(hour, DefaultBreakstart)  THEN dateadd(hh,(datediff
(hh,0,ClockOut)/24)*24 +  datepart(hour, DefaultBreakEnd),0)  WHEN datediff(hh,0,ClockIn)%
24 < datepart(hour, DefaultBreakEnd)  AND   datediff(hh,0,ClockOut)%24 = datepart(hour,
DefaultBreakstart) THEN Clockout end)), 0), 108), 1,2) + '.' +  Cast((Cast(substring(convert
(varchar(10),  dateadd(second, sum(datediff(second, case WHEN  datediff(hh,0,ClockIn)%
24 < datepart(hour, DefaultBreakstart)  AND datediff(hh,0,ClockOut)%24 >=  datepart(hour,
DefaultBreakstart),0)  WHEN datediff(hh,0,ClockIn)%24 = datepart(hour, DefaultBreakstart)
AND datediff(hh,0,ClockOut)%24 >= datepart(hour, DefaultBreakstart) THEN ClockIn END,
case WHEN datediff(hh,0,ClockIn)%24 < datepart(hour, DefaultBreakEnd) AND datediff
(hh,0,ClockOut)%24 >  datepart(hour, DefaultBreakstart)  THEN dateadd(hh,(datediff
(hh,0,ClockOut)/24)*24 +  datepart(hour, DefaultBreakEnd),0)  WHEN datediff(hh,0,ClockIn)%
24 < datepart(hour, DefaultBreakEnd)   AND datediff(hh,0,ClockOut)%24 = datepart(hour,
DefaultBreakstart) THEN Clockout end)), 0), 108), 4,2) as Int)  * 10)/6  as varchar(4)) as
decimal(4,2)),0),
HoursAfter = ISNULL(Cast(substring(convert(varchar(10),
dateadd(second, sum(datediff(second,   case when DATEDIFF(hh,0,ClockIn)%24 < DatePart
(hour, DefaultEnd)  AND DATEDIFF(hh,0,ClockOut)%24 >=  DatePart(hour, DefaultEnd)
THEN DATEADD(hh,(DATEDIFF(hh,0,ClockIn)/24)* 24 +  DatePart(hour, DefaultEnd),0) END  ,
ClockOut)), 0), 108), 1,2) + '.' +  Cast((Cast(substring(convert(varchar(10),  dateadd
(second,  sum(datediff(second,  case when DATEDIFF(hh,0,ClockIn)%24 < DatePart(hour,
DefaultEnd)  AND  DATEDIFF(hh,0,ClockOut)%24 >= DatePart(hour, DefaultEnd)  THEN
DATEADD(hh,(DATEDIFF(hh,0,ClockIn)/24)* 24 +  DatePart(hour, DefaultEnd),0)  END,
ClockOut)), 0), 108), 4,2) as Int) * 10)/6  as varchar(4)) as decimal(4,2)),0)

This give me a result of :

TotalHours          Hours_Before        Hours_During         Hours_After
11.47                     0.6                         0.1                           0.89

Now, I need to change the TotalHours to be : TotalHours - (Hours_Before + Hours_During + Hours_After)

Do I have to take the entire calculation of Hours_Before, Hours_During and Hours_After for my calculation?  Is there an easier way?
0
NerishaB
1 Solution

Chief Database ArchitectCommented:
use a stored procedure
save the resolts to variables.

the just use tha variable for any calculation.
0

Commented:
Create a stored procedure like below....

Declare TotalHours Decimal(18,2)
Declare Hours_Before Decimal(18,2)
Declare Hours_During Decimal(18,2)
Declare Hours_After Decimal(18,2)
Declare FinalCalculation Decimal(18,2)

Set FinalCalculation =  TotalHours - (Hours_Before + Hours_During + Hours_After)

print FinalCalculation

0

Commented:
Put the entire query in Comman table expression CTE and then perfom the calculation.

``````WITH CTE
AS
(
)

SELECT
*,
TotalHours - (Hours_Before + Hours_During + Hours_After) [HrsCalculation]
FROM CTE
``````
0

Author Commented:

Please give me a basic example of how to do this?
0

Commented:
Just palce your all the select query inside the CTE like below
``````WITH CTE
AS
(
SELECT
TotalHours = 11.47  ,
Hours_Before = 0.6,
Hours_During  = 0.1   ,
Hours_After = 0.89
FROM Tbla
)

SELECT
*,
TotalHours - (Hours_Before + Hours_During + Hours_After) [HrsCalculation]
FROM CTE
``````
0

Author Commented:
It's not that simple.  See attached, my entire code:

Can you help me with the attached code
``````; with cte as (select Pers.Name + ' ' + Pers.Surname As FullName, RC.ClockTime, Dir.Name,
Convert(varchar,ShiftRulesGroup.Default_Shift_End,8) AS DefaultShiftEnd,
Convert(varchar,ShiftRulesGroup.Default_Shift_Start,8) AS DefaultShiftStart,
Convert(varchar,SRBreak.SR_Break_StartTime,8) AS BreakStart,
Convert(varchar,SRBreak.SR_Break_EndTime,8) AS BreakEnd,
row_no = row_number()  over (partition by Pers.Name + ' ' + Pers.Surname order by RC.ClockTime)
from Personnel Pers INNER JOIN RawClocks RC ON Pers.Personel_Id = RC.Person_ID
INNER JOIN Direction Dir ON RC.Direction_ID = Dir.Direction_ID
INNER JOIN Emp_ShiftGroup EmpShift ON Pers.Personel_ID = EmpShift.Employee_ID
INNER JOIN Rules_ShiftRulesGroup ShiftRulesGroup ON  ShiftRulesGroup.ShiftRulesGroup_ID =  EmpShift.ShiftRulesGroup_ID
INNER JOIN Rules_SRDay SRDay ON ShiftRulesGroup.ShiftRulesGroup_ID =  SRDay.ShiftRulesGroup_ID
And srday.cycledayno=  EmpShift.StartDate_CycleDayNo + (day(RC.ClockTime -  EmpShift.EmpSR_StartDate)  % ShiftRulesGroup.SRShiftCycle_Days)-1
LEFT OUTER JOIN Rules_SRBreak SRBreak ON SRBreak.SRDay_ID = SRDay.SRDay_ID),
Cte2 as (
select c1.FullName, ClockIn = c1.ClockTime, ClockOut = case when c2.Name = 'OUT'  then c2.ClockTime else  dateadd(day, datediff(day, 0, c1.ClockTime), 0) +  DateAdd(dd, DateDiff(dd, 0, c1.DefaultShiftEnd), 0) end,  DefaultStart = c1.DefaultShiftStart, DefaultEnd = c1.DefaultShiftEnd,  DefaultBreakstart = c1.BreakStart,  DefaultBreakEnd = c1.BreakEnd
from cte c1 left join cte c2 on c1.row_no = c2.row_no - 1  and  c1.FullName = c2.FullName  where c1.Name = 'IN')  select FullName, Date =  convert(nvarchar(10),ClockIn, 111),  MIN(ClockIn) As Calc_first_clock,  MAX(ClockOut) As Calc_last_clock,
TotalHours_Worked = CAST(datediff(minute, MIN(ClockIn), MAX(ClockOut)) /  60.0 as decimal(4,2)),
Hours_Worked_Before = isnull(Cast(substring(convert(varchar(10),  dateadd(second,  sum(datediff(second, ClockIn,  case when datediff(hh,0,ClockIn)%24 < datepart(hour, DefaultStart)  AND  datediff(hh,0,ClockOut)%24 >= datepart(hour, DefaultStart) THEN dateadd(hh,(datediff(hh,0,ClockOut)/24)*  24 +  datepart(hour, DefaultStart),0) end)), 0), 108), 1,2) + '.' + Cast((CAST(substring(convert(varchar(10),  dateadd(second, sum(datediff(second, ClockIn,  case when datediff(hh,0,ClockIn)%24 < datepart(hour, DefaultStart)  AND datediff(hh,0,ClockOut)%24 >= datepart(hour, DefaultStart)  THEN dateadd(hh,(datediff(hh,0,ClockOut)/24)* 24 +  datepart(hour, DefaultStart),0) end)), 0), 108), 4,2) as Int) * 10)/6  as varchar(4)) as decimal(4,2)),0),
Hours_Worked_During = ISNULL(Cast(substring(convert(varchar(10), dateadd(second, sum(datediff(second,   case WHEN datediff(hh,0,ClockIn)%24 < datepart(hour, DefaultBreakstart) AND datediff(hh,0,ClockOut)%24  >=  datepart(hour, DefaultBreakstart)  THEN dateadd(hh,(datediff(hh,0,ClockIn)/24)*24 +  datepart(hour, DefaultBreakstart),0)  WHEN datediff(hh,0,ClockIn)%24 = datepart(hour, DefaultBreakstart)  AND datediff(hh,0,ClockOut)%24 >=  datepart(hour, DefaultBreakstart) THEN ClockIn END,  case WHEN datediff(hh,0,ClockIn)%24 <  datepart(hour, DefaultBreakEnd)  AND  datediff(hh,0,ClockOut)%24 > datepart(hour, DefaultBreakstart)  THEN dateadd(hh,(datediff(hh,0,ClockOut)/24)*24 +  datepart(hour, DefaultBreakEnd),0)  WHEN datediff(hh,0,ClockIn)%24 < datepart(hour, DefaultBreakEnd)  AND   datediff(hh,0,ClockOut)%24 = datepart(hour,  DefaultBreakstart) THEN Clockout end)), 0), 108), 1,2) + '.' +  Cast((Cast(substring(convert(varchar(10),  dateadd(second, sum(datediff(second, case WHEN  datediff(hh,0,ClockIn)%24 < datepart(hour, DefaultBreakstart)  AND datediff(hh,0,ClockOut)%24 >=  datepart(hour, DefaultBreakstart)  THEN dateadd(hh,(datediff(hh,0,ClockIn)/24)*24 +   datepart(hour, DefaultBreakstart),0)  WHEN datediff(hh,0,ClockIn)%24 = datepart(hour, DefaultBreakstart)  AND datediff(hh,0,ClockOut)%24 >= datepart(hour, DefaultBreakstart) THEN ClockIn END,  case WHEN datediff(hh,0,ClockIn)%24 < datepart(hour, DefaultBreakEnd) AND datediff(hh,0,ClockOut)%24 >  datepart(hour, DefaultBreakstart)  THEN dateadd(hh,(datediff(hh,0,ClockOut)/24)*24 +  datepart(hour, DefaultBreakEnd),0)  WHEN datediff(hh,0,ClockIn)%24 < datepart(hour, DefaultBreakEnd)   AND datediff(hh,0,ClockOut)%24 = datepart(hour, DefaultBreakstart) THEN Clockout end)), 0), 108), 4,2) as Int)  * 10)/6  as varchar(4)) as decimal(4,2)),0),
Hours_Worked_AfterShift = ISNULL(Cast(substring(convert(varchar(10),  dateadd(second, sum(datediff(second,   case when DATEDIFF(hh,0,ClockIn)%24 < DatePart(hour, DefaultEnd)  AND DATEDIFF(hh,0,ClockOut)%24 >=  DatePart(hour, DefaultEnd)  THEN DATEADD(hh,(DATEDIFF(hh,0,ClockIn)/24)* 24 +  DatePart(hour, DefaultEnd),0) END  , ClockOut)), 0), 108), 1,2) + '.' +  Cast((Cast(substring(convert(varchar(10),  dateadd(second,  sum(datediff(second,  case when DATEDIFF(hh,0,ClockIn)%24 < DatePart(hour, DefaultEnd)  AND  DATEDIFF(hh,0,ClockOut)%24 >= DatePart(hour, DefaultEnd)  THEN DATEADD(hh,(DATEDIFF(hh,0,ClockIn)/24)* 24 +  DatePart(hour, DefaultEnd),0)  END, ClockOut)), 0), 108), 4,2) as Int) * 10)/6  as varchar(4)) as decimal(4,2)),0)
FROM cte2
GROUP BY  convert(nvarchar(10), ClockIn, 111),FullName,  convert(nvarchar(10),ClockIn, 12), DefaultStart, DefaultEnd
``````
0

Assistant ConsultantCommented:
SAve in temp table and use it.
0

Author Commented:
Thanks
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.