[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

SELECT UNION CLAUSE ?

The following Stored Procedure works fine. The FROM clause includes the statement:
ON tblStatesAll.StateFS = tblCustomers.State

This logic works fine for tblCustomer records for states in the U.S. Based on a match (ON tblStatesAll.StateFS = tblCustomers.State), I retrieve an integer value for tblStatesAll.BondsFS for the respective state.

I then use this integer value in the following SELECT clause. For example: If a tblCustomers record is read with a State value of AZ (ARIZONA) with a RptYear = 2005 and tblStatesAll.BondsFS = 5 for the state of AZ, then DATEFI = 6/30/2000 (2005 - 5).

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll

My challenge is that if the tblStatesAll.StateFS value is NOT EQUAL to tblCustomers.State, I need to select the  tblStatesAll.BondsFS record for the State of DE (Delaware). All foreign country tblCustomer records need to be matched to tblStatesAll record for the state of DE.

The tblCustomers.State value is blank for most Foreign Countries so I could have the tblCustomers value default to DE (Delaware) when the table is loaded. However, there are instances where the State field has a value of VI (Virgin Islands), GU (Guam) and these values are not on the tblStatesAll table. Thus, ON tblStatesAll.StateFS <> tblCustomers.State.  

I was thinking of creating a SELECT UNION clause and the secondary select would have in the FROM clause:
 
ON tblStatesAll.StateFS <> tblCustomers.State.

I guess this would work in instances where the tblCustomers table was modified to default to a value of DE for the STATE field. However, I would still have a problem where the value of state was GU (GUAM) or VI(Virgin Islands).

Do you know how I could retrieve the tblStatesAll record for DE (Deleware) for Foreign country records where the tblCustomers record has a value of VI or GU. These tblCustomer records would not be found on the tblStatesAll table. I need to follow a business rule for my application which states that for non-U.S. entities, the value of tblStatesAll.BondsFS should be the value for the state of DELEWARE ?

TABLE: tblStatesAll
-------   ------------
State     BondsFS
AL         1
AZ         5
CA         4
CO         2
DE         3

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll

CREATE PROCEDURE dbo.procFlACS

@RptYear int
AS

DECLARE @DateFI AS DATETIME
DECLARE @DateMF AS DATETIME
DECLARE @DateC AS DATETIME
DECLARE @DateE AS DATETIME
DECLARE @DateI AS DATETIME

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll
SELECT @DateMF = CAST((@RptYear - tblStatesAll.MutualFS) AS VARCHAR) + '-06-30' from tblStatesAll
SELECT @DateC = CAST((@RptYear - tblStatesAll.CashFS) AS VARCHAR) + '-06-30' from tblStatesAll
SELECT @DateE = CAST((@RptYear - tblStatesAll.StocksFS) AS VARCHAR) + '-06-30' from tblStatesAll
SELECT @DateI = CAST(((@RptYear - tblStatesAll.IRAFS)+71) AS VARCHAR) + '-12-31' from tblStatesAll

SELECT Null AS Title, UPPER(tblCustomers.FirstName) AS [First Name], UPPER(tblCustomers.MiddleInitial) AS [MI],
UPPER(tblCustomers.LastName) AS [Last Name], UPPER(tblCustomers.SecondNameFirst) AS [Second Name First],
UPPER(tblCustomers.SecondNameMid) AS [Second Name Mid], UPPER(tblCustomers.SecondNameLast) AS [Second Name Last],
Null AS Relation, UPPER(tblCustomers.Address1) AS [Address 1], UPPER (tblCustomers.Address2) AS [Address 2],
UPPER(tblCustomers.Address3) AS [Address 3], UPPER(tblCustomers.Address4) AS [Address 4], UPPER(tblCustomers.City) AS City,
UPPER(tblCustomers.State) AS State, tblCustomers.Zip AS Zip, tblCustomers.SSN, Null AS [DDA Number], Null AS [Check/Cert Number],
tblProducts.CUSIP, tblProducts.SecurityName AS [Security Name], Null AS [Sub-Issue],
Right(tblCustomers.OfficeNumber,3) + ' ' + tblCustomers.CustomerNumber AS [Account Number],
tblProducts.PropertyType AS [Property Type], Null AS [Property Status],
CASE WHEN tblProducts.CashBalance IS NULL THEN tblProducts.Quantity ELSE tblProducts.CashBalance END AS [Check/Cert Amount]
 
FROM tblStatesAll INNER JOIN (tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber = tblProducts.CustomerNumber)
ON tblStatesAll.StateFS = tblCustomers.State
WHERE (tblProducts.PropertyType='OTHER')   AND tblStatesAll.FallCycle= '1'  OR
               (tblProducts.PropertyType='MUTUAL FUND' AND tblCustomers.DateLost <= @DateMF AND tblStatesAll.FallCycle= '1'  AND tblProducts.IRACode Is Null) OR
               (tblProducts.PropertyType='CASH' AND tblCustomers.DateLost <= @DateC AND tblStatesAll.FallCycle= '1'  AND tblProducts.IRACode Is Null) OR
               (tblProducts.PropertyType='EQUITY' AND tblCustomers.DateLost <= @DateE AND tblStatesAll.FallCycle= '1'  AND tblProducts.IRACode Is Null) OR
               (tblProducts.PropertyType='FIXED INCOME' AND tblCustomers.DateLost <= @DateFI AND tblStatesAll.FallCycle= '1'  AND tblProducts.IRACode Is Null) OR
               (tblCustomers.DateOfBirth <= @DateI AND tblStatesAll.FallCycle= '1'  AND tblProducts.IRACode Is Not Null) OR
               (tblCustomers.DateOfBirth Is Null AND tblStatesAll.FallCycle= '1' )  
 
ORDER BY tblCustomers.State
GO
0
zimmer9
Asked:
zimmer9
  • 2
2 Solutions
 
Anthony PerkinsCommented:
Perhaps by doing a LEFT JOIN on the tblStatesAll table.  Something like this:

SELECT      Null AS Title,
            UPPER(tblCustomers.FirstName) AS [First Name],
            UPPER(tblCustomers.MiddleInitial) AS [MI],
            UPPER(tblCustomers.LastName) AS [Last Name],
            UPPER(tblCustomers.SecondNameFirst) AS [Second Name First],
            UPPER(tblCustomers.SecondNameMid) AS [Second Name Mid],
            UPPER(tblCustomers.SecondNameLast) AS [Second Name Last],
            Null AS Relation,
            UPPER(tblCustomers.Address1) AS [Address 1],
            UPPER (tblCustomers.Address2) AS [Address 2],
            UPPER(tblCustomers.Address3) AS [Address 3],
            UPPER(tblCustomers.Address4) AS [Address 4],
            UPPER(tblCustomers.City) AS City,
            CASE
                  WHEN tblStatesAll.StateFS Is NOT Null THEN UPPER(tblCustomers.State)
                  ELSE 'DE'
            END AS State,
            tblCustomers.Zip AS Zip,
            tblCustomers.SSN,
            Null AS [DDA Number],
            Null AS [Check/Cert Number],
            tblProducts.CUSIP,
            tblProducts.SecurityName AS [Security Name],
            Null AS [Sub-Issue],
            Right(tblCustomers.OfficeNumber,3) + ' ' + tblCustomers.CustomerNumber AS [Account Number],
            tblProducts.PropertyType AS [Property Type],
            Null AS [Property Status],
            CASE
                        WHEN tblProducts.CashBalance IS NULL THEN tblProducts.Quantity
                        ELSE tblProducts.CashBalance
            END AS [Check/Cert Amount]
FROM      tblCustomers
            INNER JOIN tblProducts ON tblCustomers.CustomerNumber = tblProducts.CustomerNumber
            LEFT JOIN tblStatesAll ON tblCustomers.State = tblStatesAll.StateFS
WHERE      tblProducts.PropertyType='OTHER'
            AND tblStatesAll.FallCycle= '1'  
            OR (tblProducts.PropertyType='MUTUAL FUND' AND tblCustomers.DateLost <= @DateMF AND tblStatesAll.FallCycle= '1'  AND tblProducts.IRACode Is Null)
            OR (tblProducts.PropertyType='CASH' AND tblCustomers.DateLost <= @DateC AND tblStatesAll.FallCycle= '1'  AND tblProducts.IRACode Is Null)
            OR (tblProducts.PropertyType='EQUITY' AND tblCustomers.DateLost <= @DateE AND tblStatesAll.FallCycle= '1'  AND tblProducts.IRACode Is Null)
            OR (tblProducts.PropertyType='FIXED INCOME' AND tblCustomers.DateLost <= @DateFI AND tblStatesAll.FallCycle= '1'  AND tblProducts.IRACode Is Null)
            OR (tblCustomers.DateOfBirth <= @DateI AND tblStatesAll.FallCycle= '1'  AND tblProducts.IRACode Is Not Null)
            OR (tblCustomers.DateOfBirth Is Null AND tblStatesAll.FallCycle= '1')  
 ORDER BY tblCustomers.State
0
 
Scott PletcherSenior DBACommented:
What about the join to tblProducts?  If you don't get a mtach on tblCustomers, then the tblCustomers.CustomerNumber will be NULL, so obviously won't be available for a match on tblProducts.  Do you want to use DE's CustomerNumber?

There are many comparisons to tblProducts in the WHERE clause, so I think this is a critical issue in choosing the best approach.
0
 
Anthony PerkinsCommented:
Since you have AND tblStatesAll.FallCycle= '1'  in your WHERE clause, you can remove it from all the OR'd conditions, as in:

WHERE     tblProducts.PropertyType='OTHER'
          AND tblStatesAll.FallCycle= '1'  
          OR (tblProducts.PropertyType='MUTUAL FUND' AND tblCustomers.DateLost <= @DateMF AND tblProducts.IRACode Is Null)
          OR (tblProducts.PropertyType='CASH' AND tblCustomers.DateLost <= @DateC AND tblProducts.IRACode Is Null)
          OR (tblProducts.PropertyType='EQUITY' AND tblCustomers.DateLost <= @DateE AND tblProducts.IRACode Is Null)
          OR (tblProducts.PropertyType='FIXED INCOME' AND tblCustomers.DateLost <= @DateFI AND tblProducts.IRACode Is Null)
          OR (tblCustomers.DateOfBirth <= @DateI AND tblProducts.IRACode Is Not Null)
          OR (tblCustomers.DateOfBirth Is Null)  

On a separate note since tblStatesAll.FallCycle could now be Null because of the LEFT JOIN, you may want to reconsider that condition.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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