Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [DocumentApproval]
- GO
- SELECT
- DU.EntryId AS ParentID
- ,PIV.[MEDI_BENEFITLEVEL] as userdata_medi_benefitlevel
- ,ISNULL(BD.EntryID, 181) AS match_bundleid
- ,ISNULL(BD.BundleName,'Limited Medical - Choice Premier') AS match_coverage
- ,tcd.entryid as actual_coverageid
- ,tcd.userid
- ,tcd.bundleid as actual_bundleid
- ,tcd.coverage as actual_coverage
- ,case
- when tcd.bundleid = bd.entryid
- then 1
- else case
- when tcd.bundleid is null then 1
- else 0
- end
- end as correct_values
- FROM DocumentApproval..DA_UserData AS UD
- PIVOT (
- max(Value)
- for fieldname in (
- [MEDI_COVERAGELEVEL]
- ,[MEDI_BENEFITLEVEL]
- ,[MEDI_PRODUCT]
- )
- ) AS PIV
- INNER JOIN DocumentApproval..DA_User AS DU
- ON DU.ID = PIV.UserID
- INNER JOIN Ships..TN_PolicyBundle_Data AS BD
- ON BD.Company = 'TrueChoices'
- AND BD.BundleName LIKE 'Limited Medical - %'
- LEFT JOIN (
- SELECT
- T.EntryID
- ,T.ParentID
- ,T.Coverage
- ,T.UserID
- ,T.BundleID
- ,CASE B.TCProductName
- WHEN '' THEN T.Coverage
- ELSE B.TCProductName
- END AS TCProductName
- FROM Ships..TN_TCCoverageDriver_Data AS T
- INNER JOIN Ships..TN_PolicyBundle_Data AS B
- ON B.EntryID = T.BundleID
- WHERE REPLACE(ISNULL(T.CancelDate,''),' / /','') = ''
- ) AS TCD
- ON TCD.ParentID = DU.EntryID
- AND TCD.TCProductName = (
- CASE BD.TCProductName
- WHEN '' THEN BD.BundleName
- ELSE BD.TCProductName
- END
- )
- WHERE (CASE
- WHEN BD.BundleName LIKE ('%- ' + (
- CASE ISNULL(PIV.[MEDI_BENEFITLEVEL],'')
- WHEN '' THEN 'zzz'
- ELSE PIV.[MEDI_BENEFITLEVEL]
- END
- )) THEN 1
- ELSE 0
- END) = 1
- --and (case
- -- when tcd.bundleid = bd.entryid
- -- then 1
- -- else case
- -- when tcd.bundleid is null then 1
- -- else 0
- -- end
- --end) = 0
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement