Advertisement
Guest User

Untitled

a guest
Jul 2nd, 2015
225
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.80 KB | None | 0 0
  1. USE [DocumentApproval]
  2. GO
  3.  
  4. SELECT
  5. DU.EntryId AS ParentID
  6. ,PIV.[MEDI_BENEFITLEVEL] as userdata_medi_benefitlevel
  7. ,ISNULL(BD.EntryID, 181) AS match_bundleid
  8. ,ISNULL(BD.BundleName,'Limited Medical - Choice Premier') AS match_coverage
  9. ,tcd.entryid as actual_coverageid
  10. ,tcd.userid
  11. ,tcd.bundleid as actual_bundleid
  12. ,tcd.coverage as actual_coverage
  13. ,case
  14. when tcd.bundleid = bd.entryid
  15. then 1
  16. else case
  17. when tcd.bundleid is null then 1
  18. else 0
  19. end
  20. end as correct_values
  21. FROM DocumentApproval..DA_UserData AS UD
  22. PIVOT (
  23. max(Value)
  24. for fieldname in (
  25. [MEDI_COVERAGELEVEL]
  26. ,[MEDI_BENEFITLEVEL]
  27. ,[MEDI_PRODUCT]
  28. )
  29. ) AS PIV
  30. INNER JOIN DocumentApproval..DA_User AS DU
  31. ON DU.ID = PIV.UserID
  32. INNER JOIN Ships..TN_PolicyBundle_Data AS BD
  33. ON BD.Company = 'TrueChoices'
  34. AND BD.BundleName LIKE 'Limited Medical - %'
  35. LEFT JOIN (
  36. SELECT
  37. T.EntryID
  38. ,T.ParentID
  39. ,T.Coverage
  40. ,T.UserID
  41. ,T.BundleID
  42. ,CASE B.TCProductName
  43. WHEN '' THEN T.Coverage
  44. ELSE B.TCProductName
  45. END AS TCProductName
  46. FROM Ships..TN_TCCoverageDriver_Data AS T
  47. INNER JOIN Ships..TN_PolicyBundle_Data AS B
  48. ON B.EntryID = T.BundleID
  49. WHERE REPLACE(ISNULL(T.CancelDate,''),' / /','') = ''
  50. ) AS TCD
  51. ON TCD.ParentID = DU.EntryID
  52. AND TCD.TCProductName = (
  53. CASE BD.TCProductName
  54. WHEN '' THEN BD.BundleName
  55. ELSE BD.TCProductName
  56. END
  57. )
  58. WHERE (CASE
  59. WHEN BD.BundleName LIKE ('%- ' + (
  60. CASE ISNULL(PIV.[MEDI_BENEFITLEVEL],'')
  61. WHEN '' THEN 'zzz'
  62. ELSE PIV.[MEDI_BENEFITLEVEL]
  63. END
  64. )) THEN 1
  65. ELSE 0
  66. END) = 1
  67.  
  68. --and (case
  69. -- when tcd.bundleid = bd.entryid
  70. -- then 1
  71. -- else case
  72. -- when tcd.bundleid is null then 1
  73. -- else 0
  74. -- end
  75. --end) = 0
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement