• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

Creating a Complex IF like Statement or Case Statement in T-SQL

I am creating a T-SQL View that joins 5 tables of election types to one main table of people.  Not every person from my main table is in all 5 of the election tables (but if they are, they can not appeaer more than once within each election type table).  So I am doing Left Outer Joins from my main table to the 5 election tables.

Each of these 5 election tables have the same structure and fields.  What I want to do, is for each record, compare the values of a Date field in each one, and return the earliest non-null value.  

Here is an example.  Let's say for one record, the value of the "Effective Date" field in each election table is:

Table1: 7/1/2008
Table2: Null
Table3: 1/1/2008
Table4: 9/1/2008
Table5: Null

So I want to return 1/1/2008.

I thought about writing a CASE statement, but am not sure how to do that, because I think that checks the value of one particular field in a specific table, where I am comparing simliar fields across all five tables.

Can someone help?

Thanks.
0
JoeMiskey
Asked:
JoeMiskey
  • 4
  • 2
1 Solution
 
chapmandewCommented:
post your actual sql query w/ the fields...
0
 
jamesguCommented:
select user_id, min(Effective_Date) from (

select user_id, Effective_Date from main_table a, table1 b
where conditions
union
select  user_id, Effective_Date from main_table a, table2 b
where conditions
union
select  user_id, Effective_Date from main_table a, table3 b
where conditions
union
select  user_id, Effective_Date from main_table a, table4 b
where conditions
union
select  user_id, Effective_Date from main_table a, table5 b
where conditions
) TT
group by user_id
0
 
JoeMiskeyAuthor Commented:
jamesgu.
I am not sure if that solution will work (or maybe I am just unsure how to implement it within my VBA code).  Per chapmandew's request, I am posting some code below.  I cleaned it up a bit, as I am returning about 25 other fields that just kind oif muddle the code a bit.

Select
      m.Company_Tax_ID , m.Social_Security_Num
      , DCA.Effective_Date, DCA.Amount, DCA.Pay_Freq
      , HCA.Effective_Date, HCA.Amount, HCA.Pay_Freq
      , HRA.Effective_Date, HRA.Amount, HRA.Pay_Freq
      , PRK.Effective_Date, PRK.Amount, PRK.Pay_Freq
      , TRN.Effective_Date, TRN.Amount, TRN.Pay_Freq
From
      Member_Master as m
Left Outer Join
      eqry_Current_Elections_DCAP as DCA
On
      m.Company_Tax_ID=DCA.Company_Tax_ID
                     and m.Social_Security_num=DCA.Social_Security_Num
Left Outer Join
      eqry_Current_Elections_MEDFSA as HCA
On
      m.Company_Tax_ID=HCA.Company_Tax_ID
                      and m.Social_Security_num=HCA.Social_Security_Num
Left Outer Join
      eqry_Current_Elections_HRA as HRA
On
      m.Company_Tax_ID=HRA.Company_Tax_ID
                     and m.Social_Security_num=HRA.Social_Security_Num
Left Outer Join
      eqry_Current_Elections_PARKING as PRK
On
      m.Company_Tax_ID=PRK.Company_Tax_ID
                     and m.Social_Security_num=PRK.Social_Security_Num
Left Outer Join
      eqry_Current_Elections_TRANS as TRN
On
      m.Company_Tax_ID=TRN.Company_Tax_ID
                     and m.Social_Security_num=TRN.Social_Security_Num
Where
      m.Depcode='00'
      and (isnull(DCA.YearID,0)+isnull(HCA.YearID,0)+isnull(HCA.YearID,0)+isnull(PRK.YearID,0)+isnull(TRN.YearID,0))>0

What I really want to return is instead of an Effective Date for each election type, I would like to return the earliest non-null Effective Date for that record.  If it were Excel, it would be this function:

MIN(DCA.Effective_Date, HCA.Effective_Date, HRA.Effective_Date, PRK.Effective_Date, TRN.Effective_Date)

The Pay_Freq field I want to return is a little trickier.  Just like the Effective_Date field, I only want to return one value instead of five.  Any non-null values in HCA, DCA, PRK, and TRN will always be the same, but the value from HRA may be different.  If there is a value in HCA, DCA, PRK, or TRN, I want to return any one of these values.  If they are all Null, I want to return the value from HRA.

Hope this clears things up a bit without making it too confusing!
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
JoeMiskeyAuthor Commented:
Perhaps I am going about this problem the wrong way.  When it comes to the elections types, we really are starting with one large table with the following fields:

Company_Tax_ID
Social_Security_Num
Plan_ID (where can equal 'DCA','HCA','HRA','PRK',or 'TRN')
Effective_Date
End_Date
Amount
Pay_Freq

From this table, I created my five views for each plan type using SQL code like this:

CREATE VIEW dbo.eqry_Current_Elections_HRA
     AS
SELECT
     *
FROM
     MEMBER_BENEFIT_ELECTIONS
WHERE
     PLAN_ID='HRA'
     AND GETDATE() BETWEEN EFFECTIVE_DATE AND END_DATE

The date criteria I placed on each view ensures that each person will have no more than one record in each of the five views.

I was actually thinking that the preferred way of doing what I want to do may be to actually use an Aggregate Query instead of breaking my main election table up into five views and linking them, but because of the nature of the fields I want to return, I am not sure if it is possible to return what I want using that method.  That is why I was using the metholdology I described in my first few posts.  

Essentially, I want to return one single record per Company_Tax_ID/Social_Security_Num combination that returns the following fields:

Company_Tax_ID
Social_Security_Num
Earliest Non-Null Effective_Date
DCA Amount
HCA Amount
HRA Amount
PRK Amount
TRN Amount
Pay_Freq (according to the rules I set in my previous post)

If anyone thinks that they could do this easier with an Aggregate Query instead of the method I was trying to do, that is fine also.  Whatever it takes to get the results we need!

Thanks.
0
 
jamesguCommented:
what about amount value, do you want all 5 values?
0
 
JoeMiskeyAuthor Commented:
Yes, as I have shown above I want all five of the amounts (and 0 for the ones that don't exist).  

This part was no problem using my original method.  I simply used functions like
ISNULL(DCA.Amount,0)
etc
to return each amount (and return a 0 if Null).
0
 
JoeMiskeyAuthor Commented:
OK, I think I figured out how to get what I need, by creating some User Defined Functions.  This is my first stab at UDFs in SQL so they might be a bit ugly, but they appear to work.  I could not figure out how to send the UDFs arrays of unknown sizes for comparison, so I made each one with exactly five parameters.

First, here is the one to pull the earliest date:

CREATE FUNCTION dbo.efn_MinVal_Date5
      (@myInput1 As datetime
      ,@myInput2 As datetime
      ,@myInput3 As datetime
      ,@myInput4 As datetime
      ,@myInput5 As datetime)
      returns datetime
BEGIN
      DECLARE @MinVal as datetime
      SET @MinVal=GetDate()
      IF Not (@myInput1 is null)
            SET  @minVal=@myInput1
      IF (Not (@myInput2 is null)) and (@myInput2<@MinVal)
            SET  @minVal=@myInput2
      IF (Not (@myInput3 is null)) and (@myInput3<@MinVal)
            SET  @minVal=@myInput3
      IF (Not (@myInput4 is null)) and (@myInput4<@MinVal)
            SET  @minVal=@myInput4
      IF (Not (@myInput5 is null)) and (@myInput5<@MinVal)
            SET  @minVal=@myInput5
    Return @MinVal
END

Here is the one to pull the first non-null value it finds.  They key with this is to enter the parameters in order of preference:

CREATE FUNCTION dbo.efn_PullFirst_5
      (@myInput1 As nvarchar(30)
      ,@myInput2 As nvarchar(30)
      ,@myInput3 As nvarchar(30)
      ,@myInput4 As nvarchar(30)
      ,@myInput5 As nvarchar(30))
      returns nvarchar(30)
BEGIN
      DECLARE @FirstVal as nvarchar(30)
      IF Not (@myInput5 is null)
            SET  @FirstVal=@myInput5
      IF Not (@myInput4 is null)
            SET  @FirstVal=@myInput4
      IF Not (@myInput3 is null)
            SET  @FirstVal=@myInput3
      IF Not (@myInput2 is null)
            SET  @FirstVal=@myInput2
      IF Not (@myInput1 is null)
            SET  @FirstVal=@myInput1
    Return @FirstVal
END
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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