Get total no of minutes by hour hand between two dates

  • am trying to avoid using while loop but OK with it...

    We are talking about 500K source data...

    Original Data ...

    CDMLOCID CDMPOLL JOBCODE DATETIMEIN DATETIMEOUT

    201 2021-06-20 00:00:00.000 10 2021-06-20 10:32:00.000 2021-06-20 15:07:00.000

    201 2021-06-20 00:00:00.000 17 2021-06-20 15:44:00.000 2021-06-20 21:10:00.000SourceData

     

    Expected results ....

    ExpectedData

  • Something like this?

    Creating the 2 Tables

    select CDMLOCID = '201', CDMPOLL = cast('2021-06-20 00:00:00.000' as datetime2), JOBCODE = '10', DATETIMEIN = cast('2021-06-20 10:32:00.000' as datetime2), DATETIMEOUT = cast('2021-06-20 15:07:00.000' as datetime2)
    into #kek
    insert into #kek
    select CDMLOCID = '201', CDMPOLL = cast('2021-06-20 00:00:00.000' as datetime2), JOBCODE = '17', DATETIMEIN = cast('2021-06-20 15:44:00.000' as datetime2), DATETIMEOUT = cast('2021-06-20 21:10:00.000' as datetime2)



    select Hour_Value = 1
    into #kek2
    insert into #kek2
    Select 2 UNION Select 3 UNION Select 4 UNION Select 5 UNION Select 6 UNION Select 7 UNION Select 8 UNION Select 9 UNION Select 10
    UNION Select 11 UNION Select 12 UNION Select 13 UNION Select 14 UNION Select 15 UNION Select 16 UNION Select 17
    UNION Select 18 UNION Select 19 UNION Select 20 UNION Select 21 UNION Select 22 UNION Select 23 UNION Select 24

    Querying the result:

    --Sorry Formating looks like shit when copie, but thats only way to make it readable in forum
    select CDMLOCID
    ,CDMPOLL
    ,JOBCODE
    ,Hour_Value
    ,Minute_Value = case when DATEPART(HOUR,DATETIMEIN) = b.Hour_Value
    THEN DATEPART(MINUTE,DATETIMEIN)
    when DATEPART(HOUR,DATETIMEOUT) = b.Hour_Value
    THEN DATEPART(MINUTE,DATETIMEOUT)
    else 60 END
    --,DATEPART(MINUTE,DATETIMEIN)
    --,DATEPART(MINUTE,DATETIMEOUT)
    from #kek a
    inner join #kek2 b on b.Hour_Value between DATEPART(HOUR,DATETIMEIN) AND DATEPART(HOUR,DATETIMEOUT)
  • you are the very best !!! thank you...

    I was doing the same with 2 tables but using multiple updates for either full 60 minutes or partial minutes (the end time minutes were hard)...

     

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply