Help me to write a SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help me to write a SQL query

Author

Topic

lulu2792
Starting Member

12 Posts

Posted - 2010-04-27 : 22:30:05

My question at here:
[url]http://stackoverflow.com/questions/2726369/help-to-the-way-to-write-a-query-for-the-requirement[/url]

Please help me. Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-27 : 22:54:20

similar to Bill's query but i would not use DATEPART(DAYOFYEAR, Time) as this is only valid for one year data. If you have more than 1 year data, you will be grouping it wrongly like 2009-02-01 with 2010-02-01 as both will give you the same value for DATEPART(DAYOFYEAR, Time).

Use DATEADD(DAY, DATEDIFF(DAY, 0, Time), 0) to strip of the time component of the TIME column for grouping / partioning.


;WITH RankedRealTimeData 
AS 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY DATEADD(DAY, DATEDIFF(DAY, 0, [Time]), 0) ORDER BY [Time] DESC) AS RN
    FROM   RealTimeData
)
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, [Time]), 0), Value
FROM   RankedRealTimeData 
WHERE   RN = 1;

KH
[spoiler]Time is always against us[/spoiler]

lulu2792
Starting Member

12 Posts

Posted - 2010-04-27 : 23:06:27

Run Well. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources