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
  • Last Modified:

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)),
HoursBefore = 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),  
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)  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),  
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
Asked:
NerishaB
1 Solution
 
Aaron ShiloChief Database ArchitectCommented:
use a stored procedure
save the resolts to variables.

the just use tha variable for any calculation.
0
 
Mrugesh1Commented:
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)

 -- Here is your code. Assign your calculation to respective variable...


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

print FinalCalculation



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


WITH CTE
AS
(
	--Your code
)

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

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NerishaBAuthor Commented:

Please give me a basic example of how to do this?
0
 
KrtyknmCommented:
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

Open in new window

0
 
NerishaBAuthor 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

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
SAve in temp table and use it.
0
 
NerishaBAuthor Commented:
Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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