Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a simple data set that looks like this:
(Sorry, but the "Insert Table" function isn't working for me.)
AccountNumber StartDateTime EndDateTime
A123456 1/1/2012 16:00 1/1/2012 18:25
A123457 1/1/2012 16:00 1/2/2012 08:15
A123458 1/1/2012 16:00 1/3/2012 14:45
I need to be able to calculate the number of hours for each day. In this example, the daily totals would be:
1/1/2012 = 18.42 hours
1/2/2012 = 32.25 hours
1/3/2012 = 14.75 hours
I'm sure I'm not the first one to encounter this type of issue. Would this be done in the script, in the expression, or a combination of the two?
Thanks.
Hi,
I would do it in the script load.
Load *
AccountNumber,
StartDateTime,
EndDateTime,
If(Day(StartDateTime)=Day(EndDateTime),EndDateTime-StartDateTime,Floor(EndDateTime)-StartDateTime) as Hours_Day
from table;
I don´t know if Floor(EndDateTime)-StartDateTime will work but If won´t work is because the types of the dates are different. I think this is the best way to get what you want but probably are better options.
Hope this helps
I think you can do it just like I posted here, using a while loop to iterate over the days in your intervals and a complicated-looking conditional to calculate the hours for eah single day.