looping help

  • I have 3 tables. The script below returns 3 records. What I need to do is go through each record and join it to PolicyReviewLocks by PolicyNumber. If there isn't a match, then insert into PolicyReviewLocks values (PolicyNumber, @user-id). If there is a match (PolicyNumber = 11), then query table PolicyReviews for the next oldest date record. In my example, that record would be PolicyNumber 10 with the date of 6/1/16. Take that record and join it back to PolicyReviewLocks by PolicyNumber and loop though the same matching logic again.

    create table PolicyReviewStatus

    (ReviewStatusID int,

    ReviewID int,

    Status char(1),

    ProcessedBy int)

    create table PolicyReviews

    (ReviewID int,

    PolicyNumber int,

    DateCreated datetime)

    create table PolicyReviewLocks

    (PolicyNumber int,

    UserID int)

    insert into policyreviewstatus values (1,1,'P',123);

    insert into policyreviewstatus values (2,2,'P',123);

    insert into policyreviewstatus values (3,3,'N',123);

    insert into policyreviewstatus values (4,4,'P',123);

    insert into policyreviewstatus values (5,5,'P',12);

    insert into policyreviewstatus values (6,6,'P',10);

    insert into policyreviews values (1,14,'2016-08-01 00:00:00.00')

    insert into policyreviews values (2,14,'2016-08-01 10:00:00.00')

    insert into policyreviews values (3,14,'2016-08-01 11:00:00.00')

    insert into policyreviews values (4,14,'2016-09-02 00:00:00.00')

    insert into policyreviews values (5,10,'2016-06-01 10:00:00.00')

    insert into policyreviews values (6,11,'2016-05-02 00:00:00.00')

    insert into policyreviewlocks values (11,10)

    ---------------------------------------------------------------------------------------

    declare @status char(1), @userid int

    set @status = 'P'

    set @userid = 9999

    select *

    from policyreviewstatus prs

    inner join policyreviews pr on pr.reviewid = prs.reviewid

    where prs.status = @status

    and pr.datecreated = (select min(datecreated) from policyreviews

    where policynumber = pr.policynumber)

    order by pr.policynumber

  • anyone?

  • What are your expected results? It's certainly not clear from your description.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • The final results will be below for table PolicyReviewLocks. PolicyNumber 10 and 14 doesn't exist so they are inserted. PolicyNumber 11 exists, so I need to be able to re-query table PolicyReviews for the next oldest date which is the record with PolicyNumber 10 with date of 6/1/16. Using this record, I want to join it back again to table PolicyReviewLocks to see if the PolicyNumber exist. Keep looping until there is no match in which the record is written, or if there is a match to continue until no more PolicyNumber to compare.

    PolicyNumberUserID

    1110

    109999

    149999

    I was thinking of breaking it up into 2 parts, one that does the insert for no matches which is easy. The other part that I need help is looping through the matches.

  • This sounds like a typical recursive cte to me. I didn't dig into this much deeper than a skim reading though.

    Here is the BOL entry. https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

    And a decent example of a very common usage. http://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I think that you're overcomplicating it. This gives the results that you're looking for without any loops.

    INSERT PolicyReviewLocks(PolicyNumber, UserID)

    SELECT DISTINCT pr.PolicyNumber, @User

    FROM PolicyReviews pr

    INNER JOIN PolicyReviewStatus prs

    ON prs.ReviewID = pr.ReviewID

    WHERE prs.Status = 'P'

    AND NOT EXISTS (

    SELECT 1

    FROM PolicyReviewLocks prl

    WHERE prl.PolicyNumber = pr.PolicyNumber

    )

    SELECT *

    FROM PolicyReviewLocks prl

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply