SQL Query help - 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)

SQL Query help

Author

Topic

levicar
Starting Member

2 Posts

Posted - 2010-03-01 : 04:16:27

Hello.

Can anyone help with this problem.

I have a table, which looks like this:

ID DATE_FROM STAT NUMBER
---------------------------------------
100 1.12.2009 20 1
200 10.12.2009 20 1
100 10.12.2009 25 2
100 15.12.2009 20 3
200 15.12.2009 25 2

Table contains data for statuses of an ID. It has a valid from date and numer is a following number of a state for one id. If you want to get the latest status of one ID, you just pick the last number.
But I need to get number of IDs, who had a status 25 on a date 11.12.2009.
In this case the result must be:

ID DATE_FROM STAT NUMBER
---------------------------------------
100 10.12.2009 25 2

Thank you

Best regards,
Levi

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-01 : 05:50:04

Can you explain why the result is 10.12.2009 when you say you need result on 11.12.2009?

No, you're never too old to Yak'n'Roll if you're too young to die.

levicar
Starting Member

2 Posts

Posted - 2010-03-01 : 06:28:35

The result is 10.12.2009 because record ID=100 DATE_FROM=10.12.2009 has STAT=25 until next record was inserted ID=100 DATE_FROM=15.12.2009.

So this record had STAT=25 between 10.12.2009 and 15.12.2009.

If I had two dates DATE_FROM and DATE_TO it would be easy to resolve problem, but now I don't know how to do it.

Thank you.

BR, levi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 10:44:45

[code]
SELECT t.ID,t1.DATE_FROM,t1.STAT,t1.NUMBER
FROM YourTable t
CROSS APPLY (SELECT TOP 1 DATE_FROM,STAT,NUMBER
FROM YourTable
WHERE ID=t.ID
AND DATE_FROM< '20091211'
ORDER BY DATE_FROM DESC)t1
WHERE t1.STAT=25
[/code]

you may replace hardcoded date '20091211' with variable and make it work for any passed date value

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources