Solved

How can I combine two SQL Views to match results from the 1st view?

Posted on 2011-03-18
15
296 Views
Last Modified: 2012-05-11
I have two SQL Views that give me information and scores based on Customer ID, Open Account Type and Open Account Age.  

View 1:
This view give me Distinct Customer ID, Open Account Type (1-5, with 1 being highest point value) and a Score based on the Account Type.
SELECT DISTINCT TOP (100) PERCENT PBSA.dbo.AccountOwner.Cust AS CustID, 
    MIN(PBSA.dbo.AccountType.RiskFactor) AS AcctType, 
        CASE 
            WHEN MIN(PBSA.dbo.AccountType.RiskFactor) BETWEEN 1 AND 1 THEN 15 
            WHEN MIN(PBSA.dbo.AccountType.RiskFactor) BETWEEN 2 AND 2 THEN 7 
            WHEN MIN(PBSA.dbo.AccountType.RiskFactor) BETWEEN 3 AND 4 THEN 5 
        ELSE 0 
        END AS AcctTypeScore
FROM         PBSA.dbo.Account INNER JOIN
                   PBSA.dbo.AccountOwner ON PBSA.dbo.Account.Id = PBSA.dbo.AccountOwner.Account INNER JOIN
                      PBSA.dbo.AccountType ON PBSA.dbo.Account.Type = PBSA.dbo.AccountType.Code
WHERE     (PBSA.dbo.Account.Closed <> '1')
GROUP BY PBSA.dbo.AccountOwner.Cust

Open in new window


CustID   |   AcctType   |   AcctTypeScore
1234   |   1   |   15
5678   |   2   |   7
9876   |   5   |   0

View 2:
This view give me Distinct Customer ID, Open Account Age (by month) and a Score based on how many months old the account is (without taking the account type into consideration)
SELECT DISTINCT TOP (100) PERCENT PBSA.dbo.AccountOwner.Cust AS CustID, 
    MIN(DATEDIFF(month, PBSA.dbo.Account.OpenDate, GETDATE())) AS AcctMnth, 
        CASE 
            WHEN MIN(DATEDIFF(month, PBSA.dbo.Account.OpenDate, GETDATE())) BETWEEN 0 AND 6 THEN 15 
            WHEN MIN(DATEDIFF(month, PBSA.dbo.Account.OpenDate, GETDATE())) BETWEEN 7 AND 17 THEN 10 
        ELSE 0 
        END AS AcctMnthScore
FROM         PBSA.dbo.Account INNER JOIN
                   PBSA.dbo.AccountOwner ON PBSA.dbo.Account.Id = PBSA.dbo.AccountOwner.Account INNER JOIN
                      PBSA.dbo.AccountType ON PBSA.dbo.Account.Type = PBSA.dbo.AccountType.Code
WHERE     (PBSA.dbo.Account.Closed <> '1')
GROUP BY PBSA.dbo.AccountOwner.Cust
ORDER BY CustID

Open in new window


CustID   |   AcctMnth   |   AcctMnthScore
1234   |   12   |   10
5678   |   4     |   15
9876   |   43   |   0

What I need to know is... can I marry these two views together to take into consideration the the highest scored account type and grab the earliest open date from that Account Type?  The issue I'm running into is having the two queries separate, I'm getting a score in View 2 of the most recently open account, which could be an Account Type of 5, when the Customer has another open account which is a Type of 1.  I need to get the AcctMnthDate to shoot a score based on highest scored open AcctType.  
0
Comment
Question by:NCollinsBBP
  • 9
  • 4
  • 2
15 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 35179413
You can use UNION keyword between those 2 select's.
Should be something like:
SELECT ....
FROM ...
WHERE ....
GROUP BY ....

UNION

SELECT ...
SELECT ....
FROM ...
WHERE ....
GROUP BY ....
ORDER BY ....
0
 

Author Comment

by:NCollinsBBP
ID: 35180011
Thank you, VMontalvao.  I will give this a try when I get to where I can access my DB Server.  (just wanted to let you know I saw this and it's not being ignored.  Your efforts are greatly appreciated.)
0
 

Author Comment

by:NCollinsBBP
ID: 35189053
All right... I'm going to rephrase my question a little bit.  Sorry for any confusion.  (I don't think I went about asking correctly in the 1st place)

I currently have this code...
SELECT DISTINCT TOP (100) PERCENT PBSA.dbo.AccountOwner.Cust AS CustID,
    PBSA.dbo.AccountType.RiskFactor AS AcctType
    CASE
        WHEN PBSA.dbo.AccountType.RiskFactor BETWEEN 1 AND 1 THEN 15
        WHEN PBSA.dbo.AccountType.RiskFactor BETWEEN 1 AND 1 THEN 7
        WHEN PBSA.dbo.AccountType.RiskFactor BETWEEN 1 AND 1 THEN 5
    ELSE 0
    END AS AcctTypeScore
FROM PBSA.dbo.Account INNER JOIN
    PBSA.dbo.AccountOwner ON PBSA.dbo.Account.ID = PBSA.dbo.AccountOwner.Account INNER JOIN
    PBSA.dbo.AccountType ON PBSA.dbo.Account.Type = PBSA.dbo.AccountType.Code
WHERE (PBSA.dbo.Account.Closed <> '1')
GROUP BY PBSA.dbo.AccountOwner.Cust, PBSA.dbo.AccoutnType.RiskFactor
ORDER BY CustID

Open in new window


Now, I did not include the "MIN()" function around the PBSA.dbo.AccountType.RiskFactor... and my results look like...
CustID    |    AcctType    |    AcctTypeScore
12345    |         1             |               15    
12345    |         4             |                5    
45678    |         1             |               15    
45678    |         1             |               15    
45678    |         2             |                7    
78901    |         2             |                7    
78901    |         2             |                7    
78901    |         3             |                5    


When I add the MIN() function around the PBSA.dbo.AccountType.RiskFactor I get...
CustID    |    AcctType    |    AcctTypeScore
12345    |         1             |               15    
45678    |         1             |               15      
78901    |         2             |                7    


...which is what I want for this portion of scoring as it keeps the lowest RiskFactor tied to an open account.

Now, I want to add a column into the mixwhich is PBSA.dbo.Account.OpenDate so I can get a count of how many months old the lowest RiskFactor Open account.  I want to be able to use this CASE...
CASE 
    WHEN MIN(DATEDIFF(month, PBSA.dbo.Account.OpenDate, GETDATE())) BETWEEN 0 AND 6 THEN 15 
    WHEN MIN(DATEDIFF(month, PBSA.dbo.Account.OpenDate, GETDATE())) BETWEEN 7 AND 17 THEN 10 
ELSE 0 
END AS AcctMnthScore

Open in new window


I'm in need of keeping the lowest RiskFactor number, and but the earliest of open account of that low RiskFactor (If more than one)

I want to be able to get something that looks like...
CustID    |    AcctType    |    AcctTypeScore       |    AcctMonth    |    AcctMonthScore
12345    |         1             |               15                  |            8            |               10    
45678    |         1             |               15                  |            32          |                0          
78901    |         2             |                7                   |            4            |               15    

0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 150 total points
ID: 35189172
Sorry, I missunderstood. Try to put the rest of columns in the 2st select in the 1st one. Like this:

SELECT DISTINCT TOP (100) PERCENT PBSA.dbo.AccountOwner.Cust AS CustID, 
    MIN(PBSA.dbo.AccountType.RiskFactor) AS AcctType, 
        CASE 
            WHEN MIN(PBSA.dbo.AccountType.RiskFactor) BETWEEN 1 AND 1 THEN 15 
            WHEN MIN(PBSA.dbo.AccountType.RiskFactor) BETWEEN 2 AND 2 THEN 7 
            WHEN MIN(PBSA.dbo.AccountType.RiskFactor) BETWEEN 3 AND 4 THEN 5 
        ELSE 0 
        END AS AcctTypeScore
    MIN(DATEDIFF(month, PBSA.dbo.Account.OpenDate, GETDATE())) AS AcctMnth, 
        CASE 
            WHEN MIN(DATEDIFF(month, PBSA.dbo.Account.OpenDate, GETDATE())) BETWEEN 0 AND 6 THEN 15 
            WHEN MIN(DATEDIFF(month, PBSA.dbo.Account.OpenDate, GETDATE())) BETWEEN 7 AND 17 THEN 10 
        ELSE 0 
        END AS AcctMnthScore
FROM         PBSA.dbo.Account INNER JOIN
                   PBSA.dbo.AccountOwner ON PBSA.dbo.Account.Id = PBSA.dbo.AccountOwner.Account INNER JOIN
                      PBSA.dbo.AccountType ON PBSA.dbo.Account.Type = PBSA.dbo.AccountType.Code
WHERE     (PBSA.dbo.Account.Closed <> '1')
GROUP BY PBSA.dbo.AccountOwner.Cust 
ORDER BY CustID 

Open in new window

0
 

Author Comment

by:NCollinsBBP
ID: 35190031
I tried your code above and received some odd results...

Example:
CustID    |    AcctType    |    AcctTypeScore       |    AcctMonth    |    AcctMonthScore
12345    |         1            |               15               |            9           |               10          


The first three columns are true, as this Customer has an Open Account with RiskFactor of '1', so the AcctTypeScore would be 15.   But, the AcctMnth and AcctMnthScore are coming from another open account this customer has with a RiskFactor of '5' that has been open for 9 months.  So, it's not grabbing the OpenDate attached to the AcctType / AcctTypeScore used in the 2nd and 3rd column result.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 35190357
I need to see your data to see where's the problem.
If both queries have same FROM, WHERE and GROUP BY column, the final query should return correct data.
0
 

Author Comment

by:NCollinsBBP
ID: 35190999
If I get rid of the MIN() on the DATEDIFF portion of the query (code below)...

SELECT DISTINCT TOP (100) PERCENT PBSA.dbo.AccountOwner.Cust AS CustID, 
    MIN(PBSA.dbo.AccountType.RiskFactor) AS AcctType,
        CASE 
            WHEN MIN(PBSA.dbo.AccountType.RiskFactor) BETWEEN 1 AND 1 THEN 15 
            WHEN MIN(PBSA.dbo.AccountType.RiskFactor) BETWEEN 2 AND 2 THEN 7 
            WHEN MIN(PBSA.dbo.AccountType.RiskFactor) BETWEEN 3 AND 4 THEN 5 
        ELSE 0 
        END AS AcctTypeScore,
    DATEDIFF(month, PBSA.dbo.Account.OpenDate, GETDATE()) AS AcctMnth, 
        CASE 
            WHEN DATEDIFF(month, PBSA.dbo.Account.OpenDate, GETDATE()) BETWEEN 0 AND 6 THEN 15 
            WHEN DATEDIFF(month, PBSA.dbo.Account.OpenDate, GETDATE()) BETWEEN 7 AND 17 THEN 10 
        ELSE 0 
        END AS AcctMnthScore
FROM         PBSA.dbo.Account INNER JOIN
                   PBSA.dbo.AccountOwner ON PBSA.dbo.Account.Id = PBSA.dbo.AccountOwner.Account INNER JOIN
                      PBSA.dbo.AccountType ON PBSA.dbo.Account.Type = PBSA.dbo.AccountType.Code
WHERE     (PBSA.dbo.Account.Closed <> '1') AND (PBSA.dbo.AccountOwner.Cust = 'ABC123')
GROUP BY PBSA.dbo.AccountOwner.Cust, PBSA.dbo.Account.OpenDate 

Open in new window


I get...
CustID    |    AcctType    |    AcctTypeScore       |    AcctMonth    |    AcctMonthScore
ABC123    |         1            |               15               |           48            |               0          
ABC123    |         1            |               15               |           53            |               0          
ABC123    |         2            |               15               |            39           |               0          
ABC123    |         2            |               15               |           50            |               0          
ABC123    |         5            |               15               |            9           |               10          


What I'd want to keep is the    ABC123   |    1    |    15    |    48    |    0  
Do we need to add in another point of reference to the query?  Possibly the Account Number?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 40

Expert Comment

by:Sharath
ID: 35195985
Could you run this to provide some sample data with expected result?
SELECT DISTINCT PBSA.dbo.AccountOwner.Cust      AS CustID, 
                  PBSA.dbo.AccountType.RiskFactor AS AcctType, 
                  CASE 
                    WHEN PBSA.dbo.AccountType.RiskFactor BETWEEN 1 AND 1 THEN 15 
                    WHEN PBSA.dbo.AccountType.RiskFactor BETWEEN 1 AND 1 THEN 7 
                    WHEN PBSA.dbo.AccountType.RiskFactor BETWEEN 1 AND 1 THEN 5 
                    ELSE 0 
                  END AS AcctTypeScore, 
                  PBSA.dbo.ACCOUNT.OpenDate, 
                  CASE 
                    WHEN DATEDIFF(MONTH,PBSA.dbo.ACCOUNT.OpenDate,GETDATE()) BETWEEN 0 AND 6 THEN 15
                    WHEN DATEDIFF(MONTH,PBSA.dbo.ACCOUNT.OpenDate,GETDATE()) BETWEEN 7 AND 17 THEN 10
                    ELSE 0 
                  END AS AcctMnthScore 
    FROM PBSA.dbo.ACCOUNT 
         INNER JOIN PBSA.dbo.AccountOwner 
           ON PBSA.dbo.ACCOUNT.ID = PBSA.dbo.AccountOwner.ACCOUNT 
         INNER JOIN PBSA.dbo.AccountType 
           ON PBSA.dbo.ACCOUNT.TYPE = PBSA.dbo.AccountType.Code 
   WHERE (PBSA.dbo.ACCOUNT.Closed <> '1') 
ORDER BY CustID

Open in new window

0
 

Author Comment

by:NCollinsBBP
ID: 35197717
Sharath_123,

The results I get from that (if I go by the example earlier of Customer ID ABC123 are...

CustID    |    AcctType    |    AcctTypeScore       |               OpenDate                |    AcctMonthScore
ABC123    |         1            |               15               |   2006-10-02 00:00:00:000   |               0          
ABC123    |         1            |               15               |   2007-03-05 00:00:00:000   |               0          
ABC123    |         2            |               15               |   2007-01-18 00:00:00:000   |               0          
ABC123    |         2            |               15               |   2007-12-10 00:00:00:000   |               0          
ABC123    |         5            |               15               |   2010-06-18 00:00:00:000   |               10      
   

With digging deeper, those appear to be the appropriate OpenDate's for the the accounts types they're tied to.
0
 

Author Comment

by:NCollinsBBP
ID: 35197774
Correction...

CustID    |    AcctType    |    AcctTypeScore       |               OpenDate                |    AcctMonthScore
ABC123    |         1            |               15               |   2006-10-02 00:00:00:000   |               0          
ABC123    |         1            |               15               |   2007-03-05 00:00:00:000   |               0          
ABC123    |         2            |               0                |   2007-01-18 00:00:00:000   |               0          
ABC123    |         2            |               0                |   2007-12-10 00:00:00:000   |               0          
ABC123    |         5            |               0                |   2010-06-18 00:00:00:000   |               10          
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 35199307
NCollins, can you send some data sample?
Will be helpful to make some tests.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 350 total points
ID: 35199324
try this.
WITH CTE1 
     AS (SELECT DISTINCT PBSA.dbo.AccountOwner.Cust         AS CustID, 
                         PBSA.dbo.AccountType.RiskFactor    AS AcctType, 
                         CASE 
                           WHEN PBSA.dbo.AccountType.RiskFactor BETWEEN 1 AND 1 THEN 15 
                           WHEN PBSA.dbo.AccountType.RiskFactor BETWEEN 1 AND 1 THEN 7 
                           WHEN PBSA.dbo.AccountType.RiskFactor BETWEEN 1 AND 1 THEN 5 
                           ELSE 0 
                         END AS AcctTypeScore, 
                         PBSA.dbo.ACCOUNT.OpenDate, 
                         DATEDIFF(MONTH,OpenDate,GETDATE()) AS AcctMonth, 
                         CASE 
                           WHEN DATEDIFF(MONTH,PBSA.dbo.ACCOUNT.OpenDate,GETDATE()) BETWEEN 0 AND 6 THEN 15
                           WHEN DATEDIFF(MONTH,PBSA.dbo.ACCOUNT.OpenDate,GETDATE()) BETWEEN 7 AND 17 THEN 10
                           ELSE 0 
                         END AS AcctMnthScore 
           FROM PBSA.dbo.ACCOUNT 
                INNER JOIN PBSA.dbo.AccountOwner 
                  ON PBSA.dbo.ACCOUNT.ID = PBSA.dbo.AccountOwner.ACCOUNT 
                INNER JOIN PBSA.dbo.AccountType 
                  ON PBSA.dbo.ACCOUNT.TYPE = PBSA.dbo.AccountType.Code 
          WHERE (PBSA.dbo.ACCOUNT.Closed <> '1')), 
     CTE2 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(PARTITION BY CustID ORDER BY AcctType, AcctMonth) AS rn 
           FROM CTE1) 
  SELECT CustID,AcctType,AcctTypeScore,AcctMonth,AcctMnthScore
    FROM CTE2 
   WHERE rn = 1 
ORDER BY CustID

Open in new window

0
 

Author Comment

by:NCollinsBBP
ID: 35199626
Sharath_123,

This may have done it!  Completing some extensive testing as we speak.  I will get back to you very shortly.

-Nick
0
 

Author Comment

by:NCollinsBBP
ID: 35205473
Sarath_123,

Sorry for the delay on the response.  This was tested heavily (and tweaked minimally for appropriate scoring logic purposes) and works exactly as required.  Thank you greatly.

-Nick
0
 

Author Closing Comment

by:NCollinsBBP
ID: 35205486
Thank you to both VMontalvao & Sarath_123 for thier patience and hard work on getting me to a wonderful solution.  

-Nick
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 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

13 Experts available now in Live!

Get 1:1 Help Now