Solved

SQL Query Help

Posted on 2013-11-22
13
262 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now