Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table like this:
prova:
LOAD * INLINE [
mac_, from_, to_, value_
m1, 1/12/2005, 31/1/2006, 1000
m1, 1/12/2006, 31/1/2007, 2000
m2, 1/12/2005, 31/1/2006, 1500
];
I would like to know the fraction of each interval (from_->to_) in each year in order to calculate the total value_ amount for each year. In the example for mac_ = m1 and year = 2006: 1000*1/12 + 2000 * 1/12.
I think I can find the solution by using several nested if, but is there any ready-made function in qv which returns the overlapping of two time intervals? (i.e.: 1/1/2006-31/12/2006 vs 1/12/2005-31/1/2006)
Thanks in advance for your help
I didn´t get what you are trying to do exactly, but search QVs help for "Date and Time Function".
You may find a way to solve it in the script using INTERVALMATCH
Regards
Fernando
thanks, I haven't found anything useful in date and time functions. The intervalmatch lets me link a single date with an interval while i need to find the intersection of two intervals
I have a feeling I'm misunderstanding the question, but how about:
prova:
LOAD *,
Delta2006 / 365 as Pct2006
;
LOAD *,
RangeMax(RangeMin(to_, MakeDate(2006,12,31)) - RangeMax(from_, MakeDate(2006)) , 0) As Delta2006
;
LOAD * INLINE [
mac_, from_, to_, value_
m1, 1/12/2005, 31/1/2006, 1000
m1, 1/12/2006, 31/1/2007, 2000
m2, 1/12/2005, 31/1/2006, 1500
];
I have a feeling I'm misunderstanding the question, but how about:
prova:
LOAD *,
Delta2006 / 365 as Pct2006
;
LOAD *,
RangeMax(RangeMin(to_, MakeDate(2006,12,31)) - RangeMax(from_, MakeDate(2006)) , 0) As Delta2006
;
LOAD * INLINE [
mac_, from_, to_, value_
m1, 1/12/2005, 31/1/2006, 1000
m1, 1/12/2006, 31/1/2007, 2000
m2, 1/12/2005, 31/1/2006, 1500
];
is
date(max(total <mac_> from_))-date(min(total <mac_> to_))
what you're looking for?