?
Solved

SQL Query Help

Posted on 2013-11-22
13
Medium Priority
?
268 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

770 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