Link to home
Create AccountLog in
Avatar of doehlemis
doehlemis

asked on

Select Until feature required

I have an SQL query that I return with some data, for example :
       ID  Days Dis    Datefrom     Dateto          Abs  Check
98765 163    NULL 2010-05-11 2010-10-21 78      0
87654 157    NULL 2009-09-18 2010-02-22 158    1
76543 132    NULL 2008-12-02 2009-04-13 93      0
65432 116    NULL 2008-05-07 2008-08-31 112    1
54321 248    NULL 2007-05-13 2008-01-16 NULL 0

The query returns a list dependant on an employee.
The Dis column is either 1 or NULL, the Days column is the difference between Dateto-DateFrom, the Abs column is the difference of DateFrom of ID x  - Dateto of ID x+1 (basically the difference of days between the two results). The check column is a marker that is set to 1 if either the Dis is set to 1 or the Abs > 100.
Now heres my problem.
I want to return only the row up until the first check=1 and nothing after it ans sum the results of Abs.
This query is a sub query of a greater query.
If i was to query this sub as Select * from (subquery) where check<>1, I would get lines  1,3 and 5 - but I only want line 1.
Is there a select until check=1 equivalent to stop the select once this condition is met.
My second thought was to make the check increment everytime a result marker was hit, so the table would then be something like:
      ID  Days Dis     Datefrom     Dateto          Abs  Check
98765 163    NULL 2010-05-11 2010-10-21 78      0
87654 157    NULL 2009-09-18 2010-02-22 158    1
76543 132    NULL 2008-12-02 2009-04-13 93      1
65432 116    NULL 2008-05-07 2008-08-31 112    2
54321 248    NULL 2007-05-13 2008-01-16 NULL 2
Then I could query on if Check<=0 which would then work as I require, however everytime I try to do anything with a counter inside the sub query I get an error stating not allowed to use a variable when combined with a query result set.  
Anyone any ideas how I can do this (without procedures/functions or temp tables - want it as a single SQL qub query I can run))

Look forward to your ideas.
Regards
Jason
ASKER CERTIFIED SOLUTION
Avatar of Christopher Gordon
Christopher Gordon
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
select top (1) and then your condition
Avatar of doehlemis
doehlemis

ASKER

Thanks guys - neither will work however - let me emphasize on a point.
The results returned are already against one employee record - the ID is a contract ID for that employee and is retrieved in order by an ORDER BY ID DESC clause.
I need to recover from the returned list the lines up UNTIL the first change in the Check column., IE if the dataset is as below:
98765 163    NULL 2010-05-11 2010-10-21 78      0
87654 157    NULL 2009-09-18 2010-02-22 158    1
76543 132    NULL 2008-12-02 2009-04-13 93      0
65432 116    NULL 2008-05-07 2008-08-31 112    1
54321 248    NULL 2007-05-13 2008-01-16 NULL 0
Then return the SUM of Abs up until a change in the Check Column.  In this instance, just the first contract 98765 for example so the sum would be 78 only.
If the dataset was :
98765 163    NULL 2010-05-11 2010-10-21 78      0
76543 132    NULL 2008-12-02 2009-04-13 93      0
65432 116    NULL 2008-05-07 2008-08-31 112    1
87654 157    NULL 2009-09-18 2010-02-22 158    1
54321 248    NULL 2007-05-13 2008-01-16 NULL 0
Then I would SUM the first two rows, IDs 98765 and 76543 so (78+93), and stop the sum when I get to 65432 as this is the first one that changes the Check column.  I would not want to count ID 54321 in either case as this follows on from when the check column has already been altered.

Hope that clarifies the need.
@gohord:
My Check column is a condition column in a query that is like:
select ID,Date1,Date2,Check=case when (date2previous-date1)>100 OR (dis='y') then 1 else 0 end.
(heavily abbreviated just to give the idea!)
I only want the first lines up until the check column changes in the already returned dataset, not all in the dataset that have a 1 against the check column.
@mattibut:
I need as many lines as are returned in order from first to n in the dataset that do not have check>=1 up UNTIL the check changes, and cease any others after it also - not just the first line.

Regards
Jason

The query example I provided should be returning all rows up to and including the first time CHECK field has a "1".  

If you remove the "=" sign after the the ">" then it will not include that first "1" row.  

You can change the ABS value in the sample table I provided to illustrate the point.

So change to...

select      *
from      @myTable
where      id > ( select MAX(Id) from @myTable where [check] = 1 group by employeeId)

(This should return all rows in example up to the first instance of "1").

You can lose the Employee ID in the sub query if it's already filtered appropriately.

select      *
from      @myTable
where      id > ( select MAX(Id) from @myTable where [check] = 1)

If you need a summary then...

select      SUM([ABS])
from      @myTable
where            id > ( select MAX(Id) from @myTable where [check] = 1)

Again this assumes ID is sorted DESC order.  Also, I'm only looking at my example table and not the big picture so I easily could be missing something.

You said:
..My Check column is a condition column in a query that is like:
select ID,Date1,Date2,Check=case when (date2previous-date1)>100 OR (dis='y') then 1 else 0 end.

Question on that: Where is date2previous coming from?  We can probably "incapsulate" this logic in a CTE before we do the query.

Thanks gohord,
I think I understand where you are coming from now and yes, you solution looks like it should work, however my next problem is implementing your solution.  Maybe it would be easier to see the full code to understand where I am coming from - this will help explain date2previous.
Bear in mind I am developing this in SQL Studio with the final view point of this being implemented into a Reporting Services Report - its not pretty code and the majority of things like Dismiss,LeaveHome and ArriveHome can be removed from final view when I am happy with the returnset:
 
DECLARE @employeeid INT
SET @employeeid=116715

SELECT
 ConID=Contract.ID,
 DaysSS=CAST(Crw.ArriveHome AS INT)-CAST(Crw.LeaveHome AS INT),
 Dismiss=(SELECT TOP 1 'Y'  
   FROM Contract AS C
   JOIN Crew AS Cr ON Cr.IDContract=C.ID
   JOIN Document ON Document.IDOwner=Cr.ID
   WHERE C.IDEmployee=@employeeid
   AND Document.IDCategory=6
   AND document.IDCateList=190
   AND Document.Deleted=0
   AND Cr.arrivehome IS NOT NULL
 ),
  LeaveHome=Crw.LeaveHome,
  ArriveHome=Crw.ArriveHome,

  AbsenceDD=CAST(Crw.LeaveHome AS INT)-CAST(
   (SELECT TOP 1 PreviousCrew.ArriveHome
    FROM Contract AS PreviousContract
    JOIN Crew AS PreviousCrew ON PreviousCrew.IDContract=PreviousContract.ID
    WHERE PreviousContract.ID<Contract.ID
    AND PreviousContract.IDEmployee=@employeeid
    ORDER BY PreviousContract.ID DESC
   ) AS INT),

  test=CASE
   WHEN (CAST(Crw.LeaveHome AS INT)-CAST(
    (SELECT TOP 1 PreviousCrew.ArriveHome
     FROM Contract AS PreviousContract
     JOIN Crew AS PreviousCrew ON PreviousCrew.IDContract=PreviousContract.ID
     WHERE PreviousContract.ID<Contract.ID
     AND PreviousContract.IDEmployee=@employeeid
     ORDER BY PreviousContract.ID DESC
     ) AS INT)>100)
     OR
     ((SELECT TOP 1 'Y'  
       FROM Contract AS C
       JOIN Crew AS Cr ON Cr.IDContract=C.ID
       JOIN Document ON Document.IDOwner=Cr.ID
       WHERE C.IDEmployee=@employeeid
       AND Document.IDCategory=6
       AND document.IDCateList=190
       AND Document.Deleted=0
       AND Cr.arrivehome IS NOT NULL
     )='Y')
   THEN 1
   ELSE 0
  END

FROM Contract
 JOIN Crew AS Crw ON Crw.IDContract=Contract.ID

WHERE Crw.LeaveHome IS NOT NULL
  AND Crw.ArriveHome IS NOT NULL
  AND Contract.IDEmployee=@employeeid and Crw.IDEmployee=@employeeid

ORDER BY Contract.ID DESC

In this example this will actually return the following:
ConID DaysSS Dismiss LeaveHome ArriveHome AbsenceDD test
72856 163       NULL     2010-05-11 2010-10-21  78                0  
67225 157       NULL     2009-09-18 2010-02-22  158              1
60601 132       NULL     2008-12-02 2009-04-13  93                0
55089 116       NULL     2008-05-07 2008-08-31  112              1
46345 248       NULL     2007-05-13 2008-01-06  NULL           0


From what I understand of what you say, I simply encapsulate my query inside another select query,
ie,

select * from (queryabove) as mytable where conid>(select MAX(conid) from (queryabove) where test=1)
I tried this but failed.
Got this far which worked
Select * from (queryabove (minus the last order statement)) as mytable order by ConID DESC
Placing the same querycode within the where clause seems to fail it.
ie select * from (queryabove(minus the last order statement)) as mytable where conid>(select max(conid) from (queryabove(minus last order statement)) where test=1)

Seems harsh to have to place the same query in two different parts considering the length and complexity of the query.
I do not understand what you mean by CTE - can you ellaborate - it is a term I an not familiar with (yet!), but sounds like it may be to fix the thing I mentioned just above about having the same query in two places??

Regards
Jason
@gohord,
Just google whacked CTE and found out what it is - nice idea and would probably have worked great ... if..... I wasnt on SQL 2000!

SQL Server 2000 SP4 2000.8.00.2039 - Doh!

Seems WITH was introduced with SQL 2005.

Regards
Jason
@doehlemis,

Back in the office.  I'm taking a look at your query now.  Yeh, CTE's are not available in SQL 2000.  And yep, you can't do the order by clause in the subquery.  Before I get too far, does it make sense from a complete system design perspective to put a Clustered Index (in DESC order) on CONID?  This would eliminate the need to place the order by in the subquery.

If you think that will help then yes, but as long as this doesnt involve altering the original table in any way.  I have only read only access to the DB for creating view reports only and no ability to modify the tables.
Regards
Jason
Yeah, the Clustered Index would definitely require altering the DB.  Will have to scratch that idea.  I'll take another look at the SQL you posted in a bit.
With your help I managed to get my query completed.  Thanks for all the suggestions - it would appear a whole lot easier if I was on 2005 though and not 2000!