SQL Case when number repeats

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

ubsmailAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
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
 
Scott PletcherSenior DBACommented:
;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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
That's assuming SQL2005 / SQL2008.  For SQL 2000 (or earlier), we'll have to do it a different way, with more overhead.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
ubsmailAuthor Commented:
Its SQL 2000.
0
 
8080_DiverCommented:
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
 
8080_DiverCommented:
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
 
ubsmailAuthor Commented:
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
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
sub "id" for whatever uniquely identifies a row within an order
0
 
8080_DiverCommented:
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
 
danrosenthalCommented:
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
 
ubsmailAuthor Commented:
There is not a cloumn that is used specified for sequence,  however orderitemid might serve as such.
Amzn-orders-Tbl.gif
0
 
8080_DiverCommented:
@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
 
ubsmailAuthor Commented:
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
 
8080_DiverCommented:
Is this a homework/class project? :-/
0
 
Scott PletcherSenior DBACommented:
Sure looks like it to me. :-/
0
 
8080_DiverCommented:
It didn't necessarily at first but that last post with the quoted bit kind of blew its cover, IMHO. ;-)
0
 
cyberkiwiCommented:
I have received and issued specs that read like that.
Not for homework though, real moneywork.
0
 
ubsmailAuthor Commented:
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
 
ubsmailAuthor Commented:
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
 
ubsmailAuthor Commented:
I got an A
0
 
8080_DiverCommented:
"I got an A"

Okay, so was it or was it not homework? ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.