CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Leo Vo
Leo Vo

Reputation: 10330

Help to the way to write a query for the requirement

I need to write a SQL-Server query but I don't know how to solve. I have a table RealtimeData with data:

 Time                   |    Value
4/29/2009 12:00:00 AM   |   3672.0000
4/29/2009 12:01:00 AM   |   3645.0000
4/29/2009 12:02:00 AM   |   3677.0000
4/29/2009 12:03:00 AM   |   3634.0000
4/29/2009 12:04:00 AM   |   3676.0000      // is EOD of day "4/29/2009"
4/30/2009 12:00:00 AM   |   3671.0000
4/30/2009 12:01:00 AM   |   3643.0000
4/30/2009 12:02:00 AM   |   3672.0000
4/30/2009 12:03:00 AM   |   3634.0000
4/30/2009 12:04:00 AM   |   3632.0000
4/30/2009 12:05:00 AM   |   3672.0000      // is EOD of day "4/30/2009"
5/1/2009 12:00:00 AM    |   3673.0000
5/1/2009 12:01:00 AM    |   3642.0000
5/1/2009 12:02:00 AM    |   3672.0000
5/1/2009 12:03:00 AM    |   3634.0000
5/1/2009 12:04:00 AM    |   3635.0000      // is EOD of day "5/1/2009"

I want to get the EOD's data of days which exist in table. (EOD = end of day). With the my sample's data, I will need to reture a table like following:

   Time      |    Value
4/29/2009    |  3676.0000
4/30/2009    |  3672.0000
5/1/2009     |  3635.0000

Note: I write the comment so that you can know where is EOD. And SQL Server is version 2005.

Note: the data in RealtimeData table is very large with more than 400.000 rows. Please help me to write in optimization.

Please help me to solve my problem. Thanks.

Upvotes: 2

Views: 308

Answers (4)

Atif Sheikh
Atif Sheikh

Reputation: 1

;With wcte as ( Select vTime,vValue,Row_Number() over (partition by Convert(DateTime,Convert(varchar(10),vTime,110)) order by vTime Desc) rno from @vTable )Select vTime,vValue from wcte where rno = 1

Upvotes: 0

silent.hill
silent.hill

Reputation: 1

Using Sql 92 is the best solution for anyone who dont want to use specified DB system.

Like this:

Select A.*
From RealtimeData A
Where A.RTime >= (
    select Max(B.RTime)
    From RealtimeData B
    Where Cast((B.RTime - A.RTime) as int) <= 0
)

Upvotes: -1

Bill Karwin
Bill Karwin

Reputation: 562330

WITH RankedRealTimeData AS (
  SELECT *, ROW_NUMBER() OVER (
      PARTITION BY CONVERT(VARCHAR(10), [TIME], 121) 
      ORDER BY Time DESC) AS RN
  FROM RealTimeData
)
SELECT * FROM RankedRealTimeData WHERE RN=1;

Upvotes: 3

Samuel Neff
Samuel Neff

Reputation: 74909

SELECT 
    CAST(Time as DATE) EodDate, 
    (
        SELECT  TOP 1
                Value
        FROM    RealtimeData I
        WHERE   CAST(I.Time AS Date) = CAST(O.Time AS Date)
        ORDER BY    Time DESC
    ) EodValue
FROM 
    RealtimeData O
GROUP BY CAST(Time as DATE)
ORDER BY CAST(Time as DATE)

Upvotes: 2

Related Questions