DECLARE @Fruct TABLE ( [Name] VARCHAR(100))
INSERT @Fruct
VALUES ('арбуз'), ('большой арбуз'),
('мандарин' ), ('маленький мандарин'),
('банан')
SELECT Fruct.[Name], FructLike.[Name]
FROM @Fruct AS Fruct
INNER JOIN @Fruct AS FructLike ON FructLike.[Name] LIKE '%' + Fruct.[Name] + '%'
WHERE Fruct.[Name] <> FructLike.[Name]
арбуз большой арбуз
мандарин маленький мандарин
SELECT заявки.заявка,
DATEDIFF( ss, заявки.дата, подтверждение.дата) AS ВСекундах,
подтверждение.дата - заявки.дата AS КакДата
FROM таблица AS заявки
INNER JOIN таблица AS подтверждение ON заявки.заявка = подтверждение.заявка
WHERE (заявки.действие = 'Создание заявки')
AND (подтверждение.действие = 'Подтверждение заявки')
SELECT заявка,
CONVERT(VARCHAR, DATEDIFF( ss, MAX(дата), MIN(дата)) / 86400) AS Дней,
CONVERT(VARCHAR, MAX(дата) - MIN(дата), 108 ) AS Часов
FROM таблица
GROUP BY заявка
SELECT заявка, действие, дата,
дата -
( SELECT TOP 1 дата
FROM таблица AS предыдущее
WHERE (предыдущее.заявка = таблица.заявка) AND (предыдущее.дата < таблица.дата)
ORDER BY дата DESC
) AS КакДата
FROM таблица
SELECT заявка, действие, дата,
дата - LAG( дата ) OVER(PARTITION BY заявка ORDER BY дата) AS КакДата
FROM таблица
SELECT [ФИО], 1 AS Line,
[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16],
[17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
FROM
( SELECT [ФИО],
DAY([Событие]) AS [День],
FORMAT([Событие], 'HH:mm') AS [Время]
FROM [Проходная]
WHERE FORMAT([Событие], 'yyyy-MM') = @P) AS enDay
PIVOT ( MIN([Время])
FOR [День] IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
[11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
[21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) AS enPivot
UNION
SELECT [ФИО], 2,
[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16],
[17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
FROM
( SELECT [ФИО],
DAY([Событие]) AS [День],
FORMAT([Событие], 'HH:mm') AS [Время]
FROM [Проходная]
WHERE FORMAT([Событие], 'yyyy-MM') = @P) AS enDay
PIVOT ( MAX([Время])
FOR [День] IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
[11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
[21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) AS enPivot
UNION
SELECT [ФИО], 3,
[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16],
[17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
FROM
( SELECT [ФИО],
DAY([Событие]) AS [День],
FORMAT(MAX([Событие]) - MIN([Событие]), 'HH:mm') AS [Время]
FROM [Проходная]
WHERE FORMAT([Событие], 'yyyy-MM') = @P
GROUP BY [ФИО], DAY([Событие])) AS enDay
PIVOT ( MAX([Время])
FOR [День] IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
[11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
[21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) AS enPivot
ORDER BY 1, 2
SELECT DISTINCT FORMAT([Событие], 'yyyy-MM')
FROM [Проходная]
ORDER BY 1 DESC
SELECT FORMAT(MAX([Событие]), 'yyyy-MM')
FROM [Проходная]
SELECT name
FROM sys.databases
WHERE owner_sid <> 0x01
_UsersWorkHistory
получить дату последнего изменения. Скрипт в помощь.DECLARE @DBD TABLE ( [Name] VARCHAR(100), [Date] DATETIME)
DECLARE @N NVARCHAR(100), @D DATETIME
DECLARE @Q NVARCHAR(100), @P NVARCHAR(100) = '@DD DATETIME OUTPUT'
DECLARE DB CURSOR FOR
SELECT [name]
FROM sys.databases
WHERE owner_sid <> 0x01
OPEN DB
FETCH NEXT FROM DB INTO @N
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Q = 'SELECT @DD = MAX(_Date) FROM ' + @N + '.[dbo].[_UsersWorkHistory]'
BEGIN TRY
EXECUTE sp_executesql @Q, @P, @DD = @D OUTPUT
END TRY
BEGIN CATCH
SET @N = @N + ' Not 1C'
SET @D = NULL
END CATCH
INSERT @DBD VALUES (@N, @D)
FETCH NEXT FROM DB INTO @N
END
CLOSE DB
DEALLOCATE DB
SELECT * FROM @DBD
GO
nvarchar(max)
нельзя включать в индекс. Документация.nvarchar(max)
;URL NVARCHAR(MAX) NOT NULL,
URLIndex AS HASHBYTES('SHA',URL), -- можно добавить в индекс
DECLARE @DBD TABLE ( [Name] VARCHAR(100), [Recovery Model] INT, [Offset] INT)
DECLARE @N NVARCHAR(100), @R INT, @F INT
DECLARE @Q NVARCHAR(100), @P NVARCHAR(100) = '@FF INT OUTPUT'
DECLARE DB CURSOR FOR
SELECT [name], [recovery_model]
FROM sys.databases
WHERE owner_sid <> 0x01
OPEN DB
FETCH NEXT FROM DB INTO @N, @R
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Q = 'SELECT @FF = Offset FROM ' + @N + '.[dbo].[_YearOffset]'
BEGIN TRY
EXECUTE sp_executesql @Q, @P, @FF = @F OUTPUT
END TRY
BEGIN CATCH
SET @N = @N + ' Not 1C'
SET @F = NULL
END CATCH
INSERT @DBD VALUES (@N, @R, @F)
FETCH NEXT FROM DB INTO @N, @R
END
CLOSE DB
DEALLOCATE DB
SELECT * FROM @DBD
GO
DECLARE @ID BINARY(32), @Key BINARY(32), @Num INT
DECLARE Renumb CURSOR FOR
SELECT [_Document156_IDRRef], [_KeyField],
ROW_NUMBER() OVER( PARTITION BY [_Document156_IDRRef] ORDER BY [_KeyField]) AS Num
FROM [_Document156_VT2812]
OPEN Renumb
FETCH NEXT FROM Renumb INTO @ID, @Key, @Num
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE _Document156_VT2812
SET _LineNo2813 = @Num
WHERE (_Document156_IDRRef = @ID) AND (_KeyField = @Key )
FETCH NEXT FROM Renumb INTO @ID, @Key, @Num
END
CLOSE Renumb
DEALLOCATE Renumb