Having Issues with sql Case statement in query.

[Kelly Moyer]

************************************************
This code runs fine

with these results

InsurerID Total SubAccounts Total Contracts
WSL 3752 3653

*************************************************

With t1 as
(Select x.InsurerID, Count(Distinct x.SubaccountID) as 'Total SubAccount', 
Count(Distinct x.Contractno) as 'Total Contracts'
from
(
Select InsurerID, SubAccountID, Contractno, SubAccountNoUnits
from VApositionsHistoryAll
where (Historyasofdate = '09/14/2010' or Historyasofdate = '04/30/2010'
and SubAccountNoUnits > 0) and InsurerID = 'WSL'
Union all
select InsurerID, SubAccountId, ContractNo, ISNULL(numberofunits, 0) * 
( case when ISNULL(activitysign, '+') = '-' THEN ( -1 ) ELSE 1 END)
from VAActivity
where (filedate >'05/01/2010' and filedate <'09/15/2010') and InsurerID = 'WSL'
) as x
Group by InsurerId, ContractNo)
Select InsurerID, sum("Total SubAccount") as 'Total SubAccounts', sum("Total Contracts") as 'Total Contracts'
from t1
group by InsurerID

*************************************************
The below code is the problem Code

Trying to add Case statements.

The desired result is:

InsurerID TotalSubAccounts TotalContracts Match NonMatch
WSL 3752 3653 3600 152

The matches are by subaccountID

Help:

I am struggling with my sql query and in need of help. I have included information below and in the code box.

Matches =

If Historyasofdate = 04/30/2010
then add subaccountnounits from vapositionshistoryall table to numberofunits from the vaactivity table to get "Begin Position"

If Historyasofdate = 09/14/2010 then "Ending Position"

"Beginning position" - Ending Position = 0 would be a "Match"

Non Matches:

Anything else would be a "NonMatch"

I thought that I could use case to come up with this but I am stuggling.


I am not sure if I am approaching this properly. Help any suggestions appreciated.

*************************************************
With t1 as
(Select x.InsurerID, Count(Distinct x.SubaccountID) as 'Total SubAccount', 
Count(Distinct x.Contractno) as 'Total Contracts',
 
(Case when (a.Historyasofdate = '04/30/2010') then (a.SubAccountNoUnits + x.numberofunits)else 0 end) as 'Start_POS_Act',
(Case when (a.Historyasofdate = '09/14/2010') then (a.SubAccountNoUnits)else 0 end) as 'End_Position',
(Case when ('Start_POS_Act' - 'End_Position' = 0) Then 1 Else 0 end) as 'Matches_Y_or_N'
 
From
 
(Select InsurerID, SubAccountID, Contractno, SubAccountNoUnits
from VApositionsHistoryAll a
where (Historyasofdate = '04/30/2010' or Historyasofdate = '09/14/2010')
and SubAccountNoUnits > 0 and InsurerID = 'WSL'
 
Union all
 
(select InsurerID, SubAccountId, ContractNo, ISNULL(numberofunits, 0) * 
(case when ISNULL(activitysign, '+') = '-' THEN ( -1 ) ELSE 1 END)
from VAActivity 
where (filedate >'05/01/2010' and filedate <'09/15/2010') and InsurerID = 'WSL') as x
Group by InsurerId, ContractNo)
 
Select InsurerID, sum("Total SubAccount") as 'Total SubAccounts', 
sum("Total Contracts") as 'Total Contracts', 
(Case when "Matches_Y_or_N" = 1 then Count("Matches_Y_or_N") else 0 end) as 'Number of Matches',
(Case when "Matches_Y_or_N" = 0 then Count("Matches_Y_or_N") else 1 end) as 'Number of NonMatches'
from t1
group by InsurerID