#sql #sql-server #sql-server-2005 #sql-server-2008
#sql #sql-server #sql-server-2005 #sql-server-2008
Вопрос:
У меня есть 3 столбца, 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
Как мне разработать запрос, который сообщит мне, какие агенты выполнили все цели для начинающих в течение 4 месяцев подряд с 3/22 по 6/22?
Вау, спасибо всем за помощь!
Как насчет 4 месяцев из 6-месячного периода, где первым месяцем является любой месяц, в котором вы достигли одной из целей?
Комментарии:
1. В этом случае вы ищете 4 месяца подряд, а ваше
WHERE
предложение охватывает только 4 месяца (март, апрель, май, июнь). Всегда ли так будет?
Ответ №1:
Примерные данные и пример ниже. В основном, группируйте по идентификатору и ищите количество отдельных месяцев.
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
Комментарии:
1. Кажется, что отсутствует последовательное требование (с текущим
WHERE
предложением)2. @MartinSmith — хороший момент, я наполовину использовал его данные и наполовину использовал его опубликованную логику. Теперь только с использованием логики!
3. Придирки: это будет работать в течение 4 или 5 или даже 12 месяцев, но не в течение 13 или более.
4. @ypercube — очень правильная точка зрения. Это ограничение этого подхода.
Ответ №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')
Комментарии:
1. -1 —
Intersects
недопустимое ключевое слово, вы используете одну и ту же дату в каждом запросе (ни одна из которых не является допустимой датой), и даже при правильном синтаксисе это требует, чтобы он вводил каждую дату вручную и подзапрос на дату / месяц.2. DV удален. Тем не менее, это все еще очень неэффективный способ сделать это.
Ответ №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
Ответ №4:
Основной трюк заключается в создании «искусственного» идентификатора, чтобы использовать его в качестве привязки в рекурсивном CTE:
ОБЪЯВИТЬ ТАБЛИЦУ @T (идентификатор INT , Lvl 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]