Counting Days in Specific Month between Two Dates

[bilionut]

Hi to All,

Starting from this post:

Counting Days in Specific Month between Two Dates

I need to use the date format including hour and minute

For example between 01.01.2017 00:00:00 and 01.02.2017 12:00: 00 it should be 1.5 days

In  the following example, room "w" should have been occupied 100%, meaning 31 days, but after running the script below the result is a total of 34,4999 days and 111%

Where is the mistake?

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='#,##0.00 €;-#,##0.00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

tabBookings:

LOAD RecNo() as BookID, *

Inline [

    AptID, Start, End

  w,12/30/2016 14:47:54, 1/8/2017 12:00:00

  w,1/8/2017 12:00:01, 1/25/2017 18:00:00

  w,1/25/2017 18:00:00, 1/29/2017 17:59:59

  w,1/29/2017 18:00:00, 1/31/2017 12:00:00

  w,1/31/2017 12:00:02, 2/13/2017 14:13:05

];

tabMonthOccup:

LOAD *,

     Num(DaysBooked/DaysInMonth,'0%') as Occupancy;

LOAD *,

     MonthName(MonthBookStart) as MonthBook,

     MonthBookEnd-MonthBookStart+1 as DaysBooked,

     MonthStart(MonthBookStart,1)-MonthStart(MonthBookStart) as DaysInMonth;

LOAD BookID,

     Date(RangeMax(Start,MonthStart(Start,IterNo()-1))) as MonthBookStart,

     Date(RangeMin(End  ,MonthStart(Start,IterNo())-1)) as MonthBookEnd

Resident tabBookings

While MonthStart(Start,IterNo()-1)<=End;

[MK_QSL · Accepted solution]

tabMonthOccup:

LOAD *,

     Num(DaysBooked/DaysInMonth,'0.0%') as Occupancy;

LOAD *,

     MonthName(MonthBookStart) as MonthBook,

     ((Interval(MonthBookEnd-MonthBookStart)*24)/24) as DaysBooked,

     MonthStart(MonthBookStart,1)-MonthStart(MonthBookStart) as DaysInMonth;

LOAD BookID,

     RangeMax(Start,MonthStart(Start,IterNo()-1)) as MonthBookStart,

     RangeMin(End  ,MonthEnd(Start,IterNo()-1)) as MonthBookEnd

Resident tabBookings

While MonthStart(Start,IterNo()-1)<=End;

[bilionut]

Hi Manish,

Thank you for your fast reply. It should work great, but a weird thing is happening.

In the example posted by me, your script works fine, but using real-world data it shows one negative value for DaysBooked

I can not figure out where that can be

BookID	Occupancy	DaysBooked	Start	End
6810	0.611992284	18.3597685185	6/11/2016 15:21:56	7/23/2016 14:47:54
6810	0.7295676523	22.6165972222	6/11/2016 15:21:56	7/23/2016 14:47:54
6811	0.2381742832	7.3834027778	7/23/2016 14:47:54	1/8/2017 12:37:49
6811	0.2427826314	7.5262615741	7/23/2016 14:47:54	1/8/2017 12:37:49
6811	0.9666666667	29	7/23/2016 14:47:54	1/8/2017 12:37:49
6811	0.9677419355	30	7/23/2016 14:47:54	1/8/2017 12:37:49
6812	0.5515837814	17.0990972222	1/8/2017 12:37:49	1/25/2017 15:00:31
6813	0.1298752987	4.0261342593	1/25/2017 15:00:31	1/29/2017 15:38:09
6814	0.043500224	1.3485069444	1/29/2017 15:38:09	1/31/2017 12:00:02
6815	-0.016129779	-0.5000231481	1/31/2017 12:00:02	3/13/2017 14:13:05
6815	0.4062070639	12.5924189815	1/31/2017 12:00:02	3/13/2017 14:13:05
6815	0.9642857143	27	1/31/2017 12:00:02	3/13/2017 14:13:05
6816	0.5615348716	17.4075810185	3/13/2017 14:13:05	5/27/2017 15:09:51
6816	0.8590916219	26.6318402778	3/13/2017 14:13:05	5/27/2017 15:09:51
6816	0.9666666667	29	3/13/2017 14:13:05	5/27/2017 15:09:51

[MK_QSL]

Provide sample data for two different BookID. One showing correct result and one showing negative. I will check and come back to you on this.