Get total no of minutes by hour hand between two dates

  • I 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.000

    SourceData

    Expected results ....

    ExpectedData

     

    CDMLOCID CDMPOLL JOBCODE HOURHAND MINTUES

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

    " " " 11 60

    " " " 12 60

    " " " 13 60

    " " " 14 60

    " " " 15 07

    201 2021-06-20 00:00:00.000 17 15 44

    " " " 16 60

    " " " 17 60

    " " " 18 60

    " " " 19 60

    " " " 20 60

    " " " 21 10

  • Can we assume that DATETIMEIN and DATETIMEOUT will be on the same day?

    Is MINTUES a mistype? Do you mean MINUTES?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • The additional rows were generated using dbo.fnTally

    select case when fn.n=0 then k.CDMLOCID else '"' end CDMLOCID,
    case when fn.n=0 then convert(varchar(23), k.CDMPOLL, 121) else '"' end CDMPOLL,
    case when fn.n=0 then k.JOBCODE else '"' end JOBCODE,
    (v.in_hr+fn.n)%24 hourhand,
    case when fn.n=0 then in_min
    when fn.n<v.diff_hr then 60
    else v.out_min end minutehand
    from #kek k
    cross apply (values (datepart(minute, DATETIMEIN),
    datepart(hour, DATETIMEIN),
    datepart(minute, DATETIMEOUT),
    datediff(hour, DATETIMEIN, DATETIMEOUT)))
    v(in_min, in_hr, out_min, diff_hr)
    cross apply dbo.fnTally(0, v.diff_hr) fn;
    CDMLOCIDCDMPOLLJOBCODEhourhandminutehand
    2012021-06-20 00:00:00.000101032
    """1160
    """1260
    """1360
    """1460
    """157
    2012021-06-20 00:00:00.000171544
    """1660
    """1760
    """1860
    """1960
    """2060
    """2110

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

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

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