Solved

SQL Case when number repeats

Posted on 2010-11-29
22
433 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: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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

822 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