By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,570 Members | 1,251 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,570 IT Pros & Developers. It's quick & easy.

Date Difference for different records with same GroupId

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


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

This example uses the Northwind sample application. Pretend that
CustomerID=GroupId, and OrderDate=VisitDate.
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.CustomerID, Orders.OrderDate, DateDiff("d",(select
max(OrderDate) from Orders as O2 where O2.CustomerID=Orders.CustomerID
and O2.OrderDate<Orders.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


Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

P: n/a
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.CustomerID, Orders.OrderDate,
DateDiff("d",[Orders].[OrderDate],(select max(OrderDate) from Orders
as O2 where O2.CustomerID=Orders.CustomerID)) 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.


Nov 13 '05 #4

P: n/a
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.CustomerID, Orders.OrderDate,
DateDiff("d",[Orders].[OrderDate],(select max(OrderDate) from Orders
as O2 where O2.CustomerID=Orders.CustomerID)) 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.


Nov 13 '05 #5

P: n/a
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.CustomerID, Orders.OrderDate,
DateDiff("d",[Orders].[OrderDate],(select max(OrderDate) from Orders
as O2 where O2.CustomerID=Orders.CustomerID)) 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.


Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.