March 1, 2011 at 12:22 pm
Hi
I have a table of data that contains the following. I need to do a select on the table that asseses the data and makes changes during the select.
The rules i have to abide by are as follows....(question below)
if intman is >= '21' and <= '28' then admtype = 'EM'
if (admtype = 'DC' and los > '0') then admtype = 'IP'
------------------------------------------------------------
CREATE TABLE PS_TestForOnline
(
ukey varchar (10),
intman VARCHAR(5),
admtype VARCHAR(5),
los VARCHAR(5)
);
INSERT INTO PS_TestForOnline
VALUES('1','21','EM','7' );
INSERT INTO PS_TestForOnline
VALUES('2','19','EM','8' );
INSERT INTO PS_TestForOnline
VALUES('3','28','EM','10' );
INSERT INTO PS_TestForOnline
VALUES('4','13','IP','2' );
INSERT INTO PS_TestForOnline
VALUES('5','11','DC','0' );
INSERT INTO PS_TestForOnline
VALUES('6','15','DC','0' );
INSERT INTO PS_TestForOnline
VALUES('7','25','IP','12' );
INSERT INTO PS_TestForOnline
VALUES('8','11','DC','3' );
select * from PS_TestForOnline
-------------------------------------------------------------
My question is as follows,
how do i lay out the
select ukey, (intman where ?....), (admtype where ?......), los
from PS_TestForOnline
where .......etc
I'm trying to prepare data prior to use through reporing services so less manual intervention is required into report production.
Many Thanks in advance for any advice given.
March 1, 2011 at 1:16 pm
SELECT ukey,
intman,
admtype,
los,
CASE WHEN intman >= '21'
AND intman <= '28' THEN 'EM'
WHEN admtype = 'DC'
AND los > '0' THEN 'IP'
END AS conditional_admtype
FROM PS_TestForOnline
Sidebar: It is a bad idea to use equality operators on character data types but that is besides your question. Consider changing the ukey, intman and los columns to an integer data type (e.g. TINYINT, SMALLINT, INT or BIGINT).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply