/* XML FORMAT IS AS FOLLOWS:<PARAMS> 1..1 Always 1 instance <MAIN> <ACTION> 1..1 Always 1 instance COUNT 'COUNT' or 'SELECT' </ACTION> <VERSION> 1..1 Always 1 instance CURRENT CURRENT or a date in YYYMMDD format </VERSION> <POT> 1..1 Always 1 instance ACTIVE ACTIVE, CANCELLED or COMPLEX </POT> <THRESHOLD> 1..1 Always 1 instance lt150 lt999 or ge999 where 999 is a numeric value <THRESHOLD> </MAIN> <FILTERS> 1..1 Always 1 instance <DECEASED> 1..1 Always 1 instance INCLUDE INCLUDE, EXCLUDE or ONLY </DECEASED> <VIP> 1..1 Always 1 instance EXCLUDE INCLUDE, EXCLUDE or ONLY </VIP> <CLAIMS> 1..1 Always 1 instance ONLY INCLUDE, EXCLUDE or ONLY </CLAIMS> <CRM> 1..1 Always 1 instance INCLUDE </CRM> <SPECIALNEEDS> 1..1 Always 1 instance: values are 'Include' and 'Only' <NEED> 0..4 If 'Include', then 0-4 choices: 0 = no includes. Deaf If 'Only', then 1-4 choices otherwise nothing is selected </NEED> NEED options are Deaf/Visually Impared/Blind Audio/Blind Braille <NEED> Braille Audio </NEED> </SPECIALNEEDS> <MULTIPOL> 1..1 Always 1 instance INCLUDE INCLUDE, EXCLUDE or ONLY </MULTIPOL> <MULTICASE> 1..1 Always 1 instance EXCLUDE INCLUDE, EXCLUDE or ONLY </MULTICASE> <POLICIES> 0..1 May be none, but never more than one <POLICY> 0/1..0/n If Policies exists, there must be at least one and could be many </POLICY> </POLICIES> </FILTERS> <CALLER> 0..1 Calling Appliation and user details are optional <APPLICATION> 0..1 <NAME> 0..1 </NAME> <VERSION> 0..1 </VERSION> <APPLICATION> <USER> 0..1 <ID> 0..1 </ID> <NAME> 0..1 </NAME> <IPADDRESS> 0..1 </IPADDRESS> </USER> </CALLER></PARAMS> */
SELECT COUNT(*) FROM (SELECT B.policy as 'Policy'FROM Pot B JOIN PolicyPrice PF on PF.policy = B.policy LEFT JOIN OpsBatchRecord OBR on OBR.policy= PF.policy AND OBR.MailStatus <> 'Cancelled' LEFT JOIN PolicyHdr F on F.policy= B.policy LEFT JOIN DECEASED DECEASED on DECEASED.householdref = F.householdref JOIN ClaimHdr CLAIMS on CLAIMS.policy= B.policy JOIN CRM CRM on CRM.policy= B.policy LEFT JOIN SpecialNeeds SPN on SPN.householdref = F.householdref WHERE B.Band = 'ACTIVE' AND PF.NetDetriment >150 AND OBR.BatchID is null AND DECEASED.householdref is null ) X
declare @Param XML = '<PARAMS> <!--1..1 Always 1 instance --> <MAIN> <ACTION> <!-- 1..1 Always 1 instance "COUNT" or "SELECT"--> COUNT </ACTION> <VERSION> <!--1..1 Always 1 instance CURRENT or a date in YYYMMDD format-->CURRENT </VERSION> <POT> <!--1..1 Always 1 instance ACTIVE, CANCELLED or COMPLEX -->ACTIVE </POT> <THRESHOLD> <!--1..1 Always 1 instance lt999 or ge999 where 999 is a numeric value--> lt150 </THRESHOLD> </MAIN> <FILTERS> <!-- 1..1 Always 1 instance --> <DECEASED> <!-- 1..1 Always 1 instance INCLUDE, EXCLUDE or ONLY -->INCLUDE </DECEASED> <VIP> <!--1..1 Always 1 instance INCLUDE, EXCLUDE or ONLY--> EXCLUDE </VIP> <CLAIMS> <!-- 1..1 Always 1 instance INCLUDE, EXCLUDE or ONLY --> ONLY </CLAIMS> <CRM> <!-- 1..1 Always 1 instance -->INCLUDE </CRM> <SPECIALNEEDS> <!-- 1..1 Always 1 instance: values are "" and "Only" --> Include <NEED> <!-- 0..4 If "Include", then 0-4 choices: 0 = no includes. If "Only", then 1-4 choices otherwise nothing is selected --> Deaf </NEED> <!-- NEED options are Deaf/Visually Impared/Blind Audio/Blind Braille --> <NEED> Braille Audio </NEED> </SPECIALNEEDS> <MULTIPOL> <!--1..1 Always 1 instance INCLUDE, EXCLUDE or ONLY--> INCLUDE </MULTIPOL> <MULTICASE> <!--1..1 Always 1 instance INCLUDE, EXCLUDE or ONLY--> EXCLUDE </MULTICASE> <POLICIES> <!-- 0..1 May be none, but never more than one --> <POLICY> <!-- 0/1..0/n If Policies exists, there must be at least one and could be many--> </POLICY> </POLICIES> </FILTERS> <CALLER> <!-- 0..1 Calling Appliation and user details are optional --> <APPLICATION> 0..1 <NAME> 0..1 </NAME> <VERSION> 0..1 </VERSION> </APPLICATION> <USER> 0..1 <ID> 0..1 </ID> <NAME> 0..1 </NAME> <IPADDRESS> 0..1 </IPADDRESS> </USER> </CALLER></PARAMS>'--select @ParamSELECT Main.c.value('(./ACTION/text())[1]','varchar(10)') AS Action, Main.c.value('(./VERSION/text())[1]','varchar(10)') AS Version, Main.c.value('(./POT/text())[1]','varchar(10)') AS Pot, Main.c.value('(./THRESHOLD/text())[1]','varchar(6)') as Threshold, Filters.c.value('(./DECEASED/text())[1]','varchar(10)') AS Deceased, Filters.c.value('(./VIP/text())[1]','varchar(10)') AS VIP, Filters.c.value('(./CLAIMS/text())[1]','varchar(10)') AS Claims, Filters.c.value('(./CRM/text())[1]','varchar(10)') AS CRM, Special.n.value('(./NEED/text())[1]','varchar(10)') AS Need, Special.n.value('(./NEED/text())[2]','varchar(10)') AS Need, Special.n.value('(./NEED/text())[3]','varchar(10)') AS Need, Special.n.value('(./NEED/text())[4]','varchar(10)') AS Need, Filters.c.value('(./MULTIPOL/text())[1]','varchar(10)') AS MiltiPol, Filters.c.value('(./MULTICASE/text())[1]','varchar(10)') AS MultiCase, Filters.c.value('(./POLICIES/POLICY/text())[1]','varchar(10)') AS Policies, Caller.c.value('(./APPLICATION/NAME/text())[1]','varchar(10)') AS ApplicationName, Caller.c.value('(./APPLICATION/VERSION/text())[1]','varchar(10)') AS ApplicationVersion, Caller.c.value('(./USER/ID/text())[1]','varchar(10)') AS UserId, Caller.c.value('(./USER/NAME/text())[1]','varchar(75)') AS UserName, Caller.c.value('(./USER/IPADDRESS/text())[1]','varchar(16)') AS UserIPAdressFROM @Param.nodes('PARAMS') p(c)CROSS APPLY p.c.nodes('MAIN') Main(c)CROSS APPLY p.c.nodes('FILTERS') Filters(c)CROSS APPLY Filters.c.nodes('SPECIALNEEDS') Special(n)CROSS APPLY p.c.nodes('CALLER') Caller(c)
