How many days fall between 2 dates?

[Jason_Michaelides]

Hi guys,

I have a problem I just can't get my head around - hoping you can help [8-|]

- At the top of my load script I set two variables - vMinDate and vMaxDate.
- I have a table of Adverts with campaign StartDates and EndDates.

LOAD
Ad_ID,
StartDate,
EndDate
FROM Ads.qvd(qvd);
[/Code]
I'd like to calculate, for each Advert, how many days of the campaign fall between vMinDate and vMaxDate (inclusive).
E.g. - vMinDate = 01/01/2011; vMaxDate = 31/01/2011
Ad_ID 1 - StartDate=10/01/2011; EndDate=20/01/2011; DaysInRange should = 11
Ad_ID 2 - StartDate=20/12/2010; EndDate=02/01/2011; DaysInRange should = 2
Ad_ID 3 - StartDate=10/11/2010; EndDate=20/12/2010; DaysInRange should = 0
Ad_ID 4 - StartDate=05/02/2011; EndDate=20/02/2011; DaysInRange should = 0
Ad_ID 5 - StartDate=20/01/2011; EndDate=20/02/2011; DaysInRange should = 12
Ad_ID 6 - StartDate=01/12/2010; EndDate=01/03/2011; DaysInRange should = 31
Any ideas!?
Thank you,
Jason
 </body>

[jbeierschmitt]

Did you try the function networkdays?

[disqr_rm · Accepted solution]

This should help you or at least give you an idea of possibilities. Please post your feedback.

Rakesh

Ads:
LOAD * INLINE [
Ad_ID, StartDate, EndDate
1, 1/1/2010, 5/1/2010
2, 1/3/2011, 1/17/2011
3, 1/1/2010, 12/31/2011
4, 5/1/2011, 5/5/2011
];

LET vMinDate = makedate(2011, 1, 1);
LET vMaxDate = makedate(2011, 1,31);

NewAds:
LOAD
 Ad_ID,
 if(EndDate < '$(vMinDate)' or StartDate > '$(vMaxDate)', 0, rangemin(EndDate, '$(vMaxDate)') - rangemax(StartDate, '$(vMinDate)')) as Days
RESIDENT Ads;

[Jason_Michaelides]

Thanks, but NetworkDays will only give working days and weekends are just as important for this.

[Jason_Michaelides]

Rakesh - you're a genius! Thank you so much that worked perfectly

Jason

[disqr_rm]

Thanks Jason, it's nice to be appreciated.

Glad that it works and I could help. Good luck!!!

Rakesh