Calc Diff Date with ID - Karl Dewey

[kritter0021]

These queries worked but now I need to incorporate ID with the data in these
queries. Thanks!

ID Product Date Dif Date
1 A 1/23/2009 40
1 A 3/4/2009 65
1 A 5/8/2009 0
2 B 3/2/2009 28
2 B 3/30/2009 0

Use these three queries --

kritter0021
SELECT YourTable.Product, YourTable.ProdDate, (SELECT COUNT(*) FROM
YourTable AS [XX] WHERE [XX].Product = YourTable.Product AND [XX].ProdDate
<= YourTable.ProdDate) AS RANK
FROM YourTable
ORDER BY YourTable.Product, YourTable.ProdDate;

kritter0021_X
SELECT kritter0021.Product, kritter0021.ProdDate, kritter0021.RANK
FROM kritter0021
UNION ALL SELECT YourTable.Product, NULL AS ProdDate, Max([RANK]) +1 AS RANK1
FROM YourTable INNER JOIN kritter0021 ON YourTable.Product =
kritter0021.Product
GROUP BY YourTable.Product;

SELECT kritter0021_X.Product, kritter0021_X.ProdDate,
IIf([kritter0021_X_1].[ProdDate] Is
Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate]))
AS Expr1
FROM kritter0021_X INNER JOIN kritter0021_X AS kritter0021_X_1 ON
kritter0021_X.Product = kritter0021_X_1.Product
WHERE (((kritter0021_X_1.RANK)=[kritter0021_X].[RANK]+1))
ORDER BY kritter0021_X.Product, kritter0021_X.ProdDate,
IIf([kritter0021_X_1].[ProdDate] Is
Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate]));

 

[vanderghast]

SELECT a.id,
a.product,
a.[date],
Nz(MIN(b.[date]) - a.[date], 0 ) AS diffDate

FROM yourTable AS a
LEFT JOIN yourTable AS b
ON a.id = b.id
AND a.product = b.product
AND a.date < b.date

GROUP BY a.id, a.product, a.[date]


should do.


Vanderghast, Access MVP

These queries worked but now I need to incorporate ID with the data in
these
queries. Thanks!

ID Product Date Dif Date
1 A 1/23/2009 40
1 A 3/4/2009 65
1 A 5/8/2009 0
2 B 3/2/2009 28
2 B 3/30/2009 0

Use these three queries --

kritter0021
SELECT YourTable.Product, YourTable.ProdDate, (SELECT COUNT(*) FROM
YourTable AS [XX] WHERE [XX].Product = YourTable.Product AND
[XX].ProdDate
<= YourTable.ProdDate) AS RANK
FROM YourTable
ORDER BY YourTable.Product, YourTable.ProdDate;

kritter0021_X
SELECT kritter0021.Product, kritter0021.ProdDate, kritter0021.RANK
FROM kritter0021
UNION ALL SELECT YourTable.Product, NULL AS ProdDate, Max([RANK]) +1 AS
RANK1
FROM YourTable INNER JOIN kritter0021 ON YourTable.Product =
kritter0021.Product
GROUP BY YourTable.Product;

SELECT kritter0021_X.Product, kritter0021_X.ProdDate,
IIf([kritter0021_X_1].[ProdDate] Is
Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate]))
AS Expr1
FROM kritter0021_X INNER JOIN kritter0021_X AS kritter0021_X_1 ON
kritter0021_X.Product = kritter0021_X_1.Product
WHERE (((kritter0021_X_1.RANK)=[kritter0021_X].[RANK]+1))
ORDER BY kritter0021_X.Product, kritter0021_X.ProdDate,
IIf([kritter0021_X_1].[ProdDate] Is
Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate]));

--
Build a little, test a little.

That works when you are calculating it between two fields, not when you
are
trying to use the previous date in the same field. So

Product Date Dif Date

I need DiffDate beween the first two records calculated. Not sure how to
make it do that. 3/4/09 - 1/23/09 = 40.

 

[KARL DEWEY]

Try these --
kritter0021
SELECT YourTable.ID, YourTable.Product, YourTable.ProdDate, (SELECT COUNT(*)
FROM
YourTable AS [XX] WHERE [XX].Product = YourTable.Product AND [XX].ProdDate
<= YourTable.ProdDate) AS RANK
FROM YourTable
ORDER BY YourTable.ID, YourTable.Product, YourTable.ProdDate;

kritter0021_X
SELECT YourTable.ID, kritter0021.Product, kritter0021.ProdDate,
kritter0021.RANK
FROM kritter0021
UNION ALL SELECT YourTable.ID, YourTable.Product, NULL AS ProdDate,
Max([RANK]) +1 AS RANK1
FROM YourTable INNER JOIN kritter0021 ON YourTable.Product =
kritter0021.Product
GROUP BY YourTable.ID, YourTable.Product;

SELECT kritter0021_X.ID, kritter0021_X.Product, kritter0021_X.ProdDate,
IIf([kritter0021_X_1].[ProdDate] Is
Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate]))
AS Expr1
FROM kritter0021_X INNER JOIN kritter0021_X AS kritter0021_X_1 ON
kritter0021_X.Product = kritter0021_X_1.Product
WHERE (((kritter0021_X_1.RANK)=[kritter0021_X].[RANK]+1))
ORDER BY kritter0021_X.ID, kritter0021_X.Product, kritter0021_X.ProdDate,
IIf([kritter0021_X_1].[ProdDate] Is
Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate]));


--
Build a little, test a little.

These queries worked but now I need to incorporate ID with the data in these
queries. Thanks!

ID Product Date Dif Date
1 A 1/23/2009 40
1 A 3/4/2009 65
1 A 5/8/2009 0
2 B 3/2/2009 28
2 B 3/30/2009 0

Use these three queries --

kritter0021
SELECT YourTable.Product, YourTable.ProdDate, (SELECT COUNT(*) FROM
YourTable AS [XX] WHERE [XX].Product = YourTable.Product AND [XX].ProdDate
<= YourTable.ProdDate) AS RANK
FROM YourTable
ORDER BY YourTable.Product, YourTable.ProdDate;

kritter0021_X
SELECT kritter0021.Product, kritter0021.ProdDate, kritter0021.RANK
FROM kritter0021
UNION ALL SELECT YourTable.Product, NULL AS ProdDate, Max([RANK]) +1 AS RANK1
FROM YourTable INNER JOIN kritter0021 ON YourTable.Product =
kritter0021.Product
GROUP BY YourTable.Product;

SELECT kritter0021_X.Product, kritter0021_X.ProdDate,
IIf([kritter0021_X_1].[ProdDate] Is
Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate]))
AS Expr1
FROM kritter0021_X INNER JOIN kritter0021_X AS kritter0021_X_1 ON
kritter0021_X.Product = kritter0021_X_1.Product
WHERE (((kritter0021_X_1.RANK)=[kritter0021_X].[RANK]+1))
ORDER BY kritter0021_X.Product, kritter0021_X.ProdDate,
IIf([kritter0021_X_1].[ProdDate] Is
Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate]));

--
Build a little, test a little.

That works when you are calculating it between two fields, not when you are
trying to use the previous date in the same field. So

Product Date Dif Date

I need DiffDate beween the first two records calculated. Not sure how to
make it do that. 3/4/09 - 1/23/09 = 40.