Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm fairly new to QV so here's my question.
I'm stuck with this problem.
How do you get records selecting a year where the selected year is present within a start and enddate record.
For example
ID, Begindate, Enddate
1, 01-01-2008, 26-5-2010
2, 05-06-2009, 27-5-2016
3, 05-06-2010, 31-12-2011
4, 01-01-2000, 15-08-2020
5, 05-09-2011, 21-10-2012
6, 05-07-2007, 31-11-2008
7, 08-08-2008, 07-08-2012
8, 11-10-2000, 15-03-2002
9, 04-03-2007, 03-04-2008
10, 04-04-2011, 31-12-2012
If I would select the year 2010, it should give me records with ID 1,2,3,4 and 7.
How should I do this. I can't get this right with the Master Calendar Solution because I have 2 different date fields.
Or I'm going at it the wrong way......
Thanks!
Thanks you for the reply Sir,
As u've said:
"If you have them in a table, you can load this table and use these dates."
Can i use the following code to get all the dates from my table:
AllDates:
Load
distinct startdate as date resident tablename;
concatenate
Load distinct enddate as date resident tablename;
and then intervalmatch:
interalmatch(date) load startdate,enddate resident tablename;
Will this work ?
Regards
Your "date" field will be incomplete - it will only contain the dates that happen to be the start of end of some range. It will not contain the dates in between. But the intervalmatch will work. So if an incomplete "date" field is OK, then you can use the the above code.
/HIC
Sir,
actually i'm nt able to understand:
" it will only contain the dates that happen to be the start of end of some range. It will not contain the dates in between"
can u explain a bit more.......
no actually i want all the dates & not incomplete dates.........then i think i should remove distinct and then concatenate.
plz throw some light
Regards
If you want all the dates, then the table containing the "date" field must have one record per date. But your suggested solution will only load the dates that already exist in your table that contain the ranges. In other words, your dates table will have just some of the dates.
Ranges table: One record per date range.
Dates table: One record per date.
Intervalmatch table: One record per match between the two previous tables.
Try the script posted above with a Load ... autogenerate.
/HIC
SIr,
I'll have to read and analyse what u're saying at least 4-5 times then i'll understand it........i'm not even half as sharp as you.
for the time being i've just removed distinct & loading all the start and enddates.........isnt it okay?
Regards