I have a table PolicyStatusLog as shown below:

If the last IdStatusChangedTo of a Policy is 1, then the Policy is still active.

Let's say I want to get all active policies(i.e IdPolicys) for the month of January. This will include polices where the status was last changed to active(i.e 1) before or during January.

I hope I explained the problem clearly, but I can always give more details as required.

How do I write a query for this in SQL? Thanks.

CodePudding user response：

Assuming you have a policy table...

Get the latest PolicyStatusLog record (prior to Feb 1st) for each policy, and keep only policies where the latest status is. 1.

SELECT
  *
FROM
  policy    AS p
CROSS APPLY
(
  SELECT TOP 1 *
    FROM PolicyStatusLog
   WHERE IDPolicy = policy.ID
     AND DateChanged < '2022-02-01'
ORDER BY DateChanged DESC
)
  AS s
WHERE
  s.IdStatusChangedTo = 1

Assumes all policies have at least one row in the log. For example, if there's a new policy, is there an initial row in the log with status 1?