SQL Query Support

I have a table of Insureds,  wellandp.Policyrisk. I need a query to perform the following.

1. I need to select  wellandp.Policyrisk.PolNumber, wellandp.Policyrisk.PolEffDate, wellandp.Policyrisk.ReferCode from wellandp.Policyrisk where wellandp.Policyrisk.coveragetype = 'CTL'
2. For the Insured listed, find all of the wellandp.Policyrisk.RiskType = 'INDPCP' risk for the same wellandp.Policyrisk.PolNumber, wellandp.Policyrisk.PolEffDate, wellandp.Policyrisk.ReferCode as listed in number 1
3. Group By wellandp.Policyrisk.PolNumber, wellandp.Policyrisk.PolEffDate, wellandp.Policyrisk.ReferCode(List should on contain 'INDPCP' RiskTypes
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.

ste5anSenior DeveloperCommented:
1. Do it exactly like described.
2. How are Insureds and  wellandp.Policyrisk related?
3. Group what? But simply do that.
0
mburk1968Author Commented:
1.I'm not familiar with writing queries on the same tbl.
2. PolicyNumber...
3. The record set
0
ste5anSenior DeveloperCommented:
1. Exactly as you wrote it:

SELECT  wellandp.Policyrisk.PolNumber ,
        wellandp.Policyrisk.PolEffDate ,
        wellandp.Policyrisk.ReferCode
FROM    wellandp.Policyrisk
WHERE   wellandp.Policyrisk.coveragetype = 'CTL';

Open in new window


2. Same, exactly as you wrote:

SELECT  wellandp.Policyrisk.PolNumber ,
        wellandp.Policyrisk.PolEffDate ,
        wellandp.Policyrisk.ReferCode
FROM    wellandp.Policyrisk
WHERE   wellandp.Policyrisk.coveragetype = 'CTL'
        AND wellandp.Policyrisk.RiskType = 'INDPCP';

Open in new window


3. And again as you wrote:

SELECT  wellandp.Policyrisk.PolNumber ,
        wellandp.Policyrisk.PolEffDate ,
        wellandp.Policyrisk.ReferCode ,
        COUNT(*)
FROM    wellandp.Policyrisk
WHERE   wellandp.Policyrisk.coveragetype = 'CTL'
        AND wellandp.Policyrisk.RiskType = 'INDPCP'
GROUP BY wellandp.Policyrisk.PolNumber ,
        wellandp.Policyrisk.PolEffDate ,
        wellandp.Policyrisk.ReferCode;

Open in new window


Please do yourself a favor: Get T-SQL Fundamentals from Itzig Ben-Gan.
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.

mburk1968Author Commented:
That doesn't exactly work.

I need to first find all the records with Coveragetype = 'CTL'

1. The query brings back 1018 records

SELECT  wellandp.Policyrisk.PolNumber ,
        wellandp.Policyrisk.PolEffDate ,
        wellandp.Policyrisk.ReferCode
FROM    wellandp.Policyrisk
WHERE   wellandp.Policyrisk.coveragetype = 'CTL';

Then I need a sub query that finds any records that match the above data set by PolNumber, PolEffDate and ReferCode with a RiskType = 'INDPCP'  

SELECT  wellandp.Policyrisk.PolNumber ,
        wellandp.Policyrisk.PolEffDate ,
        wellandp.Policyrisk.ReferCode ,
            wellandp.Policyrisk.coveragetype ,
        COUNT(*)
FROM    wellandp.Policyrisk
WHERE   wellandp.Policyrisk.Risktype = 'INDPCP'
GROUP BY wellandp.Policyrisk.PolNumber ,
        wellandp.Policyrisk.PolEffDate ,
        wellandp.Policyrisk.ReferCode ,
      wellandp.Policyrisk.coveragetype;

In the end I only want to display those records INDPCP records that matched up with the outer select.
0
Scott PletcherSenior DBACommented:
You can do it in a single SELECT/scan of the table:

SELECT  wellandp.Policyrisk.PolNumber ,
        wellandp.Policyrisk.PolEffDate ,
        wellandp.Policyrisk.ReferCode ,
        wellandp.Policyrisk.coveragetype ,
        COUNT(*)
FROM    wellandp.Policyrisk
WHERE   wellandp.Policyrisk.coveragetype = 'CTL' OR wellandp.Policyrisk.Risktype = 'INDPCP'
GROUP BY wellandp.Policyrisk.PolNumber ,
        wellandp.Policyrisk.PolEffDate ,
        wellandp.Policyrisk.ReferCode ,
        wellandp.Policyrisk.coveragetype
HAVING  MAX(CASE WHEN wellandp.Policyrisk.coveragetype = 'CTL' THEN 1 ELSE 0 END) = 1 AND
                MAX(CASE WHEN wellandp.Policyrisk.Risktype = 'INDPCP' THEN 1 ELSE 0 END) = 1
0
mburk1968Author Commented:
I don't believe I am explaining it correctly. I'm hoping this makes more sense.

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
0
mburk1968Author Commented:
This is what I have however I am not retrieving any records
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;

Open in new window

0
mburk1968Author Commented:
This was my solution
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

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:
I needed a single line for INDPCP Risk with a TVGrossPremium that was greater than 0.00 that had a matching CTL record.
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.