Need to build a SQL query for getting the specific output

Closed Posted 6 years ago Paid on delivery
Closed

I have data stored in a table in the form of branches of a tree. All these branches were made after a specific iteration process. However, the sequence of branches made by me and the sequence of actual required branches is not coming exactly same. For clarity, the required sequence for the branches is shown in the screenshot attached.

The Branch no. 5 in top image is being assigned Branch no. 4 in my sql query because of all the Red coloured branches made after making all the black coloured branches.

Hence, is there any way, I could shift Branch no. 9 of my sql output to required Branch no. 4 as from the first screenshot, Red Branch no. 4 is the closest subset of Black Branch no. 3. And similarly Red Branch no. 6 is the closest subset of Black Branch no. 5 and then 7 is for 6.

Actually, the numbers 1-12 in excel screenshot are the required Branch numbers. And I was able to create branches just like it in the second screenshot. This is the data from my output table. Its just that branch number given to the sequence of rows made by my code and the required excel screenshot is not matching(Branch no 4 in first and second screenshot have different States). Thats why I wanted to shift only the red marked rows next to their superset. As visible from the first screenshot, Branch no. 4 has States from 95->96->97->103->104 which is best subset of Branch no. 3 which has states from 95->96->97->103->104->105. Hence I want to move my Branch no. 9 to required Branch no. 4. In short I want to identify each branch I made and move it just next to its superset. So is there any way I could update my Branch no. 9 to Branch no. 4? The values 4 and 9 could be switched and same for other branches till all of them are at their correct place.

For more clarity, I have added new script in which I have marked Red rows, those rows only which have to be adjusted with the correct Branch no.

And I need this SQL Query to be compatible with SQL Server 2008 R2.

Microsoft SQL Server SQL

Project ID: #14055570

About the project

18 proposals Remote project Active 6 years ago

18 freelancers are bidding on average $7/hour for this job

jagdishbhatt38

I am having more than 8 years of experience in IT industry.I have worked on .net(c#.net,vb.net,asp,asp.net,mvc) with sql server 2005/2008/2012/2014/2016 and oracle(pl/sql). I can provide you better solution by utili More

$6 USD / hour
(32 Reviews)
5.2
vw7590795vw

Please review my profile. https://www.freelancer.com/u/vw7590795vw.html I have 7.5 years experience in SQL and 4.8 years experience in SSRS, SSIS. Please let me know more details and when we will start. Sky p More

$4 USD / hour
(22 Reviews)
4.1
srinichal

Ready to discuss further details as I am an expert in automation and delivering custom softwares and scripts

$5 USD / hour
(3 Reviews)
3.7
rohitagarberg

MM. Hi, Please let me know more about the excel output you shown like how you are getting the To_state_ID and all . So that i understand the necessary changes required. Thanks.

$8 USD / hour
(10 Reviews)
3.1
artifexonline

Hi, I have reviewed your requirement and I can do this job as per your requirement. We have huge expertise in WORDPRESS ,Payment gateway, Laravel, Node.Js , React.Js, CakePHP, Codeigniter, Angular.js, Bootstrap, AP More

$16 USD / hour
(0 Reviews)
0.0
vishu2921

I have two years of experience working with SQL at Optum global soltions Pvt Ltd. I am very good at writing complex SQL queries

$5 USD / hour
(0 Reviews)
0.0
asharm0662

I have worked on many ETL projects in finance industry with requirements of using SQL among other data transfer and modeling tools. I would be happy to share my resume with you.

$11 USD / hour
(0 Reviews)
0.0
anavia

As a certified Database Developer I feel more tan capable of doing this job based on best practices. I am your guy

$5 USD / hour
(0 Reviews)
0.0
deepthy88

A proposal has not yet been provided

$5 USD / hour
(0 Reviews)
0.0
shirishpatelvw

Having 13+ years experience in SQL Server 2000 to 2016

$6 USD / hour
(0 Reviews)
0.0
sunilmudunuri

I have 8 years of work experience working in a reputed MNC as a Database Lead, main skill set is Sql Server 2005/2008/2012, MYSQL Oracle (PL/SQL), MSBI, Data analytics using R-Programming and SAAS. I would be able More

$5 USD / hour
(0 Reviews)
0.0
upendradba

A proposal has not yet been provided

$5 USD / hour
(0 Reviews)
0.0
Nischey

I have been working as an SQL developer from past 4 years and the task seems pretty tricky, However I would like to take this challenge and solve the problem.

$5 USD / hour
(0 Reviews)
0.0
briandexdickBK

A proposal has not yet been provided

$5 USD / hour
(0 Reviews)
0.0
makhan786

Hi, I can do your work because I have an experience in this work. I am working on SQL since 4 years. You can check my profile also. If you like it than we can chat about more details. Regards, Adnan Khan

$8 USD / hour
(0 Reviews)
0.0