Solved

# SQL - Simplify calculation

Posted on 2011-05-11
341 Views
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
Question by:NerishaB

LVL 15

Expert Comment

use a stored procedure
save the resolts to variables.

the just use tha variable for any calculation.
0

LVL 3

Expert Comment

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

LVL 3

Expert Comment

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 Comment

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

LVL 3

Expert Comment

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 Comment

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

LVL 21

Accepted Solution

SAve in temp table and use it.
0

Author Closing Comment

Thanks
0

## Featured Post

### Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.