Differences between lists of dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2000 and above.

Say I have a table like this:
ID Change_Date Value
1 01/05/2003 Red
1 13/05/2003 Blue
1 14/05/2003 Red
2 10/05/2003 Blue
2 13/05/2003 Red
2 15/05/2003 Blue

Assuming today is 16/05/2003 and we count all days up to, but not including
today, how do I create a SELECT query that will transform it into

ID Colour Total Days
1 Red 14
1 Blue 1
2 Red 2
2 Blue 4

Regards
 
Take a look at the DateDiff Function and use it to create a calculated column.
 
Thanks John. The date Datediff part is easy. What I want to know, is what SQL
I would use to compare one date to the next date given that the dates in
question are not on the same row. Note that example is sorted by ID then
change_date. What SQL would you write to produce the results listed below.
You can't simply use datediff.

Regards
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Wouldn't it be:

PARAMETERS [End Date] DateTime;
SELECT ID, Colour, Count(*) As [Total Days]
FROM table_name
WHERE Change_Date < [End Date]
GROUP BY ID, Colour

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYqPwIechKqOuFEgEQIaqgCeOOBGFKI0GCQzLZipqpnXWbc1KTAAnRap
d8Cgl0SAzsIcMjSjVfvEM/us
=6RtP
-----END PGP SIGNATURE-----
 
Not quite, but you get my point, so many thanks. I found a potentially good
solution that I'm fiddling with ath the moment. I'll post back if I can get
it to work. I also saw Allen Browne's post to the
http://support.microsoft.com/?id=210504
link. The pitfall (if any) of using that is that if you wanted to use
largely the same SQL in a different environemnt you might get caught out with
the DLOOKUP. I'm thinking thta using more generic SQL, you can get the
previous/next record with a self join, that is not an equi-join. The join on
dates/times field would have to be a "<" or a ">" and you'd have to do a
group by with a min(datetime) or max(datetime) depending on what you want to
achieve. It's a bit gibberish at the moment but I'll post back if it works.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Wouldn't it be:

PARAMETERS [End Date] DateTime;
SELECT ID, Colour, Count(*) As [Total Days]
FROM table_name
WHERE Change_Date < [End Date]
GROUP BY ID, Colour

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYqPwIechKqOuFEgEQIaqgCeOOBGFKI0GCQzLZipqpnXWbc1KTAAnRap
d8Cgl0SAzsIcMjSjVfvEM/us
=6RtP
-----END PGP SIGNATURE-----

Thanks John. The date Datediff part is easy. What I want to know, is what SQL
I would use to compare one date to the next date given that the dates in
question are not on the same row. Note that example is sorted by ID then
change_date. What SQL would you write to produce the results listed below.
You can't simply use datediff.

Regards

:
Click to expand...
Click to expand...
 
Back
Top