Split column into 2 when stop-date is in new month

[#1]

Hi all,

I have the following datamodel:

SQL SELECT
     ID
     STARTDATE,
     month(STARTDATE) as Month,
     STOPDATE,
     FAILUREID,
     FAILURENAME

I want to calculate the sum of the time between STARTDATE and STOPDATE in one month. This is not the problem in one month.

But when the STARTDATE and STOPDATE are in two different months, I only have the possibility of selecting data from the start month till endofmonth(STARTDATE).

I don't know how to get data when I'm choosing the next month.

One possibility would be splitting the record. But I don't know how to do that?

Can you help me or give me another reason?

Thanks a lot!

Flo

[MK_QSL]

Can you provide sample data alongwith your requirements?

[#3]

This is a very rare case but I'll try to explain.

The STARTTIME was on 11.30.2016 9:00 PM and the STOPTIME was on 12.01.2016 01:00 AM.

When I'm choosing november, to calculate the minutes between 9:00 PM an 12:00 PM isn't a problem.

But when I'm choosing december, there aren't any data, because the Month-Value is from STARTTIME.

Because of that, I think, I should split this data into two rows. The first row has the following field-data:

STARTDATE = 11.30.2016 9:00 PM

STOPDATA = 11.30.2016 12:00 PM

The second row:

STARTDATE = 12.01.2016 00:00 AM

STOPDATE = 12.01.2016 01:00 AM

Then I can select data in december.

Maybe there ist another possibility?

[el_aprendiz111]

Hi,

This could serve you

[#5]

Hi,

Thank you, but sorry, your solution don't help me with the splitting date in rows problem. Or I can't see the solution.

Regards