complex 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 2000 Forums

SQL Server Development (2000)

complex query

Author

Topic

girisignin
Starting Member

11 Posts

Posted - 2010-01-05 : 05:15:29

Hi i have some problem, here i posted the data

11101 BLLI 0 12/10/09 5:30
11101 25 0 12/10/09 6:59
11101 PERN 1 12/11/09 23:34
11101 32 1 12/12/09 1:10
11101 BLLI 0 12/18/09 20:12
11101 25 0 12/18/09 22:07
11101 PERN 1 12/19/09 21:40
11101 32 1 12/19/09 23:16
11101 BLLI 0 12/26/09 5:40
11101 25 0 12/26/09 7:31
11101 PERN 1 12/27/09 14:03
11101 25 0 12/27/09 16:22
11101 PERN 1 12/28/09 13:42
11101 25 0 12/28/09 16:04

from the above data, i want to write a query to display data as below

11101 BLLI 0 12/10/09 5:30 25 0 12/10/09 6:59
11101 PERN 1 12/11/09 23:34 32 1 12/12/09 1:10
etc..
indly help me
thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-05 : 05:45:25

[code]SELECT t1.ID,t1.SecondField,t1.DateField,
t2.ID,t2.SecondField,t2.DateField,
FROM Table t1
JOIN Table t2
ON t2.ID=t1.ID
AND t1.SecondField IN ('BLLI','PERN')
AND t2.SecondField NOT IN ('BLLI','PERN')
AND t2.DateField=(SELECT TOP 1 DateField
FROM Table
WHERE ID=t1.ID
AND DateField < t1.DateField
ORDER BY DateField DESC)
[/code]
replace fields with actual field names

girisignin
Starting Member

11 Posts

Posted - 2010-01-05 : 05:56:40

thank you in advance
i will try this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-05 : 06:06:34

good luck!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources