I have a database with several tables, each of which contains thousands of rows. There are three columns: Tag, Time, and Value. In a given table, all of the values in the tag column are identical, but the time and value columns will have different values. For example, in the table named FIC_153_Dtotal, the value in the Tag column for every row will bt FIC_153_Dtotal, but each row will have a different Time and Value. There are a couple of thousands rows for each day. This query gives me the last value for a given day:
select * from FIC_153_Dtotal where TIME = (select MAX(time) from FIC_153_Dtotal where time between '12/4/2011 00:00:00.000' and '12/4/2011 23:59:59.999')
But what I really want to do is put in a range of several days and be able to generate the last value in each day of the date range. So, for example, if I use the range:
between '1/1/2011 00:00:00.000' and '11/7/2011 23:59:59.999'
I want to get 7 values -- the last value for each day between 11/1 and 11/7.
I think I could get this using a cursor, but the query will be in an SRS report, so I think I need to do some kind of grouping, rather than using a cursor. Any ideas on how to do this?
the "group by" key being the date value (without time)...