Solved

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

Posted on 2008-10-27
7
608 Views
Last Modified: 2012-06-21
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
Comment
Question by:JoeMiskey
  • 4
  • 2
7 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22816813
post your actual sql query w/ the fields...
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22816912
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
 

Author Comment

by:JoeMiskey
ID: 22820940
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:JoeMiskey
ID: 22821524
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
 
LVL 9

Expert Comment

by:jamesgu
ID: 22821642
what about amount value, do you want all 5 values?
0
 

Author Comment

by:JoeMiskey
ID: 22821812
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
 

Accepted Solution

by:
JoeMiskey earned 0 total points
ID: 22822627
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

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

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
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…

758 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

22 Experts available now in Live!

Get 1:1 Help Now