Get total no of minutes by hour hand between two dates

Question

Post reply

Get total no of minutes by hour hand between two dates

SSIS\SSRS\SSAS

SSCertifiable

Points: 6038
More actions
June 24, 2021 at 3:43 am

#3899921

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
Expected results ....

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

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

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

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

Is MINTUES a mistype? Do you mean MINUTES?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2160 More actions · Answer 2

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