General Date Scripting help

[#1]

Hi

More a general scripting query, rather than a QV specific query.

However it  is really wearing me down, any help would be greatly apprciated.

I have the columns MSN, Date and Day Dif, I want to add the columns calculating the missing dates, in the format below.

MSN	Date	Day Dif	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
116403	05/01/2015	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	01/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	02/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	03/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	04/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	05/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	06/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	08/01/2014	2	6.44%	0	0	0	0	0	0	0	0	0	0	0
116404	09/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	02/02/2014	24	70.90%	7.14%	0	0	0	0	0	0	0	0	0	0
116404	03/02/2014	1	0	3.57%	0	0	0	0	0	0	0	0	0	0
116404	04/02/2014	1	0	3.57%	0	0	0	0	0	0	0	0	0	0
116404	05/02/2014	1	0	3.57%	0	0	0	0	0	0	0	0	0	0
116404	06/02/2014	1	0	3.57%	0	0	0	0	0	0	0	0	0	0
116404	23/12/2014	320	0	78.57%	100.00%	100.00%	100.00%	100.00%	100.00%	100.00%	100.00%	100.00%	100.00%	70.97%
116404	24/12/2014	1	0	0	0	0	0	0	0	0	0	0	0	3.22%
116404	25/12/2014	1	0	0	0	0	0	0	0	0	0	0	0	3.22%
116404	26/12/2014	1	0	0	0	0	0	0	0	0	0	0	0	3.22%
116404	27/12/2014	1	0	0	0	0	0	0	0	0	0	0	0	3.22%
116404	05/01/2015	9	16.10%	0	0	0	0	0	0	0	0	0	0	12.88%
116405	01/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0

Regards

[datanibbler]

Hi Richard,

sorry, would you mind elaborating a bit on exactly what you want to do? I'm guessing ...

[#3]

Hi

good point, I have been looking a this so long , I almost expect every one else to understand .

I am trying to calculate how many days are missing between the date and previous(date) in table, and then calculate which months those missing dates occur. This can be expressed as a percentage of the month or number of days in said month and added to the origional table as above.  I will then use table later on in the script.

I hope this makes sense

[#4]

Try this, Peek would fetch previous records and returns zero, if there isn't any.

Load

Date(Floor(Date))-Peek(Date(Floor(Date) as Dates_Diff,

(Date(Floor(Date))-Peek(Date(Floor(Date) /Floor(MonthEnd(Date))-Floor(MonthStart(Date))+1)*100 as [% of Days Missing]

From Table

Order by Date.

[#5]

Use Peek or Previous Function to get the previous row value.