SQL Server Verify consecutive codes around a date

jm2pwxwz  于 2023-08-02  发布在  其他
关注(0)|答案(2)|浏览(79)

I have two tables:
Table 1 consists of ID and INDX . INDX is the date around the CODE from Table2 needs to be verified.
Table2 consists of ID , CODE , DAT , QRTR . DAT is the date on which the CODE is given with corresponding quarter of the year in QRTR .

Table1:
| ID | INDX |
| ------------ | ------------ |
| 1 | 2014-06-07 |

Table2:

IDCODEDATQRTR
1A2013-08-2020133
1A2013-12-2020134
1A2014-01-0220141
1A2014-11-1820144
1A2015-02-0820151
1A2015-04-2020152
1A2015-11-1620154
1A2016-04-1220162
1A2017-01-2020171
1A2017-12-1220174
1A2018-01-0320181
1A2019-05-2020193
1A2020-01-0120201
1A2021-04-1620212
1A2021-08-2020213
1A2021-12-3020214

Now I need to know if CODE 'A' was present within 300 days until INDX date.
If so, I need to verify if CODE 'A' is given at least one QRTR per year starting with the first data entry of CODE 'A' (within 300 days prior INDX ) including the future CODE s.

I tried the following:

SELECT
    t1.ID,
    years.Year,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM table2 t2
            WHERE t2.ID = t1.ID
                AND t2.CODE = 'A'
                AND years.Year = YEAR(DATEADD(QUARTER, t2.QRTR % 10 - 1, DATEADD(YEAR, t2.QRTR / 10 - 1900, '19000101')))
        ) THEN 'Yes'
        ELSE 'No'
    END AS IsQuarterly
FROM
    (
        SELECT DISTINCT ID
        FROM table1
    ) AS t1
CROSS JOIN
    (
        SELECT DISTINCT YEAR(DATEADD(QUARTER, (QRTR % 10) - 1, DATEADD(YEAR, (QRTR / 10) - 1900, '19000101'))) AS Year
        FROM table2
        WHERE CODE = 'A'
    ) AS years
ORDER BY
    t1.ID,
    years.Year;

Data: db<>fiddle .

For the sake of presentation clarity I just included ID 1 in the tables above.

With my query i just get if at least one QRTR a year CODE 'A' is present but i dont get information until which time CODE 'A' was present and whether there has been a new period of CODE 'A'.

I expect the following, because i think its better to derive a period on which the CODE 'A' was present at least one QRTR a year.
| ID | START | END |
| ------------ | ------------ | ------------ |
| 1 | 2013-08-20 | 2021-12-30 |
| 2 | 2017-12-12 | 2019-05-20 |
| 2 | 2021-08-20 | 2021-12-30 |

I am not sure what will be the best solution to display the result.

31moq8wy

31moq8wy1#

This is a classic Gaps-and-Islands problem.

You first need to aggregate up the data per year, and check for the total count per year.

Then use gaps-and-islands techniques to split it into groups of rows which are consecutive years, finally taking the min and max dates for each of those groups.
In older versions of SQL Server, use YEAR( instead of DATETRUNC(year, , the latter is more performant if available.

WITH Data AS (
    SELECT
      t2.ID,
      Year = DATETRUNC(year, t2.DAT),
      MinDate = MIN(t2.DAT),
      Maxdate = MAX(t2.DAT),
      PrevYear =   LAG(DATETRUNC(year, t2.DAT)) OVER (PARTITION BY t2.ID ORDER BY DATETRUNC(year, t2.DAT)),
      NextYear =  LEAD(DATETRUNC(year, t2.DAT)) OVER (PARTITION BY t2.ID ORDER BY DATETRUNC(year, t2.DAT))
    FROM Table1 t1
    JOIN Table2 t2
      ON t2.ID = t1.ID
     AND t2.DAT >= DATEADD(day, -300, t1.INDX)
    GROUP BY
      t2.ID,
      DATETRUNC(year, t2.DAT)
),
Islands AS (
    SELECT
      *,
      NextMaxDate = LEAD(d.MaxDate) OVER (PARTITION BY d.ID ORDER BY d.Year)
    FROM Data d
    WHERE d.PrevYear IS NULL
       OR d.PrevYear < DATEADD(year, -1, Year)
       OR d.NextYear IS NULL
       OR d.NextYear > DATEADD(year, 1, Year)
)
SELECT
  ID,
  Start = MinDate,
  [End] = ISNULL(NextMaxDate, MaxDate)
FROM Islands i
WHERE i.PrevYear IS NULL
   OR i.PrevYear < DATEADD(year, -1, Year);

db<>fiddle

kd3sttzy

kd3sttzy2#

I'm not sure I got the proper filters you wanted around the dates so adjust as necessary. The main portion of the logic uses universally available date functions and there's no need to use the value in qrtr at all:

with data as (
    select t1.indx, t2.id, t2.dat,
        datediff(year, min(dat) over (partition by t2.id), t2.dat) -
        dense_rank() over (partition by t2.id order by year(t2.dat)) as grp
    from table1 t1 left outer join table2 t2 on t2.id = t1.id
    where t2.code = 'A' and t2.dat >= dateadd(day, -300, t1.indx)
)
select id, min(dat) as min_dat, max(dat) as max_dat
from data
group by id, grp
order by id, min(dat);

相关问题