March 1, 2011 at 12:56 pm
Hi, realized i may have posted this originally in the wrong section of forum.
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:33 pm
Read up on the CASE statement in Books Online.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 1, 2011 at 1:34 pm
I think you are asking for a SELECT that changes the values of the data during the SELECT process, but does not update the actual table....correct? If that is the case, I think you are looking for this:
select ukey,
case when intman between 21 and 28 then 'EM'
when admtype = 'DC' and los > '0' then 'IP'
else admtype end as AdmType,
los
from PS_TestForOnline
where (fill in the criteria here...i didn't see where you had any)
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 2, 2011 at 3:25 am
many thanks for that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply