How to get records sorted in SQL Server programming to get my desired recordset order?

Hi...I have this situation where I would like to sort the records in a desired order:

I have the following order of recordset:

InvestNo    InvTitle            Units
1001            John R              0.5000
1002            Samantha       0.7500
1003            Bruce               1.5000
1004            Edward            2.0000
1005            Kyle                  0.5000

Now, these Investments sometime gets split and transferred to different Investors...to different Investors or Investments.
For Example, John's shares partly gets transferred to Rachel , so we have

1006           Rachael         0.2500

Similar way...Edward's shares transferred to Joe , so we have

1007          Joe                1.0000

Now...I want to add these new records and sort these records like the following:

InvestNo    InvTitle            Units
1001            John R              0.2500
1006           Rachael            0.2500
1002            Samantha       0.7500
1003            Bruce               1.5000
1004            Edward            1.0000
1007            Joe                   1.0000
1005            Kyle                  0.5000

How do I achieve this in Sql server programming or stored procedure or any other suggestions will be greatly appreciated.

Thanks in advance!
Jay RAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Is there another field like type (I have called it here) to put John and Rachael near each other?

InvestNo    InvTitle            Units                type (could be hidden)
1001            John R              0.2500               A
1006           Rachael            0.2500               A1
1002            Samantha       0.7500               B
1003            Bruce               1.5000               C
1004            Edward            1.0000               D
1007            Joe                   1.0000                D1
1005            Kyle                  0.5000               E
Jim HornMicrosoft SQL Server Data DudeCommented:
>John's shares partly gets transferred to Rachel
Ok, I'll bite.   Show us where in the data that .25 units moved from 1001-John R to 1006-Rachel.
That has to be in data somewhere, maybe a table with OldInvestNo-NewInvestNo-Units-Date.
Mark ElySenior Coldfusion DeveloperCommented:
lol Jim... It must be a Hedge Fund.  

Jim is correct, all equity transfers have to be accountable. You might have over simplified your example. When the equity gets split what links each user as a beneficiary or qualified agent of the transfer.  Is there a another table that tracks the protfolio and all its members?  

For example:

Protfolio A is owned by John R who has two kids Rachel and Joe.  Therefore you have a table that tracks the portofio and its members (John, Rachel, and Joe).  When assets are transferred from one member to another you  reference the portfolio to identify what members belong to it.  THIS is where you would assign your first level of grouping (On the porfolio).  Now you can sort by units within that porfolio to get the order you want.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jay RAuthor Commented:
Yes...correct! There is a table called Transfer History Table that records this transfer information.

John(InvestNo 1001)  --->   Rachael(InvestNo 1006)   .2500 units transferred
Edward(InvestNo1004 ) --->     Joe(InvestNo 1007 )  1.000 units transferred

Now...also there could be multiple transfers at a time as well .
For Example now Rachael could have transferred to Jenifer 0.125 units.
and Joe could also have transferred to Henry 0.5000 units.
So, in this scenario...I have to show my recordsets as below:


InvestNo    InvTitle            Units                OldInvestNo
1001            John R              0.2500               -
1006           Rachael            0.1250               1001
1008           Jenifer               0.1250               1006
1002            Samantha       0.7500               -
1003            Bruce               1.5000               -
1004            Edward            1.0000               -
1007            Joe                   0.5000                1004
1009           Henry              0.5000                1007
1005            Kyle                  0.5000               -

So...how do i achieve this sort order?

Thank you very much for your help in advance!
Mike EghtebasDatabase and Application DeveloperCommented:
try

create table #t(InvestNo int,InvTitle varchar(20), Units float, OldInvestNo varchar(5))
select * from #t
drop table #t
insert #t(InvestNo,InvTitle, Units, OldInvestNo) values
(1001, 'John R', 0.2500, null)
, (1006, 'Rachael', 0.2500, 1001)
, (1002, 'Samantha', 0.7500, null)
, (1003, 'Bruce', 1.5000, null)
, (1004, 'Edward', 1.0000 , null)
, (1007, 'Joe', 1.0000, 1004)
, (1005, 'Kyle', 0.5000, null)

Select InvestNo,InvTitle, Units, coalesce(OldInvestNo, InvestNo) AS oLD From #t

;with D AS (
Select Row_Number() over(Partition By coalesce(OldInvestNo, InvestNo) Order By InvestNo) As RowNumber,
InvestNo,InvTitle, Units 
From #t)
Select RowNumber, InvestNo,InvTitle, convert(decimal(5,4),units) as TheUnit
From D

Open in new window


produced:
1	1001	John R	0.2500
2	1006	Rachael	0.2500
1	1002	Samantha	0.7500
1	1003	Bruce	1.5000
1	1004	Edward	1.0000
2	1007	Joe	1.0000
1	1005	Kyle	0.5000

Open in new window


This seems to work.  BTW, RowNumber is optional.

Question: Is it possible later Rachael share share to further break down? If so, there needs to be some adjustments to the tsql.       

Mike
Jay RAuthor Commented:
Hi Mike...thanks for this suggestion.

Answer to your question:
Yes...there is a possibility of it to further breakdown...but that new break in share should go underneath original Rachael share. Like the following recordset:

InvestNo    InvTitle            Units                OldInvestNo
1001            John R              0.2500               -
1006           Rachael            0.1250               1001
1008           Jenifer               0.1250               1006
1002            Samantha       0.7500               -
1003            Bruce               1.5000               -
1004            Edward            1.0000               -
1007            Joe                   0.5000                1004
1009           Henry              0.5000                1007
1005            Kyle                  0.5000               -
Mike EghtebasDatabase and Application DeveloperCommented:
The following delivers what you are asking for:
Create Proc spEquitySplit 
@InvestNoStart int = null
, @InvestNoEnd int = null
As

Begin
	
	Declare @NumberOfInvestors int;
	Declare @InvestNo int;
	Declare @InvTitle varchar(20); 
	Declare @Units float; 
	Declare @OldInvestNo varchar(5);
	
	create table #Report(InvestNo int,InvTitle varchar(20), Units float, OldInvestNo varchar(5));
	 
	if (@InvestNoStart IS NOT NULL AND @InvestNoEnd IS NOT NULL)
		Select @NumberOfInvestors =  count(*) From #t 
	    Where InvestNo between @InvestNoStart and @InvestNoEnd
	    AND OldInvestNo is null
    else if (@InvestNoStart IS NOT NULL AND @InvestNoEnd IS NULL)
	    Select @NumberOfInvestors =  count(*) From #t 
	    Where InvestNo >= @InvestNoStart AND OldInvestNo is null
    else
	    Select @NumberOfInvestors =  count(*) From #t 
	    Where OldInvestNo is null

	Select 'Nember of Original Investors: ' + Convert(varchar(10) ,@NumberOfInvestors)

    if (@InvestNoStart IS NULL)
	     Set @InvestNoStart = 0;

    if (@InvestNoEnd IS NULL)
	     Set @InvestNoEnd = 1000000;

    Declare InvestData cursor For
       Select InvestNo From #t 
	   Where InvestNo between @InvestNoStart and @InvestNoEnd
	   AND OldInvestNo is null
	   Order By InvestNo;

   OPEN InvestData 
       Fetch Next From InvestData Into @InvestNo--, @InvTitle, @Units, @OldInvestNo
           WHILE @@FETCH_STATUS = 0
           BEGIN
 
			   
				;WITH EmpsCTE AS             
				(
				SELECT  InvestNo, OldInvestNo, InvTitle, Units
				FROM #t WHERE InvestNo = @InvestNo 
				--Order By InvestNo 
				UNION ALL
				SELECT M.InvestNo, M.OldInvestNo, M.InvTitle, M.Units
				FROM EmpsCTE AS S 
				JOIN #t AS M
				ON S.InvestNo = M.OldInvestNo
				) 
				Insert #Report(InvestNo,InvTitle, Units, OldInvestNo)
				SELECT InvestNo, InvTitle, Units, OldInvestNo
				FROM EmpsCTE

           FETCH NEXT FROM InvestData INTO @InvestNo--, @InvTitle, @Units, @OldInvestNo
           END
    CLOSE InvestData
    DEALLOCATE InvestData

	Select InvestNo,InvTitle, Units From #Report

End
-- ------------------------------
-- to test above, make the temp table below:
create table #t(InvestNo int,InvTitle varchar(20), Units float, OldInvestNo varchar(5))

insert #t(InvestNo,InvTitle, Units, OldInvestNo) values
(1001, 'John R', 0.2500, null)
, (1006, 'Rachael', 0.1250, 1001)
, (1002, 'Samantha', 0.7500, null)
, (1003, 'Bruce', 1.5000, null)
, (1004, 'Edward', 1.0000 , null)
, (1007, 'Joe', 0.5000, 1004)
, (1005, 'Kyle', 0.5000, null)
, (1008, 'Jenifer', 0.1250, 1006)
, (1009, 'Henry', 0.5000, 1007)
) 

SELECT InvestNo, InvTitle, Units
FROM EmpsCTE

Open in new window

If you call:   exec spEquitySplit             -- it will return all there is in the table
1001	John R	0.25
1006	Rachael	0.125
1008	Jenifer	0.125
1002	Samantha	0.75
1003	Bruce	1.5
1004	Edward	1
1007	Joe	0.5
1009	Henry	0.5
1005	Kyle	0.5

Open in new window

If you call:   exec spEquitySplit 1004   -- it will start from 1004 until it ends
If you call:   exec spEquitySplit 1001, 1003    -- it will start from 1001 to 1003
If you call:   exec spEquitySplit 1004, 1004    -- it will process a single InvestNo 1004
1004	Edward	1
1007	Joe	0.5
1009	Henry	0.5

Open in new window

We have one limitation. The proc arguments has to be the original InvestNo only. Meaning we cannot run exec spEquitySplit 1007 because Joe (see above) is not the original investor.

Mike

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jay RAuthor Commented:
Will let you know shortly...thanks very much Mike!

Trying to resolve different problem...will try your solution & get back to you!
Jay RAuthor Commented:
Mike...I tried your solution. I have a quick question.

My original temp table #t has 1774 records. And after i used this logic to sort these records , my recordset got down to 1322 records. shouldn't it be the same number of records? Am i missing something?

Also...i added the following few test records...i see some duplicates as shown below:

insert #t(InvestNo,InvTitle, Units, OldInvestNo) values
(1001, 'John R', 0.2500, null)
, (1006, 'Rachael', 0.1250, 1001)
, (1002, 'Samantha', 0.7500, null)
, (1003, 'Bruce', 1.5000, null)
, (1004, 'Edward', 1.0000 , null)
, (1007, 'Joe', 0.5000, 1004)
, (1005, 'Kyle', 0.5000, null)
, (1008, 'Jenifer', 0.1250, 1006)
, (1009, 'Henry', 0.5000, 1007)
, (1010, 'kathy', 0.0000, null)
, (1010, 'dan', 0.1000, null)
, (1011, 'jay', 0.5000, 1010)


My recordset shows as below:

1001      John R      0.25
1006      Rachael      0.125
1008      Jenifer      0.125
1002      Samantha      0.75
1003      Bruce      1.5
1004      Edward      1
1007      Joe      0.5
1009      Henry      0.5
1005      Kyle      0.5
1010      kathy      0
1010      dan      0.1
1011      jay      0.5
1011      jay      0.5
1010      kathy      0
1010      dan      0.1
1011      jay      0.5
1011      jay      0.5
Mike EghtebasDatabase and Application DeveloperCommented:
On the duplicate records :
The assumption was InvestNo is unique. After I changed to , (1015, 'dan', 0.1000, null), it didn't produce extra records. Make sure this field gets unique constraints.

, (1010, 'kathy', 0.0000, null)
, (1010, 'dan', 0.1000, null)

On the loss of records:
In the stored procedure submitted, we have the following logic:

    if (@InvestNoStart IS NULL)      -- if no starting InvestNo provided, it sets it to zero.
         Set @InvestNoStart = 0;       -- this will work so long as InvestNos are always positive numbers.

    if (@InvestNoEnd IS NULL)            -- if no ending InvestNo provided, it sets it to 1,000,000
        Set @InvestNoEnd = 1000000; -- most likely you have investNos larger than that. So cahnge it:


Set @InvestNoEnd = 1000000000

Mike
Jay RAuthor Commented:
thanks Mike...for quick reply.

On the loss of records...InvestNos are not larger than 1000000.
also, I see all the 1774 Investnos rows present in temp table #t but not coming thru in #report
Mike EghtebasDatabase and Application DeveloperCommented:
Because it is working with the sample data, I don't see why it fails to do so in your case.

You can troubleshoot by providing proc start and end parameters like:
      keep it at zero----v                   v-- knowing 1020 is 60th InvesNo, for example
exec spEquitySplit 0, 1020

This way you spot check incrementally to see where (possibly abnormal data) is causing the problem.

Also, I modified the code a bit to show top-investor number (ranking) like:

      1      1001      John R      0.25
      1      1008      Jenifer      0.125
      1      1006      Rachael      0.125    --this helps to see Jenifer and Rachael are with John
      2      1002      Samantha      0.75
      3      1003      Bruce      1.5
      4      1004      Edward      1
      4      1009      Henry      0.5
      4      1007      Joe      0.5
      5      1005      Kyle      0.5
      6      1010      kathy      0
      6      1011      jay      0.5
      7      1015      dan      0.1

What is the data type for your fields other than Unit which you have provided originally.

Mike

Alter Proc spEquitySplit 
@InvestNoStart int = null
, @InvestNoEnd int = null
As

Begin
	
	Declare @NumberOfInvestors int;
	Declare @InvestNo int;
	Declare @InvTitle varchar(20); 
	Declare @Units float; 
	Declare @OldInvestNo varchar(5);
	
	create table #Report(InvestNo int,InvTitle varchar(20), Units float, OldInvestNo varchar(5));
	 
	if (@InvestNoStart IS NOT NULL AND @InvestNoEnd IS NOT NULL)
		Select @NumberOfInvestors =  count(*) From #t 
	    Where InvestNo between @InvestNoStart and @InvestNoEnd
	    AND OldInvestNo is null
    else if (@InvestNoStart IS NOT NULL AND @InvestNoEnd IS NULL)
	    Select @NumberOfInvestors =  count(*) From #t 
	    Where InvestNo >= @InvestNoStart AND OldInvestNo is null
    else
	    Select @NumberOfInvestors =  count(*) From #t 
	    Where OldInvestNo is null

	Select 'Nember of Original Investors: ' + Convert(varchar(10) ,@NumberOfInvestors)

    if (@InvestNoStart IS NULL)
	     Set @InvestNoStart = 0;

    if (@InvestNoEnd IS NULL)
	     Set @InvestNoEnd = 1000000;

    Declare InvestData cursor For
       Select InvestNo From #t 
	   Where InvestNo between @InvestNoStart and @InvestNoEnd
	   AND OldInvestNo is null
	   Order By InvestNo;

   OPEN InvestData 
       Fetch Next From InvestData Into @InvestNo--, @InvTitle, @Units, @OldInvestNo
           WHILE @@FETCH_STATUS = 0
           BEGIN
 
			   
				;WITH EmpsCTE AS             
				(
				SELECT  InvestNo, OldInvestNo, InvTitle, Units
				FROM #t WHERE InvestNo = @InvestNo 
				--Order By InvestNo 
				UNION ALL
				SELECT M.InvestNo, M.OldInvestNo, M.InvTitle, M.Units
				FROM EmpsCTE AS S 
				JOIN #t AS M
				ON S.InvestNo = M.OldInvestNo
				) 
				Insert #Report(InvestNo,InvTitle, Units, OldInvestNo)
				SELECT InvestNo, InvTitle, Units, @InvestNo
				FROM EmpsCTE

           FETCH NEXT FROM InvestData INTO @InvestNo--, @InvTitle, @Units, @OldInvestNo
           END
    CLOSE InvestData
    DEALLOCATE InvestData

	Select --ROW_NUMBER() OVER (ORDER BY InvestNo) AS 'No',
	DENSE_RANK() OVER (ORDER BY OldInvestNo) AS 'TopInvestorNo',
	InvestNo,InvTitle, Units  
	From #Report
	Order By --ROW_NUMBER() OVER (ORDER BY InvestNo),
	DENSE_RANK() OVER (ORDER BY OldInvestNo)
End

Open in new window

Jay RAuthor Commented:
InvestNo is int
Invtitle is Varchar(35)
Units decimal(20,6)
Jay RAuthor Commented:
The temp table #t was created just above the code that defines cursor...and i checked if 1774 records are present in #t.
Is there any limitations for cursor that affects during fetching the data to temp table #report??
Mike EghtebasDatabase and Application DeveloperCommented:
Please see the revisions to my last code on ways to troubleshoot the discrepancy on the number of records and ranking feature I just added to the proc.

It seems there is not much I could do on the number of the records because I do not have access to your data. If after some effort you were not able to locate the source of the problem, consider closing this thread to start a new question.

This time, you can post the solution we have thus far and describe the problem with the number of records. What you most likely get is:

1. The existing could be further improved (by other experts and most of them are more experienced compared to me).

2. Some fresh eyes looking at the problem may bring about a satisfactory resolution.


Mike
Mike EghtebasDatabase and Application DeveloperCommented:
re:> Is there any limitations for cursor that affects during fetching the data to temp table #report??

I do not know. I need to find out. If you decided to post a new question, make sure bring this up and possibly send me an email (use the envelope icon top-right).

I am preparing for Exam 70-461 this is why I am at my PC 24/7.

Mike
Mike EghtebasDatabase and Application DeveloperCommented:
My last input:
	Select DENSE_RANK() OVER (ORDER BY OldInvestNo) AS 'TopInvestorNo',
	InvestNo,InvTitle, Units  
	From #Report
	Order By DENSE_RANK() OVER (ORDER BY OldInvestNo), InvestNo

Open in new window



Paul Maxwell, Jim Horn, ScottPletcher are some of our top experts.
Jay RAuthor Commented:
Thanks very much Mike!...Good luck with your exam:)

i will try to troubleshoot why am missing some of the records & will keep you posted!...or open a different question about that issue!
Mike EghtebasDatabase and Application DeveloperCommented:
A: run          exec spEquitySplit 0, 1020                
B: Run         Select count(*) From Table1 Where InvestNo <=1020

Compare number of records in A and B  to see if number of record match.

Then increase to to-parameter and compare  it again. When there is mismatch you possibly can locate the cause by this isolation.

exec spEquitySplit 0, 1200               Select count(*) From Table1 Where InvestNo <=1200

etc.

Also run Select Distinct  InvestNo From Table1

to see if you are getting 1774 or less. If less, then you have duplicate InvestNo problem. You need to make that field unique.

Mike
Jay RAuthor Commented:
I see that there is the original  Investno...with the original units size  is still there in the table .
So...am not going to have unique InvestNo.

i.e...

insert #t(InvestNo,InvTitle, Units, OldInvestNo) values

(1001, 'John R', 0.5000, null)    <---I still have this record in my temp #t
,(1001, 'John R', 0.2500, null)

, (1006, 'Rachael', 0.1250, 1001)
, (1002, 'Samantha', 0.7500, null)
, (1003, 'Bruce', 1.5000, null)
, (1004, 'Edward', 1.0000 , null)
, (1007, 'Joe', 0.5000, 1004)
, (1005, 'Kyle', 0.5000, null)
, (1008, 'Jenifer', 0.1250, 1006)
, (1009, 'Henry', 0.5000, 1007)
, (1010, 'kathy', 0.0000, null)

, (1010, 'dan', 0.1500, null)     <---I still have this record in #t
, (1010, 'dan', 0.1000, null)
, (1011, 'jay', 0.5000, 1010)

...They still would like to see these original units in the resultset without getting rid of it.
Any ideas??
Mike EghtebasDatabase and Application DeveloperCommented:
if you have:

(1001, 'John R', 0.5000, null)    <---I still have this record in my temp #t
,(1001, 'John R', 0.2500, null)
.
.

, (1006, 'Rachael', 0.1250, 1001)     <-- Which 1001 is this from?

Can you add an PK field like:

(1, 1001, 'John R', 0.5000, null)    <---I still have this record in my temp #t
,(2, 1001, 'John R', 0.2500, null)
.
.

, (1006, 'Rachael', 0.1250, 1001, 1)     <-- and indicate 1001 with ID = 1, for example?

Other than that, I would say there is no other solution for your current setup.

Mike
Mike EghtebasDatabase and Application DeveloperCommented:
If you have:

(1001, 'John R', 0.5000, null)    <---I still have this record in my temp #t
,(1001, 'John R', 0.2500, null)
.
.

re:> still have this record

Is there another field in the database to help us to exclude 1001, 'John R', 0.5000, null  from the process?
Jay RAuthor Commented:
create table #test1(InvestNo int,InvTitle varchar(20), Units float, OldInvestNo int, unittype varchar(20))
insert #test1(InvestNo,InvTitle, Units, OldInvestNo, unittype) values

(1001, 'John R', 0.5000, null,'IntitialInvestment')
,(1001, 'John R', 0.2500, null,'Transferout')

, (1006, 'Rachael', 0.2500, 1001,'TransferIn')
, (1006, 'Rachael', 0.1250, 1001,'Transferout')

, (1008, 'Jenifer', 0.1250, 1006,'TransferIn')

, (1002, 'Samantha', 0.7500, null,'IntitialInvestment')
, (1003, 'Bruce', 1.5000, null,'IntitialInvestment')

, (1004, 'Edward', 1.0000 , null, 'IntitialInvestment')
, (1004, 'Edward', 0.5000 , null, 'TransferOut')
, (1007, 'Joe', 0.5000, 1004,'TransferIn')
, (1007, 'Joe', 0.25000, 1004,'Transferout')
, (1009, 'Henry', 0.25000, 1007,'TransferIn')

, (1005, 'Kyle', 0.5000, null, 'IntitialInvestment')


, (1010, 'kathy', 0.0000, null, 'IntitialInvestment')

, (1010, 'dan', 0.5000, null,'InitialInvestment')  
, (1010, 'dan', 0.2500, null,'Transferout')
, (1011, 'jay', 0.2500, 1010,'TransferIn')

select * from #test1
Jay RAuthor Commented:
Will get back to you shortly...just trying to resolve another problem.
Thanks very much!
Mike EghtebasDatabase and Application DeveloperCommented:
Starting with:
4/1/15:
(1001, 'John R', 0.5000, null,'IntitialInvestment')      

4/2/15
(1001, 'John R', 0.5000, null,'IntitialInvestment')      
,(1001, 'John R', 0.2500, null,'Transferout')
,(1006, 'Rachael', 0.2500, 1001,'TransferIn')

4/3/15
(1001, 'John R', 0.5000, null,'IntitialInvestment')      
,(1001, 'John R', 0.0500, null,'Transferout')
(1111, 'Hans', 0.2000, 1001,'TransferIn')
,(1006, 'Rachael', 0.1250, 1001,'TransferIn')
,(1112, 'Jen', 0.1250, 1001,'TransferIn')

It could get very complicated as you can see above.

I am strongly suggesting use of PK field.

Without PK ID the solution will be much harder (which is not to necessary).

Post a new question to get a better attention. State you have a solution based on unique InvestNO but it is not. Then make a presentation like above with all sort of possibilities so the experts can design an appropriate solution for you.

Starting new question is the best way forward otherwise you are forcing the experts to read 20+ posts here which they will not get much out of it. This is why the other experts may not want to part-take in this thread.

Mike
Jay RAuthor Commented:
ok..thanks Mike! ...will do
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.