July 16, 2014 at 1:48 pm
Luis, your solution is also working fine. Just had few typos in which I've fixed and its giving the right results.
Thanks
July 16, 2014 at 2:18 pm
T-SQL beginnger (7/16/2014)
Luis, your solution is also working fine. Just had few typos in which I've fixed and its giving the right results.Thanks
It's good to know that my blind-coding abilities still work. 🙂
July 16, 2014 at 2:23 pm
haha .. yeah it sure does work. They have asked me if I can get the second part of the code done without correlated subqueries as well. This following part I mean:
pol.data_set_nbr = (
SELECT MAX(i.data_set_nbr)
FROM dbo.pdr_pol AS i
WHERE i.acct_no = pol.acct_no
AND i.policy_id = pol.policy_id
AND i.stat_tran_c NOT LIKE '%CNCLPA'
)
AND (
(
pol.stat_tran_c IN( 'CANCEL', 'CANREWRITE')
AND sts.cur_status_c = 'CANCEL'
)
OR (
pol.stat_tran_c IN ('REINSTATE','REINWGAP')
AND sts.cur_status_c <> 'CANCEL'
AND pol.vers_cntl_maj_min = (
SELECT MAX(i.vers_cntl_maj_min)
FROM dbo.pdr_pol AS i
WHERE i.acct_no = pol.acct_no
AND i.policy_id = pol.policy_id
AND i.data_set_nbr = pol.data_set_nbr
)
AND pol.vers_cntl_maj_min <> pol.vers_data_maj_min
)
OR (
pol.stat_tran_c NOT IN ('REINSTATE','REINWGAP')
AND sts.cur_status_c <> 'CANCEL'
AND pol.vers_cntl_maj_min = (
SELECT MAX(i.vers_cntl_maj_min)
FROM dbo.pdr_pol AS i
WHERE i.acct_no = pol.acct_no
AND i.policy_id = pol.policy_id
AND i.data_set_nbr = pol.data_set_nbr
)
AND pol.vers_cntl_maj_min = pol.vers_data_maj_min
)
);
You can just suggest me a way and I will try to do it myself.
Thanks !
July 17, 2014 at 1:25 am
I actually had to support a policy management system at an insurance company and had to deal with latest versions similar to this. Largely you've already got good solutions from the guys here (hardly shocking, they know what they're doing). I wrote up an article [/url]comparing the performance of these approaches here. You might want to look at it. Depending on your data distribution, different solutions will work better for you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2014 at 1:30 am
Double post. Not sure why. Apologies.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2014 at 6:42 am
Grant, can you please give me the link of that article that you've written ?
July 18, 2014 at 7:57 am
Any help for the 2nd part guys ?
July 18, 2014 at 9:47 am
T-SQL beginnger (7/18/2014)
Any help for the 2nd part guys ?
Sorry, not from me. I'm not sure I can work something out without some sample data to test.
July 18, 2014 at 12:16 pm
Well I have attached the requirements document for the view which I'm trying to understand as well. This is the first time I'm working in insurance domain so it's taking me more time to ramp up to things.
Maybe I'll end up coding it from scratch now since it's to understand coder's logic used unless you have insurance background which you guys seem to have.
If you can suggest any approach then that would be great as well
July 20, 2014 at 6:18 am
T-SQL beginnger (7/17/2014)
Grant, can you please give me the link of that article that you've written ?
It's right there in the post.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 21, 2014 at 5:29 am
T-SQL beginnger (7/18/2014)
Any help for the 2nd part guys ?
I agree with Luis, it's very difficult to work with filters as complex as this without sample data. However, here's a stab at it. It's unlikely it will work without errors so you'll have to figure 'em out!
WITH
Stage2 AS (
SELECT pol.Source_System_Identifier
,pol.BATCH_NUM
,pol.ACCT_NO
,pol.stat_header_id
,pol.POLICY_NO
,pol.POLICY_NBR_SEQ
,pol.POLICY_ID
,pol.VERS_CNTL_MAJ_MIN
,pol.vers_data_maj_min
,pol.DATA_SET_NBR
,sts.cur_status_c AS attrb_data
,pol.tran_eff_d AS policy_tran
,sts.effective_d AS status_tran
,[MAX_vers_cntl_maj_min] = MAX(pol.vers_cntl_maj_min) OVER(PARTITION BY pol.acct_no, pol.policy_id, pol.data_set_nbr)
,[MAX_data_set_nbr] = MAX(CASE WHEN pol.stat_tran_c NOT LIKE '%CNCLPA' THEN pol.data_set_nbr ELSE NULL END) OVER(PARTITION BY pol.acct_no, pol.policy_id)
,[Grp] = CASE
WHEN pol.stat_tran_c IN ('CANCEL', 'CANREWRITE') AND sts.cur_status_c = 'CANCEL' THEN 1
WHEN pol.stat_tran_c IN ('REINSTATE','REINWGAP') AND sts.cur_status_c <> 'CANCEL' THEN 2
WHEN pol.stat_tran_c NOT IN ('REINSTATE','REINWGAP') AND sts.cur_status_c <> 'CANCEL' THEN 3
ELSE NULL END
FROM dbo.PDR_POL pol
CROSS APPLY (
SELECT TOP 1 cur_status_c, effective_d
FROM dbo.pol_policy_status_v sts
WHERE pol.acct_no = sts.acct_no
AND pol.policy_id = sts.policy_id
ORDER BY
effective_id DESC,
process_dt DESC,
policy_status_v_id DESC
) sts
)
SELECT *
FROM Stage2
WHERE data_set_nbr = MAX_data_set_nbr
AND (
[Grp] = 1
OR [Grp] = 2 AND vers_cntl_maj_min = MAX_vers_cntl_maj_min AND vers_cntl_maj_min <> vers_data_maj_min
OR [Grp] = 3 AND vers_cntl_maj_min = MAX_vers_cntl_maj_min AND vers_cntl_maj_min = vers_data_maj_min
)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 21, 2014 at 7:45 am
Thanks Chris,
Yeah there were minor alias issues but after correcting them I was able to run the code and get the same result set.
Really appreciate your help !
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply