|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Can CASE match more than once ?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 Hi ... I made a mistake in my logic. What I meant was
> WHEN data1 = 1 THEN 'Is One' so I expect 1 to appear twice. All else the same.> WHEN data1 > 0 then 'Greater Than Zero' Show quote "Andrew Webb" <andrew.w***@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:39:06 +0100, "Andrew Webb"
<andrew.w***@eme-med.co.uk> wrote: > Hi ... I made a mistake in my logic. What I meant was CASE returns only the first match.> > > 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
you can simply use procedure/function to use if condition. However post DDL,Sample data to help you better Regards R.D Show quote "Andrew Webb" wrote: > Hi ... 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.w***@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,
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 Show quote "Andrew Webb" <andrew.w***@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:
Show quote >Hi Hi Andrew,> >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. 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)  |
|||||||||||||||||||||||
Other interesting topics