troubleshooting Question

cusrosr vs subquery

Avatar of ol muser
ol muserFlag for United States of America asked on
Sybase Database
1 Comment1 Solution557 ViewsLast Modified:
Hi, Is there a way to use a sub query or a combination of select+delete statements to accomplish what the cursor does?

In short I need to be able to select for evey patient that is in the patientdetails table details of the labwork that satisfy certain conditions as below.

    declare cur_patients cursor
        for select patientid, notepadid, lastdocumented, currenthospdate,  MostRecentDischDate
        from patiendetails
    open cur_patients
    fetch cur_patients into @pid, @npid, @LDdate, @CHdate, @MRDdate
    while (@@fetch_status <> -1)
    begin
        select @startdate = dateadd(dd, -180, getdate())
        if (@LDdate < @startdate)
           select @startdate = @LDdate
        select @undocenddate = getdate()
        if (@CHdate != NULL)
        begin
            select @undocenddate = @CHdate  
        end
        select @undocstartdate = @LDdate
        if (@MRDdate != NULL AND @MRDdate > @LDDate)
        begin
            select @undocstartdate = dateadd(dd, 1, @MRDdate)
        end
       
       
        insert tempdb..ptlabs
        select distinct
            teo.PatientId
            ,teo.OrderDate
            ,teo.OrderResultValue
            ,teo.ChecklistTestCode
            ,teo.OrderName
            ,teo.OrderResultNonMayoLab
            ,teo.LastChangedDate
        from labresults teo
        where patientid = @pid
            and completed = 'y'
            and @startdate <= LastChangedDate
            and LastChangedDate <= getdate()    
            and @undocstartdate <= lastchangeddate
            and lastchangeddate <= @undocenddate
           
        fetch next from cur_patients into @pid, @npid, @LDdate, @CHdate, @MRDdate
    end
    close cur_patients
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros