How to get previous value(last matched records results) in SQL query result

Question

Post reply

How to get previous value(last matched records results) in SQL query result

manavairajan

Right there with Babe

Points: 781
More actions
October 30, 2019 at 7:07 pm
Go to Answer
#3693247
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.
```
NameTimeStampResultExpected Result
BTC02-09-16 7:00230230
ABC02-09-16 7:00988777988777
CARCO02-09-16 7:000988777
CITI02-09-16 7:00124988777
CF02-09-16 7:002988777
SFG02-09-16 7:00224444988777
GOL02-09-16 7:005457309988777
SIMCO02-09-16 7:0079988393988777
UCL02-09-16 7:001649692988777
Mul02-09-16 7:00992246988777
ABC02-09-16 7:00421421
FUL02-09-16 7:00144421
SEC02-09-16 7:004421
SILVER02-09-16 7:0022422242
ONC02-09-16 7:0014202292242
FUL02-09-16 7:00799922462242
IIF02-09-16 7:0018731822242
LOC02-09-16 7:00799867282242
UCT02-09-16 7:0002242
MOC02-09-16 7:002522242
INC02-09-16 7:001421
```
Attachments:
You must be logged in to view attached files.

Brahmanand Shukla

SSC Eights!

Points: 990

October 31, 2019 at 8:04 am

Answer

#3693410

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

Attachments:

You must be logged in to view attached files.

SQL Server Carpenter - Consultant & Trainer
Author of Learn T-SQL from Scratch

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

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?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

drew.allen SSC Guru Points: 76997 More actions · Answer 2

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

manavairajan Right there with Babe Points: 781 More actions · Answer 3

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.

SampleData1

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)

manavairajan Right there with Babe Points: 781 More actions · Answer 4

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)

manavairajan Right there with Babe Points: 781 More actions · Answer 5

Hi Allen,

I have missed to attach Expected result in previous post. So, again attached for your reference. SampleData1

manavairajan Right there with Babe Points: 781 More actions · Answer 6

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)

Phil Parkin SSC Guru Points: 246986 More actions · Answer 7

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;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

drew.allen SSC Guru Points: 76997 More actions · Answer 8

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 s

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

manavairajan Right there with Babe Points: 781 More actions · Answer 9

Thank you dear for your support.

I will validate and update the my comments.

Thanks & Regards,

Kanagarajan S.

manavairajan Right there with Babe Points: 781 More actions · Answer 10

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.