D
DawnTreader
Hello All
i am trying to find a way of narrowing down to a record where the date is
just one record that nearest matches the date from another table.
this is the scenario, i have products which go under a warranty for a
certain number of running hours. these hours are recorded at specific dates.
these products also have warranty claims against them. i need to make sure
that the amount of running hours at the time of the claim is less then the
allowed amount to be in warranty.
there is one other possible stumling block, warranties can be extended and
therefore the running hours can have 2 values depending on the product.
i am making a query that finds the running hours at the date of the claim
and then returning the date and amount of hours to a bit of code. i think i
can handle the code end, but the query to get me the values i need are
stumping me. i need it to not return more than 1 line for each warranty claim
and unfortunately i am not getting it.
the sql for the query i created looks like this:
SELECT
subtblBlockList.ProductID,
subtblRunningHours.BlockID,
subtblRunningHours.RHDate,
subtblRunningHours.HoursAtDate,
tblProductList.WarrantyHours,
tblProductList.HoursExtended,
tblWarrantyClaim.WarrantyClaimID,
Max(DateDiff("d",[RHDate],[DateofClaim])) AS ClosestDate,
Max([WarrantyHours]-[HoursAtDate]) AS RHDiff,
Max(([WarrantyHours]+[HoursExtended])-[HoursAtDate]) AS RHDiffExt
FROM
tblProductList RIGHT JOIN
((subtblBlockList LEFT JOIN subtblRunningHours
ON subtblBlockList.BlockID = subtblRunningHours.BlockID)
LEFT JOIN tblWarrantyClaim ON
subtblBlockList.ProductID = tblWarrantyClaim.ProductID)
ON tblProductList.ProductID = subtblBlockList.ProductID
GROUP BY
subtblBlockList.ProductID,
subtblRunningHours.BlockID,
subtblRunningHours.RHDate,
subtblRunningHours.HoursAtDate,
tblProductList.WarrantyHours,
tblProductList.HoursExtended,
tblWarrantyClaim.WarrantyClaimID
HAVING
(((subtblRunningHours.HoursAtDate) Is Not Null) AND
((Max(DateDiff("d",[RHDate],[DateofClaim]))) Between 0 And -1)
AND ((Max([WarrantyHours]-[HoursAtDate]))>0))
ORDER BY
tblWarrantyClaim.WarrantyClaimID;
this gets me close to the desired results but i am still getting multiple
warranty claim id's where i only want it to show once. i know that the
between is part of the problem, but if i were to just look for those that are
0 i wouldnt get some records and it would not entirely serve the purpose.
i need to know if the warranty claim happened at a point in time where the
product has no available running hours. for example if product 12345 had a
warranty saying that it had 4000 running hours before warranty was up then if
that happened on a date before the warranty claim it would invalidate the
warranty claim, but if the warranty claim was made on a date before the 4000
running hours were up then the claim is valid.
so if the warranty claim date was 01/mar/08 and the product has 4000 running
hours available and :
Running Hours Date
1000 01/dec/07
2000 01/feb/08
3000 01/Jun/08
4000 01/aug/08
was the data on the history of the running hours i would like it to return
the 01/feb/08 date.
any ideas and suggestions would be much appreciated
i am trying to find a way of narrowing down to a record where the date is
just one record that nearest matches the date from another table.
this is the scenario, i have products which go under a warranty for a
certain number of running hours. these hours are recorded at specific dates.
these products also have warranty claims against them. i need to make sure
that the amount of running hours at the time of the claim is less then the
allowed amount to be in warranty.
there is one other possible stumling block, warranties can be extended and
therefore the running hours can have 2 values depending on the product.
i am making a query that finds the running hours at the date of the claim
and then returning the date and amount of hours to a bit of code. i think i
can handle the code end, but the query to get me the values i need are
stumping me. i need it to not return more than 1 line for each warranty claim
and unfortunately i am not getting it.
the sql for the query i created looks like this:
SELECT
subtblBlockList.ProductID,
subtblRunningHours.BlockID,
subtblRunningHours.RHDate,
subtblRunningHours.HoursAtDate,
tblProductList.WarrantyHours,
tblProductList.HoursExtended,
tblWarrantyClaim.WarrantyClaimID,
Max(DateDiff("d",[RHDate],[DateofClaim])) AS ClosestDate,
Max([WarrantyHours]-[HoursAtDate]) AS RHDiff,
Max(([WarrantyHours]+[HoursExtended])-[HoursAtDate]) AS RHDiffExt
FROM
tblProductList RIGHT JOIN
((subtblBlockList LEFT JOIN subtblRunningHours
ON subtblBlockList.BlockID = subtblRunningHours.BlockID)
LEFT JOIN tblWarrantyClaim ON
subtblBlockList.ProductID = tblWarrantyClaim.ProductID)
ON tblProductList.ProductID = subtblBlockList.ProductID
GROUP BY
subtblBlockList.ProductID,
subtblRunningHours.BlockID,
subtblRunningHours.RHDate,
subtblRunningHours.HoursAtDate,
tblProductList.WarrantyHours,
tblProductList.HoursExtended,
tblWarrantyClaim.WarrantyClaimID
HAVING
(((subtblRunningHours.HoursAtDate) Is Not Null) AND
((Max(DateDiff("d",[RHDate],[DateofClaim]))) Between 0 And -1)
AND ((Max([WarrantyHours]-[HoursAtDate]))>0))
ORDER BY
tblWarrantyClaim.WarrantyClaimID;
this gets me close to the desired results but i am still getting multiple
warranty claim id's where i only want it to show once. i know that the
between is part of the problem, but if i were to just look for those that are
0 i wouldnt get some records and it would not entirely serve the purpose.
i need to know if the warranty claim happened at a point in time where the
product has no available running hours. for example if product 12345 had a
warranty saying that it had 4000 running hours before warranty was up then if
that happened on a date before the warranty claim it would invalidate the
warranty claim, but if the warranty claim was made on a date before the 4000
running hours were up then the claim is valid.
so if the warranty claim date was 01/mar/08 and the product has 4000 running
hours available and :
Running Hours Date
1000 01/dec/07
2000 01/feb/08
3000 01/Jun/08
4000 01/aug/08
was the data on the history of the running hours i would like it to return
the 01/feb/08 date.
any ideas and suggestions would be much appreciated