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:
ID | CODE | DAT | QRTR |
---|---|---|---|
1 | A | 2013-08-20 | 20133 |
1 | A | 2013-12-20 | 20134 |
1 | A | 2014-01-02 | 20141 |
1 | A | 2014-11-18 | 20144 |
1 | A | 2015-02-08 | 20151 |
1 | A | 2015-04-20 | 20152 |
1 | A | 2015-11-16 | 20154 |
1 | A | 2016-04-12 | 20162 |
1 | A | 2017-01-20 | 20171 |
1 | A | 2017-12-12 | 20174 |
1 | A | 2018-01-03 | 20181 |
1 | A | 2019-05-20 | 20193 |
1 | A | 2020-01-01 | 20201 |
1 | A | 2021-04-16 | 20212 |
1 | A | 2021-08-20 | 20213 |
1 | A | 2021-12-30 | 20214 |
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.
2条答案
按热度按时间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 ofDATETRUNC(year,
, the latter is more performant if available.db<>fiddle
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: