troubleshooting Question

SQL Grouping problem

Avatar of T Hoecherl
T HoecherlFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
4 Comments1 Solution230 ViewsLast Modified:
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?


Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros