Retrieving Results with Previous Values to the Results

1

Title sounds pretty weird, but best I could describe.

I want to retrieve the 2 previous entries that matches the same Analysis which met the report criteria.

Data table, very Simplified.
Report Criteria With Analysis = 'pH'
Expected Results.

DataSet
DataSet609×602 102 KB

I am absolutely in the dark as a newbie. Can this be done?

2

Yes, you can use LAG or LEAD to get the results. But there's no other key? You just take the next three results in chronological order?

3

It's easier if you have sample data, but try this:

create table #c (EnteredDate datetime, analysis varchar(20), Result numeric(5,2))
insert into #c values
('1/2/2022 08:00', 'pH', 5.54),
('1/2/2022 08:00', 'Conductivity', 452.00),
('1/2/2022 08:00', 'Chloride', 2.22),
('2/8/2022 09:00', 'pH', 8.12),
('2/8/2022 09:00', 'Sodium', 6.64),
('5/18/2022 07:30', 'pH', 3.14),
('8/15/2022 12:13', 'pH', 6.77),
('8/15/2022 12:13', 'Phosphate', 3.50),
('8/15/2022 12:13', 'Conductivity', 125.00),
('10/4/2022 12:13', 'pH', 8.11),
('10/15/2022 12:13', 'pH', 6.01),
('11/1/2022 12:13', 'pH', 18.30)

declare @StartDate SmallDateTime = '5/1/2022',
		@Enddate SmallDateTime = '10/1/2022'

;with cte as (
Select EnteredDate, Analysis, Result
  from #c
where EnteredDate between @StartDate and @EndDate),
cteRowNum as 
	(Select EnteredDate, Analysis, Result
  from #c)

select EnteredDate, Analysis, Result, Previous, PriorPrevious
  from (
		select r.EnteredDate, r.Analysis, r.Result , c.Result as DateResults,
				LAG(r.Result) OVER (ORDER BY r.EnteredDate) as Previous,
				LAG(r.Result,2) OVER (ORDER BY r.EnteredDate) as PriorPrevious
		 from cteRowNum r
			left join cte c
				on r.analysis = c.analysis
				and r.enteredDate = c.enteredDate
		where r.Analysis = 'pH'
		) c
where c.DateResults is not null
4

This may be a bit simpler - here is sample data:

Declare @testData Table (EnteredDate datetime, Analysis varchar(20), Result numeric(5,2));
 Insert Into @testData (EnteredDate, Analysis, Result)
 Values ('1/2/2022 08:00', 'pH', 5.54)
      , ('1/2/2022 08:00', 'Conductivity', 452.00)
      , ('1/2/2022 08:00', 'Chloride', 2.22)
      , ('2/8/2022 09:00', 'pH', 8.12)
      , ('2/8/2022 09:00', 'Sodium', 6.64)
      , ('5/18/2022 07:30', 'pH', 3.14)
      , ('8/15/2022 12:13', 'pH', 6.77)
      , ('8/15/2022 12:13', 'Phosphate', 3.50)
      , ('8/15/2022 12:13', 'Conductivity', 125.00)
      , ('10/4/2022 12:13', 'pH', 8.11)
      , ('10/15/2022 12:13', 'pH', 6.01)
      , ('11/1/2022 12:13', 'pH', 18.30);

Here is a solution:

Declare @analysis  varchar(20) = 'Ph'
      , @startDate datetime    = '20220501'
      , @endDate   datetime    = '20221001';

   With matchedAnalysis
     As (
 Select *
      , PreviousResult = lag(td.Result) Over(Order By td.EnteredDate)
      , PriorResult = lag(td.Result, 2) Over(Order By td.EnteredDate)
   From @testData               td
  Where td.Analysis = @analysis
        )
 Select *
   From matchedAnalysis         ma
  Where ma.EnteredDate >= @startDate
    And ma.EnteredDate <  dateadd(day, 1, @endDate);  -- end date is inclusive