Need help writing a stored procedure

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)

Need help writing a stored procedure

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-07-16 : 08:53:36

A claim will go to a certain department by looking at the last four numbers of the claim. To get the last four numbers of the claim I wrote this:

Select SUBSTRING(claim,6,4)
from Claim Info

This works... what I now need help with is how to write this statement...

if claim is between 0000-0908 then it is dept 1
if claim is between 0909-1817 then it is dept 2
if claim is between 1818-2726 then it is dept 3 etc...

Do I put the select statement together with this above?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 09:05:05

SELECT CASE
WHEN RIGHT(Claim, 4) BETWEEN '0000' AND '0908' THEN 1
WHEN RIGHT(Claim, 4) BETWEEN '0909' AND '1817' THEN 2
WHEN RIGHT(Claim, 4) BETWEEN '1818' AND '2726' THEN 3
END
FROM Table1

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

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-07-16 : 09:05:48

something like this :-

select case when convert(int,right(claim,4)) between ... then 'dept1'
when convert(int,right(claim,4)) between ... then 'dept2
............
............
else 'DepartmentNotFound' end as [Department]
from
TABLENAME

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-16 : 09:08:16

select case when SUBSTRING(claim,6,4) between '0000' and '0908' then 1 end as dep1,case when SUBSTRING(claim,6,4) between '0909' and '1817' then 2 end as dep2,
..
from table

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-16 : 09:09:52

Too slow

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 09:27:05

Must be wife asking you to fulfill the marital responsibilites

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-16 : 09:28:04

quote:
Originally posted by Peso

Must be wife asking you to fulfill the marital responsibilites

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

Madhivanan

Failing to plan is Planning to fail

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-07-16 : 09:29:57

I hope not then he will be divored soon

Thanks for the info going to try it now I'm sure I'll have more questions

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-07-16 : 11:08:08

Got another question...

How do I get everything in the database and this select case to work together.

@dept char(10),
@FromDate datetime,
@ToDate datetime

SELECT CASE
WHEN RIGHT(Claim, 4) BETWEEN '0000' AND '0908' THEN 01
WHEN RIGHT(Claim, 4) BETWEEN '0909' AND '1817' THEN 02
WHEN RIGHT(Claim, 4) BETWEEN '1818' AND '2726' THEN 03
WHEN RIGHT(Claim, 4) BETWEEN '2727' AND '3635' THEN 04
WHEN RIGHT(Claim, 4) BETWEEN '3636' AND '4544' THEN 05
WHEN RIGHT(Claim, 4) BETWEEN '4545' AND '5453' THEN 06
WHEN RIGHT(Claim, 4) BETWEEN '5454' AND '6362' THEN 07
WHEN RIGHT(Claim, 4) BETWEEN '6363' AND '7271' THEN 08
WHEN RIGHT(Claim, 4) BETWEEN '7272' AND '8180' THEN 09
WHEN RIGHT(Claim, 4) BETWEEN '8181' AND '9089' THEN 10
WHEN RIGHT(Claim, 4) BETWEEN '9090' AND '9999' THEN 11
END

FROM
ClaimInfo

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 11:13:44

[code]@Dept CHAR(10),
@FromDate DATETIME,
@ToDate DATETIME

SELECT *
FROM ClaimInfo
WHERE CASE
WHEN RIGHT(ClaimSSN, 4) BETWEEN '0000' AND '0908' THEN '01'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '0909' AND '1817' THEN '02'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '1818' AND '2726' THEN '03'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '2727' AND '3635' THEN '04'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '3636' AND '4544' THEN '05'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '4545' AND '5453' THEN '06'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '5454' AND '6362' THEN '07'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '6363' AND '7271' THEN '08'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '7272' AND '8180' THEN '09'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '8181' AND '9089' THEN '10'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '9090' AND '9999' THEN '11'
END = @Dept
AND PmtDate BETWEEN @FromDate AND @ToDate
ORDER BY PmtDate[/code]The best thing is to make an auxiliary table and store all "conversion" records there and make a JOIN against that table.

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-07-16 : 11:26:13

When I try what you sent I get:

Must declare the scalar variable "@dept".

How do I make an auxiliary table?

@dept CHAR(9),
@FromDate DATETIME,
@ToDate DATETIME

SELECT *
FROM ClaimInfo
WHERE CASE
WHEN RIGHT(ClaimSSN, 4) BETWEEN '0000' AND '0908' THEN '01'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '0909' AND '1817' THEN '02'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '1818' AND '2726' THEN '03'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '2727' AND '3635' THEN '04'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '3636' AND '4544' THEN '05'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '4545' AND '5453' THEN '06'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '5454' AND '6362' THEN '07'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '6363' AND '7271' THEN '08'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '7272' AND '8180' THEN '09'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '8181' AND '9089' THEN '10'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '9090' AND '9999' THEN '11'
END = @dept
AND PmtDate BETWEEN @FromDate AND @ToDate
ORDER BY PmtDate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 15:03:39

[code]CREATE PROCEDURE dbo.uspMyFirstStoredProcedure
(
@Dept CHAR(10),
@FromDate DATETIME,
@ToDate DATETIME
)
AS

SET NOCOUNT ON

SELECT *
FROM ClaimInfo
WHERE CASE
WHEN RIGHT(ClaimSSN, 4) BETWEEN '0000' AND '0908' THEN '01'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '0909' AND '1817' THEN '02'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '1818' AND '2726' THEN '03'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '2727' AND '3635' THEN '04'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '3636' AND '4544' THEN '05'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '4545' AND '5453' THEN '06'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '5454' AND '6362' THEN '07'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '6363' AND '7271' THEN '08'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '7272' AND '8180' THEN '09'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '8181' AND '9089' THEN '10'
WHEN RIGHT(ClaimSSN, 4) BETWEEN '9090' AND '9999' THEN '11'
END = @Dept
AND PmtDate BETWEEN @FromDate AND @ToDate
ORDER BY PmtDate[/code]

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-07-17 : 08:12:59

Thanks! I'm all set now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources