[thelist] sql help

[Joshua Olson]

----- Original Message ----- 
From: "Richard Bennett" <richard.b at gritechnologies.com>
Sent: Friday, September 12, 2003 3:23 PM


> Now I'm trying to join this to a list of all the days in the month, to
show
> the user's status at that time:
>
> Of course I get a <null> on all days except 01 and 04 when I use a regular
> join.
>
> Any idea how I can use the last available value, if the date doesn't have
a
> value of it's own?

Richard,

One approach may be to populate a table with every date within the range.
It need only have one column.

Let's call that table "thedates", and the shortdate column "aday"

>From your explaination I think you've already done this join.

To get the data you want, try something like this:

SELECT aday, user
, (SELECT TOP 1 status
    FROM userdata ud2
    WHERE ud2.thedate <= userdata.thedate
    ORDER BY ud2.thedate DESC) AS status
FROM thedates
LEFT OUTER JOIN userdata
ON userdata.thedate = thedates.aday
ORDER BY aday

This is of course the quick and dirty brute force method.

HTH,

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.210.0168