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:47 am

#3899923

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

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

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

ktflash Default port Points: 1400 More actions · Answer 1

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)

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 2

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)...