quote:

---

Originally posted by JJ297

So sorry here's the table. Don't know how to line them all up but it's four columns:

SPSID Claim StatusCode Module
1 Abc3897 07 05
1 Abc5887 07 07
1 Abc0897 07 01
1 Abc7797 07 09
1 Abc5497 07 C12
1 Abc2297 07 I1

Here's the stored procedure:

@Mods CHAR(9),
@FromDate DATETIME,
@ToDate DATETIME
)
AS

SET NOCOUNT ON

SELECT *
FROM SPSListings
WHERE CASE
When module = 'C12' then 'ICTU'
When module = 'I1' then 'I&E'
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 = @Mods
AND PmtDate BETWEEN @FromDate AND @ToDate
ORDER BY PmtDate

---

quote:

---

Originally posted by JJ297

The module's have been changed to different names. In the database I have listed C12 which is ICTU module and I1 which is called I&E.

All the other modules are determined by the last four digits of the claim number.

On the page I have a drop down box of modules
01 - 11, ICTU and I&E a top textbox and from textbox (to enter dates)

If you select module 01 and enter a from and to date you get that data.

If you select ICTU (module) and enter from and to date you get that info pertaining to ICTU data

I am using the Claim field to get the modules from 01 - 11

I am using the module field to get the data pertaining to ICTU and I&E

Is my case statement set up correctly?

---