This is difficult for me to explain, but I'll try to be as clear as possible. I have three tables that I have joined. I'm using Left Joins right now because I want to see all of the records in VPPD that meet the criteria provided in the WHERE clause.
I have joined the VPPD table to the VPID table. Next I need to count the number of records in the VPID table based on POLICY_ID and the criteria in the WHERE clause. Unfortunately I get a count of all records in the VPID for the Policy_ID regardless of the criteria in the WHERE clause.
I apologize if the above is confusing. Please ask any questions that may help clarify.
Here is what I have tried thus far:
(SELECT COUNT(*) FROM VW_PI_DESC WHERE POLICY_ID = VPPD.POLICY_ID) AS ITEMCOUNT
FROM VW_PP_DESC VPPD
LEFT JOIN VW_PI_DESC VPID
LEFT JOIN VW_IP_DESC VIP
LEFT JOIN VW_INSURED VI
WHERE (VPPD.EFFECTIVE_DATE<='2015-04-20' AND VPPD.EXPIRATION_DATE>='2015-04-20')
AND VPID.CLASS_CODE IN ('B06A', 'B06B')
GROUP BY VPPD.POLICY_ID,
I am using SSMS 2012.
Any help would be greatly appreciated.