Query question 3

[Zargo]

Hi all,

Does anybody know how i must create a query to get the following result:

TableA

DOCSID DATE LINDEX
112334 14-12-2006 20:18:00 6007
112334 14-12-2006 20:23:00 1023
112334 14-12-2006 20:54:00 1005
112334 14-12-2006 20:56:00 1058

I want to know all docsid's which has 1005 AFTER 6007. How can i find those docsid's?

 

[dinzana]

select distinct docsid
from tableA
where [date] > '14-12-2006 20:18:00'
and lindex = 1005

 

[bborissov]

Can you post the records from where you want that results?
I don't understand what you meant with 1005 AFTER 6007?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

Not sure of what your sequence actually is, but maybe this?

select a.DOCSID, a.DATE, a.LINDEX
from TableA a
inner join
(
select min(Date) as DATE from TableA where LINDEX = 6007
) b
on a.DATE >= b.DATE
and a.LINDEX = 1005

Hope it helsp,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[monksnake]

select distinct DOCSID
from TableA
where [DATE] > (select max([DATE]) from TableA where LINDEX = 6008)
and LINDEX = 1005

<.

 

[Zargo]

Hi,

Thanks AlexCuse, but it doesn't give the results. What i want to know is(it is an huge table) from that table all docsid's which has first a LINDEX 6007 and after this for the same docsid a LINDEX 1005. Docsid 112334 is an example. How can i find all the docsid's with the same criteria?

 

[Zargo]

THere must something with a.docsid = b.docsid but how to add that in the queries?

 

[bborissov]

Based on Alex's response:

select a.DOCSID, a.DATE, a.LINDEX
       from TableA a
inner join
(
select DocsId, min(Date) as DATE
       from TableA
       GROUP BY DocsId       
       where LINDEX = 6007) b
on  a.DATE >= b.DATE
AND a.DocsId = b.DocsId
and a.LINDEX = 1005

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[monksnake]

OOOHHOHOHOHHHHOHOHHh, now I get it, AFTER this, means still same doctype as the one that has 6007.
(This is taking Alex's query):

select a.DOCSID, a.DATE, a.LINDEX
from TableA a
inner join
(
select min(Date) as DATE from TableA where LINDEX = 6007
[!]and DOCSID = a.DOCSID[/!]) b
on a.DATE >= b.DATE
and a.LINDEX = 1005


<.

 

[Zargo]

Thanks guys. A star for the golden solution...