Count of versions between specific dates

user_3982586 0
Viewed 46

I have a data like below

+------+---------+--------------+
| ID   | Version | CreationDate |
+------+---------+--------------+
| 8103 | 1       | 20-Nov-2020  |
+------+---------+--------------+
| 8103 | 2       | 30-Mar-2021  |
+------+---------+--------------+
| 8103 | C       | 04-Jun-2021  |
+------+---------+--------------+
| 7546 | A       | 05-May-2020  |
+------+---------+--------------+
| 7546 | 1       | 08-Apr-2021  |
+------+---------+--------------+
| 7546 | 2       | 02-Sep-2021  |
+------+---------+--------------+
| 4233 | 1       | 01-Jun-2019  |
+------+---------+--------------+
| 4233 | 2       | 01-Feb-2020  |
+------+---------+--------------+
| 6626 | 1       | 14-Jul-2020  |
+------+---------+--------------+

Required Output.

+------+--------+
| ID   | Count  |
+------+--------+
| 8103 | 2      |
+------+--------+
| 7546 | 1      |
+------+--------+
| 4233 | IGNORE |
+------+--------+
| 6626 | 0      |
+------+--------+

Logic:

  1. IDs first version should be created after 2020. If it is created before 2020, then ignore. E.g 4233 in my sample data
  2. Count the number of version after the initial version within one year after it gets created. If we see the ID 7546, the version-2 is created after one year of version A. But I need to find how many versions were created within a year of its first version.

My Try:

SELECT ID,SUM(CASE WHEN (year(creation_date)-MIN(year(creation_date)))=0 THEN 1 END) as CNT
FROM MYTABLE
WHERE year(creation_date)>2019
GROUP BY ID

I'm really stuck getting how to get that count. Please help

sql-server-2017 sql-server sql
1 Answers

You can use a combination of window functions and conditional aggregation for this

SELECT
  ID,
  Cnt = CASE WHEN MIN(MinDate) < '20200101'
          THEN 'Ignore'
          ELSE CAST(
             COUNT(CASE WHEN DATEDIFF(day, MinDate, CreationDate) <= 365 THEN 1 END)
           AS varchar(11))
        END
FROM (
    SELECT *
      rn = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CreationDate),
      MinDate = FIRST_VALUE(CreationDate) OVER (PARTITION BY ID ORDER BY CreationDate
              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    FROM MYTABLE
)
WHERE rn > 1
GROUP BY ID;
user_148689970
Related