SELECT TOP 100
MAX (c.ClientName) AS Name, MAX (p.IdClientPolicy) AS [Policy_ID],

MAX(c.MailState) AS State, MAX(c.MailZipCode) AS Zip, MAX(c.ContactPhone) AS 'phone', MAX(c.Email) AS Email,
MAX (p.PolicyNumber) AS [Policy_num], MAX(p.EffectiveDate) AS EffDate, MAX(p.ExpirationDate) AS ExpDate,
 --PL
MAX(varOccupation.[VALUE]) AS Occupation,


(SELECT SUM(Total) FROM ClientInvoice WHERE ClientInvoice.idclientpolicy = MAX(p.IdClientPolicy)) TotalBilled,

  --Referal Analysis
MAX (p.IdMode) AS [Policy_mode], MAX(varAssociation.[VALUE]) AS Association,

IIF((SELECT COUNT(p2.IdClientPolicy) FROM ClientPolicy p2
    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 != ''))
    WHERE
    p2.IdStatus = 12) > 0, 'Yes', 'No'
) AS [HasActivePolicy],

IIF((SELECT COUNT(p2.IdClientPolicy) FROM ClientPolicy p2
    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 != ''))
    WHERE
    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'
) AS [Is_Still_Student]

FROM clientpolicy p
LEFT JOIN CLIENT c ON c.IdClient = p.IdClient
LEFT JOIN ClientType t ON c.IdType = t.IdClientType
LEFT JOIN PolicyModes M ON M.IdPolicyMode = p.IdMode
LEFT JOIN policydataform q ON p.IdClientPolicy = q.IdPolicy
LEFT JOIN POLICYDATA_old varOccupation ON varOccupation.idpolicyform = q.Id AND varOccupation.IdVariable = 20040

--referals
LEFT JOIN POLICYDATA_old varAssociation ON varAssociation.idpolicyform = q.Id AND varAssociation.IdVariable = 20041

WHERE p.idinsurance = 1032 AND p.EffectiveDate >= '2020-01-01' AND p.EffectiveDate <= '2020-12-31'

AND varOccupation.VALUE IN ('AMHCA Student', 'CAMFT Student', 'AAMFT Student', 'OCA Student')

GROUP BY p.PolicyNumber


---------------------------- 2022 Query ------------------------------------------
--select top 1000
--max (c.ClientName) as Name, max (p.IdClientPolicy) as [Policy_ID],

--max(c.MailState) as State, max(c.MailZipCode) as Zip, max(c.ContactPhone) as 'phone', max(c.Email) as Email,
--max (p.PolicyNumber) as [Policy_num], max(p.EffectiveDate) as EffDate, max(p.ExpirationDate) as ExpDate,
-- --PL
--max(varOccupation.[Value]) as Occupation,


--(SELECT sum(Total) FROM ClientInvoice where ClientInvoice.idclientpolicy = max(p.IdClientPolicy)) TotalBilled,

--  --Referal Analysis
--max (p.IdMode) as [Policy_mode], max(varAssociation.[Value]) as Association,

--IIF((SELECT COUNT(p2.IdClientPolicy) FROM ClientPolicy p2
--  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 != ''))
--  WHERE
--  p2.IdStatus = 12) > 0, 'Yes', 'No'
--) as [HasActivePolicy],

--IIF((SELECT COUNT(p2.IdClientPolicy) FROM ClientPolicy p2
--  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 != ''))
--  WHERE
--  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'
--) as [Is_Still_Student]

--from clientpolicy p
--LEFT JOIN CLIENT c ON c.IdClient = p.IdClient
--LEFT JOIN ClientType t ON c.IdType = t.IdClientType
--LEFT JOIN PolicyModes M ON M.IdPolicyMode = p.IdMode
--LEFT JOIN policydataform q on p.IdClientPolicy = q.IdPolicy
--JOIN POLICYDATA varOccupation ON varOccupation.idpolicyform = q.Id and varOccupation.IdVariable = 20040

----referals
--LEFT JOIN POLICYDATA varAssociation ON varAssociation.idpolicyform = q.Id and varAssociation.IdVariable = 20041

--where p.idinsurance = 1032 and p.EffectiveDate >= '2022-01-01' and p.EffectiveDate <= '2022-12-31'

--and varOccupation.Value in ('AMHCA Student', 'CAMFT Student', 'AAMFT Student', 'OCA Student')

--group by p.PolicyNumber