Hello all,
I have a report with three columns: Unique Key || Issue Date Time || Completed Date Time. i would have a variable column called “TimeBlock”
I need to able to list all hours from issue date to completed date time . for example. the TimeBlocks coulumn would display the data is like this.
Unique Key= 1||IssueDate= 12/20/2016 12:00 PM|| CompletedDate =12/20/2016 3:00 PM || TimeBlock = 12:00 PM;1:00PM;2:00PM;3:00PM.
i have been able to do this in MS access but not SAP BI PS4.1.
please let me know if you have any ideas on how to go about this.
Welcome to B:bob:B!
This is a question which comes up quite a lot. It would be worthwhile spending some time searching on here, I bet you can find a number of relevant discussions. Good luck 8)
however i have done hours and hours of research regarding this topic but the only thing that comes up is questions regarding Days/Time between two dates, what i am in need of is a list of hours between two dates, if date issued was at 10:00 AM and Date completed was at 1:00 PM i would expect the timeBlock variable to list or enumerate the following 10:00AM;11:00AM;12:00PM;1:00PM all within the same row. please let me know if you have a post where this question was answered so that may review it. any help is greatly appreciated.
This may sound awful, but your best bet is perhaps something like:
Convert the timestamps for the 2 dates into a value of minutes.
if minute value of timestamp date 1 > minute value of timestamp date 2 then ((daysbetween date 1, date 2), - 2)*24 + (minute value of midnight - minute value date 1)/60 + (minute value date 2)/60 else ((daysbetween date 1, date 2), - 1)*24 + (minute value date 2 - minute value date 1)/60
That (or something along these lines) should produce your desired results…
Sorry, misread the original requirement. Would the date/time values always be within 24 hours of each other and would the time stamp always be on the hour? i.e 13:00 as opposed to 13:13… If the answer is yes to both, then it makes things a bit easier.
(BOB member since 2002-08-15)