?
Solved

SQL Case when number repeats

Posted on 2010-11-29
22
Medium Priority
?
438 Views
Last Modified: 2012-05-10
Experts,

I need to do a case select or something similar to add a field to an order. I was thinking another method maybe a union using a where clasue to select repeating order numbers. The first with the order number must have 'N' for the CONTINUED field. The 2nd row on with the same order number must have a 'Y'. How can make a case select or where clause to determine rpeating order numbers?
select

orderid ODR_NUM,
purchasedate ORDER_DATE,
case when "When the orderid repeats" then 'Y' else 'N' end as CONTINUED,

from AmznOrderFulfill 

order by orderid

Open in new window

0
Comment
Question by:ubsmail
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 4
  • +2
22 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34234971
;with cte as (
select

orderid ODR_NUM,
purchasedate ORDER_DATE,
row_number() over(partition by orderid order by purchasedate) as rownum

from AmznOrderFulfill
)
select ODR_NUM, ORDER_DATE,
case when rownum > 1 then 'Y' else 'N' end as CONTINUED
   
order by ODR_NUM
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 34234975
That's assuming SQL2005 / SQL2008.  For SQL 2000 (or earlier), we'll have to do it a different way, with more overhead.
0
 

Author Comment

by:ubsmail
ID: 34234980
Its SQL 2000.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 22

Expert Comment

by:8080_Diver
ID: 34234987
I think that you are going to need to tell us which version of SQL Server you are using before we can fully address your question.

For instance, if you are using SQL Server 2005 or later, you can use a feature (RWO_NUMBER) that would let you identify which of the rows for any given orderid is the "first" row and which are additional rows.

Having said that, if you execute the SELECT statement you provide, minus the psuedo-case statement that you inserted, do you get the data in a usable order such that, if you could identify the first row of each orderid, you could(theoretically) work with that data?  Or do you get your orderid rows in a jumbled order where, for instance, orderid 15's rows come out in 3rd, 1st, 2nd row order?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34234996
Do you have an identity column so that you can tell which of the orderid rows for a given orderid is the first one?
0
 

Author Comment

by:ubsmail
ID: 34235017
Yes the order is usable order by ord_num. It makes no difference which row of the order mumber is the begining row just that one is not continued and the rest are.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34235032
select
orderid ODR_NUM,
purchasedate ORDER_DATE,
case when x.id is null then 'Y' else 'N' end as CONTINUED
from AmznOrderFulfill
left join (
      select orderid, MIN(id) id
      from AmznOrderFulfill
      group by orderid) x on x.id=AmznOrderFulfill.id
order by orderid, id asc
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34235040
sub "id" for whatever uniquely identifies a row within an order
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34235049
If I understand you correctly, you can execute the SQL statement (minus the CASE) and get a "usable" result.  However, you have not addressed my follow-up quetion regarding whether there is some sort of sequencing column within the orderID set.
0
 
LVL 15

Expert Comment

by:danrosenthal
ID: 34235059
I can think of several ways to do this. Here is one with the assumption that you have a simple primary key on this table....

SELECT
  o.primaryKeyID
  , o.orderid ODR_NUM
  , o.purchasedate ORDER_DATE
  , CASE WHEN (SELECT count(1) FROM AmznOrderFulfill WHERE orderID = o.orderID AND primaryKeyID < o.primaryKeyID) > 0 THEN 'Y' ELSE 'N' END as CONTINUED
from AmznOrderFulfill o

If you don't have a simple primary key then you could use "ROW_NUMBER() OVER (PARTITION BY .." syntax.
0
 

Author Comment

by:ubsmail
ID: 34235109
There is not a cloumn that is used specified for sequence,  however orderitemid might serve as such.
Amzn-orders-Tbl.gif
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34235143
@danrosenthal,

The author is on SS2000 . . . ROW_NUMBER is off the table. ;-)

@ubsmail,

In that case, substitute orderitemid in place of the id in cyberwiki's query.
0
 

Author Comment

by:ubsmail
ID: 34235355
Thanks Guys it seems to work great.

After reading further it looks like the repeating rows will not contain the same data as the first rows. (See Below). Would the best way to handle this be with a union then use the where clause to select just the countuned = 'Y' If so I do not know how to use that case in a where clause.

"Indicate with an 'X' or 'Y' if this record is a continuation of the prior record. This is used when more than 5 products are required for a given order, or in cases where multiple shipping methods or shipping addresses are used. When this is marked,, it is not necessary to repeat any customer information on the next record. For cases with multiple shipping methods or multiple shipping addresses,, each new continued entry needs to have its own shipping address and or shipping method. Only the additional product entries are recorded. Leave blank or type 'N' to indicate that this record is not a continuation."
select
AmznOrderFulfill.orderid ODR_NUM,
purchasedate ORDER_DATE,
case when x.id is null then 'Y' else 'N' end as CONTINUED





from AmznOrderFulfill 
left join (
      select orderid, MIN(orderitemid) id
      from AmznOrderFulfill
      group by orderid) x on x.id=AmznOrderFulfill.orderitemid

Where CONTINUED 'Y'


order by AmznOrderFulfill.orderid, id desc



from AmznOrderFulfill 
left join (
      select orderid, MIN(orderitemid) id
      from AmznOrderFulfill
      group by orderid) x on x.id=AmznOrderFulfill.orderitemid


order by AmznOrderFulfill.orderid, id desc

Open in new window

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34239183
Is this a homework/class project? :-/
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34241739
Sure looks like it to me. :-/
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34241777
It didn't necessarily at first but that last post with the quoted bit kind of blew its cover, IMHO. ;-)
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34241870
I have received and issued specs that read like that.
Not for homework though, real moneywork.
0
 

Author Comment

by:ubsmail
ID: 34242203
That is actual software documentation. The documentation is so bad it just looks like homework. Any answers on turing the case select into a where clause? Homework is due:)
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 1600 total points
ID: 34242614
So using http:#a34235032 spliced with orderitemid , in other words

select
orderid ODR_NUM,
purchasedate ORDER_DATE,
case when x.orderitemid is null then 'Y' else 'N' end as CONTINUED
from AmznOrderFulfill
left join (
      select orderid, MIN(orderitemid) orderitemid
      from AmznOrderFulfill
      group by orderid) x on x.id=AmznOrderFulfill.id
order by orderid, id asc

still does not work?
0
 

Author Comment

by:ubsmail
ID: 34288295
Thanks everyone this is what I ended up with
Select


CONVERT(CHAR(40),buyername) as LASTNAME,
CONVERT(CHAR(40),left(buyername,CharIndex(' ',buyername))) as FIRSTNAME,
CONVERT(CHAR(40),shipaddress1) as ADDRESS1,
CONVERT(CHAR(40),shipaddress2) as ADDRESS2,
CONVERT(CHAR(20),shipcity) as CITY,
CONVERT(CHAR(3),CASE when LEN(RTRIM(shipstate))>2 then c_statecode.statecode ELSE shipstate end) as STATE,
PHONE = '(' + SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(buyerphonenumber, '(', ''), ')', ''), ' ', ''), '-', ''), '.', ''), '+', ''), 1, 3) + ')' + SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(buyerphonenumber, '(', ''), ')', ''), ' ', ''), '-', ''), '.', ''), '+', ''), 4, 3) + '-' + SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(buyerphonenumber, '(', ''), ')', ''), ' ', ''), '-', ''), '.', ''), '+', ''), 7, 4),
CASE When LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(buyerphonenumber, '(', ''), ')', ''), ' ', ''), '-', ''), '.', ''), '+', ''))<>10 Then 'Phone:' + buyerphonenumber else '' end as COMMENT,
CONVERT(CHAR(2),'AM') as CARDTYPE,
CONVERT(CHAR(19),'4444 3333 2222 1111') as CARDNUM,
CONVERT(CHAR(5),'12/12') as EXPIRES,
CONVERT(CHAR(9),'AMAZON') as SOURCE_KEY,
CONVERT(CHAR(3),'5D') as SHIPVIA,
CONVERT(CHAR(1),'N') as CONTINUED,
convert(char(9),purchasedate, 1) as ORDER_DATE,
CAST(RIGHT(AmznOrderFulfill.orderid,7) as NUMERIC(8,0)) as ODR_NUM,
CONVERT(CHAR(20),sku) as PRODUCT01,
CAST(quantitypurchased as NUMERIC(8,2)) as QUANTITY01,
CONVERT(CHAR(1),'Y') as USEPRICES,
CAST(itemprice as NUMERIC(12,4)) as PRICE01,
CONVERT(CHAR(1),'Y') as USESHIPAMT,
CAST(AmazonSum.shipping as NUMERIC(8,2)) as SHIPPING,
CONVERT(CHAR(3),'123') as RCODE,
CONVERT(CHAR(32),AmznOrderFulfill.orderid) as INTERNETID




From AmznOrderFulfill
left join c_statecode on shipstate=c_statecode.state
left join AmazonSum on AmazonSum.orderid=AmznOrderFulfill.orderid
left join (
      select AmznOrderFulfill.orderid, MIN(orderitemid) id
      from AmznOrderFulfill
      group by AmznOrderFulfill.orderid) x on x.id=AmznOrderFulfill.orderitemid


where x.id is not null


UNION

Select

CONVERT(CHAR(40),'') as LASTNAME,
CONVERT(CHAR(40),'') as FIRSTNAME,
CONVERT(CHAR(40),'') as ADDRESS1,
CONVERT(CHAR(40),'') as ADDRESS2,
CONVERT(CHAR(20),'') as CITY,
CONVERT(CHAR(3),'') as STATE,
'' as PHONE,
'' as COMMENT,
CONVERT(CHAR(2),'') as CARDTYPE,
CONVERT(CHAR(19),'') as CARDNUM,
CONVERT(CHAR(5),'') as EXPIRES,
CONVERT(CHAR(9),'') as SOURCE_KEY,
CONVERT(CHAR(3),'') as SHIPVIA,
CONVERT(CHAR(1),'Y') as CONTINUED,
convert(char(9),'') as ORDER_DATE,
CAST(null as NUMERIC(8,0)) as ODR_NUM,
CONVERT(CHAR(20),sku) as PRODUCT01,
CAST(quantitypurchased as NUMERIC(8,2)) as QUANTITY01,
CONVERT(CHAR(1),'Y') as USEPRICES,
CAST(itemprice as NUMERIC(12,4)) as PRICE01,
CONVERT(CHAR(1),'N') as USESHIPAMT,
CAST(null as NUMERIC(8,2)) as SHIPPING,
CONVERT(CHAR(3),'') as RCODE,
CONVERT(CHAR(32),AmznOrderFulfill.orderid) as INTERNETID



From AmznOrderFulfill
left join (
      select AmznOrderFulfill.orderid, MIN(orderitemid) id
      from AmznOrderFulfill
      group by AmznOrderFulfill.orderid) x on x.id=AmznOrderFulfill.orderitemid


where x.id is null

union


Select

CONVERT(CHAR(40),'') as LASTNAME,
CONVERT(CHAR(40),'') as FIRSTNAME,
CONVERT(CHAR(40),'') as ADDRESS1,
CONVERT(CHAR(40),'') as ADDRESS2,
CONVERT(CHAR(20),'') as CITY,
CONVERT(CHAR(3),'') as STATE,
'' as PHONE,
'' as COMMENT,
CONVERT(CHAR(2),'') as CARDTYPE,
CONVERT(CHAR(19),'') as CARDNUM,
CONVERT(CHAR(5),'') as EXPIRES,
CONVERT(CHAR(9),'') as SOURCE_KEY,
CONVERT(CHAR(3),'') as SHIPVIA,
CONVERT(CHAR(1),'Y') as CONTINUED,
convert(char(9),'') as ORDER_DATE,
CAST(null as NUMERIC(8,0)) as ODR_NUM,
CONVERT(CHAR(20),'9-AMAZON') as PRODUCT01,
CAST('1' as NUMERIC(8,2)) as QUANTITY01,
CONVERT(CHAR(1),'Y') as USEPRICES,
CAST(AmazonSum.AmazonCom as NUMERIC(12,4)) as PRICE01,
CONVERT(CHAR(1),'N') as USESHIPAMT,
CAST(null as NUMERIC(8,2)) as SHIPPING,
CONVERT(CHAR(3),'') as RCODE,
CONVERT(CHAR(32),AmznOrderFulfill.orderid) as INTERNETID



From AmznOrderFulfill
left join AmazonSum on AmazonSum.orderid=AmznOrderFulfill.orderid




order by CONVERT(CHAR(32),AmznOrderFulfill.orderid),CONVERT(CHAR(1),'N')

Open in new window

0
 

Author Closing Comment

by:ubsmail
ID: 34288300
I got an A
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34292274
"I got an A"

Okay, so was it or was it not homework? ;-)
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question