Avoid to expand data to work with dates

[#1]

Good morning, I am looking for a piece of script about “master calendars” or a similar solution to the following problem:

My BBDD data source generates rows or records like these  (date format dd/mm/yy):

HOTEL	M	MCDO	TO	TTOO	ID	CTO	FIPE	FFPE	THA	BASE
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77

Where the Price (BASE) is 75 y 77 for the periods included between FIPE (Begining Period Date) and FFPE (Ending Period Date) respectively, that means, for the first record or row the valid price (BASE) is 75 during 8 days and for the second record the valid price is 77 during 20 days.

Summarizing, in order to analyze Price averages for  differents periods etc the only way I found was to join a new column called FECHA (DATE) and transformate the original BBDD table in this other:

HOTEL	M	MCDO	TO	TTOO	ID	CTO	FIPE	FFPE	THA	BASE	FECHA
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	01/01/2012
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	02/01/2012
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	03/01/2012
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	04/01/2012
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	05/01/2012
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	06/01/2012
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	07/01/2012
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	08/01/2012
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	01/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	02/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	03/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	04/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	05/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	06/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	07/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	08/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	09/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	10/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	11/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	12/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	13/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	14/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	15/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	16/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	17/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	18/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	19/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	20/11/2011

This way I can deal with dates, data and formulas with total flexibility thanks to the new field FECHA (DATE), but is unbearable for access or excel.

Could please anyone suggest me a solution or advice in QV script code to work with dates without having to expand the BBDD data source?

Thank you

[#2]

Perfect, now I think I have to create a master calendar linked to FECHA in order to compare months of different years an so on.

Thank you very much.

[#3]

Please one last question,

I made the master calendar and I see that the first date of 'FECHA' it takes is not the very first date of all, I assume it is because I am using 'peek' without having ordering BBDD by FECHA.

I tried to put ...FROM....ORDER BY FECHA but at that line of the script 'FECHA' do not exists yet so it gives me script error.

How could I order by 'FECHA' to make the mastercalendar correctly?

Thanks

[hic]

you use the following lines to find the first and last date:

let varmindate = num(Peek('FECHA',0,'BBDD'));

let varmaxdate = num(Peek('FECHA',-1,'BBDD'));

I suggest you replace these by

MinMaxDate:

Load Min(FECHA) as MinFECHA, Max(FECHA) as MaxFECHA resident BBDD;

let varmindate = num(Peek('MinFECHA',-1,'MinMaxDate'));

let varmaxdate = num(Peek('MaxFECHA ',-1,'MinMaxDate'));

Drop Table MinMaxDate;

HIC

[#5]

It seems there is something wrong with 'varmaxdate', it takes value <NULL> whereas varmindate is 40180 which is right.