July 5, 2016 at 4:51 pm
Hi team,
I am currently working on weather data to see if there is rain in past 3 hours. I have data for 10 years.
The sample data is presented below
ID datetime rain
1 2016/01/01 01:00:00 0
1 2016/01/01 03:00:00 8
1 2016/01/01 05:00:00 6
1 2016/01/01 06:00:00 0
2 2016/01/01 08:00:00 6
2 2016/01/01 10:00:00 0
2 2016/01/01 15:00:00 0
3 2016/01/01 16:00:00 0
3 2016/01/01 20:00:00 10
3 2016/01/01 21:00:00 3
4 2016/01/01 23:00:00 4
4 2016/01/02 01:00:00 5
4 2016/01/02 05:00:00 0
I need to have a column to see if there is any rain in past 3 hours. if there is rain it should show '1' else '0'.
the output should look this
0
1
1
1
1
1
0
0
1
1
1
1
0
I have hourly data for 10 years. Any help is appreciated.
Thanks in Advance.
July 6, 2016 at 8:48 am
duddu.venkataramana (7/5/2016)
Hi team,I am currently working on weather data to see if there is rain in past 3 hours. I have data for 10 years.
The sample data is presented below
ID datetime rain
1 2016/01/01 01:00:00 0
1 2016/01/01 03:00:00 8
1 2016/01/01 05:00:00 6
1 2016/01/01 06:00:00 0
2 2016/01/01 08:00:00 6
2 2016/01/01 10:00:00 0
2 2016/01/01 15:00:00 0
3 2016/01/01 16:00:00 0
3 2016/01/01 20:00:00 10
3 2016/01/01 21:00:00 3
4 2016/01/01 23:00:00 4
4 2016/01/02 01:00:00 5
4 2016/01/02 05:00:00 0
I need to have a column to see if there is any rain in past 3 hours. if there is rain it should show '1' else '0'.
the output should look this
0
1
1
1
1
1
0
0
1
1
1
1
0
I have hourly data for 10 years. Any help is appreciated.
Thanks in Advance.
Hi Venkat,
First let me say welcome to SSC I personally always like to see new people come to the forums looking for help and asking questions.
With that said, please review THIS[/url] on how to post these kinds of questions to get help, this helps everyone get going fast and gets a little more motivated to help you when they can setup the data needed and work a solution.
Here is what I came up with.
declare @tmpWeatherData table (ID int, RecDate Datetime, RainAMT int)
INSERT INTO @tmpWeatherData (ID,RecDate, RainAMT)
VALUES
(1,'2016/01/01 01:00:00',0),(1,'2016/01/01 03:00:00',8),(1,'2016/01/01 05:00:00',6),(1,'2016/01/01 06:00:00',0),(2,'2016/01/01 08:00:00',6)
,(2,'2016/01/01 10:00:00',0),(2,'2016/01/01 15:00:00',0),(3,'2016/01/01 16:00:00',0),(3,'2016/01/01 20:00:00',10),(3,'2016/01/01 21:00:00',3)
,(4,'2016/01/01 23:00:00',4),(4,'2016/01/02 01:00:00',5),(4,'2016/01/02 05:00:00',0)
SELECT
CASE
WHEN t2.RainAMT > 0 THEN 1
ELSE 0
END as RainPrev3Hrs
FROM @tmpWeatherData t1
OUTER APPLY (SELECT SUM(RainAMT) as RainAMT FROM @tmpWeatherData WHERE RecDate BETWEEN DateAdd(hh,-3,t1.RecDate) and t1.RecDate ) t2
ORDER BY t1.ID,t1.RecDate
Results
0
1
1
1
1
1
0
0
1
1
1
1
0
July 6, 2016 at 10:06 am
Hello Smendle,
Thank you very much for the reply. I have gone through the link posted. I will make sure to follow those steps for any further help.
Regards,
Venkat
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply