Alexbr_95

Untitled

Jan 18th, 2022 (edited)
2,024
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT TOP 100
  2. MAX (c.ClientName) AS Name, MAX (p.IdClientPolicy) AS [Policy_ID],
  3.  
  4. MAX(c.MailState) AS State, MAX(c.MailZipCode) AS Zip, MAX(c.ContactPhone) AS 'phone', MAX(c.Email) AS Email,
  5. MAX (p.PolicyNumber) AS [Policy_num], MAX(p.EffectiveDate) AS EffDate, MAX(p.ExpirationDate) AS ExpDate,
  6.  --PL
  7. MAX(varOccupation.[VALUE]) AS Occupation,
  8.  
  9.  
  10. (SELECT SUM(Total) FROM ClientInvoice WHERE ClientInvoice.idclientpolicy = MAX(p.IdClientPolicy)) TotalBilled,  
  11.  
  12.   --Referal Analysis
  13. MAX (p.IdMode) AS [Policy_mode], MAX(varAssociation.[VALUE]) AS Association,
  14.  
  15. IIF((SELECT COUNT(p2.IdClientPolicy) FROM ClientPolicy p2
  16.     INNER JOIN Client c2 ON c2.IdClient = p2.IdClient AND (c2.IdClient = MAX(c.IdClient) OR c2.Email = MAX(c.Email) OR (c2.ContactPhone = MAX(c.ContactPhone) AND c2.ContactPhone IS NOT NULL AND c2.ContactPhone != ''))
  17.     WHERE
  18.     p2.IdStatus = 12) > 0, 'Yes', 'No'
  19. ) AS [HasActivePolicy],
  20.  
  21. IIF((SELECT COUNT(p2.IdClientPolicy) FROM ClientPolicy p2
  22.     INNER JOIN Client c2 ON c2.IdClient = p2.IdClient AND (c2.IdClient = MAX(c.IdClient) OR c2.Email = MAX(c.Email) OR (c2.ContactPhone = MAX(c.ContactPhone) AND c2.ContactPhone IS NOT NULL AND c2.ContactPhone != ''))
  23.     WHERE
  24.     p2. Occupation != '' AND p2.Occupation IS NOT NULL AND p2.Occupation NOT IN ('AMHCA Student', 'CAMFT Student', 'AAMFT Student', 'OCA Student') AND p2.Occupation NOT LIKE '%student%') > 0, 'No', 'Yes'
  25. ) AS [Is_Still_Student]
  26.  
  27. FROM clientpolicy p
  28. LEFT JOIN CLIENT c ON c.IdClient = p.IdClient
  29. LEFT JOIN ClientType t ON c.IdType = t.IdClientType
  30. LEFT JOIN PolicyModes M ON M.IdPolicyMode = p.IdMode
  31. LEFT JOIN policydataform q ON p.IdClientPolicy = q.IdPolicy
  32. LEFT JOIN POLICYDATA_old varOccupation ON varOccupation.idpolicyform = q.Id AND varOccupation.IdVariable = 20040
  33.  
  34. --referals
  35. LEFT JOIN POLICYDATA_old varAssociation ON varAssociation.idpolicyform = q.Id AND varAssociation.IdVariable = 20041
  36.  
  37. WHERE p.idinsurance = 1032 AND p.EffectiveDate >= '2020-01-01' AND p.EffectiveDate <= '2020-12-31'
  38.  
  39. AND varOccupation.VALUE IN ('AMHCA Student', 'CAMFT Student', 'AAMFT Student', 'OCA Student')
  40.  
  41. GROUP BY p.PolicyNumber
  42.  
  43.  
  44. ---------------------------- 2022 Query ------------------------------------------
  45. --select top 1000
  46. --max (c.ClientName) as Name, max (p.IdClientPolicy) as [Policy_ID],
  47.  
  48. --max(c.MailState) as State, max(c.MailZipCode) as Zip, max(c.ContactPhone) as 'phone', max(c.Email) as Email,
  49. --max (p.PolicyNumber) as [Policy_num], max(p.EffectiveDate) as EffDate, max(p.ExpirationDate) as ExpDate,
  50. -- --PL
  51. --max(varOccupation.[Value]) as Occupation,
  52.  
  53.  
  54. --(SELECT sum(Total) FROM ClientInvoice where ClientInvoice.idclientpolicy = max(p.IdClientPolicy)) TotalBilled,  
  55.  
  56. --  --Referal Analysis
  57. --max (p.IdMode) as [Policy_mode], max(varAssociation.[Value]) as Association,
  58.  
  59. --IIF((SELECT COUNT(p2.IdClientPolicy) FROM ClientPolicy p2
  60. --  INNER JOIN Client c2 ON c2.IdClient = p2.IdClient AND (c2.IdClient = max(c.IdClient) OR c2.Email = max(c.Email) OR (c2.ContactPhone = max(c.ContactPhone) AND c2.ContactPhone IS NOT NULL AND c2.ContactPhone != ''))
  61. --  WHERE
  62. --  p2.IdStatus = 12) > 0, 'Yes', 'No'
  63. --) as [HasActivePolicy],
  64.  
  65. --IIF((SELECT COUNT(p2.IdClientPolicy) FROM ClientPolicy p2
  66. --  INNER JOIN Client c2 ON c2.IdClient = p2.IdClient AND (c2.IdClient = max(c.IdClient) OR c2.Email = max(c.Email) OR (c2.ContactPhone = max(c.ContactPhone) AND c2.ContactPhone IS NOT NULL AND c2.ContactPhone != ''))
  67. --  WHERE
  68. --  p2. Occupation != '' AND p2.Occupation IS NOT NULL AND p2.Occupation NOT IN ('AMHCA Student', 'CAMFT Student', 'AAMFT Student', 'OCA Student') AND p2.Occupation NOT LIKE '%student%') > 0, 'No', 'Yes'
  69. --) as [Is_Still_Student]
  70.  
  71. --from clientpolicy p
  72. --LEFT JOIN CLIENT c ON c.IdClient = p.IdClient
  73. --LEFT JOIN ClientType t ON c.IdType = t.IdClientType
  74. --LEFT JOIN PolicyModes M ON M.IdPolicyMode = p.IdMode
  75. --LEFT JOIN policydataform q on p.IdClientPolicy = q.IdPolicy
  76. --JOIN POLICYDATA varOccupation ON varOccupation.idpolicyform = q.Id and varOccupation.IdVariable = 20040
  77.  
  78. ----referals
  79. --LEFT JOIN POLICYDATA varAssociation ON varAssociation.idpolicyform = q.Id and varAssociation.IdVariable = 20041
  80.  
  81. --where p.idinsurance = 1032 and p.EffectiveDate >= '2022-01-01' and p.EffectiveDate <= '2022-12-31'
  82.  
  83. --and varOccupation.Value in ('AMHCA Student', 'CAMFT Student', 'AAMFT Student', 'OCA Student')
  84.  
  85. --group by p.PolicyNumber
RAW Paste Data Copied