Calculate difference between MAX and MIN dates

[swimtiger]

I have a field that contains the date that a client was placed into a job. There are multiple placements for each client, and I would like to calculate the number of days between the first and last placements for each client.

 

[dodge20]

See if this article helps you

http://www.fabalou.com/Access/Modules/datediff.htm

Dodge20

 

[dodge20]

If I remeber right this is a little more tricky than you would think. I think you might get an error message if you don't use a temp table. I found this code, which is similar to what you want.


SELECT COUNT(user_id)
FROM (SELECT user_id, MAX(TableDate) MaxUserDate
FROM board
GROUP BY user_id) TempTable
WHERE DATEDIFF(day, MaxUserDate, getdate()) <= 30

Dodge20

 

[swimtiger]

Hi Dodge20,
Thanks for the fast reply. I have to admit that I am just learning SQL, so I could use a little explanation, please. I am trying to calculate the difference between the MAX and MIN for a date field.

Where does the MIN date fit into your query?:
SELECT COUNT(user_id)
FROM (SELECT user_id, MAX(TableDate) MaxUserDate
FROM board
GROUP BY user_id) TempTable
WHERE DATEDIFF(day, MaxUserDate, getdate()) <= 30

 

[dodge20]

Here is how to use max and min together

SELECT COUNT(user_id)
FROM (SELECT user_id, MAX(TableDate) MaxUserDate, Min(TableDate) MinUserDate
FROM board
GROUP BY user_id) TempTable
WHERE DATEDIFF(day, MaxUserDate, MinUserDate)

Dodge20

 

[swimtiger]

When I run this query:
SELECT COUNT(tblClient.ClientID)
FROM (SELECT (tblClient.ClientID), MAX(tblJobPlacement.PlacementDate) MaxUserDate, Min(tblJobPlacement.PlacementDate) MinUserDate
FROM board
GROUP BY user_id) TempTable
WHERE DATEDIFF(day, MaxUserDate, MinUserDate);

I get this message:
Syntax error (missing operator) in query expression
'MAX(tblJobPlacement.PlacementDate) MaxUserDate'

Thanks for your patience with an SQL newbie.

 

[dodge20]

I wasn't aware you were pulling from 2 tables. Your query will change quite a bit then. You will have to join the 2 tables together. Maybe you don't need info from the 2 tables. I think you are just using userid because that is what I used in my example. Try just changing tblClient.clientID to a field in the tblJobPlacement table.

Also board was the name of the table I used you will want to change it to tblJobPlacement.

SELECT COUNT(examplefield)
FROM (SELECT examplefield, MAX(tblJobPlacement.PlacementDate) MaxUserDate, Min(tblJobPlacement.PlacementDate) MinUserDate
FROM tblJobPlacement
GROUP BY user_id) TempTable
WHERE DATEDIFF(day, MaxUserDate, MinUserDate);

Dodge20

 

[swimtiger]

Ok,

I ran your query (I replaced examplefield with clientid), and I got the same error message.

Syntax error (missing operator) in query expression
'MAX(tblJobPlacement.PlacementDate) MaxUserDate'.



Any ideas?

 

[dodge20]

I am running out of ideas here. Give this a try.


SELECT COUNT(examplefield)
FROM (SELECT examplefield, MAX(PlacementDate) AS MaxUserDate, Min(PlacementDate) AS MinUserDate
FROM tblJobPlacement
GROUP BY examplefield) tblJobPlacement
WHERE DATEDIFF(day, MaxUserDate, MinUserDate);

Dodge20

 

[dodge20]

Now that I think about it, I don't think you even need the temp table. This should work.

SELECT MAX(PlacementDate) AS MaxUserDate, Min(PlacementDate) AS MinUserDate
FROM tblJobPlacement
WHERE DATEDIFF(day, MaxUserDate, MinUserDate);

Dodge20

 

[r937]

try this

[tt]select clientid
, datediff(&quot;d&quot;
, max(PlacementDate)
, min(PlacementDate) ) as diff
from board
group
by clientid[/tt]

rudy

 

[swimtiger]

Ok, thanks for the help guys. You definitely got me in the right direction. Here is what ended up working for me:
SELECT tblJobPlacement.ClientID, Max([tblJobPlacement]![PlacementDate])-Min([tblJobPlacement]![PlacementDate])
FROM tblJobPlacement
GROUP BY tblJobPlacement.ClientID;