locked
Calculating the downtime values RRS feed

  • 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

Answers

  • ;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),
                    dateadd(MINUTE,
                    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),
                    dateadd(MINUTE,
                    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