Solved

SQL Case when number repeats

Posted on 2010-11-29
22
429 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
  • 7
  • 7
  • 4
  • +2
22 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
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:ScottPletcher
ScottPletcher earned 100 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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:ScottPletcher
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 400 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now