Writing a case statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Writing a case statement

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-04-08 : 13:52:53

I wrote a stored procedure to grab the last four SSN from my table:

Select right(ssn,4)
from SSNInfo

Could someone help me write this case statement to go along with my stored procedure I just wrote. I need:

case 0000 -3635 = PA1
case 0000 - 0908 = M1
case 0909 - 1817 = M2

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 13:56:37

SELECT *,
CASE
WHEN RIGHT(SSN, 4) BETWEEN '1818' AND '3635' THEN 'PA1'
WHEN RIGHT(SSN, 4) BETWEEN '0000' AND '0908' THEN 'M1'
WHEN RIGHT(SSN, 4) BETWEEN '0909' AND '1817' THEN 'M2'
END AS MyVirtualColumn
FROM SSNInfo

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-08 : 13:59:33

[code]SELECT CASE
WHEN CAST(right(ssn,4) as int) >0000 AND CAST(right(ssn,4) as int)< 3635
THEN 'PA1'
WHEN cast(right(ssn,4) as int) >0000 AND cast(right(ssn,4) as int) < 0908
THEN 'M1'
WHEN cast(right(ssn,4) as int) >0909 AND cast(right(ssn,4) as int )< 1817
THEN 'M2'
END
FROM SSNInfo
[/code]
Also why do you have overlapping conditions specified? i think first condition would be >1817 and <3635

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-04-08 : 14:59:00

Thank you both for your replies. I don't need the first statement as it's listing an entire group where I Have it broken down more.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-04-09 : 13:41:22

Okay I thought I was done but here's another question...

I have a dropdown box on the other page and trying to get the data field value called Mods

Well I enhanced the case statement you helped me with, with this:

@module varchar(10),
@FromDate datetime,
@ToDate datetime

AS
SELECT *,

CASE

WHEN RIGHT(SSN, 4) BETWEEN '0000' AND '0908' THEN 'Mod01'
WHEN RIGHT(SSN, 4) BETWEEN '0909' AND '1817' THEN 'Mod02'
When Right(SSN, 4) BETWEEN '1818' and '2726' then 'Mod03'
When Right(SSN, 4) BETWEEN '2727' and '3635' then 'Mod04'
When Right(SSN, 4) BETWEEN '3636' and '7271' then 'Mod05'
When Right(SSN, 4) BETWEEN '4545' and '5453' then 'Mod06'
When Right(SSN, 4) BETWEEN '5454' and '6362' then 'Mod07'
When Right(SSN, 4) BETWEEN '6363' and '7271' then 'Mod08'
When Right(SSN, 4) BETWEEN '7272' and '8180' then 'Mod09'
When Right(SSN, 4) BETWEEN '8181' and '9089' then 'Mod10'
When Right(SSN, 4) BETWEEN '9090' and '9999' then 'Mod11'

END AS Module
FROM SpsList

where module=@module and pmtdate between @fromdate and @todate
order by pmtdate

My where clause is not working due to Module not being a column in the SPSList table. How do I get the stored procedure to recognize the new column named Module when the procedure is run?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 14:41:06

Make it a DERIVED TABLE.

select * from
(
SELECT *,
CASE
WHEN RIGHT(SSN, 4) BETWEEN '0000' AND '0908' THEN 'Mod01'
WHEN RIGHT(SSN, 4) BETWEEN '0909' AND '1817' THEN 'Mod02'
When Right(SSN, 4) BETWEEN '1818' and '2726' then 'Mod03'
When Right(SSN, 4) BETWEEN '2727' and '3635' then 'Mod04'
When Right(SSN, 4) BETWEEN '3636' and '7271' then 'Mod05'
When Right(SSN, 4) BETWEEN '4545' and '5453' then 'Mod06'
When Right(SSN, 4) BETWEEN '5454' and '6362' then 'Mod07'
When Right(SSN, 4) BETWEEN '6363' and '7271' then 'Mod08'
When Right(SSN, 4) BETWEEN '7272' and '8180' then 'Mod09'
When Right(SSN, 4) BETWEEN '8181' and '9089' then 'Mod10'
When Right(SSN, 4) BETWEEN '9090' and '9999' then 'Mod11'
END AS Module
FROM SpsList
where pmtdate between @fromdate and @todate
) AS d
where module=@module
order by pmtdate

E 12°55'05.25"
N 56°04'39.16"

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-04-09 : 15:30:57

Great thanks that works!

I have another question

...

How would I add an if else statement to this code since I'm selecting an item from a dropdown box on the other page. I have the info coming over in a session then I want it to display in on the other page. So if the user selects Mod 1 from the dropdown box then display that info etc...

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources