SQL Grouping problem
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?
T
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?
T
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If execution is a big factor, instead of CONVERT you can use:
DATEADD(DAY, DATEDIFF(DAY, 0, time), 0)
That is less clear but (typically) runs faster than CONVERT.
DATEADD(DAY, DATEDIFF(DAY, 0, time), 0)
That is less clear but (typically) runs faster than CONVERT.
ASKER
Thank you. This worked perfectly.
https://www.experts-exchange.com/A_3203.html
the "group by" key being the date value (without time)...
https://www.experts-exchange.com/A_1499.html