filling in gaps

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with the following fields

ShipID = PK
Sku = Product ID
Date = Products Trans Date
ShippedToDate

ShipID Sku Date ShippedToDate
1 AC 10/1/2004 5
2 AC 10/2/2004
3 AC 10/3/2004
4 AC 10/4/2004 17
5 AC 10/5/2004 23

in the case of the the empty value what is the best way to have it populate
from the prior record
 
Hi,

Have a query like

-----------
SELECT *
FROM myTable
WHERE NOT ShippedToDate Is Null
-----------


call it q1 It just removes the field with a null in ShippedToDate.

Then, the following query should do

---------------------
SELECT a.ShipID,
LAST(a.SKU),
LAST(a.Date),
LAST(b.ShippedToDate)

FROM ( myTable As a LEFT JOIN q1 As b
ON a.SKU=b.SKU and a.Date >= b.Date)
LEFT JOIN q1 As c
ON a.SKU=c.SKU and a.Date >= c.Date

GROUP BY a.ShipID, b.Date
HAVING b.Date=MAX(c.Date)
--------------------

where b and c are the same sets, initially, the set of records with the same
SKU than the one they have to match, in set a, but with a date before, or
equal, to the one of set a. Sure, sets b and c contains only records with
not-null values for ShippedToDate. Set b is then restricted further by
keeping only the record that match its maximum date. We technically need a
HAVING clause to involve the MAX(c.date), and b.Date, used in the
comparison, also need to be in the GROUP list.

Since we group by a.ShipID ( a primary key), and restricted set b to just
one record, through the GROUP BY, the LAST aggregate can be replaced by
MIN, MAX, or FIRST. That assumes you have just one Date for a given SKU, or,
if you prefer, that the couple (SKU, Date) is a potential primary key.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks

Worked great



Michel Walsh said:
Hi,

Have a query like

-----------
SELECT *
FROM myTable
WHERE NOT ShippedToDate Is Null
-----------


call it q1 It just removes the field with a null in ShippedToDate.

Then, the following query should do

---------------------
SELECT a.ShipID,
LAST(a.SKU),
LAST(a.Date),
LAST(b.ShippedToDate)

FROM ( myTable As a LEFT JOIN q1 As b
ON a.SKU=b.SKU and a.Date >= b.Date)
LEFT JOIN q1 As c
ON a.SKU=c.SKU and a.Date >= c.Date

GROUP BY a.ShipID, b.Date
HAVING b.Date=MAX(c.Date)
--------------------

where b and c are the same sets, initially, the set of records with the same
SKU than the one they have to match, in set a, but with a date before, or
equal, to the one of set a. Sure, sets b and c contains only records with
not-null values for ShippedToDate. Set b is then restricted further by
keeping only the record that match its maximum date. We technically need a
HAVING clause to involve the MAX(c.date), and b.Date, used in the
comparison, also need to be in the GROUP list.

Since we group by a.ShipID ( a primary key), and restricted set b to just
one record, through the GROUP BY, the LAST aggregate can be replaced by
MIN, MAX, or FIRST. That assumes you have just one Date for a given SKU, or,
if you prefer, that the couple (SKU, Date) is a potential primary key.



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
 
Back
Top