Sql Help: Subquery 1

[Memento]

SQL Server 2000

Here's my SQL:

Set Nocount On
DECLARE @STAFF INT
SET @STAFF = '100'

SELECT planev.pl_staff,
(CONVERT(VARCHAR(10), planev.pl_date, 101)) + ' ' +
(CONVERT(VARCHAR(5), planev.pl_time, 8)) as FirstTime,
(SELECT Min((CONVERT(VARCHAR(10), planev_1.pl_date, 101)) + ' ' + (CONVERT(VARCHAR(5), planev_1.pl_time, 8)))
FROM planev INNER JOIN planev planev_1 ON planev.pl_staff = planev_1.pl_staff
WHERE
(CONVERT(VARCHAR(10), planev_1.pl_date, 101)) + ' ' + (CONVERT(VARCHAR(5), planev_1.pl_time, 8))>
(CONVERT(VARCHAR(10), planev.pl_date, 101)) + ' ' + (CONVERT(VARCHAR(5), planev.pl_time, 8))
and (planev.pl_staff = @STAFF) ) as EndTime
FROM planev
WHERE (planev.pl_staff = @STAFF)
Set NoCount Off

Here's the Results:
pl_staff FirstTime EndTime
100 03/26/2007 08:00 03/26/2007 10:00
100 03/26/2007 10:00 03/26/2007 10:00
100 04/02/2007 10:00 03/26/2007 10:00
100 04/03/2007 11:00 03/26/2007 10:00
100 04/04/2007 09:00 03/26/2007 10:00
100 04/05/2007 13:00 03/26/2007 10:00
100 04/05/2007 15:00 03/26/2007 10:00
100 04/05/2007 16:00 03/26/2007 10:00
100 04/09/2007 09:00 03/26/2007 10:00
100 04/09/2007 10:00 03/26/2007 10:00

The desired end result:
pl_staff FirstTime EndTime
100 03/26/2007 08:00 03/26/2007 10:00
100 03/26/2007 10:00 04/02/2007 10:00
100 04/02/2007 10:00 04/03/2007 11:00
100 04/03/2007 11:00 04/04/2007 09:00
100 04/04/2007 09:00 04/05/2007 13:00
100 04/05/2007 13:00 04/05/2007 15:00
100 04/05/2007 15:00 04/05/2007 16:00
100 04/05/2007 16:00 04/09/2007 09:00
100 04/09/2007 09:00 04/09/2007 10:00
100 04/09/2007 10:00 04/09/2007 13:00

I'm close. I know it's getting the Min corectly for the staff, but I need it to choose the next date.

 

[bborissov]

Can you post some data and desired result from it?

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

 

[rac2]

If you have these four rows for pl_staff = 100

pl_staff FirstTime
100 03/26/2007 08:00
100 03/26/2007 10:00
100 04/02/2007 10:00
100 04/03/2007 11:00

and you join that table to itself based on pl_staff where pl_staff = 100 the result would be sixteen rows

pl_staff FirstTime_A FirstTime_B
100 03/26/2007 08:00 03/26/2007 08:00
100 03/26/2007 08:00 03/26/2007 10:00
100 03/26/2007 08:00 04/02/2007 10:00
100 03/26/2007 08:00 04/03/2007 11:00
100 03/26/2007 10:00 03/26/2007 08:00
100 03/26/2007 10:00 03/26/2007 10:00
100 03/26/2007 10:00 04/02/2007 10:00
100 03/26/2007 10:00 04/03/2007 11:00
100 04/02/2007 10:00 03/26/2007 08:00
100 04/02/2007 10:00 03/26/2007 10:00
100 04/02/2007 10:00 04/02/2007 10:00
100 04/02/2007 10:00 04/03/2007 11:00
100 04/03/2007 11:00 03/26/2007 08:00
100 04/03/2007 11:00 03/26/2007 10:00
100 04/03/2007 11:00 04/02/2007 10:00
100 04/03/2007 11:00 04/03/2007 11:00

If you add the further condition that FirstTime_B is after FirstTime_A you eliminate 10 rows leaving

pl_staff FirstTime_A FirstTime_B

100 03/26/2007 08:00 03/26/2007 10:00
100 03/26/2007 08:00 04/02/2007 10:00
100 03/26/2007 08:00 04/03/2007 11:00


100 03/26/2007 10:00 04/02/2007 10:00
100 03/26/2007 10:00 04/03/2007 11:00



100 04/02/2007 10:00 04/03/2007 11:00




The minimum value of FirstTime_B in the remaing 6 rows is
03/26/2007 10:00.

This will be the same for every row in the outer query.


One solution is to get a different result for every row in the outer query by using a value from the outer query as a condition for the subquery.

SELECT     planev.pl_staff,
    (CONVERT(VARCHAR(10), planev.pl_date, 101)) + ' ' +
    (CONVERT(VARCHAR(5), planev.pl_time, 8)) as FirstTime,

        (SELECT Min((CONVERT(VARCHAR(10), planev_1.pl_date, 101)) + ' ' + (CONVERT(VARCHAR(5), planev_1.pl_time, 8)))

      [COLOR=orange]FROM planev AS planev_1[/color]

            WHERE
                (CONVERT(VARCHAR(10), planev_1.pl_date, 101)) + ' ' + (CONVERT(VARCHAR(5), planev_1.pl_time, 8))>
                (CONVERT(VARCHAR(10), planev.pl_date, 101)) + ' ' + (CONVERT(VARCHAR(5), planev.pl_time, 8))

                and ([COLOR=orange]planev_1[/color].pl_staff = @STAFF) ) as EndTime

FROM planev 
WHERE     (planev.pl_staff = @STAFF)

So each row in planev has a different value for the date and time and the smallest value larger than the given value will also be different.

pl_staff FirstTime_A FirstTime_B
100 03/26/2007 08:00 03/26/2007 10:00
100 03/26/2007 10:00 04/02/2007 10:00
100 04/02/2007 10:00 04/03/2007 11:00
100 04/03/2007 11:00 NULL

There are no values greater than 04/03/2007 11:00 so that yields a NULL.

This scheme is sometimes referred to as a correlated subquery, meaning that a piece of it correlates or corresponds to a value in the outer query.

 

[Memento]

Thanks!

Excellent explanation. I found it very helpful. I've been racking my brain on this since last Friday.