Solved

SQL Select Insert Help

Posted on 2014-12-17
12
81 Views
Last Modified: 2015-01-02
Hello EE,

I have this table (see Text file)
Basically, I would like is, based on the Rank column, where 1 is most prioritized,
If within a parameter I passed a UPC Number, lets say we take  803500000569 (its rank is 2)

I would like to insert in another table or I could add a column in the same table and do an update (whatever the easiest)
and write a "Final Rank" to it. That finalRank would be a kind of Row Number so that every line in that table would get a number. no duplicates.  Let me explain

If I send as the first param upc 803500000569, it would start at FinalRank 1.
then I send as param another upc lets say 803500923127. this upc is on Rank 1 and Rank 2. so it go on FinalRank 1 and the previous UPC would take the FinalRank 2

then is I take upc 803500923110 you see in text file this one is Rank 1 on two rows but if you look at the Cust Column, its the same Customer number, it means that upc belongs to the same order so it must go within the rank of that customer to try to complete the order the fastest. so that UPC would take FinalRank 2 and the previous upc on FinalRank 2 would go FinalRank 3

Then after for a last example, if I take UPC 803500000590, well that one is Rank 2, its a new customer number also so I dont have to try to match it, I could put it FinalRank 4 for now ..


So at the end, I could have multiple solutions, all them good, but it jsut need to respect the order of "Rank" and Customer orders

Could you help me to work this out with a While statement or I dont know, a temp table with some insert or update, I dont from there where to go.

Let me know if I wasnt clear enough on my explanations....

thanks !!!
test.txt
0
Comment
Question by:PhilippeRenaud
  • 6
  • 4
  • 2
12 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 40505335
Not really clear, in particular which relevance the customer part has (looks like none).
This is my understanding:
1. If we assume we get the minimum rank for a given UPC (not bothering about customer), we will always have a single entry per UPC:
select @UPC, @MinRank = min(Rank) from tbl where UPC = @UPC group by UPC;

Open in new window

2. We do the same for all rows having a FinalRank.
select UPC, MinRank = min(Rank), FinalRank=min(FinalRank) from tbl where FinalRank is not null group by UPC;

Open in new window

(This is only needed for troubleshooting and tests, we don't use this result).
3. We now need to find the insertion point - the lowest FinalRank of all UPCs with the same rank. We are using the results of 2 directly (no need to store):
select @minFinal=min(FinalRank) from (
  select UPC, MinRank = min(Rank), FinalRank=min(FinalRank) from tbl where FinalRank is not null group by UPC) data where MinRank >= @MinRank

Open in new window

4. All rows starting with that FinalRank need to be incremented by one.
update tbl set FinalRank = FinalRank + 1 where FinalRank >= @minFinal;

Open in new window

5. The current UPC rows (!) get that new FinalRank.
update tbl set FinalRank = @minFinal where upc = @UPC;

Open in new window


This does not get you unique FinalRanks - you have multiple rows for one UPC, and all will get the same FinalRank.
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 40505347
Yes I can have multiple rows for same UPC but It will be different customers

so if one customer was on rank 1 and the same upc for another customer is also Rank 1

Well It would not matter where you put it on either the two it would be the same,
but if the second customer was rank 2, well you would immediatly go with the one at Rank 1

I can try your code and I will tell you if it works or not, do I only need to take all those strings you gave me or do you want to do a full code in one Block
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 40505357
oh Wait I just in my Text file the customers are wrong. Im sorry. Let me change it and you might understand better
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 40505360
here the new text file
test2.txt
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40505412
This rows of test2
UPC          Cust   Rank
---          ----   ----
803500923127 032516 2
803500923127 046107 1
803500923127 051454 2

Open in new window

should result in a Rank of 1, and hence get the highest FinalRank up to that moment?
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40505443
You'll need to do a little more. I used (incomplete) T-SQL syntax, omitting the declaration of vars, which is required. Are you using the Management Studio's Query Window for this?
And I'll have to add the cases whenever there is no row found.

Another note, what should happen if you provide the same UPC twice?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 18

Expert Comment

by:SimonAdept
ID: 40505456
Does this get you something like what you want?

select cust,po,upc,rank,row_number() over (order by rank,cust,po,upc) as [Final_Rank] from table1

Open in new window


Results;
CUST 	PO 	UPC 	RANK 	FINAL_RANK
45758 	1014768 	803500923158 	1 	1
46107 	1014769 	803500923103 	1 	2
46107 	1014769 	803500923110 	1 	3
46107 	1014769 	803500923127 	1 	4
47090 	1014770 	803500923103 	1 	5
47090 	1014770 	803500923110 	1 	6
49710 	1014778 	803500928719 	1 	7
49710 	1014778 	803500928726 	1 	8
49710 	1014778 	803500928733 	1 	9
32516 	1013892 	803500000552 	2 	10
32516 	1013892 	803500000569 	2 	11
32516 	1013892 	803500923127 	2 	12
51454 	1013892 	803500000590 	2 	13
51454 	1013892 	803500923127 	2 	14

Open in new window


i'm not sure what order to order the rows by to rank them as you want. Could you indicate on your sample data what the desired final rank for each row is?
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40505770
Not checking for repeated runs on the same UPC, in T-SQL:
declare @UPC varchar(12)
declare @minRank integer
declare @minFinal integer

set @UPC = '803500923127'

select @MinRank = min(Rank) from tbl where UPC = @UPC;

select @minFinal=min(FinalRank) from (
  select UPC, MinRank = min(Rank), FinalRank=min(FinalRank) from tbl where FinalRank is not null group by UPC
) data where MinRank >= @MinRank;

set @minFinal = isnull(@minFinal, 1)

update tbl set FinalRank = FinalRank + 1 where FinalRank >= @minFinal;
update tbl set FinalRank = @minFinal where upc = @UPC;

Open in new window

0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40505775
Simon, this is no one-time run thing. The UPCs are coming in one after another, and their sequence is important but we know nothing about that sequence.
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40505828
@Qlemo: OK, I can't make out what the business process is from the OP's description. I'd interpreted it as every time there is a new row in the table it may require a re-ranking of all the rows?
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40505835
I read it "I have a table filled with data, and another source (e.g. table, web form, application) providing a single UPC".
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 40506848
Let me try both of your day code sample today I will reply back soon
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 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

17 Experts available now in Live!

Get 1:1 Help Now