We help IT Professionals succeed at work.

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

Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
you want to read this article:
http://www.experts-exchange.com/A_3203.html

the "group by" key being the date value (without time)...
http://www.experts-exchange.com/A_1499.html
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
I *think* the query below will give you that (haven't tested it, don't have data for that).

If multiple rows have exactly the same "time", you will randomly get any one of them.  If you prefer, you can add another column to the ORDER BY to break "ties".
SELECT *
FROM (
    SELECT 
        *, ROW_NUMBER() OVER (PARTITION BY CONVERT(char(8), time, 112) ORDER BY time DESC) AS row_num
    FROM FIC_153_Dtotal
    WHERE
        time BETWEEN '1/1/2011 00:00:00.000' AND '11/7/2011 23:59:59.999'
) AS derived
WHERE row_num = 1

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.
T HoecherlDeveloper

Author

Commented:
Thank you.  This worked perfectly.