Can CASE match more than once ?

Hi
I'm tring to write a statement to analyse what orders were open on the first
day of each month from a system and return one data set with the months
listed and all the orders open during that month. eg.
Mon Order_No
Jan 001
Jan 002
Jan 003
Feb 002
Feb 003
Feb 004
The orders all have an open and closed date, so I want to check for each
month if the first of the month falls between the open and closed date of
each order.
I've set up a dummy database for testing - what I'd like to know is whether
CASE statements be made to match more than once :
SELECT MyNewField =
CASE
WHEN data1 = 1 THEN 'Is One'
WHEN data1 > 1 then 'Not One'
end,
data2
FROM APW_Test
my table is as follows
data1
1
2
3
4
So I would hope to see one result for the number 1 (Is One) and two results
for the remaining numbers because they match both case statements. However,
CASE seems to match the first statement and then stop for each record.
Is there a way I can achieve the result I want fairly simply ?
Thanks in advance.
AndrewHi ... I made a mistake in my logic. What I meant was

> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 0 then 'Greater Than Zero'
so I expect 1 to appear twice. All else the same.
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm tring to write a statement to analyse what orders were open on the
> first day of each month from a system and return one data set with the
> months listed and all the orders open during that month. eg.
> Mon Order_No
> Jan 001
> Jan 002
> Jan 003
> Feb 002
> Feb 003
> Feb 004
> The orders all have an open and closed date, so I want to check for each
> month if the first of the month falls between the open and closed date of
> each order.
> I've set up a dummy database for testing - what I'd like to know is
> whether CASE statements be made to match more than once :
> SELECT MyNewField =
> CASE
> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 1 then 'Not One'
> end,
> data2
> FROM APW_Test
> my table is as follows
> data1
> 1
> 2
> 3
> 4
> So I would hope to see one result for the number 1 (Is One) and two
> results for the remaining numbers because they match both case statements.
> However, CASE seems to match the first statement and then stop for each
> record.
> Is there a way I can achieve the result I want fairly simply ?
> Thanks in advance.
> Andrew
>|||Andrew
you can simply use procedure/function to use if condition.
However post DDL,Sample data to help you better
Regards
R.D
"Andrew Webb" wrote:

> Hi ... I made a mistake in my logic. What I meant was
>
> so I expect 1 to appear twice. All else the same.
>
> "Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
> news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
>
>|||On Fri, 16 Sep 2005 08:39:06 +0100, "Andrew Webb"
<andrew.webb@.eme-med.co.uk> wrote:

> Hi ... I made a mistake in my logic. What I meant was
>
> so I expect 1 to appear twice. All else the same.
CASE returns only the first match.|||Andrew,
It sounds to me like you need a JOIN operation, not a CASE expression.
Post the CREATE TABLE and INSERT statements for some specific
data if you want a more careful answer, but this might be close:
select data1, DisplayAnswer
from T join (
select 'equal 1' as TestCondition, 'Is One' as DisplayAnswer
union all
select 'above 1', 'Not One'
) C
on (
TestCondition = 'equal 1' and data1 = 1
) or (
TestCondition = 'above 1' and data1 > 1
)
If you select only from your 4-row table, with no other table
joined in, you cannot obtain a result that contains any row
more than once.
Steve Kass
Drew University
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm tring to write a statement to analyse what orders were open on the
> first day of each month from a system and return one data set with the
> months listed and all the orders open during that month. eg.
> Mon Order_No
> Jan 001
> Jan 002
> Jan 003
> Feb 002
> Feb 003
> Feb 004
> The orders all have an open and closed date, so I want to check for each
> month if the first of the month falls between the open and closed date of
> each order.
> I've set up a dummy database for testing - what I'd like to know is
> whether CASE statements be made to match more than once :
> SELECT MyNewField =
> CASE
> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 1 then 'Not One'
> end,
> data2
> FROM APW_Test
> my table is as follows
> data1
> 1
> 2
> 3
> 4
> So I would hope to see one result for the number 1 (Is One) and two
> results for the remaining numbers because they match both case statements.
> However, CASE seems to match the first statement and then stop for each
> record.
> Is there a way I can achieve the result I want fairly simply ?
> Thanks in advance.
> Andrew
>|||On Fri, 16 Sep 2005 08:30:00 +0100, Andrew Webb wrote:

>Hi
>I'm tring to write a statement to analyse what orders were open on the firs
t
>day of each month from a system and return one data set with the months
>listed and all the orders open during that month. eg.
>Mon Order_No
>Jan 001
>Jan 002
>Jan 003
>Feb 002
>Feb 003
>Feb 004
>The orders all have an open and closed date, so I want to check for each
>month if the first of the month falls between the open and closed date of
>each order.
Hi Andrew,
You could use a calendar table or a table of integers to get this. I'll
give an example with a table of integers that's made up "on the fly".
You can expand it as needed, or make a real table of integers (as
explained on http://www.aspfaq.com/show.asp?id=2516).
DECLARE @.StartDate smalldatetime
,@.EndDate smalldatetime
SET @.StartDate = '20050101' -- Should be first of the month
SET @.EndDate = '20051201'
SELECT DATEADD(month, Numbers.n, @.StartDate) AS Mon,
Orders.Order_No
FROM Orders
INNER JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 9 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL
SELECT 14 UNION ALL SELECT 15) AS Numbers(n)
WHERE Orders.OpenDate < DATEADD(month, Numbers.n, @.StartDate)
AND Orders.CloseDate > DATEADD(month, Numbers.n, @.StartDate)
AND DATEADD(month, Numbers.n, @.StartDate) <= @.EndDate
ORDER BY Numbers.n, Orders.Order_No
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)