(SQL) Pulling data from a large list where certain dates fall in a certain quarter

Question

Therefore, I need to pull out the email addresses of members who have not logged into my site in the last 30 days.

Since the site is already several years old, we want to pull out only a few members from certain quarters of the year.

For example, about 800,000 people who have not logged in in the last 30 days.

So, we want to pull 300,000 users from this list, but we want to break it down into three years. Three years have twelve quarters. 300,000/12 = 25,000.

So, I want to pull 25,000 from the 1st quarter of 2007

25,000 from the 2nd quarter of 2007

25,000 from the third quarter of 2007

25,000 since the fourth quarter of 2007

25,000 from the 1st quarter of 2008

etc....

"quertar", thsi ?

SELECT Email, lastlogindate
FROM Users
WHERE DATEDIFF(dd, LastLoginDate, GetDate()) > 30 

.

12 , :

DATEPART(q, LastLoginDate) = 1 AND
DATEPART(yyyy, LastLoginDate) = 2007

.

Answer 1

- :

SELECT 
    Email,LastLoginDate
    FROM (SELECT
              Email,LastLoginDate
                  ,ROW_NUMBER() OVER(PARTITION BY DATEPART(year,LastLoginDate),DATEPART(quarter,LastLoginDate) ORDER BY LastLoginDate) AS Rank
              FROM Users
              WHERE LastLoginDate < GetDate()-30
                  AND LastLoginDate > GetDate()-(3*365)
         ) dt
    WHERE Rank<25000

WHERE , LastLoginDate

, , SELECT :

,DATEPART(year,lastchgdate) as Year,DATEPART(quarter,lastchgdate) AS Quarter,rank

ORDER BY:

ORDER BY Year,Quarter,rank

Answer 2

:

• " " ( ).
• , " "

.