Transforming date range data

[GPO]

Don't know wheter this is a programming question or an SQL question, but here
goes:
I have a list of date data that I want to transform from this:
MyID MyDate MyValue
1 1 Jan 05 0
1 3 Jan 05 1
1 7 Jan 05 1
1 9 Jan 05 0
2 2 Jan 05 0
2 7 Jan 05 1
3 1 Jan 05 1
3 6 Jan 05 0
..
..
..
To:
MyID MyDate MyValue
1 1 Jan 05 0
1 2 Jan 05 0
1 3 Jan 05 1
1 4 Jan 05 1
1 5 Jan 05 1
1 6 Jan 05 1
1 7 Jan 05 1
1 8 Jan 05 1
1 9 Jan 05 0
2 2 Jan 05 0
2 3 Jan 05 0
2 4 Jan 05 0
2 5 Jan 05 0
2 6 Jan 05 0
2 7 Jan 05 1
3 1 Jan 05 1
3 2 Jan 05 1
3 3 Jan 05 1
3 4 Jan 05 1
3 5 Jan 05 1
3 6 Jan 05 0
..
..
..

Note:
1. The original list is sorted frst by ID and then by date.
2. The difference between the first and second list is that the second has
all the missing dates included and the MyValue in the previously missing date
is whatever the MyValue was on the previous date.

Any assistance would be most welcome.

GPO
(Access 2000 - 2003)

 

[Ron Weiner]

Here is a Sql way.

First Create a Table with one column that has every date for the range of
Dates You are working with. I called this table tblMyAllDates.

Then make this union query

SELECT tblMyTable.myID, tblMyTable.MyDate, tblMyTable.MyValue
FROM tblMyTable
WHERE tblMyTable.myID=1

Union

SELECT 1 AS Expr1, tblMyAllDates.AllDates, (Select Last(MyValue) from
tblMyTable WHERE MyID = 1 and myDate< Alldates) AS Expr2
FROM tblMyAllDates LEFT JOIN (Select MyDate From tblMyTable Where MyID = 1)
as tblMyTable ON tblMyAllDates.AllDates = tblMyTable.MyDate
WHERE tblMyTable.MyDate Is Null AND AllDates < (Select MAX(MyDate) From
tblMyTable Where MyID = 1) AND AllDates > (Select MIN(MyDate) From
tblMyTable Where MyID = 1)

Union

SELECT tblMyTable.myID, tblMyTable.MyDate, tblMyTable.MyValue
FROM tblMyTable
WHERE tblMyTable.myID=2

Union

SELECT 2 AS Expr1, tblMyAllDates.AllDates, (Select Last(MyValue) from
tblMyTable WHERE MyID = 2 and myDate< Alldates) AS Expr2
FROM tblMyAllDates LEFT JOIN (Select MyDate From tblMyTable Where MyID = 2)
as tblMyTable ON tblMyAllDates.AllDates = tblMyTable.MyDate
WHERE tblMyTable.MyDate Is Null AND AllDates < (Select MAX(MyDate) From
tblMyTable Where MyID = 2) AND AllDates > (Select MIN(MyDate) From
tblMyTable Where MyID = 2)

Union

SELECT tblMyTable.myID, tblMyTable.MyDate, tblMyTable.MyValue
FROM tblMyTable
WHERE tblMyTable.myID=3

Union

SELECT 3 AS Expr1, tblMyAllDates.AllDates, (Select Last(MyValue) from
tblMyTable WHERE MyID = 3 and myDate< Alldates) AS Expr2
FROM tblMyAllDates LEFT JOIN (Select MyDate From tblMyTable Where MyID = 3)
as tblMyTable ON tblMyAllDates.AllDates = tblMyTable.MyDate
WHERE tblMyTable.MyDate Is Null AND AllDates < (Select MAX(MyDate) From
tblMyTable Where MyID = 3) AND AllDates > (Select MIN(MyDate) From
tblMyTable Where MyID = 3)

ORDER BY tblMyTable.myID, tblMyTable.MyDate


The Results of this query were:

myID MyDate MyValue
1 1/1/2005 0
1 1/2/2005 0
1 1/3/2005 1
1 1/4/2005 1
1 1/5/2005 1
1 1/6/2005 1
1 1/7/2005 1
1 1/8/2005 1
1 1/9/2005 0
2 1/2/2005 0
2 1/3/2005 0
2 1/4/2005 0
2 1/5/2005 0
2 1/6/2005 0
2 1/7/2005 1
3 1/1/2005 1
3 1/2/2005 1
3 1/3/2005 1
3 1/4/2005 1
3 1/5/2005 1
3 1/6/2005 0

If it is a one time shot just for the 3 different MyID's this is by far the
fastest and a easy way to get it done. If you have Hundreds of MyID's to
group on I think you are gonna have to write some code that does this
iteratively.

 

[Ron Weiner]

After Looking at this again here is a slightly shorter version of the same
query.

SELECT tblMyTable.myID, tblMyTable.MyDate, tblMyTable.MyValue
FROM tblMyTable

Union

SELECT 1 AS Expr1, tblMyAllDates.AllDates, (Select Last(MyValue) from
tblMyTable WHERE MyID = 1 and myDate< Alldates) AS Expr2
FROM tblMyAllDates LEFT JOIN (Select MyDate From tblMyTable Where MyID = 1)
as tblMyTable ON tblMyAllDates.AllDates = tblMyTable.MyDate
WHERE tblMyTable.MyDate Is Null AND AllDates < (Select MAX(MyDate) From
tblMyTable Where MyID = 1) AND AllDates > (Select MIN(MyDate) From
tblMyTable Where MyID = 1)

Union

SELECT 2 AS Expr1, tblMyAllDates.AllDates, (Select Last(MyValue) from
tblMyTable WHERE MyID = 2 and myDate< Alldates) AS Expr2
FROM tblMyAllDates LEFT JOIN (Select MyDate From tblMyTable Where MyID = 2)
as tblMyTable ON tblMyAllDates.AllDates = tblMyTable.MyDate
WHERE tblMyTable.MyDate Is Null AND AllDates < (Select MAX(MyDate) From
tblMyTable Where MyID = 2) AND AllDates > (Select MIN(MyDate) From
tblMyTable Where MyID = 2)

Union

SELECT 3 AS Expr1, tblMyAllDates.AllDates, (Select Last(MyValue) from
tblMyTable WHERE MyID = 3 and myDate< Alldates) AS Expr2
FROM tblMyAllDates LEFT JOIN (Select MyDate From tblMyTable Where MyID = 3)
as tblMyTable ON tblMyAllDates.AllDates = tblMyTable.MyDate
WHERE tblMyTable.MyDate Is Null AND AllDates < (Select MAX(MyDate) From
tblMyTable Where MyID = 3) AND AllDates > (Select MIN(MyDate) From
tblMyTable Where MyID = 3)

ORDER BY tblMyTable.myID, tblMyTable.MyDate

 

[GPO]

Yes...

Thanks Ron. Maybe I should have mentioned that I can have anywhere up to
180,000 myIDs! Food for thought nevertheless.