# Calculating the downtime values

• ### Question

• HI

i have 4 fields in the databse called 'DATE_IN','TIME_IN', 'DATE_OUT','TIME_OUT'

The DATE_IN gives the date of a vehicles' entry in a workshop and the TIME_IN gives the time of the entry.

The DATE_OUT gives the date of the vehicles' exit from the workshop and the TIME_OUT gives the time of the exit.

These all are stored as a string like'20090401' for the date 01/04/2009 and '210000' for 9 pm of the time.

What i want to claculate is the total time of the vehicle on the workshop between the two dates' parameters @report_start_date and @report_end_date. The condition is, if a vehicle is for 2 workshop jobs like

01/04/2009 11 am to 1 pm for job 1

01/04/2009 from 11:30 am to  02/04/2009 2:30 PM  for job 2.....

i should calculate only the total hours from 11 am 01/04/2009 to 2.30 pm 02/04/2009 i.e 27:30 hrs (not 2 hrs+ 27 hrs of both jobs)  if those transactions fall within the @report_start_date and @report_end_date for that vehicle as a single column.......any idea plz

Monday, March 29, 2010 2:28 AM

• ```;with cte as (cast(stuff(stuff(DateIn,5,0,'-'),8,0,'-') + ' ' + stuff(stuff(TimeIn,3,0,':'),6,0,':') as datetime) as DateIn,
cast(stuff(stuff(DateOut,5,0,'-'),8,0,'-') + ' ' + stuff(stuff(TimeOut,3,0,':'),6,0,':') as datetime) as DateOut from myTable),

cte1 as (select * from cte where DateIn <=@Report_End_Date and DateOut >= @Report_Start_Date)

select DateIn, DateOut,
replace(convert(varchar(8),
datediff(MINUTE,
DateIn,
DateOut
), 0
), 108), ':', '') from cte1

```

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
Monday, March 29, 2010 2:38 AM
• things will be much easier if you can store your date & time in datetime data type rather than string

```declare @sample table
(
VEHICLE_ID    int,
JOB_ID        int,
DATE_IN        varchar(8),
TIME_IN        varchar(8),
DATE_OUT    varchar(8),
TIME_OUT    varchar(8)
)

insert into @sample
select    1, 1, '20090401', '110000', NULL, NULL    union all
select    1, 2, '20090401', '113000', '20090402', '143000'

; with data
as
(
select    VEHICLE_ID, JOB_ID,
DT_IN    = convert(datetime, DATE_IN + ' ' + stuff(stuff(TIME_IN, 3, 0, ':'), 6, 0, ':')),
DT_OUT    = convert(datetime, DATE_OUT + ' ' + stuff(stuff(TIME_OUT, 3, 0, ':'), 6, 0, ':'))
from    @sample
)
select    VEHICLE_ID,
TIME_HOUR = datediff(minute, min(DT_IN), max(DT_OUT)) / 60.0
from    data
group by VEHICLE_ID```

KH Tan
Monday, March 29, 2010 2:48 AM

### All replies

• ```;with cte as (cast(stuff(stuff(DateIn,5,0,'-'),8,0,'-') + ' ' + stuff(stuff(TimeIn,3,0,':'),6,0,':') as datetime) as DateIn,
cast(stuff(stuff(DateOut,5,0,'-'),8,0,'-') + ' ' + stuff(stuff(TimeOut,3,0,':'),6,0,':') as datetime) as DateOut from myTable),

cte1 as (select * from cte where DateIn <=@Report_End_Date and DateOut >= @Report_Start_Date)

select DateIn, DateOut,
replace(convert(varchar(8),
datediff(MINUTE,
DateIn,
DateOut
), 0
), 108), ':', '') from cte1

```

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
Monday, March 29, 2010 2:38 AM
• things will be much easier if you can store your date & time in datetime data type rather than string

```declare @sample table
(
VEHICLE_ID    int,
JOB_ID        int,
DATE_IN        varchar(8),
TIME_IN        varchar(8),
DATE_OUT    varchar(8),
TIME_OUT    varchar(8)
)

insert into @sample
select    1, 1, '20090401', '110000', NULL, NULL    union all
select    1, 2, '20090401', '113000', '20090402', '143000'

; with data
as
(
select    VEHICLE_ID, JOB_ID,
DT_IN    = convert(datetime, DATE_IN + ' ' + stuff(stuff(TIME_IN, 3, 0, ':'), 6, 0, ':')),
DT_OUT    = convert(datetime, DATE_OUT + ' ' + stuff(stuff(TIME_OUT, 3, 0, ':'), 6, 0, ':'))
from    @sample
)
select    VEHICLE_ID,
TIME_HOUR = datediff(minute, min(DT_IN), max(DT_OUT)) / 60.0
from    data
group by VEHICLE_ID```

KH Tan
Monday, March 29, 2010 2:48 AM
• ```CREATE PROCEDURE [Ausfleet_user].[SP_RPT_DOWNTIME_ANALYSIS]

@START_DATE VARCHAR(50),
@END_DATE VARCHAR(50)
AS
BEGIN

DECLARE @V_END_DATE DATE_CHAR,
@V_START_DATE DATE_CHAR

--Convert start date from format dd/mm/yyyy to yyyymmdd
SET @V_START_DATE = SUBSTRING(@START_DATE, 7,4) + SUBSTRING(@START_DATE,4, 2) + SUBSTRING(@START_DATE, 1,2)
--Convert end Date from format dd/mm/yyyy to yyyymmdd
SET @V_END_DATE = SUBSTRING(@END_DATE, 7,4) + SUBSTRING(@END_DATE,4, 2) + SUBSTRING(@END_DATE, 1,2)
END```

i HAVE POSTED MY STORED PROCEDURE ABOVE WHERE THE 4 FILEDS I HAVE DESCRIBED EARLIER ARE FROM THE TABLE

AUSFLEET_USER

.WSP_WORKSHOP_TRANSACTION

Monday, March 29, 2010 2:58 AM
• In above post,

I have done the conversion of the date to match it with the format the user enters the date here in australia i.e dd/mm/yyyy.

what i have missed there is the select statement to get the results from the table

AUSFLEET_USER.WSP_WORKSHOP_TRANSACTION WHICH CONTAINS ALL THE DATE_IN, DATE_OUT, TIME_IN, TIME_OUT FIELDS AS THE CHARACTER STRING....

I THINK THIS IS MORE CLEAR OF MY PROBLEM

Monday, March 29, 2010 3:06 AM
• Read the replies you already got and don't play with substring when it's not needed.
Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
Monday, March 29, 2010 3:08 AM