Want to add date condition in query

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 2008 Forums

Transact-SQL (2008)

Want to add date condition in query

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-10-30 : 12:59:02

I am trying to use the following query.
i want to get last 7 days rows based on status_date, i would like to partition the data after it gets last 7 days data on that would like to apply the partition.


SELECT acct_unit, STATUS_DATE, STATUS_FLAG, SUR_KEY
FROM (select acct_unit, STATUS_DATE, STATUS_FLAG, SUR_KEY,
 Row_number() over(partition by acct_unit order by STATUS_DATE DESC) rn 
 FROM TSPI
     ) temp WHERE rn <=2

Thank you very much for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 13:35:05

sorry not fully clear. partition for what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-30 : 15:44:29

NOt clear either, may be add a where clause?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SELECT 
    acct_unit, 
    STATUS_DATE, 
    STATUS_FLAG, 
    SUR_KEY
FROM 
    (
        SELECT 
            acct_unit, 
            STATUS_DATE, 
            STATUS_FLAG, 
            SUR_KEY,
            ROW_NUMBER() OVER(PARTITION BY acct_unit ORDER BY STATUS_DATE DESC) AS rn 
        FROM 
            TSPI
        WHERE
            STATUS_DATE >= DATEADD(DAY, -7, CAST(SYSDATETIME() AS DATE))
    ) AS temp 
WHERE 
    rn <=2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources