SQL判断是否已经过去了连续几个月

Question

我有 3 列，第 1 列表示代理 ID，第 1 列表示他们所处的级别（初级、中级、高级），第 1 列表示他们达到该级别的月末日期。

如果示例数据如下所示，

360123, Beginner, 1/22/2011
360123, Null, 2/22/2011
360123, Beginner, 3/22/2011
360123, Intermediate, 4/22/2011
360123, Beginner, 5/22/2011
360123, Beginner, 6/22/2011

我如何设计一个查询来告诉我从 3/22 到 6/22 连续 4 个月哪些代理实现了所有初学者目标？

哇，谢谢大家的帮助！

6 个月期间中的 4 个月（其中第一个月是您实现其中一个目标的任何一个月）怎么样？

Answer 1

示例数据和示例如下。基本上，按 ID 分组并搜索不同月份的计数。

DECLARE @T table (id int, lvl varchar(100), datefield smalldatetime)
INSERT INTO @T
VALUES
(360123, 'Beginner', '1/22/2011'),
(360123, Null, '2/22/2011'),
(360123, 'Beginner', '3/22/2011'),
(360123, 'Intermediate', '4/22/2011'),
(360123, 'Beginner', '5/22/2011'),
(360123, 'Beginner', '6/22/2011')

SELECT ID
FROM @T
WHERE Lvl = 'Beginner'
AND datefield BETWEEN '3/1/2011' AND '6/30/2011 23:59:59'
GROUP BY ID
HAVING COUNT(DISTINCT(MONTH(DateField))) = 4

Answer 2

(Select id from table_name where date ='6/22/2011' and level ='Beginner')
intersect
(Select id from table_name where date ='5/22/2011' and level ='Beginner')
intersect

(Select id from table_name where date ='4/22/2011' and level ='Beginner')
intersect 

(Select id from table_name where date ='3/22/2011' and level ='Beginner')

Answer 3

SELECT id
FROM TableX
WHERE level = 'Beginner'
  AND datefield >= '2011-03-22' 
  AND datefield < '2011-06-23'
GROUP BY id
HAVING COUNT(DISTINCT YEAR(datefield), MONTH(datefield)) = 4
   AND ( YEAR(MAX(datefield))*12+MONTH(MAX(datefield)) )
     - ( YEAR(MIN(datefield))*12+MONTH(MIN(datefield)) ) = 4 - 1

Answer 4

主要技巧是创建“人工”Id 以将其用作递归 CTE 中的锚点：

DECLARE @T TABLE (
身份证号 INT ,
等级 VARCHAR (100),
日期字段 SMALLDATETIME);

INSERT  INTO @T
VALUES (360123, 'Beginner', '1/22/2011'),
(360123, NULL, '2/22/2011'),
(360123, 'Beginner', '3/22/2011'),
(360123, 'Intermediate', '4/22/2011'),
(360123, 'Beginner', '12/22/2011'),
(360123, 'Beginner', '01/22/2012');

DECLARE @BeginTime AS DATETIME = '1/22/2011';

DECLARE @EndTime AS DATETIME = '6/22/2012';

WITH   M
AS     (SELECT DISTINCT Id,
                        MONTH(datefield) AS Mth,
                        YEAR(datefield) AS Yr,
                        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY YEAR(datefield), MONTH(datefield)) AS RN
        FROM   @T AS T
        WHERE  Lvl = 'Beginner'
               AND T.Datefield >= @BeginTime
               AND T.Datefield <= @EndTime),
       C (Id, RN, MonthsInARow, Mth, Yr)
AS     (SELECT M.Id,
               RN,
               CAST (1 AS INT),
               Mth,
               Yr
        FROM   M
        WHERE  RN = 1
        UNION ALL
        SELECT M.Id,
               M.RN,
               CASE 
               WHEN M.Mth = C.Mth + 1
                         OR (M.Mth = 1
                             AND C.Mth = 12
                             AND M.Yr = C.yr + 1) THEN C.MonthsInARow + 1 ELSE 1 
               END,
               M.Mth,
               M.Yr
        FROM   M
               INNER JOIN
               C
               ON M.Id = C.Id
                  AND M.RN = C.RN + 1)
SELECT [C].[Id],MAX([C].[MonthsInARow]) MAXMonth
FROM   C
GROUP BY [Id]