Need Help with Date

[#1]

Hi

I have a table like this. Here i have created a Master Calendar using the start_time column. Now in this table i have an start_Time and End_Time Column and duration_seconds table is interval between end_time and start_time in seconds. but my requirement is to find out and display the data with day wise with its duration  . There is no issue if the start_time and end_time is same day but when the start_time and end_time different for example KEY value 210924 started on 1st May and ended on 4th May then how can we get the duration of each date starting from 1st May to 4th May individually . Please help

Also if the End_Time is NULL , then it means the Process is still running so i need to get the data of each date starting from starting time till current day until the duration time.

ID	KEY	start_time	end_time	duration_seconds
253856	192457	4/7/2015 16:08	4/7/2015 18:12	7469
253875	192944	4/7/2015 16:20	4/7/2015 18:12	6760
278091	208795	4/20/2015 23:48	4/21/2015 0:12	1414
286719	210924	4/28/2015 0:26	4/28/2015 0:28	113
289563	210924	5/1/2015 20:58	5/4/2015 17:08	245425
289564	211646	5/1/2015 20:59	5/4/2015 17:08	245369
296519	217382	5/11/2015 17:32	NULL	607225
308091	208795	5/25/2015 11:48	5/25/2015 12:12	1394

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[rwunderlich]

It depends on how you define a day duration. If it's a 24 hour period, then

=floor(end - start)      // # of 24 periods

if it's calendar days, the:

=floor(end) - floor(start)      // # of calendar days

for the NULL end in either case, use an if() to provide the missing date::

=floor(if(IsNull(end), today(2), end))  - floor(start)

-Rob

http://masterssummit.com

http://robwunderlich.com

[#4]

gwassenaar‌

The solution you have given is working fine with the End_Time column as NULL but now i am getting the same field as \N  value instead of NULL when i am fetching from DB

The below line may be   creating problem and no record is getting added to the table

timestamp(alt(timestamp#(end_time, 'MM/DD/YYYY h:mm'),now()),'M/D/YYYY h:mm') as end_time,

and

LOAD *, rangemax(daystart(start_time) + IterNo() -1,start_time) as s_time

while iterno() <= ceil(end_time - floor(start_time));

Could you please suggest how to handle \N in qlikview and above line