Date Difference for different records with same GroupId

[tamilan71]

Hello All

I have table with following fields:

GroupId VisitDate
1 10/19/1993
1 11/24/1998
2 10/18/1993
2 10/29/1998
3 10/21/1993
3 11/03/1998
4 10/25/1993
4 10/29/1998
4 04/29/1999
5 04/26/1994
5 03/29/1999
6 09/06/2001
7 09/07/1995
7 09/12/2000

In this table, I want to create a third field dynamically, where the
difference in date between the two visits for the same GroupId should
be displayed, so the output should be like this...

GroupId VisitDate Diff_VisitDate
1 10/19/1993 0
1 11/24/1998 5 Years

2 10/18/1993 0
2 10/29/1997 4 Years

3 10/21/1993 0
3 11/03/1998 5 Years

4 10/25/1993 0
4 10/29/1994 1 Year
4 04/29/1999 5 Years 6 Months

5 04/26/1994 0
5 03/29/1999 5 Years
5 03/28/2000 6 Years

6 09/06/2001 0

7 09/07/1995 0
7 09/12/2001 6 Years

So basically for the first VisitDate for every GroupId, Diff_VisitDate
should be 0 and for every successive VisitDate, this date should be
subtracted from the first VisitDate and displayed in Diff_VisitDate. Is
it possible to do this for a database of 3000 records?
Any help in this regard will be highly appreciated.
Thanks in advance.
Anita

[Tom van Stiphout]

On 31 Jan 2005 20:11:15 -0800, ta*******@yahoo .com wrote:

This example uses the Northwind sample application. Pretend that
CustomerID=Grou pId, and OrderDate=Visit Date.
Since you are asking for difference in dates, we know we have to use
the DateDiff function. It takes two dates: the one for the current
row, and the maximum date smaller than that of the current row (which
we get using a subselect).

Paste this into a new query (sql view) in the Northwind database:

SELECT Orders.Customer ID, Orders.OrderDat e, DateDiff("d",(s elect
max(OrderDate) from Orders as O2 where O2.CustomerID=O rders.CustomerI D
and O2.OrderDate<Or ders.OrderDate) ,[Orders].[OrderDate]) AS
DiffOrderDate FROM Orders;

If you like this solution, promise me one thing: do NOT store this
"calculated value" in a new field. Rather use this query to calculate
the difference on the fly. That way you're not violating an important
relational database design rule.

3000 records: peanuts.

-Tom.

Hello All

I have table with following fields:

GroupId VisitDate
1 10/19/1993
1 11/24/1998
2 10/18/1993
2 10/29/1998
3 10/21/1993
3 11/03/1998
4 10/25/1993
4 10/29/1998
4 04/29/1999
5 04/26/1994
5 03/29/1999
6 09/06/2001
7 09/07/1995
7 09/12/2000

In this table, I want to create a third field dynamically, where the
difference in date between the two visits for the same GroupId should
be displayed, so the output should be like this...

GroupId VisitDate Diff_VisitDate
1 10/19/1993 0
1 11/24/1998 5 Years

2 10/18/1993 0
2 10/29/1997 4 Years

3 10/21/1993 0
3 11/03/1998 5 Years

4 10/25/1993 0
4 10/29/1994 1 Year
4 04/29/1999 5 Years 6 Months

5 04/26/1994 0
5 03/29/1999 5 Years
5 03/28/2000 6 Years

6 09/06/2001 0

7 09/07/1995 0
7 09/12/2001 6 Years

So basically for the first VisitDate for every GroupId, Diff_VisitDate
should be 0 and for every successive VisitDate, this date should be
subtracted from the first VisitDate and displayed in Diff_VisitDate. Is
it possible to do this for a database of 3000 records?
Any help in this regard will be highly appreciated.
Thanks in advance.
Anita

[tamilan71]

Tom
THank you so much for your reply. I am sorry to tell you that I have 2
problems while I used your solution, it subtracts from the previus
orderdate and not from the max orderdate of that group and the second
problem is that it gives number of days, is it anyway possible to
convert it to years/months.
My apologises if I am asking for too much.
Thanks again.

[Tom van Stiphout]

On 1 Feb 2005 05:17:32 -0800, ta*******@yahoo .com wrote:

No problem; just take out the part that says that the date needs to be
less than that of the current row:
SELECT Orders.Customer ID, Orders.OrderDat e,
DateDiff("d",[Orders].[OrderDate],(select max(OrderDate) from Orders
as O2 where O2.CustomerID=O rders.CustomerI D)) AS DiffOrderDate
FROM Orders;

I'll leave the conversion from days to years up to you. One hint: if
you can get away with months, just change a single letter in the
DateDiff function. If you truly need "Four Years and Eight Months" a
custom function would likely be needed.

-Tom.

Tom
THank you so much for your reply. I am sorry to tell you that I have 2
problems while I used your solution, it subtracts from the previus
orderdate and not from the max orderdate of that group and the second
problem is that it gives number of days, is it anyway possible to
convert it to years/months.
My apologises if I am asking for too much.
Thanks again.

[tamilan71]

Thanks Tom
It worked and i had to write a custom function to make it to "Four Year
and Eight Months".
Thanks for your wonderful help. I really appreciate it.
Tom van Stiphout wrote:

On 1 Feb 2005 05:17:32 -0800, ta*******@yahoo .com wrote:

No problem; just take out the part that says that the date needs to be less than that of the current row:
SELECT Orders.Customer ID, Orders.OrderDat e,
DateDiff("d",[Orders].[OrderDate],(select max(OrderDate) from Orders
as O2 where O2.CustomerID=O rders.CustomerI D)) AS DiffOrderDate
FROM Orders;

I'll leave the conversion from days to years up to you. One hint: if
you can get away with months, just change a single letter in the
DateDiff function. If you truly need "Four Years and Eight Months" a
custom function would likely be needed.

-Tom.

Tom
THank you so much for your reply. I am sorry to tell you that I have 2problems while I used your solution, it subtracts from the previus
orderdate and not from the max orderdate of that group and the secondproblem is that it gives number of days, is it anyway possible to
convert it to years/months.
My apologises if I am asking for too much.
Thanks again.

[tamilan71]

Hello Everybody
With regard to the same query, I have another problem and I just dont
understand where am I going wrong and I m not getting the result I
need.
Following the suggestion given by Tom van and everybody else, I solved
my previous problem and I also converted the Diff_VisitDate into Months
like this :

GroupId VisitDate Diff_VisitDate( in months)
1 10/19/1993 0
1 11/24/1998 60

2 10/18/1993 0
2 10/29/1997 48

3 10/21/1993 0
3 11/03/1998 60

4 10/25/1993 0
4 10/29/1994 12
4 04/29/1999 66

5 04/26/1994 0
5 03/29/1999 60
5 03/28/2000 72

6 09/06/2001 0

7 09/07/1995 0
7 09/12/2001 72

Now I am trying to create a query, which gives me the GroupId that
does/does not have a particular value.
But when I create the query, it runs for each and every record, but i
just want the result for the set of each GroupId.

Say if I want to find the list of GroupId that does not have 60
(Diff_VisitDate ), the result should be

GroupId
2
4
6
7

Is it possible to do this ? Can somebody tell me how to group the
records in the query by GroupId.

Thanks again in advance for all your help. Kindly excuse me if this is
a silly question.
ta*******@yahoo .com wrote:

Thanks Tom
It worked and i had to write a custom function to make it to "Four Year and Eight Months".
Thanks for your wonderful help. I really appreciate it.
Tom van Stiphout wrote:

On 1 Feb 2005 05:17:32 -0800, ta*******@yahoo .com wrote:

No problem; just take out the part that says that the date needs to be

less than that of the current row:
SELECT Orders.Customer ID, Orders.OrderDat e,
DateDiff("d",[Orders].[OrderDate],(select max(OrderDate) from Orders

as O2 where O2.CustomerID=O rders.CustomerI D)) AS DiffOrderDate
FROM Orders;

I'll leave the conversion from days to years up to you. One hint: if you can get away with months, just change a single letter in the
DateDiff function. If you truly need "Four Years and Eight Months" a custom function would likely be needed.

-Tom.

Tom
THank you so much for your reply. I am sorry to tell you that I

have 2problems while I used your solution, it subtracts from the previus
orderdate and not from the max orderdate of that group and the secondproblem is that it gives number of days, is it anyway possible to
convert it to years/months.
My apologises if I am asking for too much.
Thanks again.