Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Hourly Data 1

Status
Not open for further replies.
NerdTop72

NerdTop72

Programmer
Mar 14, 2005
117
0
0
US
Hi,
I am lost again in SQL programming...

Last year I posted this question

I am now trying to take the days I have rain logged from this query GMMastros gave me...

Code: 
SELECT     FTBL_RainGauge.DateAndTime, ROUND(FTBL_RainGauge.Val, 3) AS val
FROM         FTBL_RainGauge INNER JOIN
                          (SELECT     TagIndex, DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0) AS Date, MAX(DateAndTime) AS LastRecordedRainFall
                            FROM          FTBL_RainGauge
                            WHERE      Val > 0 AND tagindex = '0' AND dateandtime > '12-1-2008'
                            GROUP BY TagIndex, DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0)) AliasName ON FTBL_RainGauge.TagIndex = AliasName.TagIndex AND 
                      FTBL_RainGauge.DateAndTime = AliasName.LastRecordedRainFall
ORDER BY FTBL_RainGauge.DateAndTime DESC

This query results are all the days that rain is logged.
How do I get the data of every hour of a day that rained?
Thanks
 
Sort by date Sort by votes
Can you show some sample data and expected results? It would go a long way in helping you.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Here is the results from the query above:

Code: 
DateandTime             Val
2009-02-22 23:58:32.000	5.0000000000000003E-2
2009-02-18 23:44:08.000	0.25
2009-02-15 23:57:40.000	0.01
2009-02-14 23:57:36.000	0.14000000000000001
2009-02-12 23:57:12.000	0.14999999999999999
2009-02-11 23:56:12.000	0.84999999999999998
2009-02-10 23:51:28.000	0.10000000000000001
2009-02-01 23:53:48.000	0.44
2009-01-27 23:48:31.000	0.01
2009-01-23 23:47:00.000	0.17999999999999999
2009-01-20 23:55:50.000	4.0000000000000001E-2
2009-01-19 23:55:43.000	7.0000000000000007E-2
2009-01-08 23:52:31.000	5.0000000000000003E-2
2009-01-07 23:50:45.000	0.02
2009-01-04 23:54:22.000	0.02
2008-12-28 23:51:53.000	0.29999999999999999
2008-12-27 23:51:49.000	5.9999999999999998E-2
2008-12-26 23:51:42.000	0.20000000000000001
2008-12-24 23:51:31.000	0.89000000000000001
2008-12-20 23:44:23.000	0.01
2008-12-18 23:58:17.000	2.9999999999999999E-2
2008-12-10 23:44:59.000	4.0000000000000001E-2
2008-12-09 23:55:03.000	1.01
2008-12-04 23:53:58.000	0.01
2008-12-03 23:53:17.000	0.01
2008-12-01 23:51:59.000	0.02

Here is a query of 12 hours on FTBL_RainGauge on a date when it had rained.

Code: 
SELECT     DateAndTime, TagIndex, Val
FROM         FTBL_RainGauge
WHERE     (DateAndTime BETWEEN CONVERT(DATETIME, '2008-12-24 00:00:00', 102) AND CONVERT(DATETIME, '2008-12-24 12:00:00', 102)) AND (TagIndex = 0)

Results:
Code: 
DateAndTime	    TagIndex	Val
12/24/2008 12:06:27 AM	0	0
12/24/2008 12:21:27 AM	0	0
12/24/2008 12:36:27 AM	0	0
12/24/2008 12:51:27 AM	0	0
12/24/2008 1:06:27 AM	0	0
12/24/2008 1:21:27 AM	0	0
12/24/2008 1:36:27 AM	0	0
12/24/2008 1:51:27 AM	0	0
12/24/2008 2:06:27 AM	0	0
12/24/2008 2:21:27 AM	0	0
12/24/2008 2:36:28 AM	0	0
12/24/2008 2:51:28 AM	0	0
12/24/2008 3:06:28 AM	0	0
12/24/2008 3:21:28 AM	0	0
12/24/2008 3:36:28 AM	0	0
12/24/2008 3:51:28 AM	0	0
12/24/2008 4:06:28 AM	0	0
12/24/2008 4:21:28 AM	0	0
12/24/2008 4:36:28 AM	0	0
12/24/2008 4:51:28 AM	0	0
12/24/2008 5:06:28 AM	0	0
12/24/2008 5:21:28 AM	0	0
12/24/2008 5:36:28 AM	0	0
12/24/2008 5:51:28 AM	0	0
12/24/2008 6:06:28 AM	0	0
12/24/2008 6:21:28 AM	0	0
12/24/2008 6:36:28 AM	0	0
12/24/2008 6:51:28 AM	0	0
12/24/2008 7:06:28 AM	0	9.99999977648258E-03
12/24/2008 7:21:28 AM	0	9.99999977648258E-03
12/24/2008 7:36:28 AM	0	9.99999977648258E-03
12/24/2008 7:51:28 AM	0	9.99999977648258E-03
12/24/2008 8:06:28 AM	0	9.99999977648258E-03
12/24/2008 8:21:28 AM	0	9.99999977648258E-03
12/24/2008 8:36:28 AM	0	9.99999977648258E-03
12/24/2008 8:51:28 AM	0	9.99999977648258E-03
12/24/2008 9:06:28 AM	0	9.99999977648258E-03
12/24/2008 9:21:28 AM	0	9.99999977648258E-03
12/24/2008 9:36:28 AM	0	9.99999977648258E-03
12/24/2008 9:51:28 AM	0	9.99999977648258E-03
12/24/2008 10:06:28 AM	0	9.99999977648258E-03
12/24/2008 10:21:29 AM	0	9.99999977648258E-03
12/24/2008 10:36:29 AM	0	9.99999977648258E-03
12/24/2008 10:51:29 AM	0	9.99999977648258E-03
12/24/2008 11:06:29 AM	0	9.99999977648258E-03
12/24/2008 11:21:29 AM	0	9.99999977648258E-03
12/24/2008 11:36:29 AM	0	9.99999977648258E-03
12/24/2008 11:40:42 AM	0	2.99999993294477E-02
12/24/2008 11:51:29 AM	0	2.99999993294477E-02

Thanks

 
Upvote 0 Downvote
Based on that last set of data (the raw data from the table), what output would you like to see?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
The last record for every hour.

Code: 
12/24/2008 12:51:27 AM    0    0
12/24/2008 1:51:27 AM    0    0
12/24/2008 2:51:28 AM    0    0
12/24/2008 3:51:28 AM    0    0  
12/24/2008 4:51:28 AM    0    0
12/24/2008 5:51:28 AM    0    0
12/24/2008 6:51:28 AM    0    0
12/24/2008 7:51:28 AM    0    9.99999977648258E-03
12/24/2008 8:51:28 AM    0    9.99999977648258E-03
12/24/2008 9:51:28 AM    0    9.99999977648258E-03
12/24/2008 10:51:29 AM    0    9.99999977648258E-03
12/24/2008 11:51:29 AM    0    2.99999993294477E-02

Thanks
 
Upvote 0 Downvote
Try this:

Code: 
SELECT     FTBL_RainGauge.DateAndTime, ROUND(FTBL_RainGauge.Val, 3) AS val
FROM         FTBL_RainGauge INNER JOIN
                          (SELECT     TagIndex, DateAdd([!]Hour[/!], DateDiff([!]Hour[/!], 0, DateAndTime), 0) AS Date, MAX(DateAndTime) AS LastRecordedRainFall
                            FROM          FTBL_RainGauge
                            WHERE      Val > 0 AND tagindex = '0' AND dateandtime > '12-1-2008'
                            GROUP BY TagIndex, DateAdd([!]Hour[/!], DateDiff([!]Hour[/!], 0, DateAndTime), 0)) AliasName ON FTBL_RainGauge.TagIndex = AliasName.TagIndex AND
                      FTBL_RainGauge.DateAndTime = AliasName.LastRecordedRainFall
ORDER BY FTBL_RainGauge.DateAndTime DESC

Note that this is the same query you posted earlier (in your original question), but I replaced Day with Hour. Give it a try... I think it's probably what you are looking for.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks George! It works!

I was reading books online datapart trying to figure this out the whole time.
lol
 
Upvote 0 Downvote
One more question...

I am trying to get just 1 day of hourly data to display and Im using this query

Code: 
SELECT     FTBL_RainGauge.DateAndTime, ROUND(FTBL_RainGauge.Val, 3) AS val
FROM         FTBL_RainGauge INNER JOIN
                          (SELECT     TagIndex, DateAdd(Hour, DateDiff(Hour, 0, DateAndTime), 0) AS Date, MAX(DateAndTime) AS LastRecordedRainFall
                            FROM          FTBL_RainGauge
                            WHERE      Val > 0 AND tagindex = '0' AND DateAndTime BETWEEN CONVERT(DATETIME, '2008-12-01 00:00:00', 102) AND CONVERT(DATETIME,              '2008-12-02 00:00:00', 102)
                            GROUP BY TagIndex, DateAdd(Hour, DateDiff(Hour, 0, DateAndTime), 0)) AliasName ON FTBL_RainGauge.TagIndex = AliasName.TagIndex AND 
                      FTBL_RainGauge.DateAndTime = AliasName.LastRecordedRainFall
ORDER BY FTBL_RainGauge.DateAndTime

is there a way to use the date add function instead of the between? All I want to resolve is 1 days worth of hourly data.

Thanks Again!
 
Upvote 0 Downvote
Actually.... when you want to get a full day's worth of data, and you have time's stored in your DateTime column, the best way to handle this is to use two conditions. Like this.

Code: 
SELECT FTBL_RainGauge.DateAndTime, ROUND(FTBL_RainGauge.Val, 3) AS val
FROM   FTBL_RainGauge INNER JOIN
         (SELECT TagIndex, DateAdd(Hour, DateDiff(Hour, 0, DateAndTime), 0) AS Date, MAX(DateAndTime) AS LastRecordedRainFall
          FROM   FTBL_RainGauge
          WHERE  Val > 0 
                 AND tagindex = '0' 
                 [!]AND DateAndTime >= CONVERT(DATETIME, '2008-12-01 00:00:00', 102)
                 AND DateAndTime <  CONVERT(DATETIME, '2008-12-02 00:00:00', 102)[/!]
          GROUP BY TagIndex, DateAdd(Hour, DateDiff(Hour, 0, DateAndTime), 0)
         ) AliasName 
           ON  FTBL_RainGauge.TagIndex = AliasName.TagIndex 
           AND FTBL_RainGauge.DateAndTime = AliasName.LastRecordedRainFall
ORDER BY FTBL_RainGauge.DateAndTime

This way, if there is an index on the DateAndTime column (and there should be for performance reasons), using multiple conditions will effectively use those indexes and your query will perform faster. For more information about WHY this is faster [google]SQL Server Sargable[/google].

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

coorsman
  • Locked
  • Question
Column Data Type. 2
Replies
9
Views
144
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
175
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Data types and sizes
Replies
18
Views
174
gmmastros
gmmastros
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
260
Chris Miller
Chris Miller
marn1997
  • Locked
  • Question
Large data base to back word engineer
Replies
5
Views
61
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top