DateTime data, special request... 2

[NerdTop72]

Please help.

I have rain gauges that record how much rain fall is in any give time in my city. The tools that record the data in SQL sever record every minute for charting purposes, weather there is rain or not.
I am not good at writing SQL queries other then normal select,insert,delete, & update.

I need to get the last recorded rainfall of the day when there is rain and I do not know how to do.

SELECT DISTINCT CONVERT(datetime, CONVERT(varchar, DateAndTime, 10)), Val
FROM         FTBL_RainGauge
WHERE     (Val > '0') AND (TagIndex = '1')

I tried dateandtime > ('23:58:00') and get bad results as well

Val is rainfall in inches, so of course this has to be more then 0
Tagindex is the rain gauge ID

This code doesn't necessarily give me the last rain information of the day, I am lost.

Thanks,
Jason

 

[gmmastros]

Try this...

Select TagIndex, Max(DateAndTime) As LastRecordedRainFall
From   FTBL_RainGuage
Where  Val > 0
Group By TagIndex

If this works, and you would like me to explain it, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[genomon]

Try selecting TOP 1 ... WHERE Val > '0' ... ORDER BY yourdatefield DESC

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[NerdTop72]

I will have to mess around more with top and max. it hasn't given me the results i need. but your pointing me in the right direction.

I need to look at all the data, past 3 years, and find all the days that it did rain, and then find out what the rain was at the end of each day. the max date&time statement is on the right track. How would i get the max date&time of every day in a year where there was rain?

Thanks alot!
Jason

 

[genomon]

Maybe try a GROUP BY clause, grouping on each day?
My T-SQL isn't what it oughta be.....

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[gmmastros]

This becomes a lot more interesting now.

Try this...

Select FTBL_RainGuage.*
From   FTBL_RainGuage
       Inner Join (
          Select TagIndex, 
                 DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0) As Date, 
                 Max(DateAndTime) As LastRecordedRainFall
          From   FTBL_RainGuage
          Where  Val > 0
          Group By TagIndex, DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0)
          ) As AliasName
          On FTBL_RainGuage.TagIndex = AliasName.TagIndex
          And FTBL_RainGuage.DateAndTime = AliasName.LastRecordedRainFall

If this works, and you would like me to explain it, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[genomon]

Nice, George!

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[NerdTop72]

Thanks George,

Yes I would appreciate and explanation and I am getting an error in my SQL query analyzer at the last as Aliasname so I am not sure if the results are what I am looking for...

SELECT     FTBL_RainGuage.*
FROM         FTBL_RainGuage INNER JOIN
                          (SELECT     TagIndex, DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0) AS Date, MAX(DateAndTime) AS LastRecordedRainFall
                            FROM          FTBL_RainGuage
                            WHERE      Val > 0
                            GROUP BY TagIndex, DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0) as AliasName ON FTBL_RainGuage.TagIndex = AliasName.TagIndex AND 
                                                   FTBL_RainGuage.DateAndTime = AliasName.LastRecordedRainFallLL )

unfortunately since I do not understand this SQL query i cannot fix it on my own. ;-)
Thanks,
Jason

 

[gmmastros]

It looks to me like you didn't copy/paste it properly. I encourage you to try again.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NerdTop72]

Thanks you were right!

It seems like it works, i am checking my data to be sure. I am getting returned information close to 11:59pm for each day. Feel free to explain it if you would like. I am a better learner from example. I am changing around some of the info with the SQL statement designer to achieve different results to help me understand what is going on.

Thanks again,
Jason

 

[gmmastros]

Sorry for the delay. I couldn't get to an internet connection until now. Anyway...

There's a lot to explain.

First, it is correct to store Date and Time in the same column. However, since you want to show the last rainfall for each day, we need an effective way to separate the date from the time.

This code does that.
DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0)

Let's take a closer look at it (starting with the inner DateDiff).

Suppose the row we are looking at has DateAndTime = Sept. 11, 2008 10:53 AM.

DateDiff returns an integer representing the difference between 2 dates in whatever units we specify in the first argument. The 0 in the formula actually represents Day 0 (or Jan 1, 1900). So...

DateDiff(Day, Jan 1 1900, Sept 11 2008 10:53 AM)

Remember, this returns an integer, so the result would be, 39700. Next, we add that number of days to the 0 date, and get, "Sept 11, 2008". He have effectively removed the time component.

Next....

How do we use that to our advantage? Remember, the goal is to show the last time, each day, where there was rainfall. It's the 'Each Day' that we need to work on. We need to group the data on day (without time).

          Select TagIndex, 
                 DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0) As Date, 
                 Max(DateAndTime) As LastRecordedRainFall
          From   FTBL_RainGuage
          Where  Val > 0
          Group By TagIndex, DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0)

First, look at the group by. We are grouping on TagIndex (the unique identifier for each rain guage, AND date (without time). There's a filter on this data for Val > 0. Also notice the return columns. We are returning TagIndex, Date (without time), and Max(DateAndTime). Technically, we don't need to return the date without time because we don't use this anywhere. It's important to group on this, though.

If you run the above query, you will see what I'm talking about. It'll show the TagIndex, Date, and DateAndTime. If this is ALL you are looking for, you can use that query. I assumed that you would also want to know other information, so I took this one step further. For example, if you want to know what the Val column shows for that date and time, we need a clever way of retrieving that information. We cannot add it to the query directly because we would need to include this in the group by. Since there could be (and likely will be) different val's throughout the day, including it in the group by would NOT be the right thing to do.

No. In fact, the only way to get that information is to go back to the original table. That's where the derived table comes in. Let's look at the final query I posted before.

Select FTBL_RainGuage.*
From   FTBL_RainGuage
       Inner Join [!]([/!]
          [blue]Select TagIndex, 
                 DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0) As Date, 
                 Max(DateAndTime) As LastRecordedRainFall
          From   FTBL_RainGuage
          Where  Val > 0
          Group By TagIndex, DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0)[/blue]
          [!]) As AliasName[/!]
          On [green]FTBL_RainGuage.TagIndex = AliasName.TagIndex
          And FTBL_RainGuage.DateAndTime = AliasName.LastRecordedRainFall[/green]

Notice the part in blue. It's the same as the previous query, right? That part is considered a derived table. To use a derived table in a bigger query, you need to put parenthesis around it and also give it an alias (that's the part in red). Now, we need to identify the join conditions (the part in green). Since we are inner joining this derived table back to the original table, the data in the derived table part acts very much like a filter. The only rows returned from the bigger query will be those rows that match the data returned from the derived table.

There are a lot of heavy duty concepts explained here:

1. Removing Time from a DateTime value
2. Grouping by a formula
3. Derived tables.

If you have any questions, let me know and I will explain more.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom