August 23, 2016 at 12:50 am
Hi
I need to get the sellprice of each buy corrosponding to sell , below is the table and required resulset.
CREATE TABLE #temptable ( [Key] varchar(32), [KeyID] int, [KeyDescription] varchar(200), [KeyAmount] float(8), [KeyVolume] float(8), [SellPrice] float(8), [ExecutionTime] datetime, [BuySell] varchar(1) )
INSERT INTO #temptable
VALUES
( '1ABC', 211, 'Inc.', -15, -1639.2, 109.28, N'2016-08-19 14:39:33.653', 'S' ),
( '1ABC', 211, 'Inc.', -10, -1087.4, 108.74, N'2016-08-19 14:04:56.000', 'S' ),
( '1ABC', 211, 'Inc.', 1, 109.24, 109.24, N'2016-08-18 17:38:40.000', 'B'),
( '1ABC', 211, 'Inc.', 9, 984.87, 109.43, N'2016-08-18 16:10:30.000', 'B' ),
( '1ABC', 211, 'Inc.', 100, 10948.99, 109.4899, N'2016-08-18 15:49:36.547', 'B' ),
( '1ABC', 211, 'Inc.', 20, 2186.8, 109.34, N'2016-08-18 14:29:30.000', 'B'),
( '1ABC', 211, 'Inc.', 40, 4373.2, 109.33, N'2016-08-18 13:44:46.643', 'B' ),
( '1ABC', 211, 'Inc.', 25, 2722.75, 108.91, N'2016-08-17 19:12:31.000', 'B' ),
( '1ABC', 211, 'Inc.', 9, 977.22, 108.58, N'2016-08-17 15:44:46.190', 'B' ),
( '1ABC', 211, 'Inc.', 30, 3256.8, 108.56, N'2016-08-17 15:37:12.430', 'S'),
( '1ABC', 211, 'Inc.', 23, 2499.18, 108.66, N'2016-08-17 15:03:52.000', 'B' ),
( '1ABC', 211, 'Inc.', 9, 977.58, 108.62, N'2016-08-17 14:57:52.000', 'B' ),
( '1ABC', 211, 'Inc.', 120, 13035.588, 108.6299, N'2016-08-17 14:25:45.770', 'B' ),
( '1ABC', 211, 'Inc.', 25, 2713, 108.52, N'2016-08-17 14:09:23.000', 'B' ),
( '1ABC', 211, 'Inc.', 100, 10851.99, 108.5199, N'2016-08-17 14:09:10.310', 'B' ),
( '1ABC', 211, 'Inc.', 70, 7625.8, 108.94, N'2016-08-17 13:36:02.000', 'B' ),
( '1ABC', 211, 'Inc.', 85, 9284.55, 109.23, N'2016-08-17 13:30:16.087', 'B' )
--DROP TABLE #temptable
SELECT *
FROM #temptable;
Resultset :
KeyKeyIDKeyDescriptionKeyAmountKeyVolumeSellPriceExecutionTimeBuySellDerivedColumn
1ABC211Inc.-15-1639.2109.2819-08-2016 14:39:34S108.58
1ABC211Inc.-10-1087.4108.7420-08-2016 14:39:34S108.58
1ABC211Inc.1109.24109.2421-08-2016 14:39:34BNA
1ABC211Inc.9984.87109.4322-08-2016 14:39:34BNA
1ABC211Inc.10010948.99109.489923-08-2016 14:39:34BNA
1ABC211Inc.202186.8109.3424-08-2016 14:39:34BNA
1ABC211Inc.404373.2109.3325-08-2016 14:39:34BNA
1ABC211Inc.252722.75108.9126-08-2016 14:39:34BNA
1ABC211Inc.9977.22108.5827-08-2016 14:39:34BNA
1ABC211Inc.303256.8108.5628-08-2016 14:39:34S109.23
1ABC211Inc.232499.18108.6629-08-2016 14:39:34BNA
1ABC211Inc.9977.58108.6230-08-2016 14:39:34BNA
1ABC211Inc.12013035.59108.629931-08-2016 14:39:34BNA
1ABC211Inc.252713 108.52 01-09-2016 14:39:34BNA
1ABC211Inc.10010851.99108.519902-09-2016 14:39:34BNA
1ABC211Inc.707625.8108.9403-09-2016 14:39:34BNA
1ABC211Inc.859284.55109.2304-09-2016 14:39:34BNA
In result set we have to populate the value of last bu for each sell.
as we have value of buy (108.5827-08-2016 14:39:34B) of 27th aug as last buy for first 2 sell. ans for sell of 28th Aug we have to populate price of last buy i'e od 4th sept.
Thanks
August 23, 2016 at 9:11 am
I see what you are after, but I have a question. Why is your buy for 108.58 associated with both sells at the top ( of 109.28 and 108.74.) There are no buys between the sell of 109.28 and 108.74.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 23, 2016 at 9:54 am
Why aren't the last buys from 2016-08-18 17:38:40.000 and 2016-08-17 15:03:52.000?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 23, 2016 at 12:29 pm
I think by "last" he means the last buy on the list before an S appears (reading top to bottom). That would actually be the first in time.
Here's part of a solution to get him started, but I still don't understand the logic that would give him a value for the sale at the top of his list (the "latest" sale).
;with cte as (SELECT *,ROW_NUMBER() over(order by executiontime desc) as RowNo
,sum(case when buysell = 'S' then 0 else 1 end) over(order by executiontime desc ROWS UNBOUNDED PRECEDING) as RunTotal
FROM #temptable
)
,cte2 as (
select *, (rowno - runtotal) as Grp, min(executiontime) over(partition by (rowno-runtotal)) as MinTime
from cte
)
select [Key], KeyID, KeyDescription, KeyAmount, KeyVolume, SellPrice, ExecutionTime, BuyPrice
from cte2 c1
outer apply (select sellprice as BuyPrice from cte2 c2 where BuySell = 'B' and c2.grp = c1.grp and c2.executiontime = c1.mintime) ca
where BuySell = 'S'
By the way, this would run best if #temptable had a unique clustered index on executiontime.
Nikki, unlike files, rows in a table have no intuitive sequence. You always need a column to ORDER BY when you start asking questions about "first" and "last" values.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 23, 2016 at 2:09 pm
There are obvious problems with the "expected results" -- such as all of the times being the same and dates in the results that don't exist in the sample data -- which makes me question whether any of the "expected results" are correct, especially since it requires an interpretation that is not internally consistent.
The "last" buy is trivial to obtain, but the expected results do not show the "last" buy under any reasonable interpretation of "last."
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 23, 2016 at 2:19 pm
Drew, the pattern of this data is what I think of as header/detail data. One very significant "S" row followed by a bunch of "B" rows. This wouldn't be uncommon in an old flat file system. Think order entry: one header row with buyer, ship to, bill to, information and several line-item rows with item#,price, and quantity. The OP expects us to read them one row at a time in sequence. It would be nice if they were numbered, but we still have a sequence in the ExecutionTime. I ran across another "sequential" pattern a couple of weeks back have submitted an article to Steve about it. If you want to see how to make sense out of this kind of pattern, just select * from cte2.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 23, 2016 at 3:53 pm
The Dixie Flatline (8/23/2016)
Drew, the pattern of this data is what I think of as header/detail data. One very significant "S" row followed by a bunch of "B" rows. This wouldn't be uncommon in an old flat file system. Think order entry: one header row with buyer, ship to, bill to, information and several line-item rows with item#,price, and quantity. The OP expects us to read them one row at a time in sequence. It would be nice if they were numbered, but we still have a sequence in the ExecutionTime. I ran across another "sequential" pattern a couple of weeks back have submitted an article to Steve about it. If you want to see how to make sense out of this kind of pattern, just select * from cte2.
I'm not disagreeing with anything you said. I'm saying that the OP has not clearly defined the rules. Until the OP does so, there's not much that can be done.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 23, 2016 at 3:56 pm
Agreed
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 23, 2016 at 10:25 pm
there are buy betwwen them, but we have to just take the first buy against each sell.
August 23, 2016 at 10:40 pm
Thanks Dixie Flatline for the answer, It is almost what I am expecting, except we have value for null should be replaced by next value of buyPrice.
Also, this data was derived from many tables that's why it do not have any sequence or index over it,
Thanks again for great help 🙂
August 24, 2016 at 6:19 am
Glad it helped, Nikki, but please take to heart the warning that SQL tables have no sequence unless you can ORDER BY on a column. If you are importing data from files, and there is some logical sequence there, preserve it. Create a rowID counter for them in the program that pulls them from the files. Here we had a date sequence to work with, but what if there were multiple rows with the same date, or an S and a B got in the wrong order? The code I posted could have given you the wrong answer. I know you're moving on to other problems now, but the absence of a natural sequence is important.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply