Help with writing a SQL Query

I need help with writing the following query.

1. I first need to retrieve all the records in dbo.vw_PolicyRisk "there are 1093"

SELECT  dbo.vw_PolicyRisk.PolNumber ,
        dbo.vw_PolicyRisk.PolEffDate ,
        dbo.vw_PolicyRisk.ReferCode ,
        dbo.vw_PolicyRisk.coveragetype ,
        dbo.vw_PolicyRisk.risktype ,
		SUM(dbo.vw_PolicyRisk.TVGrossPremium) AS TVGrossPremium ,
		dbo.vw_PolicyRisk.PolFunc ,
		dbo.vw_PolicyRisk.RiskFunc ,
		dbo.vw_PolicyRisk.PolRiskKey ,
        COUNT(*) AS COUNT
FROM    dbo.vw_PolicyRisk
WHERE   dbo.vw_PolicyRisk.CoverageType = 'CTL'
GROUP BY dbo.vw_PolicyRisk.PolNumber ,
        dbo.vw_PolicyRisk.PolEffDate ,
        dbo.vw_PolicyRisk.ReferCode ,
        dbo.vw_PolicyRisk.coveragetype ,
        dbo.vw_PolicyRisk.risktype ,
		dbo.vw_PolicyRisk.TVGrossPremium ,
		dbo.vw_PolicyRisk.PolFunc ,
		dbo.vw_PolicyRisk.RiskFunc ,
		dbo.vw_PolicyRisk.PolRiskKey;

Open in new window


2. I then need to find all the Insureds in that same table that have a dbo.vw_PolicyRisk.RiskType = 'INDPCP' for the same dbo.vw_PolicyRisk.PolNumber, dbo.vw_PolicyRisk.PolEffDate, dbo.vw_PolicyRisk.Refercode as in the above dataset.

SELECT  dbo.vw_PolicyRisk.PolNumber ,
        dbo.vw_PolicyRisk.PolEffDate ,
        dbo.vw_PolicyRisk.ReferCode ,
        dbo.vw_PolicyRisk.coveragetype ,
        dbo.vw_PolicyRisk.risktype ,
		SUM(dbo.vw_PolicyRisk.TVGrossPremium) AS TVGrossPremium ,
		dbo.vw_PolicyRisk.PolFunc ,
		dbo.vw_PolicyRisk.RiskFunc ,
		dbo.vw_PolicyRisk.PolRiskKey ,
        COUNT(*) AS COUNT
FROM    dbo.vw_PolicyRisk
WHERE   dbo.vw_PolicyRisk.RiskType = 'INDPCP'
GROUP BY dbo.vw_PolicyRisk.PolNumber ,
        dbo.vw_PolicyRisk.PolEffDate ,
        dbo.vw_PolicyRisk.ReferCode ,
        dbo.vw_PolicyRisk.coveragetype ,
        dbo.vw_PolicyRisk.risktype ,
		dbo.vw_PolicyRisk.TVGrossPremium ,
		dbo.vw_PolicyRisk.PolFunc ,
		dbo.vw_PolicyRisk.RiskFunc ,
		dbo.vw_PolicyRisk.PolRiskKey;

Open in new window


3. I need to Group by dbo.vw_PolicyRisk.PolNumber, dbo.vw_PolicyRisk.PolEffDate, dbo.vw_PolicyRisk.Refercode (My list should only contain INDPCP records at this point)

4. Sum dbo.vw_PolicyRisk.TVGrossPremium by dbo.vw_PolicyRisk.PolNumber, dbo.vw_PolicyRisk.PolEffDate, dbo.vw_PolicyRisk.Refercode  

5. Where dbo.vw_PolicyRisk.TVGrossPremium is not = 0
mburk1968Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mburk1968Author Commented:
This is what I currently have however I do not get any results.

SELECT  PolNumber ,
        PolEffDate ,
        ReferCode ,
        coveragetype ,
        risktype ,
        SUM(TVGrossPremium) AS TVGrossPremium ,
        PolFunc ,
        RiskFunc ,
        PolRiskKey ,
        COUNT(*) AS COUNT
FROM    ( SELECT    dbo.vw_PolicyRisk.PolNumber AS PolNumber ,
                    dbo.vw_PolicyRisk.PolEffDate AS PolEffDate ,
                    dbo.vw_PolicyRisk.ReferCode AS ReferCode ,
                    dbo.vw_PolicyRisk.coveragetype AS coveragetype ,
                    dbo.vw_PolicyRisk.risktype AS risktype ,
                    SUM(dbo.vw_PolicyRisk.TVGrossPremium) AS TVGrossPremium ,
                    dbo.vw_PolicyRisk.PolFunc AS PolFunc ,
                    dbo.vw_PolicyRisk.RiskFunc AS RiskFunc ,
                    dbo.vw_PolicyRisk.PolRiskKey AS PolRiskKey ,
                    COUNT(*) AS COUNT
          FROM      dbo.vw_PolicyRisk
          WHERE     dbo.vw_PolicyRisk.CoverageType = 'CTL'
          GROUP BY  dbo.vw_PolicyRisk.PolNumber ,
                    dbo.vw_PolicyRisk.PolEffDate ,
                    dbo.vw_PolicyRisk.ReferCode ,
                    dbo.vw_PolicyRisk.coveragetype ,
                    dbo.vw_PolicyRisk.risktype ,
                    dbo.vw_PolicyRisk.TVGrossPremium ,
                    dbo.vw_PolicyRisk.PolFunc ,
                    dbo.vw_PolicyRisk.RiskFunc ,
                    dbo.vw_PolicyRisk.PolRiskKey
        ) AS dtl
WHERE   RiskType = 'IND%CP'
        AND dtl.TVGrossPremium > '0'
GROUP BY PolNumber ,
        PolEffDate ,
        ReferCode ,
        coveragetype ,
        risktype ,
        TVGrossPremium ,
        PolFunc ,
        RiskFunc ,
        PolRiskKey;
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I first need to retrieve all the records in dbo.vw_PolicyRisk "there are 1093"
I'm assuming that 1093 is a Policy Risk Key that you want to be filtered and so try this:
SELECT  dbo.vw_PolicyRisk.PolNumber ,
        dbo.vw_PolicyRisk.PolEffDate ,
        dbo.vw_PolicyRisk.ReferCode ,
        dbo.vw_PolicyRisk.coveragetype ,
        dbo.vw_PolicyRisk.risktype ,
	SUM(dbo.vw_PolicyRisk.TVGrossPremium) AS TVGrossPremium ,
	dbo.vw_PolicyRisk.PolFunc ,
	dbo.vw_PolicyRisk.RiskFunc ,
	dbo.vw_PolicyRisk.PolRiskKey ,
        COUNT(*) AS COUNT
FROM    dbo.vw_PolicyRisk
WHERE   dbo.vw_PolicyRisk.CoverageType = 'CTL'
    AND dbo.vw_PolicyRisk.PolRiskKey = 1093
    AND dbo.vw_PolicyRisk.RiskType = 'INDPCP'
GROUP BY dbo.vw_PolicyRisk.PolNumber ,
        dbo.vw_PolicyRisk.PolEffDate ,
        dbo.vw_PolicyRisk.ReferCode ,
        dbo.vw_PolicyRisk.coveragetype ,
        dbo.vw_PolicyRisk.risktype ,
	dbo.vw_PolicyRisk.TVGrossPremium ,
	dbo.vw_PolicyRisk.PolFunc ,
	dbo.vw_PolicyRisk.RiskFunc ,
	dbo.vw_PolicyRisk.PolRiskKey
HAVING SUM(dbo.vw_PolicyRisk.TVGrossPremium) > 0

Open in new window

0
mburk1968Author Commented:
No that was the amount of records that were returned when I queried records that were CTL
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, then remove that filter and will looks like this:
SELECT  dbo.vw_PolicyRisk.PolNumber ,
        dbo.vw_PolicyRisk.PolEffDate ,
        dbo.vw_PolicyRisk.ReferCode ,
        dbo.vw_PolicyRisk.coveragetype ,
        dbo.vw_PolicyRisk.risktype ,
	SUM(dbo.vw_PolicyRisk.TVGrossPremium) AS TVGrossPremium ,
	dbo.vw_PolicyRisk.PolFunc ,
	dbo.vw_PolicyRisk.RiskFunc ,
	dbo.vw_PolicyRisk.PolRiskKey ,
        COUNT(*) AS COUNT
FROM    dbo.vw_PolicyRisk
WHERE   dbo.vw_PolicyRisk.CoverageType = 'CTL'
    AND dbo.vw_PolicyRisk.RiskType = 'INDPCP'
GROUP BY dbo.vw_PolicyRisk.PolNumber ,
        dbo.vw_PolicyRisk.PolEffDate ,
        dbo.vw_PolicyRisk.ReferCode ,
        dbo.vw_PolicyRisk.coveragetype ,
        dbo.vw_PolicyRisk.risktype ,
	dbo.vw_PolicyRisk.TVGrossPremium ,
	dbo.vw_PolicyRisk.PolFunc ,
	dbo.vw_PolicyRisk.RiskFunc ,
	dbo.vw_PolicyRisk.PolRiskKey
HAVING SUM(dbo.vw_PolicyRisk.TVGrossPremium) > 0

Open in new window

0
mburk1968Author Commented:
I'm not getting any results with that?

I tried this thinking I need to nest the query.

SELECT  PolNumber ,
        PolEffDate ,
        ReferCode ,
        coveragetype ,
        risktype ,
        SUM(TVGrossPremium) AS TVGrossPremium ,
        PolFunc ,
        RiskFunc ,
        PolRiskKey ,
        COUNT(*) AS COUNT
FROM    ( SELECT    dbo.vw_PolicyRisk.PolNumber AS PolNumber ,
                    dbo.vw_PolicyRisk.PolEffDate AS PolEffDate ,
                    dbo.vw_PolicyRisk.ReferCode AS ReferCode ,
                    dbo.vw_PolicyRisk.coveragetype AS coveragetype ,
                    dbo.vw_PolicyRisk.risktype AS risktype ,
                    SUM(dbo.vw_PolicyRisk.TVGrossPremium) AS TVGrossPremium ,
                    dbo.vw_PolicyRisk.PolFunc AS PolFunc ,
                    dbo.vw_PolicyRisk.RiskFunc AS RiskFunc ,
                    dbo.vw_PolicyRisk.PolRiskKey AS PolRiskKey ,
                    COUNT(*) AS COUNT
          FROM      dbo.vw_PolicyRisk
          WHERE     dbo.vw_PolicyRisk.CoverageType = 'CTL'
          GROUP BY  dbo.vw_PolicyRisk.PolNumber ,
                    dbo.vw_PolicyRisk.PolEffDate ,
                    dbo.vw_PolicyRisk.ReferCode ,
                    dbo.vw_PolicyRisk.coveragetype ,
                    dbo.vw_PolicyRisk.risktype ,
                    dbo.vw_PolicyRisk.TVGrossPremium ,
                    dbo.vw_PolicyRisk.PolFunc ,
                    dbo.vw_PolicyRisk.RiskFunc ,
                    dbo.vw_PolicyRisk.PolRiskKey
        ) AS dtl
WHERE   RiskType LIKE 'IND%CP'
        AND dtl.TVGrossPremium > '0'
GROUP BY PolNumber ,
        PolEffDate ,
        ReferCode ,
        coveragetype ,
        risktype ,
        TVGrossPremium ,
        PolFunc ,
        RiskFunc ,
        PolRiskKey;

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why are you using a subquery?
Did you try the solution I've provided?
0
mburk1968Author Commented:
I did use that and received no returned records.

To clarify in case I'm not explaining it properly. I need to find all of the records with a coveragetype of CTL

then I need to loop back through and find any records that match via PolNumber, PolEffDate and Refercode that have a risktype of INDPCP regardless of coveragetype on the above returned set.
0
typetoitCommented:
When you say
loop back through and find any records that match via PolNumber, PolEffDate and Refercode that have a risktype of INDPCP regardless of coveragetype on the above returned set.
, that makes it sound like you need another query.  If you want it to be in one query, then how do the two queries relate to each other?
0
mburk1968Author Commented:
So what you are telling me is that I cannot get the results I want with a single stored procedure?
0
typetoitCommented:
I re-read your question and I came up with something like this:
 
select dbo.vw_PolicyRisk.PolNumber,
        dbo.vw_PolicyRisk.PolEffDate,
        dbo.vw_PolicyRisk.ReferCode,
      SUM(dbo.vw_PolicyRisk.TVGrossPremium) AS TVGrossPremium
FROM    dbo.vw_PolicyRisk
inner join (select * from    dbo.vw_PolicyRisk
WHERE   dbo.vw_PolicyRisk.RiskType = 'INDPCP') INDPCPRisk
on dbo.vw_PolicyRisk.PolNumber = INDPCPRisk.PolNumber  AND dbo.vw_PolicyRisk.PolEffDate = INDPCPRisk.PolEffDate AND dbo.vw_PolicyRisk.Refercode = INDPCPRisk.Refercode
WHERE   dbo.vw_PolicyRisk.CoverageType = 'CTL'
Group by dbo.vw_PolicyRisk.PolNumber, dbo.vw_PolicyRisk.PolEffDate, dbo.vw_PolicyRisk.ReferCode
Having SUM(dbo.vw_PolicyRisk.TVGrossPremium) > 0
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
To clarify in case I'm not explaining it properly.
If possible add sample data. That will help us to understand what you pretend.
0
mburk1968Author Commented:
Okay, I think I am close. All I need to do now is roll up the matching records and SUM the TVGrossPremium so that the record either drops off or it remains because SUM(TVGrossPremium) is > 0

SELECT  vPR.Description ,
        vPR.RiskNo ,
        vPR.PolNumber ,
        vPR.PolEffDate ,
        vPR.CovEffDate_PR ,
        vPR.CovExpDate_PR ,
        vPR.ReferCode ,
        vPR.RiskType ,
        SUM(vPR.TVGrossPremium) AS TVGrossPremium ,
        vPR.PolFunc ,
        vPR.RiskFunc ,
        vPR.PolRiskKey
FROM    dbo.vw_PolicyRisk vPR
        LEFT OUTER JOIN ( SELECT    dbo.vw_PolicyRisk.PolNumber AS PolNumber ,
                                    dbo.vw_PolicyRisk.PolEffDate AS PolEffDate ,
                                    dbo.vw_PolicyRisk.ReferCode AS ReferCode ,
                                    dbo.vw_PolicyRisk.coveragetype AS coveragetype ,
                                    dbo.vw_PolicyRisk.risktype AS risktype ,
                                    SUM(dbo.vw_PolicyRisk.TVGrossPremium) AS TVGrossPremium ,
                                    dbo.vw_PolicyRisk.PolFunc AS PolFunc ,
                                    dbo.vw_PolicyRisk.RiskFunc AS RiskFunc ,
                                    dbo.vw_PolicyRisk.PolRiskKey AS PolRiskKey ,
                                    COUNT(*) AS COUNT
                          FROM      dbo.vw_PolicyRisk
                          WHERE     dbo.vw_PolicyRisk.CoverageType = 'CTL'
                          GROUP BY  dbo.vw_PolicyRisk.PolNumber ,
                                    dbo.vw_PolicyRisk.PolEffDate ,
                                    dbo.vw_PolicyRisk.ReferCode ,
                                    dbo.vw_PolicyRisk.coveragetype ,
                                    dbo.vw_PolicyRisk.risktype ,
                                    dbo.vw_PolicyRisk.TVGrossPremium ,
                                    dbo.vw_PolicyRisk.PolFunc ,
                                    dbo.vw_PolicyRisk.RiskFunc ,
                                    dbo.vw_PolicyRisk.PolRiskKey
                        ) AS dtl ON vPR.PolNumber = dtl.PolNumber
                                    AND vPR.PolEffDate = dtl.PolEffDate
                                    AND vPR.ReferCode = dtl.ReferCode
WHERE   vPR.RiskType LIKE 'IND%CP'
GROUP BY vPR.Description ,
        vPR.RiskNo ,
        vPR.PolNumber ,
        vPR.PolEffDate ,
        vPR.CovEffDate_PR ,
        vPR.CovExpDate_PR ,
        vPR.ReferCode ,
        vPR.RiskType ,
        vPR.PolFunc ,
        vPR.RiskFunc ,
        vPR.PolRiskKey
ORDER BY vPR.PolNumber

Open in new window

0
typetoitCommented:
Add "Having SUM(vPR.TVGrossPremium) > 0 " after the Group by and before the Order by
0
mburk1968Author Commented:
That did not have the desired effect. It did give me one record per policy number however the SUM doesn't appear to be working properly. See the attached.
Example.xlsx
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What should be the result for SUM? Zero?
0
mburk1968Author Commented:
If the SUM is Zero don't display the record.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't provide sample data so we can't test the SELECT statement.
You don't provide the expected value for the SUM so it's hard to know what to change to help you.

By the way, the sub query returns plenty of columns that aren't used in the main select so you can remove them. This is the version without the unused columns:
SELECT  vPR.Description ,
        vPR.RiskNo ,
        vPR.PolNumber ,
        vPR.PolEffDate ,
        vPR.CovEffDate_PR ,
        vPR.CovExpDate_PR ,
        vPR.ReferCode ,
        vPR.RiskType ,
        SUM(vPR.TVGrossPremium) AS TVGrossPremium ,
        vPR.PolFunc ,
        vPR.RiskFunc ,
        vPR.PolRiskKey
FROM    dbo.vw_PolicyRisk vPR
        LEFT OUTER JOIN ( SELECT    dbo.vw_PolicyRisk.PolNumber AS PolNumber ,
                                    dbo.vw_PolicyRisk.PolEffDate AS PolEffDate ,
                                    dbo.vw_PolicyRisk.ReferCode AS ReferCod
                          FROM      dbo.vw_PolicyRisk
                          WHERE     dbo.vw_PolicyRisk.CoverageType = 'CTL'
                          GROUP BY  dbo.vw_PolicyRisk.PolNumber ,
                                    dbo.vw_PolicyRisk.PolEffDate ,
                                    dbo.vw_PolicyRisk.ReferCode
                        ) AS dtl ON vPR.PolNumber = dtl.PolNumber
                                    AND vPR.PolEffDate = dtl.PolEffDate
                                    AND vPR.ReferCode = dtl.ReferCode
WHERE   vPR.RiskType LIKE 'IND%CP'
GROUP BY vPR.Description ,
        vPR.RiskNo ,
        vPR.PolNumber ,
        vPR.PolEffDate ,
        vPR.CovEffDate_PR ,
        vPR.CovExpDate_PR ,
        vPR.ReferCode ,
        vPR.RiskType ,
        vPR.PolFunc ,
        vPR.RiskFunc ,
        vPR.PolRiskKey
HAVING SUM(vPR.TVGrossPremium) > 0 
ORDER BY vPR.PolNumber

Open in new window

0
mburk1968Author Commented:
I have attached sample data with the query. Sample1/Query1 is with the last Having clause commented. Sample2/Query2 is with the last Having clause uncommented. I need one record per PolNumber except in the case where the TVGrossPremium of the Summed PolNumber is = 0.00. Then I shouldn't see that record at all.
SampleData.xlsx
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The problem is that last 3 columns (PolFunc, RiskFunc, vPR.PolRiskKey) don't gives you the uniqueness that you need.  If you don't need the information in those columns you can get rid of them and it should work as you expect:
SELECT  vPR.Description ,
        vPR.RiskNo ,
        vPR.PolNumber ,
        vPR.PolEffDate ,
        vPR.CovEffDate_PR ,
        vPR.CovExpDate_PR ,
        vPR.ReferCode ,
        vPR.RiskType ,
        SUM(vPR.TVGrossPremium) AS TVGrossPremium
FROM    dbo.vw_PolicyRisk vPR
        LEFT OUTER JOIN ( SELECT    dbo.vw_PolicyRisk.PolNumber AS PolNumber ,
                                    dbo.vw_PolicyRisk.PolEffDate AS PolEffDate ,
                                    dbo.vw_PolicyRisk.ReferCode AS ReferCod
                          FROM      dbo.vw_PolicyRisk
                          WHERE     dbo.vw_PolicyRisk.CoverageType = 'CTL'
                          GROUP BY  dbo.vw_PolicyRisk.PolNumber ,
                                    dbo.vw_PolicyRisk.PolEffDate ,
                                    dbo.vw_PolicyRisk.ReferCode
                        ) AS dtl ON vPR.PolNumber = dtl.PolNumber
                                    AND vPR.PolEffDate = dtl.PolEffDate
                                    AND vPR.ReferCode = dtl.ReferCode
WHERE   vPR.RiskType LIKE 'IND%CP'
GROUP BY vPR.Description ,
        vPR.RiskNo ,
        vPR.PolNumber ,
        vPR.PolEffDate ,
        vPR.CovEffDate_PR ,
        vPR.CovExpDate_PR ,
        vPR.ReferCode ,
        vPR.RiskType 
HAVING SUM(vPR.TVGrossPremium) > 0 
ORDER BY vPR.PolNumber

Open in new window

0
mburk1968Author Commented:
I commented the columns (PolFunc, RiskFunc, vPR.PolRiskKey). The new issue using PolNumber 1-CMP0000010 as my example is that this record is returned in my result set.

The reason that is should not be returned is that there is no matching CTL record.

The query should find coveragetype = CTL records then query the data again to look for risktype records - INDPCP that match the CTL returned on PolNumber, PolEffDate and ReferCode wher the sum of those two records are > 0
SampleData2.xlsx
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your sample data didn't include the vw_PolicyRisk.CoverageType column so I couldn't guess which rows are CTL or not. I also never liked the sub query that you have there so try this version and check if it works now:
SELECT  vPR.Description ,
        vPR.RiskNo ,
        vPR.PolNumber ,
        vPR.PolEffDate ,
        vPR.CovEffDate_PR ,
        vPR.CovExpDate_PR ,
        vPR.ReferCode ,
        vPR.RiskType ,
        SUM(vPR.TVGrossPremium) AS TVGrossPremium
FROM    dbo.vw_PolicyRisk vPR
	LEFT OUTER JOIN dbo.vw_PolicyRisk dtl 
		ON vPR.PolNumber = dtl.PolNumber
		AND vPR.PolEffDate = dtl.PolEffDate
                AND vPR.ReferCode = dtl.ReferCode
WHERE   vPR.RiskType LIKE 'IND%CP'
	AND dbo.vw_PolicyRisk.CoverageType = 'CTL'
GROUP BY vPR.Description ,
        vPR.RiskNo ,
        vPR.PolNumber ,
        vPR.PolEffDate ,
        vPR.CovEffDate_PR ,
        vPR.CovExpDate_PR ,
        vPR.ReferCode ,
        vPR.RiskType 
HAVING SUM(vPR.TVGrossPremium) > 0 
ORDER BY vPR.PolNumber

Open in new window

0
mburk1968Author Commented:
I receive the following

Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "dbo.vw_PolicyRisk.CoverageType" could not be bound.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, my fault. Forgot to replace with the table alias:
SELECT  vPR.Description ,
        vPR.RiskNo ,
        vPR.PolNumber ,
        vPR.PolEffDate ,
        vPR.CovEffDate_PR ,
        vPR.CovExpDate_PR ,
        vPR.ReferCode ,
        vPR.RiskType ,
        SUM(vPR.TVGrossPremium) AS TVGrossPremium
FROM    dbo.vw_PolicyRisk vPR
	LEFT OUTER JOIN dbo.vw_PolicyRisk dtl 
		ON vPR.PolNumber = dtl.PolNumber
		AND vPR.PolEffDate = dtl.PolEffDate
                AND vPR.ReferCode = dtl.ReferCode
WHERE   vPR.RiskType LIKE 'IND%CP'
	AND dtl.CoverageType = 'CTL'
GROUP BY vPR.Description ,
        vPR.RiskNo ,
        vPR.PolNumber ,
        vPR.PolEffDate ,
        vPR.CovEffDate_PR ,
        vPR.CovExpDate_PR ,
        vPR.ReferCode ,
        vPR.RiskType 
HAVING SUM(vPR.TVGrossPremium) > 0 
ORDER BY vPR.PolNumber

Open in new window

0
mburk1968Author Commented:
Thank you so much! This worked perfectly. I am going to accept the solution because it did everything I requested. That being said I gave it to the user and she has changed her mind and doesn't want to sum the two records together now. She only wants to see the INDPCP Risk that have a SUM > 0. Is that a major overhaul?
0
typetoitCommented:
If I understand you correctly, you should be able to take out the "AND dtl.CoverageType = 'CTL'" part of the where clause and get the desired results.
0
mburk1968Author Commented:
I still need to match to the CTL records however I only want to sum the TVGrossPremium for the INDPCP risk.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Unfortunally clients changing their mind is so usual :(
I'm not sure what her requirement means and if isn't that your starting situation. If not and if I understood well then you won't need the LEFT JOIN anymore, so check if this do what you need:
SELECT  vPR.Description ,
        vPR.RiskNo ,
        vPR.PolNumber ,
        vPR.PolEffDate ,
        vPR.CovEffDate_PR ,
        vPR.CovExpDate_PR ,
        vPR.ReferCode ,
        vPR.RiskType ,
        SUM(vPR.TVGrossPremium) AS TVGrossPremium
FROM    dbo.vw_PolicyRisk vPR
WHERE   vPR.RiskType LIKE 'IND%CP'
	AND vPR.CoverageType = 'CTL'
GROUP BY vPR.Description ,
        vPR.RiskNo ,
        vPR.PolNumber ,
        vPR.PolEffDate ,
        vPR.CovEffDate_PR ,
        vPR.CovExpDate_PR ,
        vPR.ReferCode ,
        vPR.RiskType 
HAVING SUM(vPR.TVGrossPremium) > 0 
ORDER BY vPR.PolNumber

Open in new window

0
mburk1968Author Commented:
The requirements are as follows.

1. Find all records with a CoverageType = 'CTL' from dbo.vw_PolicyRisk.

2. Find all records with a RiskType = 'INDPCP' and SUM(TVGrossPremium) > 0 that match the records returned from item 1.

Match the two sets on ON PolNumber AND PolEffDate AND ReferCode
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Which version of SQL Server are you using?
0
mburk1968Author Commented:
2012
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Good, then you can use a Common Table Expression (CTE):
WITH CTE_Risk
AS (
	SELECT  vPR.Description ,
			vPR.RiskNo ,
			vPR.PolNumber ,
			vPR.PolEffDate ,
			vPR.CovEffDate_PR ,
			vPR.CovExpDate_PR ,
			vPR.ReferCode ,
			vPR.RiskType ,
			SUM(vPR.TVGrossPremium) AS TVGrossPremium
	FROM    dbo.vw_PolicyRisk vPR
	WHERE   vPR.RiskType LIKE 'IND%CP'
	GROUP BY vPR.Description ,
			vPR.RiskNo ,
			vPR.PolNumber ,
			vPR.PolEffDate ,
			vPR.CovEffDate_PR ,
			vPR.CovExpDate_PR ,
			vPR.ReferCode ,
			vPR.RiskType 
	HAVING SUM(vPR.TVGrossPremium) > 0 
	)
SELECT  vPR.Description ,
        vPR.RiskNo ,
        vPR.PolNumber ,
        vPR.PolEffDate ,
        vPR.CovEffDate_PR ,
        vPR.CovExpDate_PR ,
        vPR.ReferCode ,
        vPR.RiskType ,
        vPR.TVGrossPremium
FROM    CTE_Risk vPR
	LEFT OUTER JOIN dbo.vw_PolicyRisk dtl 
	ON vPR.PolNumber = dtl.PolNumber
		AND vPR.PolEffDate = dtl.PolEffDate
        AND vPR.ReferCode = dtl.ReferCode
WHERE dtl.CoverageType = 'CTL'
ORDER BY vPR.PolNumber

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mburk1968Author Commented:
Thank you! I made a tweak or two but it worked perfectly
WITH    CTE_Risk
          AS ( SELECT   vPR.Description ,
                        vPR.RiskNo ,
                        vPR.PolNumber ,
                        vPR.PolEffDate ,
                        vPR.ReferCode ,
                        vPR.RiskType ,
                        SUM(vPR.TVGrossPremium) AS TVGrossPremium
               FROM     dbo.vw_PolicyRisk vPR
               WHERE    vPR.RiskType LIKE 'IND%CP'
               GROUP BY vPR.Description ,
                        vPR.RiskNo ,
                        vPR.PolNumber ,
                        vPR.PolEffDate ,
                        vPR.ReferCode ,
                        vPR.RiskType
               HAVING   SUM(vPR.TVGrossPremium) > 0
             )
    SELECT  vPR.Description ,
            vPR.RiskNo ,
            vPR.PolNumber ,
            vPR.PolEffDate ,
            vPR.ReferCode ,
            vPR.RiskType ,
            vPR.TVGrossPremium
    FROM    CTE_Risk vPR
            LEFT OUTER JOIN dbo.vw_PolicyRisk dtl ON vPR.PolNumber = dtl.PolNumber
                                                     AND vPR.PolEffDate = dtl.PolEffDate
                                                     AND vPR.ReferCode = dtl.ReferCode
    WHERE   dtl.CoverageType = 'CTL'
    GROUP BY vPR.Description ,
            vPR.RiskNo ,
            vPR.PolNumber ,
            vPR.PolEffDate ,
            vPR.ReferCode ,
            vPR.RiskType ,
            vPR.TVGrossPremium
    ORDER BY vPR.PolNumber
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.