• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

SQL Query Help

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
mburk1968
Asked:
mburk1968
  • 5
  • 4
  • 4
1 Solution
 
coreconceptsCommented:
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
 
mburk1968Author Commented:
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
 
LowfatspreadCommented:
;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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
mburk1968Author Commented:
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
 
coreconceptsCommented:
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
 
mburk1968Author Commented:
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
 
coreconceptsCommented:
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
 
coreconceptsCommented:
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
 
LowfatspreadCommented:
;
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
 
mburk1968Author Commented:
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
 
coreconceptsCommented:
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
 
LowfatspreadCommented:
@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
 
LowfatspreadCommented:
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now