Re: Cursor vs Set - is it possible

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: keene (anonymous_at_discussions.microsoft.com)
Date: 04/06/04 

Date: Tue, 6 Apr 2004 13:16:06 -0700

Not sure what you want, really. but you may want to try a
subquery.

select count(*) from yourtable a
where exists(select * from yourtable b where
datediff(d,a.date,b.date)=1)

>-----Original Message-----
>I always try to use a SELECT and avoid CURSOR logic -
whenever possible - but this time I can't even think of a
way to SET-base this operation.
>
>I've got a table that stores dates of ATTENDANCE. Only
non-present dates have rows for a person.
>ABSENT on 04/02/2004 gets a row for a person - ABSENT
again on 04/07/2004 gets a row for a person.
>
>I want to create a UDF that returns the "consecutive
number" of ABSENT entries in this table for a person,
based on a starting date and going backwards.
>
>I could simply loop a working variable with a date and
do a SELECT to see if a record exists - then decrement
the date by one day and check again - that was the way we
did it on the mainframe VAX we are converting from...
>
>BTW - weekends and holidays are ignored - they don't
break the CONSECUTIVE counter...
>
>Any help would be greatly appreciated...
>.
>

Relevant Pages

  • Re: update a date field with fixed date but random hours/minutes
    ... :> evaluating an expression outside of the SQL statement that you're going ... Not sure what you mean by getting rid of the subquery. ... Here is a revision (which I use a loop) that works (but I think it's ... : update study s ...
    (comp.databases.oracle.misc)
  • Re: should i use a loop or a cursor?
    ... You don't need any kind of loop... ... You can do either a subquery: ... As for computing how much was actually sold or received, ... > I have a table that stores product quantity week by week like the ...
    (microsoft.public.sqlserver.programming)
  • How to select from a table variable?
    ... I want this syntax to use to create a subquery that I can ... loop through all items in the table, ... function and/or store the items in a temporary table. ...
    (comp.databases.oracle.server)
  • Re: update a date field with fixed date but random hours/minutes
    ... Mark C. Stock wrote: ... that's probably because you selected your random values once in the subquery ... Not sure what you mean by getting rid of the subquery. ... Here is a revision (which I use a loop) that works (but I think it's ...
    (comp.databases.oracle.misc)