June 7, 2008 at 12:52 pm
This is my table
SEL
Date_Taken | Main_ID | Time | Daily_Rainfall
---------------------------------------------------
...
...
...
4/2/2005 | 202 | 1015 | 12
4/2/2005 | 202 | 1045 | 76
4/2/2005 | 202 | 1215 | 17
4/3/2005 | 202 | 1015 | 2
4/3/2005 | 202 | 1045 | 2
4/3/2005 | 202 | 1215 | 7
4/3/2005 | 203 | 715 | 2
4/3/2005 | 203 | 1345 | 2
4/3/2005 | 203 | 1530 | 7
...
...
...
5/29/2005 | 203 | 1100 | 56
5/29/2005 | 203 | 1130 | 156
5/29/2005 | 203 | 1145 | 256
5/30/2005 | 203 | 1130 | 89
5/30/2005 | 203 | 1145 | 77
...
...
...
**This table contains rainfall value for each Main_ID from time to time
Station_Info
State | Main_ID
--------------------
SEL | 202
SEL | 203
SEL | 204
SEL | 205
SEL | 209
...
...
***This table contains Main_ID location
These following query will display MAX Daily_Rainfall for each Main_ID, each Date_Taken.
SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME,
t1.DAILY_RAINFALL
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID
ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1
INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND
t1.RowNo=1
AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-3,GETDATE()),101)
AND t1.DATE_TAKEN<CONVERT(VARCHAR(10), GETDATE(), 101)
WHERE t2.STATE='SEL'
ORDER BY MAIN_ID,DATE_TAKEN,TIME
It will display as follow:-
Date_Taken | Main_ID | Time | Daily_Rainfall
-------------------------------------------------
...
...
4/2/2005 | 202 | 1045 | 76
4/3/2005 | 202 | 1215 | 7
...
...
5/29/2005| 203 | 1145 | 256
5/30/2005| 203 | 1130 | 89
...
...
I hope someone can show me the best way to do the SUM(Daily_Rainfall) each month, so the result shown as follow
Date_Taken |Main_ID | Daily-Rainfall
------------------------------------------------
...
...
4/1/2005 | 202 | 83
5/1/2005 | 203 | 345
**Date_Taken will display starting day of the month 4/1/2005, 5/1/2005, 6/1/2005
:hehe:
June 9, 2008 at 7:33 am
Try these:
;with MaxRain (DT, MID, Rainfall) as -- Max Rainfall per Day
(select date_taken, main_id, max(daily_rainfall)
from dbo.SEL
group by date_taken, main_id)
select Date_Taken, Main_ID, Time, Rainfall
from dbo.SEL
inner join MaxRain
on date_taken = dt
and main_id = mid
and daily_rainfall = rainfall
;with -- Total Rainfall per Month
Numbers (Number) as
(select row_number() over (order by t1.object_id)
from sys.all_objects t1
cross join sys.all_objects t2),
Dates (MonthBegin, MonthEnd) as
(select dateadd(month, number, '1/1/2000'),
dateadd(month, number+1, '1/1/2000') - 1
from numbers)
select MonthBegin, Main_ID, sum(daily_rainfall) as Daily_Rainfall
from dbo.SEL
inner join Dates
on SEL.Date_Taken between MonthBegin and MonthEnd
order by MonthBegin, Main_ID
If your Date_Taken field is not datetime/smalldatetime, you'll need to convert it for these to work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply