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).
Code:
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.
Code:
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
