Both values between Start and End date

• boehnc

  Ten Centuries

  Points: 1003

  More actions

  September 14, 2023 at 8:13 pm

  Go to Answer

  #4286619

  good afternoon,

  I'm trying to determine if both values (101 and 102) both occur within a 'Shift' (startdate/enddate). It originally just needed 101, but our facility added 102 and for the 'goal' to be compliant (1), they need to be both present within a shift. I'm having a bit of trouble working this out within my outer apply. The SQL Code below produces the correct output with just the value of 101. Below in the table is what I'd like it to output with both 101 and 102. Thank you in advance.

  

   

   

  DROP TABLE if exists #values

  DROP TABLE if exists #Risk

  create table #values

  (

  PatID varchar(10)

  ,StartDate datetime2

  ,EndDate datetime2

  )

   

   

  create table #Risk

  (

  PatID varchar(10)

  ,ValueDate Datetime2

  ,Value int

  )

  Insert into #values

  values

  ('111','2021-01-01 07:01:00.000', '2021-01-01 19:00:00.000')

  ,('111','2021-01-01 19:01:00.000', '2021-01-02 07:00:00.000')

  ,('111','2021-01-02 07:01:00.000', '2021-01-02 19:00:00.000')

  ,('111','2021-01-02 19:01:00.000', '2021-01-03 07:00:00.000')

  ,('131','2021-01-01 07:01:00.000', '2021-01-01 19:00:00.000')

  ,('131','2021-01-01 19:01:00.000', '2021-01-02 07:00:00.000')

  ,('131','2021-01-02 07:01:00.000', '2021-01-02 19:00:00.000')

  ,('131','2021-01-02 19:01:00.000', '2021-01-03 07:00:00.000')

  Insert into #Risk

  values

  ('111', '2021-01-01 08:01:00.000', 101)

  ,('111', '2021-01-01 19:05:00.000', 101)

  ,('111', '2021-01-01 23:07:00.000', 102)

  ,('131', '2021-01-01 07:01:00.000', 101)

  ,('131', '2021-01-01 07:06:00.000', 102)

  ,('131', '2021-01-02 10:01:00.000', 102)

  Select

  p.PatID

  ,p.StartDate

  ,p.EndDate

  ,case when a.PATID is not null then 1 else 0 end as 'Goal'

  from #values p

  OUTER APPLY (Select Top 1 r.PatID, r.ValueDate

  from #risk r

  where r.PatID=p.PatID and r.Value = 101

  and (r.ValueDate >= p.StartDate and r.ValueDate <= p.EndDate)

  Order by r.ValueDate) a

   

   

• ScottPletcher

  SSC Guru

  Points: 101095

  More actions

  September 14, 2023 at 8:57 pm

  Answer

  #4286643

  I wasn't 100% sure if 101 and 102 could appear more than once in the risk table, so I coded it to allow that, just in case.

  If each can appear only once, then you can get away with using "having count(*) = 2" rather than two separate case conditions.

  Select
  p.PatID
  ,p.StartDate
  ,p.EndDate
  ,case when a.PATID is not null then 1 else 0 end as 'Goal'
  from #values p
  OUTER APPLY (Select r.PatID
  from #risk r
  where r.PatID=p.PatID and r.Value in (101, 102)
  and (r.ValueDate >= p.StartDate and r.ValueDate <= p.EndDate)
  group by r.PatID
  having max(case when r.Value = 101 then 1 else 0 end) = 1
  and max(case when r.Value = 102 then 1 else 0 end) = 1
  ) a
  order by p.PatID, p.StartDate
  

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".