CODEWITHSUNDEEP
sqlsql-server
rage8885
rage8885

Reputation: 417

SQL filtering on multiple dates with multiple columns

I'm attempting to pull down records that are filtered by two date columns - I need to show all "active" records. Currently, I am able to pull records using the latest "effective date", but the problem is I may have active records across multiple effective dates.

An "active" record is defined as a record with an effective date prior to or equal to current date (see notes for current date assumptions), with an end date that is equal to or greater than current date. An "inactive" record would be the first and second rows of data in my example, an active record would be the third row of data.

I'm working with a data set similar to this:

+-------------+----------------+----------+---------+---------+---------+
|  Mode Name  | Effective Date | End Date | Mode ID | Param 1 | Param 2 |
+-------------+----------------+----------+---------+---------+---------+
| Single Mode |       20110102 | 20120313 |       1 | Green   | Metal   |
| Single Mode |       20120314 | 20131122 |       1 | Green   | Wood    |
| Single Mode |       20131123 | 29991231 |       1 | Orange  | Plastic |
| Multi Mode  |       20110102 | 20120313 |       5 | Orange  | Plastic |
| Multi Mode  |       20120314 | 20120501 |       5 | Red     | Metal   |
| Triple Mode |       20120314 | 20120314 |       3 | Blue    | Cloth   |
| Triple Mode |       20120315 | 20131122 |       3 | Red     | Wood    |
| Triple Mode |       20131123 | 20131130 |       3 | Red     | Wood    |
| Triple Mode |       20131201 | 29991231 |       3 | Orange  | Wood    |
| Double Mode |       20131123 | 29991231 |       2 | Green   | Metal   |
| Double Mode |       20131202 | 29991231 |       2 | Brown   | Plastic |
| Quad Mode   |       20131202 | 29991231 |       4 | Black   | Wood    |
| Quad Mode   |       20131203 | 29991231 |       4 | Green   | Plastic |
| Zero Mode   |       20090704 | 29991231 |       0 | Blue    | Cloth   |
+-------------+----------------+----------+---------+---------+---------+

What I need to do is query so that each "active" mode is shown, but only the latest active mode as defined by the "effective date" column. "Ended" modes should not be shown. An "ended" mode is defined as having an end date prior to current date - with "29991231" being defined as "no end date". Ideally, the data set above would filter down to this:

+-------------+----------------+----------+---------+---------+---------+
|  Mode Name  | Effective Date | End Date | Mode ID | Param 1 | Param 2 |
+-------------+----------------+----------+---------+---------+---------+
| Single Mode |       20131123 | 29991231 |       1 | Orange  | Plastic |
| Triple Mode |       20131201 | 29991231 |       3 | Orange  | Wood    |
| Double Mode |       20131202 | 29991231 |       2 | Brown   | Plastic |
| Quad Mode   |       20131203 | 29991231 |       4 | Green   | Plastic |
| Zero Mode   |       20090704 | 29991231 |       0 | Blue    | Cloth   |
+-------------+----------------+----------+---------+---------+---------+

Some notes:

  1. Assume "current date" in this example is 2013-12-16.
  2. You cannot filter on end date alone - as due to the way our system works, an end date of "29991231" does not guarantee a record is ended. For example, given two records with ending dates of "29991231", the one with the more recent effective date will supercede the one with an older effective date.
  3. Some records will not be shown at all because they are ended prior to the current date.
  4. This is an old system that is terribly designed. I'm sure there are ton of better ways to store data (believe me, what I'm showing you is NOT the worst part) - but unfortunately I'm stuck with what I have.

Upvotes: 1

Views: 3934

Answers (2)

Andrew
Andrew

Reputation: 8703

You can use a CTE to get the single record for each:

with MaxDate as (select
       [Mode Name],
       max([Effective Date]) as mdate
       from
       table1
       group by [Mode Name])

   select
   *
   from
   table1 t1
   inner join
   MaxDate on mdate = [Effective Date]
   and t1.[Mode Name] = MaxDate.[Mode Name]
   where [End Date] = 29991231

SQL Fiddle

Upvotes: 1

Raymund
Raymund

Reputation: 7892

Here you go

SELECT        
YourTable.ModeName, 
YourTable.EffectiveDate, 
YourTable.EndDate, 
YourTable.ModeId, 
YourTable.Param1, 
YourTable.Param2
FROM            
YourTable INNER JOIN 
(SELECT        
ModeName, 
MAX(EffectiveDate) AS MaximumEffectiveDate
FROM YourTable AS YourTable_1
WHERE (GETDATE() BETWEEN CONVERT(Date, EffectiveDate, 101) AND CONVERT(Date, EndDate, 101))
GROUP BY ModeName) AS GroupedByMode ON YourTable.ModeName = GroupedByMode.ModeName AND 
YourTable.EffectiveDate = GroupedByMode.MaximumEffectiveDate

Just change the GETDATE() with the date of your choice

Hopefully this is what you need, I copied your data and tested it to get the same results you have

enter image description here

Upvotes: 2

Related Questions