January 6, 2015 at 10:18 am
I have a table where each unique entry can have up to 12 'modes', each mode with a start and stop date.
A mode is a certain type of characteristic attributed to an entry.
There are 60 different modes and a given mode number can appear in any of the 12 mode fields.
I created a very simplistic example table below.
Criteria:
I need to find all entries that have any of the modes = 1
AND for any of the other mode fields = 2 or 3, all modes = 2 or 3 must have a mode_enddate that is NOT NULL.
example:
select * from #temp_work
where (mode1 = 1 or mode2 =1 or mode3 = 1 ...)
and if any of the other mode1-12 fields = 2 or 3 then all mode 2 or 3 mode_enddate must be NOT NULL.
I have tried a few different ways to try and get this data, but none of them have been very good.
Throwing my hands up and asking for help.
Create temporary table
create table #temp_work (
id int NULL,
mode1 varchar(20) NULL,
mode_startdate_1 date NULL,
mode_enddate_1 date NULL,
mode2 varchar(20) NULL,
mode_startdate_2 date NULL,
mode_enddate_2 date NULL,
mode3 varchar(20) NULL,
mode_startdate_3 date NULL,
mode_enddate_3 date NULL,
mode4 varchar(20) NULL,
mode_startdate_4 date NULL,
mode_enddate_4 date NULL,
mode5 varchar(20) NULL,
mode_startdate_5 date NULL,
mode_enddate_5 date NULL,
mode6 varchar(20) NULL,
mode_startdate_6 date NULL,
mode_enddate_6 date NULL,
mode7 varchar(20) NULL,
mode_startdate_7 date NULL,
mode_enddate_7 date NULL,
mode8 varchar(20) NULL,
mode_startdate_8 date NULL,
mode_enddate_8 date NULL,
mode9 varchar(20) NULL,
mode_startdate_9 date NULL,
mode_enddate_9 date NULL,
mode10 varchar(20) NULL,
mode_startdate_10 date NULL,
mode_enddate_10 date NULL,
mode11 varchar(20) NULL,
mode_startdate_11 date NULL,
mode_enddate_11 date NULL,
mode12 varchar(20) NULL,
mode_startdate_12 date NULL,
mode_enddate_12 date NULL,
)
Populate table
insert into #temp_work (id,
mode1, mode_startdate_1,mode_enddate_1,
mode2, mode_startdate_2,mode_enddate_2,
mode3, mode_startdate_3,mode_enddate_3,
mode4, mode_startdate_4,mode_enddate_4,
mode5, mode_startdate_5,mode_enddate_5,
mode6, mode_startdate_6,mode_enddate_6,
mode7, mode_startdate_7,mode_enddate_7,
mode8, mode_startdate_8,mode_enddate_8,
mode9, mode_startdate_9,mode_enddate_9,
mode10, mode_startdate_10,mode_enddate_10,
mode11, mode_startdate_11,mode_enddate_11,
mode12, mode_startdate_12,mode_enddate_12)
select
1,'1','10/12/1985',NULL,'3','11/01/2000','11/01/2000',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
go
insert into #temp_work (id,
mode1, mode_startdate_1,mode_enddate_1,
mode2, mode_startdate_2,mode_enddate_2,
mode3, mode_startdate_3,mode_enddate_3,
mode4, mode_startdate_4,mode_enddate_4,
mode5, mode_startdate_5,mode_enddate_5,
mode6, mode_startdate_6,mode_enddate_6,
mode7, mode_startdate_7,mode_enddate_7,
mode8, mode_startdate_8,mode_enddate_8,
mode9, mode_startdate_9,mode_enddate_9,
mode10, mode_startdate_10,mode_enddate_10,
mode11, mode_startdate_11,mode_enddate_11,
mode12, mode_startdate_12,mode_enddate_12)
select
2,'3','10/12/1985',NULL,'1','11/01/2000','11/01/2000',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
go
insert into #temp_work (id,
mode1, mode_startdate_1,mode_enddate_1,
mode2, mode_startdate_2,mode_enddate_2,
mode3, mode_startdate_3,mode_enddate_3,
mode4, mode_startdate_4,mode_enddate_4,
mode5, mode_startdate_5,mode_enddate_5,
mode6, mode_startdate_6,mode_enddate_6,
mode7, mode_startdate_7,mode_enddate_7,
mode8, mode_startdate_8,mode_enddate_8,
mode9, mode_startdate_9,mode_enddate_9,
mode10, mode_startdate_10,mode_enddate_10,
mode11, mode_startdate_11,mode_enddate_11,
mode12, mode_startdate_12,mode_enddate_12)
select
3,'1','12/12/2009',NULL,'2','11/01/2000','11/01/2000','3','01/01/2010',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
From this limited example, the only valid entry should be ID 1 as the mode = 3 has a NOT NULL mode_enddate.
ID's 2 and 3 both have mode_enddate_X that are NULL, so should not be included.
Thanks for any help you can provide.
January 6, 2015 at 10:38 am
Imagine if the table were normalised. Something like this:
(Id, Mode, StartDate, EndDate)
I think you'd need a query like:
select id
from table
where Mode = 1
except
select id
from table
where
(Mode = 2 and Enddate is not null) or
(Mode = 3 and EndDate is not null)
If you agree, it's just a case of writing a big UNION ALL query to get your initial data into this format first.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 6, 2015 at 12:28 pm
I second Phil's suggestion.
Minor note, looks like
AND for any of the other mode fields = 2 or 3, all modes = 2 or 3 must have a mode_enddate that is NOT NULL.
means
select id
from table
where Mode = 1
except
select id
from table
where
(Mode = 2 and Enddate is null) or
(Mode = 3 and EndDate is null)
January 6, 2015 at 12:52 pm
Phil's suggestion works.
Thanks for the assistance.
Don't know why I couldn't think of this.
January 6, 2015 at 1:15 pm
Thanks for posting back and I'm glad it worked out.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply