List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 27 2005 9:04pm
Subject:Re: query question
View as plain text  
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

Thread
query questionjabbott27 Apr
  • RE: query questionmathias fatene27 Apr
  • Re: query questionPeter Brawley27 Apr