September 6, 2016 at 11:58 am
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
September 7, 2016 at 2:28 pm
anyone?
September 7, 2016 at 3:03 pm
What are your expected results? It's certainly not clear from your description.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 7, 2016 at 3:33 pm
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.
September 8, 2016 at 7:11 am
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/
September 8, 2016 at 10:49 am
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