Geek Answers Handbook

Split, sum values ​​in calculated column specification

I am working on a table where I need to calculate the average time between trips using the following values.

Date Clockin CLockout Trip1 Trip2 ==== ======= ======== ===== ====== 01/01/2013 13:00 17:00 3 3

I have tried this.

 (datediff(minute,[Clockin],[ClockOut])/case when [Trip1]=(0) then NULL else [Trip1] end+case when [Trip2]=(0) then NULL else [Trip2] end)

and

 (datediff(minute,[Clockin],[ClockOut])/case when [Trip1]=(0) then 1 else [Trip1] end+case when [Trip2]=(0) then 1 else [Trip2] end)

The goal is to calculate the duration between trips. those. 4 hours / 3 trips if trip2 is zero otherwise 4 hours / 3 + 3 (4 hours / 6 trips)

But the above does not seem to give the correct result.

Any help would be appreciated.

+4
source share
3 answers

Depending on how you want to handle the trip amount 0 (is this a zero result?), One of the following calculations should work:

 declare @t table ([Date] date, Clockin time, CLockout time, Trip1 int, Trip2 int) insert into @t select '01/01/2013', '13:00', '17:00', 3, 3 union all select '01/01/2013', '13:00', '17:00', 0, 3 union all select '01/01/2013', '13:00', '17:00', 0, 0 union all select '01/01/2013', '13:00', '17:00', 3, null; select [minutes]=datediff(mi, Clockin, Clockout), [trips] = ((isnull(Trip1, 0)+isnull(Trip2,0))), [calc] = datediff(mi, Clockin, Clockout)/ (nullif((isnull(Trip1, 0)+isnull(Trip2,0)), 0)), [calc2] = datediff(mi, Clockin, Clockout)/ isnull((nullif((isnull(Trip1, 0)+isnull(Trip2,0)), 0)), 1) from @t
+2
source 
 CASE WHEN trip2 IS NULL THEN DATEDIFF(hour,[Clockin],[ClockOut]) / Trip1 ELSE (DATEDIFF(hour,[Clockin],[ClockOut]) / Trip1 + Trip2) * (DATEDIFF(hour,[Clockin],[ClockOut]) + (Trip1 + Trip2)) END
0
source

Try something like this:

 SELECT CONVERT(Decimal, DateDiff(hh,ClockIn,ClockOut)) / CASE WHEN Trip1 IS NULL AND Trip2 IS NULL THEN 1 ELSE CONVERT(Decimal, (COALESCE(Trip1,0) + COALESCE(Trip1,0))) END as result FROM YourTable

I was not sure what you wanted if both Trip1 and Trip2 were NULL, but I added a CASE statement to divide by 1. Instead

And here is the Fiddle .

Good luck.

0
source

More articles:


All Articles