SQLTeam.com | Weblogs | Forums

Retrieving Results with Previous Values to the Results

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.

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

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?

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

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