T-SQL : how should this SQL query checking date conditions be generalized (instead of hard coded)?

Question

Given the table USERLOGINS:

rowid|userID|logindate          |
-----+------+-------------------+
    1|     1|2018-12-28 00:00:00|
    2|     2|2019-01-03 00:00:00|
    3|     1|2019-02-25 00:00:00|
    4|     3|2019-03-02 00:00:00|
    5|     2|2019-03-04 00:00:00|
    6|     1|2019-05-29 00:00:00|
    7|     1|2019-09-09 00:00:00|
    8|     2|2019-07-07 00:00:00|
    9|     3|2019-08-08 00:00:00|
   10|     1|2019-11-28 00:00:00|

To retrieve the userid for which there's at least one login in every quarter of the last year (by removing a year, considering the current date), one could hardcode it to check every condition, with something as such:

SELECT DISTINCT userID
FROM USERLOGINS 
GROUP BY userID 
HAVING SUM(CASE WHEN logindate BETWEEN DATEADD(MONTH, -12, GETDATE())  
                                   AND DATEADD(MONTH, -12 + 3, GETDATE())     
                THEN 1 ELSE 0 
           END) > 0
   AND SUM(CASE WHEN logindate BETWEEN DATEADD(MONTH, -12 + 3, GETDATE())  
                                   AND DATEADD(MONTH, -12 + 6, GETDATE())
                THEN 1 ELSE 0 
           END) > 0
   AND SUM(CASE WHEN logindate BETWEEN DATEADD(MONTH, -12 + 6, GETDATE())   
                                   AND DATEADD(MONTH, -12 + 9, GETDATE())
                THEN 1 ELSE 0 
           END) > 0
   AND SUM(CASE WHEN logindate BETWEEN DATEADD(MONTH, -12 + 9, GETDATE())  
                                   AND GETDATE()          
                THEN 1 ELSE 0 
           END) > 0;

[The desired output is the userid 1]

However this quickly becomes unmanageable (say, trying to achieve the same but checking for every month over a period of five years). What would be a practical way to generalize the checks instead of hardcoding them all?

Answer 1

I would just check if there are 4 distinct quarters in the last year for the user.

-- users with a login in 4 quarters within year
SELECT userID
FROM USERLOGINS 
WHERE logindate > CAST(DATEADD(year, -1, GetDate()) AS DATE)
GROUP BY userID
HAVING COUNT(DISTINCT DATEPART(quarter, logindate)) = 4;

A test on rextester here

Answer 2

        ;With CTE as (

        SELECT DISTINCT userID AS userID,
                DATEADD(quarter,DATEDIFF(quarter,0,logindate),0) as logindate
        FROM   USERLOGINS AS om
        GROUP BY
                DATEADD(quarter,DATEDIFF(quarter,0,logindate),0),logindate
    )
    select
        userID,
        DATEPART(year,logindate) -
           CASE WHEN DATEPART(quarter,logindate) = 1 THEN 1 ELSE 0 END as FinancialYear,
        CASE WHEN DATEPART(quarter,logindate) = 1 THEN 4
           ELSE DATEPART(quarter,logindate) - 1 END as FinancialQuarter
    from
        CTE 
   ORDER BY FinancialYear,FinancialQuarter ASC 

You will get the Data every wise with(Column Name as 'FinancialYear') and Four quarter every year has (Column Name as 'FinancialQuarter')

Here i'm using dateadd in sql server...for more info about dateadd refer this link...

https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15