How to get previous value(last matched records results) in SQL query result
-
Hi Every one,
I need your help to achieve results. Can you please help me any one?
My requirement is get last matched records results for unmatched records.
I mean when matching the Name column with "ABC" then display corresponding records results value(988777) in expected result. else display previous result value. llly, when matching name with ABC then display corresponding records result's value(421).
Similar,
when matching Name column with "Silver" then show corresponding row result's value 2242 and unmatched records's result should be last matched result value 421 for ONC to INC
Can you please provide a select statement to achieve this expected result?
I tried with LAG function but which was return previous result value. So, I couldn't achieve expected result.
While loop also taking more time.
Please provide with simple SQL script.
Thanks in Advance your support.
Herewith attached sample data.
Name TimeStamp Result Expected Result
BTC 02-09-16 7:00 230 230
ABC 02-09-16 7:00 988777 988777
CARCO 02-09-16 7:00 0 988777
CITI 02-09-16 7:00 124 988777
CF 02-09-16 7:00 2 988777
SFG 02-09-16 7:00 224444 988777
GOL 02-09-16 7:00 5457309 988777
SIMCO 02-09-16 7:00 79988393 988777
UCL 02-09-16 7:00 1649692 988777
Mul 02-09-16 7:00 992246 988777
ABC 02-09-16 7:00 421 421
FUL 02-09-16 7:00 144 421
SEC 02-09-16 7:00 4 421
SILVER 02-09-16 7:00 2242 2242
ONC 02-09-16 7:00 1420229 2242
FUL 02-09-16 7:00 79992246 2242
IIF 02-09-16 7:00 1873182 2242
LOC 02-09-16 7:00 79986728 2242
UCT 02-09-16 7:00 0 2242
MOC 02-09-16 7:00 252 2242
INC 02-09-16 7:00 1 421
-
Please provide your sample data in the form of CREATE TABLE/INSERT statements, if you would like to receive a working solution.
I do not understand what you mean by 'unmatched' here. Why is CARCO's expected result not zero?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
You haven't given us enough information to provide a solution for you.
- You mention "matched" and "unmatched" records, but there is no indication in your data for which records fall into each of these categories.
- You mention a "last" matched value, but there is nothing in your data that would support an ordering that would give your expected results based on any reasonable ordering.
- All of your records have exactly the same timestamp, so you can't reliably sort by the timestamp.
- Sorting by name doesn't give the expected results
Please follow Phil's advice and provide sample data and expected results. It may also help to provide the query you've already tried, because that may fill in some of the gaps.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Hi Dew,
Thank you so much for looking my query.
I meant that matched value should be ABC & SILVER. when name is ABC then show corresponding result else show previous row result.
Here attached the expected result snapshot.
Create table #SampleData (Name varchar(500),Timestamp datetime,Result int)
Insert into #SampleData values('BTC','2016-10-15 08:47:29',230)
Insert into #SampleData values('ABC','2016-09-16 6:14:54',988777)
Insert into #SampleData values('CARCO','2016-09-16 6:14:56',0)
Insert into #SampleData values('CITI','2016-09-16 6:14:56',124)
Insert into #SampleData values('CF','2016-09-16 6:14:58',2)
Insert into #SampleData values('SFG','2016-09-16 6:15:00',224444)
Insert into #SampleData values('GOL','2016-09-16 6:15:02',5457309)
Insert into #SampleData values('SIMCO','2016-09-16 6:15:04',79988393)
Insert into #SampleData values('UCL','2016-09-16 6:15:06',1649692)
Insert into #SampleData values('Mul','2016-09-16 6:15:08',992246)
Insert into #SampleData values('ABC','2016-09-16 6:15:10',421)
Insert into #SampleData values('FUL','2016-09-16 6:15:12',144)
Insert into #SampleData values('SILVER','2016-09-16 6:15:14',2242)
Insert into #SampleData values('ONC','2016-09-16 6:15:18',1420229)
Insert into #SampleData values('FUL','2016-09-16 6:15:20',79992246)
Insert into #SampleData values('IIF','2016-09-16 6:15:22',1873182)
Insert into #SampleData values('LOC','2016-09-16 6:15:22',79986728)
Insert into #SampleData values('UCT','2016-09-16 6:15:24',0)
Insert into #SampleData values('MOC','2016-09-16 6:15:26',252)
Insert into #SampleData values('INC','2016-09-16 6:15:31',1) -
Hi Allen,
Thank you so much for looking my query.
There is available timestamp is as primary and use it for order by.
Herewith attached sample query for create table and insert data. Also, expected result. I hope it may help you to understand my query.
kindly let me know if you have any concern.
Create table #SampleData (Name varchar(500),Timestamp datetime,Result int)
Insert into #SampleData values('BTC','2016-10-15 08:47:29',230)
Insert into #SampleData values('ABC','2016-09-16 6:14:54',988777)
Insert into #SampleData values('CARCO','2016-09-16 6:14:56',0)
Insert into #SampleData values('CITI','2016-09-16 6:14:56',124)
Insert into #SampleData values('CF','2016-09-16 6:14:58',2)
Insert into #SampleData values('SFG','2016-09-16 6:15:00',224444)
Insert into #SampleData values('GOL','2016-09-16 6:15:02',5457309)
Insert into #SampleData values('SIMCO','2016-09-16 6:15:04',79988393)
Insert into #SampleData values('UCL','2016-09-16 6:15:06',1649692)
Insert into #SampleData values('Mul','2016-09-16 6:15:08',992246)
Insert into #SampleData values('ABC','2016-09-16 6:15:10',421)
Insert into #SampleData values('FUL','2016-09-16 6:15:12',144)
Insert into #SampleData values('SILVER','2016-09-16 6:15:14',2242)
Insert into #SampleData values('ONC','2016-09-16 6:15:18',1420229)
Insert into #SampleData values('FUL','2016-09-16 6:15:20',79992246)
Insert into #SampleData values('IIF','2016-09-16 6:15:22',1873182)
Insert into #SampleData values('LOC','2016-09-16 6:15:22',79986728)
Insert into #SampleData values('UCT','2016-09-16 6:15:24',0)
Insert into #SampleData values('MOC','2016-09-16 6:15:26',252)
Insert into #SampleData values('INC','2016-09-16 6:15:31',1)
Create table #SampleData (Name varchar(500),Timestamp datetime,Result int)
Insert into #SampleData values('BTC','2016-10-15 08:47:29',230)
Insert into #SampleData values('ABC','2016-09-16 6:14:54',988777)
Insert into #SampleData values('CARCO','2016-09-16 6:14:56',0)
Insert into #SampleData values('CITI','2016-09-16 6:14:56',124)
Insert into #SampleData values('CF','2016-09-16 6:14:58',2)
Insert into #SampleData values('SFG','2016-09-16 6:15:00',224444)
Insert into #SampleData values('GOL','2016-09-16 6:15:02',5457309)
Insert into #SampleData values('SIMCO','2016-09-16 6:15:04',79988393)
Insert into #SampleData values('UCL','2016-09-16 6:15:06',1649692)
Insert into #SampleData values('Mul','2016-09-16 6:15:08',992246)
Insert into #SampleData values('ABC','2016-09-16 6:15:10',421)
Insert into #SampleData values('FUL','2016-09-16 6:15:12',144)
Insert into #SampleData values('SILVER','2016-09-16 6:15:14',2242)
Insert into #SampleData values('ONC','2016-09-16 6:15:18',1420229)
Insert into #SampleData values('FUL','2016-09-16 6:15:20',79992246)
Insert into #SampleData values('IIF','2016-09-16 6:15:22',1873182)
Insert into #SampleData values('LOC','2016-09-16 6:15:22',79986728)
Insert into #SampleData values('UCT','2016-09-16 6:15:24',0)
Insert into #SampleData values('MOC','2016-09-16 6:15:26',252)
Insert into #SampleData values('INC','2016-09-16 6:15:31',1) -
Hi Allen,
I have missed to attach Expected result in previous post. So, again attached for your reference.
-
Hi Parkin,
Thank you so much for looking my query.
I meant that matched value should be ABC & SILVER. when name is ABC then show corresponding result else show previous row result.
sample expression given below as excel
=IF(($A2=ABC,$D2,$D1)
=IF(($A12=SILVER,$D12,$D11)
Create table #SampleData (Name varchar(500),Timestamp datetime,Result int)
Insert into #SampleData values('BTC','2016-10-15 08:47:29',230)
Insert into #SampleData values('ABC','2016-09-16 6:14:54',988777)
Insert into #SampleData values('CARCO','2016-09-16 6:14:56',0)
Insert into #SampleData values('CITI','2016-09-16 6:14:56',124)
Insert into #SampleData values('CF','2016-09-16 6:14:58',2)
Insert into #SampleData values('SFG','2016-09-16 6:15:00',224444)
Insert into #SampleData values('GOL','2016-09-16 6:15:02',5457309)
Insert into #SampleData values('SIMCO','2016-09-16 6:15:04',79988393)
Insert into #SampleData values('UCL','2016-09-16 6:15:06',1649692)
Insert into #SampleData values('Mul','2016-09-16 6:15:08',992246)
Insert into #SampleData values('ABC','2016-09-16 6:15:10',421)
Insert into #SampleData values('FUL','2016-09-16 6:15:12',144)
Insert into #SampleData values('SILVER','2016-09-16 6:15:14',2242)
Insert into #SampleData values('ONC','2016-09-16 6:15:18',1420229)
Insert into #SampleData values('FUL','2016-09-16 6:15:20',79992246)
Insert into #SampleData values('IIF','2016-09-16 6:15:22',1873182)
Insert into #SampleData values('LOC','2016-09-16 6:15:22',79986728)
Insert into #SampleData values('UCT','2016-09-16 6:15:24',0)
Insert into #SampleData values('MOC','2016-09-16 6:15:26',252)
Insert into #SampleData values('INC','2016-09-16 6:15:31',1) -
Try below query and see if it serves your purpose. Also attaching the output of the query for your review. The output is based on the insert script you have shared in your last reply.
-- *** cte_data section is not needed in the CTE if your table has the Identity Column.
-- *** Remove this section and replace RowID with your identity column name
; WITH cte_data
AS
(
SELECT *
, ROW_NUMBER() OVER(ORDER BY timestamp ASC) AS RowID
FROM #SampleData
)
, cte_to_be_matched
AS
(
SELECT *
, From_RowID = RowID
, To_RowID = ISNULL((LEAD(RowID) OVER(ORDER BY timestamp ASC) - 1), 999999999999999999)
FROM cte_data
WHERE name IN ('ABC', 'SILVER') -- *** Supply all your values to be matched here. You can also modify the query to read the XML parameter or comma seperated parameter or may be from some table
)
SELECT A.Name
, A.Timestamp
, A.Result
, B.Result AS ExpectedResult
FROM cte_data A
LEFT JOIN cte_to_be_matched B
ON A.RowID BETWEEN B.From_RowID AND B.To_RowID
ORDER BY A.timestamp ASC -
This is close, I think:
DROP TABLE IF EXISTS #SampleData;
CREATE TABLE #SampleData
(
Name VARCHAR(500)
,Timestamp DATETIME
,Result INT
);
INSERT #SampleData
(
Name
,Timestamp
,Result
)
VALUES
('BTC', '2016-10-15 08:47:29', 230)
,('ABC', '2016-09-16 6:14:54', 988777)
,('CARCO', '2016-09-16 6:14:56', 0)
,('CITI', '2016-09-16 6:14:56', 124)
,('CF', '2016-09-16 6:14:58', 2)
,('SFG', '2016-09-16 6:15:00', 224444)
,('GOL', '2016-09-16 6:15:02', 5457309)
,('SIMCO', '2016-09-16 6:15:04', 79988393)
,('UCL', '2016-09-16 6:15:06', 1649692)
,('Mul', '2016-09-16 6:15:08', 992246)
,('ABC', '2016-09-16 6:15:10', 421)
,('FUL', '2016-09-16 6:15:12', 144)
,('SILVER', '2016-09-16 6:15:14', 2242)
,('ONC', '2016-09-16 6:15:18', 1420229)
,('FUL', '2016-09-16 6:15:20', 79992246)
,('IIF', '2016-09-16 6:15:22', 1873182)
,('LOC', '2016-09-16 6:15:22', 79986728)
,('UCT', '2016-09-16 6:15:24', 0)
,('MOC', '2016-09-16 6:15:26', 252)
,('INC', '2016-09-16 6:15:31', 1);
WITH changes
AS (SELECT sd.Name
,sd.Timestamp
,sd.Result
,ChangeInd = IIF(sd.Name IN ('ABC', 'SILVER'), 1, 0)
FROM #SampleData sd)
,grps
AS (SELECT changes.Name
,changes.Timestamp
,changes.Result
,GroupNo = SUM(changes.ChangeInd) OVER (ORDER BY changes.Timestamp)
FROM changes)
SELECT grps.Name
,grps.Timestamp
,grps.Result
,ExpectedValue = FIRST_VALUE(grps.Result) OVER (PARTITION BY grps.GroupNo ORDER BY grps.Timestamp)
FROM grps
ORDER BY grps.Timestamp;If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
This is a public forum, not private messages. You don't need to respond to every single person who responded with the exact same information.
Also, my name is Drew, not Dew or Allen.
This give the same results as Brahmanand's, but it only requires one table scan whereas his requires two.
WITH SampleOrdered AS
(
SELECT *, SUM(CASE WHEN sd.[Name] IN ('ABC', 'SILVER') THEN 1 ELSE 0 END) OVER(ORDER BY sd.Timestamp, sd.Name ROWS UNBOUNDED PRECEDING) AS grp
FROM #SampleData AS sd
)
SELECT *, FIRST_VALUE(s.Result) OVER(PARTITION BY s.grp ORDER BY s.Timestamp, s.Name)
FROM SampleOrdered sDrew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Thank you dear for your support.
I will validate and update the my comments.
Thanks & Regards,
Kanagarajan S.
-
Thank you Drew for your support.
I understand you message. Going forward I should avoid to place multiple times the same post for different users.
Also, Really sorry to mentioned your name wrongly.
Thank you so much for you & Phil Parkin for given scripts. I will validate and update my comments.
Thanks
Kanagarajan S.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply