JA,
To have a SELECT statement generate a row for every day in the year,
either your raindata table needs a row for every day in the year, or you
need another table which has a row for every day of the year. Supposing
you have such a table, call it 'calendar' with a date column named
'yearday', then you could retrieve daily rainfall including NULLs with
SELECT calendar.yearday, rainfall.amount
FROM calendar
LEFT JOIN rainfall ON calendar.yearday = raindata.entrydate
ORDER BY calendar.yearday;
or if there can be multiple raindata rows for a date then
SELECT calendar.yearday, SUM( rainfall.amount )
FROM calendar LEFT JOIN rainfall ON calendar.yearday = raindata.entrydate
GROUP BY calendar.yearday;
but your rainfall column ought to be numeric.
Peter Brawley
http://www.artfulsoftware.com
-----
jabbott@stripped wrote:
>I have a table that the important parts look something like:
>keynum int,
>entryDate datetime,
>amount varchar(10)
>
>What I want to do is a query that gets me every day of the year and just has null
> values for the days that don't have anything in the amount column. Is something like that
> possible with sql? In fact, what I would really like is:
>
>select month(entryDate) as monthPart, day(entryDate) as dayPart, amount
>from raindata
>order by dayPart, monthPart
>
>just with the whole year filled in. it will make my later code simplier if I can not
> have to test for values as much.
>
>--ja
>
>
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.3 - Release Date: 4/25/2005
