March 4, 2011 at 4:54 am
I want to assign a value in a column in a table based on values in other columns on the same row.
I will like to query the table below:
Create table Samp(Token Int, TokenOrder smallint, StartDate smalldatetime, EndDate smalldatetime,
Proc1 varchar(6), Proc2 varchar(6), Proc3 varchar(6), Proc4 varchar(6),
Proc5 varchar(6), Proc6 varchar(6), Proc7 varchar(6), Proc8 varchar(6))
insert into samp values (1482709, 3, '23/03/2010', '12/04/2010', 'K453' , 'K402' , 'K471' , 'K561' , 'Y731' , 'E852' , 'L912' , '');
insert into samp values (1485275, 1, '21/02/2010', '22/04/2010', 'K301' , 'K263' , 'Y731' , 'T032' , 'E423' , 'E852' ,'' , '');
insert into samp values (1487004, 3, '27/03/2010', '12/04/2010', 'K262' , 'K531' , 'E852' , 'U201' , 'L912' ,'' , '', '');
insert into samp values (1491060, 1, '13/03/2010', '16/04/2010', 'K596' , 'K561' , 'K636' , 'Y534' , 'K753' , 'Y534' , 'Y142' , 'K601' ) ;
insert into samp values (1491575, 3, '31/03/2010', '31/03/2010', 'K751' , 'Y534' , 'K611', 'Y705', 'K614','' ,'' ,'' );
insert into samp values (1491595, 3, '30/03/2010', '30/03/2010', 'K753' , 'Y142' , 'Y534' ,'K611' , '', '', '','' );
insert into samp values (1491609, 3, '12/04/2010', '20/04/2010', 'K451' , 'K411' , 'Y698' , 'Y731' , 'Z954' , 'Z942' ,'' , '');
insert into samp values (1491915, 2, '18/03/2010', '22/03/2010', 'K753' , 'Y534' , 'K614' , 'Y981' , 'Y972' , 'U051' , 'Y981' , 'Y705' );
and assign values to a new column as follows:
For each row,
If ('K611', 'Y705' and 'K614') are found in any of the columns Proc1, Proc2, Proc3, Proc4, Proc5, Proc6, Proc7, Proc8, then 'Both In and Out' is assigned to the new column.
If only 'Y705' and 'K614' are found in any of the columns Proc1, Proc2, Proc3, Proc4, Proc5, Proc6, Proc7, Proc8, then 'Out' is assigned to the new column.
If only 'K611' is found in any of the columns Proc1, Proc2, Proc3, Proc4, Proc5, Proc6, Proc7, Proc8, then 'In' is assigned to the new column.
Otherwise 'None' is assigned
Can you help please
March 4, 2011 at 5:21 am
Not tested, but how about a case statement?
--Dummy table and data
DECLARE @Samp AS TABLE (
token INT, tokenorder SMALLINT, startdate SMALLDATETIME,
enddate SMALLDATETIME, proc1 VARCHAR(6), proc2 VARCHAR(6),
proc3 VARCHAR(6), proc4 VARCHAR(6), proc5 VARCHAR(6),
proc6 VARCHAR(6), proc7 VARCHAR(6), proc8 VARCHAR(6),
--Added column to sample table
newcolumn VARCHAR(10))
--Changed SMALLDATETIME data to ISO standard
INSERT INTO @Samp VALUES (1482709, 3, '2010-03-21', '2010-04-12',
'K453', 'K402', 'K471', 'K561', 'Y731', 'E852', 'L912', '', NULL);
INSERT INTO @Samp VALUES (1485275, 1, '2010-02-21', '2010-04-22',
'K301', 'K263', 'Y731', 'T032', 'E423', 'E852', '', '', NULL);
INSERT INTO @Samp VALUES (1487004, 3, '2010-03-27', '2010-04-12',
'K262', 'K531', 'E852', 'U201', 'L912', '', '', '', NULL);
INSERT INTO @Samp VALUES (1491060, 1, '2010-03-13', '2010-04-16',
'K596', 'K561', 'K636', 'Y534', 'K753', 'Y534', 'Y142', 'K601', NULL);
INSERT INTO @Samp VALUES (1491575, 3, '2010-03-31', '2010-03-31',
'K751', 'Y534', 'K611', 'Y705', 'K614', '', '', '', NULL);
INSERT INTO @Samp VALUES (1491595, 3, '2010-03-30', '2010-03-30',
'K753', 'Y142', 'Y534', 'K611', '', '', '', '', NULL);
INSERT INTO @Samp VALUES (1491609, 3, '2010-04-12', '2010-04-20',
'K451', 'K411', 'Y698', 'Y731', 'Z954', 'Z942', '', '', NULL);
INSERT INTO @Samp VALUES (1491915, 2, '2010-03-18', '2010-03-22',
'K753', 'Y534', 'K614', 'Y981', 'Y972', 'U051', 'Y981', 'Y705', NULL);
SELECT * FROM @Samp
--Actual Solution
UPDATE @Samp
SET newcolumn = CASE WHEN (Proc1 = 'K611' OR Proc2 = 'K611' OR Proc3 = 'K611' OR
Proc4 = 'K611' OR Proc5 = 'K611' OR Proc6 = 'K611' OR
Proc7 = 'K611' OR Proc8 = 'K611') AND
(Proc1 = 'Y705' OR Proc2 = 'Y705' OR Proc3 = 'Y705' OR
Proc4 = 'Y705' OR Proc5 = 'Y705' OR Proc6 = 'Y705' OR
Proc7 = 'Y705' OR Proc8 = 'Y705') AND
(Proc1 = 'K614' OR Proc2 = 'K614' OR Proc3 = 'K614' OR
Proc4 = 'K614' OR Proc5 = 'K614' OR Proc6 = 'K614' OR
Proc7 = 'K614' OR Proc8 = 'K614')
THEN 'In and Out'
WHEN (Proc1 = 'Y705' OR Proc2 = 'Y705' OR Proc3 = 'Y705' OR
Proc4 = 'Y705' OR Proc5 = 'Y705' OR Proc6 = 'Y705' OR
Proc7 = 'Y705' OR Proc8 = 'Y705') AND
(Proc1 = 'K614' OR Proc2 = 'K614' OR Proc3 = 'K614' OR
Proc4 = 'K614' OR Proc5 = 'K614' OR Proc6 = 'K614' OR
Proc7 = 'K614' OR Proc8 = 'K614')
THEN 'Out'
WHEN Proc1 = 'K614' OR Proc2 = 'K614' OR Proc3 = 'K614' OR
Proc4 = 'K614' OR Proc5 = 'K614' OR Proc6 = 'K614' OR
Proc7 = 'K614' OR Proc8 = 'K614'
THEN 'In'
ELSE 'None' END
SELECT * FROM @Samp
March 4, 2011 at 7:26 am
Really grateful. Many thanks
March 7, 2011 at 5:28 am
I personally would use a persisted computed column for this.
That would automatically update your new column if the data in your 'proc' columns changes.
I now await an ear-bashing from one of the more experienced DBAs....
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
March 7, 2011 at 7:26 am
How do you do that? Can you show me?
March 7, 2011 at 7:39 am
This tutorial covers the basics:
http://www.mssqltips.com/tip.asp?tip=1682 I'm afraid I'm at work at the moment and dont have the time to prepare solution tailored to your specific needs.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply