Ordering dates

[nieldaniels]

I have a table that has 4 dates for each record (birthday, anniversary, policy_start & policy_end). What I want to do is cycle through each record and then do some calculations using the gaps between the dates. So it
might be Calc1 using (birthday->anniversary), Calc2(anniverary->policy_start), Calc3(policy_start->policy_end), etc. BUT the dates might be in different orders.

In VBA in Excel, I can use the SMALL function within my calcs and this returns the minimum date, the next smallest, the next & then the biggest. But how can I do this in SQL. I guess I can push the 4 dates out to a temp table, order it and then suck them back in - but I have no idea how to do this for all the records in my primary table

ps - I'm an SQL newbie

 

[rac2]

I am thinking about what you may mean when you say "the dates might be in different orders". Do you mean that a birthday may precede or follow an anniversary date? An anniversary date may come before or after the policy_start date? Surely the policy_end date follows the policy_start date, so that one is not a problem.

The CASE expression might provide the solution you are looking for.

SELECT 
           CASE
              WHEN birthday > anniversary THEN Calc1_A
              ELSE Calc1_B
           END AS "BirthdayAnniversaryCalculation",
           CASE
              WHEN anniversary > policy_start  THEN Calc2_A
              ELSE Calc2_B
           END AS "AnniversaryPolicyStartCalculation",
   etc.