asked on

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.

coreconcepts

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

mburk1968

ASKER

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'
)

Lowfatspread

;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

mburk1968

ASKER

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

coreconcepts

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

mburk1968

ASKER

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

coreconcepts

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

coreconcepts

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

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mburk1968

ASKER

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

coreconcepts

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

Lowfatspread

@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.

Lowfatspread

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