Member 11498882 Ответов: 2

Как найти недостающие даты одного месяца в SQL sever 2014

У меня есть таблица, которая записывает, когда сотрудник компании входит в систему. Это показывает, когда они присутствуют/пришли на работу, но когда они отсутствуют , их запись не заполняется, поэтому я хочу найти даты, когда нет даты входа в систему.

Что я уже пробовал:

Если сотрудники отсутствуют 6 июля, а я нахожу данные в период с 1 по 10 июля . Так как же я могу узнать, что он отсутствует 6 июля, в то время как 6 июля нет записи. Пожалуйста, помогите мне

Suvendu Shekhar Giri

Поделитесь структурой таблицы и запросом, который вы уже пробовали.

Member 11498882

дата
1 июля 2016 года
2 июля 2016 года
3 июля 2016 года
4 июля 2016 года
5 июля 2016 года
7 июля 2016 года
8 июля 2016 года
10 июля 2016 года


Как я могу получить "6 июля 2016 года" и "9 июля 2016 года", которых нет в базе данных.

2 Ответов

Рейтинг:
19

Maciej Los

Взгляните сюда:
Поиск пробелов или пропущенных дат в диапазоне дат с помощью TSQL-SQL, кода, кофе и т. д.[^]
SQL пробелов и островов в последовательностях-простой разговор[^]
Острова и пробелы в последовательных числах[^]
T-SQL: проблема пробелов и островов[^]

Пример:

--sample table with log data 
DECLARE @log TABLE (userid INT, logdate DATE)
INSERT INTO @log (userid, logdate)
VALUES(1, '2016-07-01'), (1, '2016-07-02'),
(1, '2016-07-04'), (1, '2016-07-05'), (1, '2016-07-07'),
(1, '2016-07-08'), (1, '2016-07-10'), (2, '2016-07-01'),
(2, '2016-07-02'), (2, '2016-07-04'), (2, '2016-07-07')

--CTE to get dates in range 2016-07-01 to 2016-07-10
;WITH AllDates AS
(
	SELECT CONVERT(DATE, '2016-07-01') AS cdate
	UNION ALL
	SELECT DATEADD(D, 1, cdate) AS cdate
	FROM AllDates 
	WHERE cdate <'2016-07-10'
)
SELECT PT.aday, [1], [2]
FROM (
	SELECT l.userid, a.cdate AS aday, l.logdate 
	FROM @log AS l RIGHT JOIN AllDates AS a ON l.logdate = a.cdate  
	) AS DT
PIVOT(COUNT(DT.logdate) FOR DT.userid IN ([1], [2])) AS PT

Результат (0 - отсутствовать):
aday		1	2
2016-07-01	1	1
2016-07-02	1	1
2016-07-03	0	0
2016-07-04	1	1
2016-07-05	1	0
2016-07-06	0	0
2016-07-07	1	1
2016-07-08	1	0
2016-07-09	0	0
2016-07-10	1	0

Karthik_Mahalingam

5

Maciej Los

Спасибо, Картик.

Рейтинг:
0

Alex Banu

Я надеюсь, что у вас есть еще один столбец в этой таблице.
Что ж, вы можете это сделать.

SELECT 
dateadd(day,1,tbl1.date) as miss_dates 
FROM 
tbl as tbl1 JOIN tbl as tbl2 left join tbl1.date=dateadd(day,1,tbl2.date)
WHERE tbl1.date<>MAX(tbl1.date) and tbl2.other_column is null


Хорошо, я запустил sql и протестировал код. Просто скопируйте код вставки ниже и посмотрите результат

CREATE TABLE #tmp (
	my_date DATETIME
)

INSERT INTO #tmp (my_date) VALUES ('2016-01-01')
INSERT INTO #tmp (my_date) VALUES ('2016-01-02')
INSERT INTO #tmp (my_date) VALUES ('2016-01-04')
INSERT INTO #tmp (my_date) VALUES ('2016-01-05')
INSERT INTO #tmp (my_date) VALUES ('2016-01-07')
INSERT INTO #tmp (my_date) VALUES ('2016-01-08')
INSERT INTO #tmp (my_date) VALUES ('2016-01-10')
INSERT INTO #tmp (my_date) VALUES ('2016-01-11')

DECLARE @max_date DATETIME
SELECT @max_date = max(my_date) FROM #tmp

SELECT 
 DATEADD(day,1,t1.my_date) as miss_date
FROM 
#tmp t1 
LEFT JOIN #tmp t2 ON t1.my_date=DATEADD(day,-1,t2.my_date)
WHERE
t2.my_date is null and t1.my_date<>@max_date

Member 11498882

У меня есть только одна таблица, и я хочу найти эти недостающие даты.

Maciej Los

Существует только одна таблица, но используются 2 псевдонима (self join).
См. мой комментарий к этому ответу и мой ответ тоже.

Maciej Los

Это может быть полезно, если количество пропущенных дней равно 1. Когда разрыв больше 1, он должен потерпеть неудачу.