CODEWITHSUNDEEP
sqlt-sql
andy cicchillo
andy cicchillo

Reputation: 31

How can I find a specific status within a date range

I am trying to find a specific legalStatus for a specific date range from the following table layout.

CREATE TABLE dbo.LegalStatus (
LegalStatusID int IDENTITY,
CaseID int NOT NULL,
LegalStatusTypeID int NOT NULL,
LegalStatusDate smalldatetime NOT NULL

Example: I may have three status records.

LegalStatusID = 1,
CaseID =17,
LegalStatusTypeID = 52,
LegalStatusDate = 4/1/12

LegalStatusID = 2,
CaseID =17,
LegalStatusTypeID = 62,
LegalStatusDate = 10/1/12

LegalStatusID = 3,
CaseID =17,
LegalStatusTypeID = 72,
LegalStatusDate = 10/1/13

I am trying to report on all cases that have LegalStatusTypeID = 62 between 1/1/13 and 7/1/13. This would be easy if there was an end date. Help! Andy

Upvotes: 0

Views: 234

Answers (3)

Steve Kass
Steve Kass

Reputation: 7184

I'm understanding your question as seeking to list all CaseID values where the legal status for the case remains at 62 for the entire period that starts on '20130101' and goes up to but not including '20130701'. The legal status of a case on a given date is determined by the most recent LegalStatusTypeID value for that case before or on the given date.

If that's what you want, I think this might do it.

declare @fromD smalldatetime = '20130101';
declare @uptoD smalldatetime = '20130701';
with Cases as (
  select distinct CaseID
  from LegalStatus
)
  select CaseID
  from Cases
  where (
    select top (1) LegalStatusTypeID
    from LegalStatus as L
    where L.CaseID = Cases.CaseID
    and LegalStatusDate <= @fromD
    order by LegalStatusDate desc
  ) = 62 and not exists (
    select *
    from LegalStatus as L
    where L.CaseID = Cases.CaseID
    and LegalStatusDate > @fromD
    and LegalStatusDate <= @uptoD
    and LegalStatusTypeID <> 62
  )

This selects CaseID values for which the status on '20130101' is 62 and for which there is no subsequent status change to a value different from 62 after '20130101' but before '20130701'. If that's not what you want, perhaps this will still help. This query assumes that the legal status of a case can only change once per day, i.e., that (CaseID,LegalStatusDate) is a candidate key for your table.

I didn't test it very much, since you provided so little sample data to explain what you wanted.

Update: The original poster has clarified the question in a comment below. The following query should identify those CaseID values where the legal status for the case was 62 at any point (even just for one day) during the specified period. It selects those cases where the status was 62 on '20130101' together with those cases where the status became 62 at any point within the period.

with Cases as (
  select distinct CaseID
  from LegalStatus
)
  select CaseID
  from Cases
  where (
    select top (1) LegalStatusTypeID
    from LegalStatus as L
    where L.CaseID = Cases.CaseID
    and LegalStatusDate <= @fromD
    order by LegalStatusDate desc
  ) = 62 or exists (
    select *
    from LegalStatus as L
    where L.CaseID = Cases.CaseID
    and LegalStatusDate > @fromD
    and LegalStatusDate < @uptoD
    and LegalStatusTypeID = 62
  )

Upvotes: 0

Monty Wild
Monty Wild

Reputation: 4001

OK, if I understand your comment, for a given CaseID, you can have multiple records, each with with a LegalStatusTypeID unique to that case, each with a date, and the applicability for each LegalStatusTypeID is between that record's LegalStatusDate and the next record entered for that case's LegalStatusDate:

SELECT qrySub.CaseID, qrySub.LegalStatusDate, LegalStatus.LegalStatusDate AS 
    NextLegalStatusDate
FROM (
    SELECT LegalStatus_2.LegalStatusID, LegalStatus_2.CaseID, LegalStatus_2.
        LegalStatusTypeID, LegalStatus_2.LegalStatusDate, MIN(qryNext.
            LegalStatusID) AS NextLegalStatusID
    FROM LegalStatus AS LegalStatus_2
    LEFT JOIN (
        SELECT LegalStatusID, CaseID, LegalStatusTypeID, LegalStatusDate
        FROM LegalStatus AS LegalStatus_1
        ) AS qryNext
        ON LegalStatus_2.CaseID = qryNext.CaseID AND LegalStatus_2.LegalStatusID 
            < qryNext.LegalStatusID
    GROUP BY LegalStatus_2.LegalStatusID, LegalStatus_2.CaseID, LegalStatus_2.
        LegalStatusTypeID, LegalStatus_2.LegalStatusDate
    HAVING (LegalStatus_2.LegalStatusTypeID = 62)
    ) AS qrySub
LEFT JOIN LegalStatus
    ON qrySub.NextLegalStatusID = LegalStatus.LegalStatusID
WHERE (
        qrySub.LegalStatusDate BETWEEN CONVERT(DATETIME, '2013-01-01 00:00:00'
                    , 102) AND CONVERT(DATETIME, '2013-07-01 00:00:00', 102)
        ) OR (
        LegalStatus.LegalStatusDate BETWEEN CONVERT(DATETIME, 
                    '2013-01-01 00:00:00', 102) AND CONVERT(DATETIME, 
                    '2013-01-07 00:00:00', 102)
        ) OR (qrySub.LegalStatusDate < CONVERT(DATETIME, '2013-01-01 00:00:00', 102)
        ) AND (
        LegalStatus.LegalStatusDate > CONVERT(DATETIME, '2013-01-07 00:00:00', 
            102)
        )

You need to join the records for LegalStatusTypeID = 62 to the next record for any given case, then use the ID of that next case to get the date which is the end of applicability of the LegalStatusTypeID = 62.

Since you are talking about cases which had a LegalStatusTypeID = 62 during your date range, you need cases where the start date is in your date range, or the end date is in your date range (or both), or your date range is between the case's LegalStatusTypeID = 62 start and end dates.

Upvotes: 1

jcwrequests
jcwrequests

Reputation: 1130

  SELECT *

  FROM   LegalStatus
  WHERE  LegalStatusTypeID = 62 AND
         LegalStatusDate BETWEEN '01/01/13' and '07/01/13'

Upvotes: 0

Related Questions