Solved

SQL Query Help

Posted on 2013-11-22
13
266 Views
Last Modified: 2013-11-22
I have the following query
SELECT *
FROM wellandp.PolicyRisk
WHERE RiskType IN ('INDT', 'INDMDT', 'INDNJT') AND RiskFunc = 'FC' and TVGrossPremium = '0.00' and SlotType = ' '

Now I would like to add the following logic so that all the records display in the same result set.
2.      Use ReferCode from the above list to find another INDT, INDMDT, or INDNJT risk for the same PolNumber & PolEffDate where RiskFunc = “OP” and SlotType = “P” AND TVGrossPremium = “0”.

I'm just not certain how to combine the two.
0
Comment
Question by:mburk1968
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
13 Comments
 
LVL 3

Expert Comment

by:coreconcepts
ID: 39669145
Hi mburk,

      You can encapsulate your where clauses into two sets using parens like :

SELECT *
FROM wellandp.PolicyRisk
WHERE (
              RiskType IN ('INDT', 'INDMDT', 'INDNJT') 
              AND RiskFunc = 'FC' 
              AND TVGrossPremium = '0.00' 
              AND SlotType = ' '
              ) 
           OR 
              (
               ReferCode = [insert your logic] 
               AND RiskType IN ('INDT', 'INDMDT', 'INDNJT') 
               AND RiskFunc = “OP” 
               AND SlotType = “P” 
               AND TVGrossPremium = “0”
               )

Open in new window


Please note you need to define whatever your refer code will be above.  Let me know if that's what you meant, essentially, you will still get all of your results from your original query, and you will get any that match the second set
0
 

Author Comment

by:mburk1968
ID: 39669201
I thought I would need some type of Nested Select?

I need the records from the first query in this case 44 records returned so that I can use the ReferCode and PolEffDate from 1st as my match for the second that has slightly different criteria. The ReferCode and matching PolEffDate being the key that joins them.

USE EXPORTpahpixPROD;
GO

SELECT *
FROM wellandp.PolicyRisk
WHERE (
              RiskType = 'INDT'
              AND RiskFunc = 'FC'
              AND TVGrossPremium = '0.00'
              AND SlotType = ' '
              )
           OR
              (
               ReferCode = [ReferCode = from above query]
              PolEffDate = [PolEffDate from the above query]
              AND RiskType = 'INDT'
               AND RiskFunc = 'OP'
               AND SlotType = 'P'
               AND TVGrossPremium = '0.00'
               )
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39669234
;with cte as ( SELECT *
FROM wellandp.PolicyRisk
WHERE RiskType IN ('INDT', 'INDMDT', 'INDNJT') 
AND RiskFunc = 'FC' 
and TVGrossPremium = '0.00' 
and SlotType = ' ')
Select * from cte
Union 
Select a.* 
from (select * from wellandp.policyrisk
      Where a.tvgrosspremium="0"
        and a.risktype in ('INDT', 'INDMDT', 'INDNJT') 
        and a.riskfunC="OP"
        and a.slottype="P" 
    )  as a
inner join CTE as b
 on a.polnumber=b.polnumber
 and a.poleffdate=b.poleffdate
 and a.refercode=b.refercode
 
order by polnumber,poleffdate,riskfunc

Open in new window

0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:mburk1968
ID: 39669260
I'm receiving the following for the query below.
Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "a.TVGrossPremium" could not be bound.
Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "a.risktype" could not be bound.
Msg 4104, Level 16, State 1, Line 18
The multi-part identifier "a.riskfunC" could not be bound.
Msg 4104, Level 16, State 1, Line 19
The multi-part identifier "a.slottype" could not be bound.


;
WITH    cte
          AS ( SELECT   *
               FROM     vw_PolicyRisk
               WHERE    RiskType = 'INDT'
                        AND RiskFunc = 'FC'
                        AND TVGrossPremium = '0.00'
                        AND SlotType = ' '
             )
    SELECT  *
    FROM    cte
    UNION
    SELECT  a.*
    FROM    ( SELECT    *
              FROM      vw_policyrisk
              WHERE     a.TVGrossPremium = '0.00'
                        AND a.risktype = 'INDT'
                        AND a.riskfunC = 'OP'
                        AND a.slottype = 'P'
            ) AS a
            INNER JOIN CTE AS b ON a.polnumber = b.polnumber
                                   AND a.poleffdate = b.poleffdate
                                   AND a.refercode = b.refercode
    ORDER BY polnumber ,
            poleffdate ,
            riskfunc
0
 
LVL 3

Expert Comment

by:coreconcepts
ID: 39669262
Ah, I understand what you're looking for now.  I believe this will work:

SELECT RT.*
	, RC.* 
FROM
(

SELECT *
FROM wellandp.PolicyRisk
WHERE (
              RiskType IN ('INDT', 'INDMDT', 'INDNJT') 
              AND RiskFunc = 'FC' 
              AND TVGrossPremium = '0.00' 
              AND SlotType = ' '
              ) 
) as RT
FULL OUTER JOIN
(
SELECT * 
FROM wellandp.PolicyRisk
WHERE

               AND RiskType = 'INDT' 
               AND RiskFunc = 'OP' 
               AND SlotType = 'P' 
               AND TVGrossPremium = '0.00'
) as RC
      ON RC.ReferCode = RT.ReferCode 
     AND RC.ReferCode = RT.ReferCode 
     AND RC.PolEffDate = RT.PolEffDate 

Open in new window

0
 

Author Comment

by:mburk1968
ID: 39669278
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near 'PolEffDate'.

SELECT RT.*
      , RC.*
FROM
(

SELECT *
FROM vw_PolicyRisk
WHERE (
              RiskType = 'INDT'
              AND RiskFunc = 'FC'
              AND TVGrossPremium = '0.00'
              AND SlotType = ' '
              )
) as RT
FULL OUTER JOIN
(
SELECT *
FROM vw_PolicyRisk
WHERE
               ReferCode = RT.ReferCode
               PolEffDate = RT.PolEffDate
               AND RiskType = 'INDT'
               AND RiskFunc = 'OP'
               AND SlotType = 'P'
               AND TVGrossPremium = '0.00'
) as RC
0
 
LVL 3

Expert Comment

by:coreconcepts
ID: 39669313
Crap the problems of doing this in notepad I didn't put the AND's in there:

SELECT RT.*
      , RC.* 
FROM
(

SELECT *
FROM wellandp.PolicyRisk
WHERE (
              RiskType = 'INDT' 
              AND RiskFunc = 'FC' 
              AND TVGrossPremium = '0.00' 
              AND SlotType = ' '
              ) 
) as RT
FULL OUTER JOIN
(
SELECT * 
FROM wellandp.PolicyRisk
WHERE
               ReferCode = RT.ReferCode 
               AND PolEffDate = RT.PolEffDate 
               AND RiskType = 'INDT' 
               AND RiskFunc = 'OP' 
               AND SlotType = 'P' 
               AND TVGrossPremium = '0.00'
) as RC 

Open in new window

0
 
LVL 3

Expert Comment

by:coreconcepts
ID: 39669314
Please also note, I used a FULL OUTER JOIN, you may want that to be a LEFT JOIN - but just in case.  Apologies for the confusion too, somehow when I am copying and pasting on this browser I'm not getting the full set back:

SELECT RT.*
	, RC.* 
FROM
(

SELECT *
FROM wellandp.PolicyRisk
WHERE (
              RiskType IN ('INDT', 'INDMDT', 'INDNJT') 
              AND RiskFunc = 'FC' 
              AND TVGrossPremium = '0.00' 
              AND SlotType = ' '
              ) 
) as RT
FULL OUTER JOIN
(
SELECT * 
FROM wellandp.PolicyRisk
WHERE

               AND RiskType = 'INDT' 
               AND RiskFunc = 'OP' 
               AND SlotType = 'P' 
               AND TVGrossPremium = '0.00'
) as RC
     ON RC.ReferCode = RT.ReferCode 
     AND RC.ReferCode = RT.ReferCode 
     AND RC.PolEffDate = RT.PolEffDate 

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 39669323
;
WITH    cte
          AS ( SELECT   *
               FROM     vw_PolicyRisk
               WHERE    RiskType = 'INDT'
                        AND RiskFunc = 'FC'
                        AND TVGrossPremium = '0.00'
                        AND SlotType = ' '
             )
    SELECT  *
    FROM    cte
    UNION
    SELECT  a.*
    FROM    ( SELECT    *
              FROM      vw_policyrisk
              WHERE    TVGrossPremium = '0.00'
                        AND risktype = 'INDT'
                        AND riskfunC = 'OP'
                        AND slottype = 'P'
            ) AS a
            INNER JOIN CTE AS b ON a.polnumber = b.polnumber
                                   AND a.poleffdate = b.poleffdate
                                   AND a.refercode = b.refercode
    ORDER BY polnumber ,
            poleffdate ,
            riskfunc
0
 

Author Comment

by:mburk1968
ID: 39669332
Sorry my fault. Here is your query. It works however I am getting rows of NULLS?

SELECT  RT.* ,
        RC.*
FROM    ( SELECT    *
          FROM      dbo.vw_PolicyRisk
          WHERE     ( RiskType = 'INDT'
                      AND RiskFunc = 'FC'
                      AND TVGrossPremium = '0.00'
                      AND SlotType = ' '
                    )
        ) AS RT
        FULL OUTER JOIN ( SELECT    *
                          FROM      dbo.vw_PolicyRisk
                          WHERE     RiskType = 'INDT'
                                    AND RiskFunc = 'OP'
                                    AND SlotType = 'P'
                                    AND TVGrossPremium = '0.00'
                        ) AS RC ON RC.ReferCode = RT.ReferCode
                                   AND RC.ReferCode = RT.ReferCode
                                   AND RC.PolEffDate = RT.PolEffDate
slots.xlsx
0
 
LVL 3

Expert Comment

by:coreconcepts
ID: 39669335
Sorry to repost, keep catching things in my notepad code , also I believe the NULLS are from the FULL OUTER JOIN, below is the LEFT JOIN.  I think I was slow on the uptake there, I didn't catch that you changed your WHERE condition and implemented a VIEW so I kept thinking my copy and paste was all wrong.  Just need to update the below to fit your view and WHERE:


SELECT RT.*
	, RC.* 
FROM
(

SELECT *
FROM wellandp.PolicyRisk
WHERE (
              RiskType IN ('INDT', 'INDMDT', 'INDNJT') 
              AND RiskFunc = 'FC' 
              AND TVGrossPremium = '0.00' 
              AND SlotType = ' '
              ) 
) as RT
LEFT JOIN
(
SELECT * 
FROM wellandp.PolicyRisk
WHERE

               RiskType = 'INDT' 
               AND RiskFunc = 'OP' 
               AND SlotType = 'P' 
               AND TVGrossPremium = '0.00'
) as RC
     ON RC.ReferCode = RT.ReferCode 
     AND RC.ReferCode = RT.ReferCode 
     AND RC.PolEffDate = RT.PolEffDate 

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39669397
@coreconcepts


with the use of a join type query you have potentially multiplied the number of rows that will be returned  by the query ....

unless you assume that only 1 'OP' can exist for an 'FC' , and there aren't multiple risktypes as well...

only the UNION will present the "smallest" number of rows showing all rows for the desired conditions as originally stated... , without further confirmation of the underlying table structures/business processes.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39669402
p.s. why is the premium stored as a character datatype.... you run the risk of data being missed if leading spaces are included...
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PolyServe for SQL server 13 52
GeoClustering  and AOG 25 50
Database Owner 3 41
Need multiple Group By's 8 51
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question