Как создать запрос в существующей таблице и построить таблицу (представление) с данными bool с использованием логического состояния?

У меня есть база данных MS-SQL, которую я использую для хранения данных / информации, поступающих с оборудования, установленного на некоторых транспортных средствах (1-3 оборудования на автомобиль).

На данный момент в базе данных есть ссылка « Communication – большая таблица, используемая для хранения каждой информации из оборудования при подключении к TCP-серверу. Записи добавляются один за другим (только INSERTS здесь).

Таблица выглядит так:

введите описание изображения здесь

Мне нужен SQL запрос (команда / оператор), чтобы создать таблицу (представление) для так называемого «Еженедельного состояния связи», где я могу узнать, если / как транспортные средства сообщили в течение последних 7 дней … что-то как приведенная ниже таблица:

введите описание изображения здесь

В качестве альтернативы вышесказанному вы знаете, что в выводе будет только 7 столбцов даты, вы можете сделать ручную разворот, а не использовать оператор PIVOT.

См. SQLFiddle

 select VehicleNumber, CASE WHEN SUM(CASE WHEN DATEDIFF(d,DateTimeCommunication, DATEADD(d,-7,GETDATE())) = 0 THEN 1 ELSE 0 END) > 0 THEN 'YES' Else 'NO' END AS [7 Days Ago] ,CASE WHEN SUM(CASE WHEN DATEDIFF(d,DateTimeCommunication, DATEADD(d,-6,GETDATE())) = 0 THEN 1 ELSE 0 END) > 0 THEN 'YES' Else 'NO' END AS [6 Days Ago] ,CASE WHEN SUM(CASE WHEN DATEDIFF(d,DateTimeCommunication, DATEADD(d,-5,GETDATE())) = 0 THEN 1 ELSE 0 END) > 0 THEN 'YES' Else 'NO' END AS [5 Days Ago] ,CASE WHEN SUM(CASE WHEN DATEDIFF(d,DateTimeCommunication, DATEADD(d,-4,GETDATE())) = 0 THEN 1 ELSE 0 END) > 0 THEN 'YES' Else 'NO' END AS [4 Days Ago] ,CASE WHEN SUM(CASE WHEN DATEDIFF(d,DateTimeCommunication, DATEADD(d,-3,GETDATE())) = 0 THEN 1 ELSE 0 END) > 0 THEN 'YES' Else 'NO' END AS [3 Days Ago] ,CASE WHEN SUM(CASE WHEN DATEDIFF(d,DateTimeCommunication, DATEADD(d,-2,GETDATE())) = 0 THEN 1 ELSE 0 END) > 0 THEN 'YES' Else 'NO' END AS [2 Days Ago] ,CASE WHEN SUM(CASE WHEN DATEDIFF(d,DateTimeCommunication, DATEADD(d,-1,GETDATE())) = 0 THEN 1 ELSE 0 END) > 0 THEN 'YES' Else 'NO' END AS [1 Days Ago] ,CASE WHEN SUM(CASE WHEN DATEDIFF(d,DateTimeCommunication, GETDATE()) = 0 THEN 1 ELSE 0 END) > 0 THEN 'YES' Else 'NO' END AS [0 Days Ago] from Communications GROUP BY VehicleNumber 

Вот динамичный подход PIVOT .

 CREATE TABLE #temp(equipmentID int, vehicleNumber int, DateTimeCommunication datetime) INSERT INTO #temp(equipmentID, vehicleNumber, DateTimeCommunication) VALUES (1,100,GETDATE()), (2,110,GETDATE()), (3,120,GETDATE()), (5,140,GETDATE()), (1,100,DATEADD(day,-8,GETDATE())), (3,120,DATEADD(day,-8,GETDATE())), (4,130,DATEADD(day,-8,GETDATE())), (5,140,DATEADD(day,-8,GETDATE())) DECLARE @sql nvarchar(max), @columns nvarchar(max), @columnsSelect nvarchar(max) SELECT @columns = COALESCE(@columns + N',['+CONVERT(nvarchar(max),dateCom)+N']',N'['+CONVERT(nvarchar(max),dateCom)+N']') FROM ( SELECT DISTINCT CONVERT(date, DateTimeCommunication) as dateCom FROM #temp ) as allDates ORDER BY dateCom SELECT @columnsSelect = COALESCE(@columnsSelect + N',ISNULL(['+CONVERT(nvarchar(max),dateCom)+N'],N''NO'') as '''+CONVERT(nvarchar(max),dateCom)+'''', N'ISNULL(['+CONVERT(nvarchar(max),dateCom)+N'],N''NO'') as '''+CONVERT(nvarchar(max),dateCom)+'''') FROM ( SELECT DISTINCT CONVERT(date, DateTimeCommunication) as dateCom FROM #temp ) as allDates ORDER BY dateCom SET @sql = N'SELECT pvt.vehicleNumber, '[email protected]+' FROM ( SELECT t.equipmentID, t.vehicleNumber, CONVERT(date,DateTimeCommunication) as dateCom, CASE WHEN t.DateTimeCommunication BETWEEN DATEADD(day,-7,GETDATE()) AND GETDATE() THEN N''YES'' ELSE N''NO'' END as communicated FROM #temp t ) as dat PIVOT( MAX(communicated) FOR dateCom IN('[email protected]+') ) as pvt' PRINT(@sql) EXEC (@sql) DROP TABLE #temp 

Если вам не нужна эта динамика или только для заданного диапазона дат, просто упростите ее еще раз:

 CREATE TABLE #temp(equipmentID int, vehicleNumber int, DateTimeCommunication datetime) INSERT INTO #temp(equipmentID, vehicleNumber, DateTimeCommunication) VALUES (1,100,GETDATE()), (2,110,GETDATE()), (3,120,GETDATE()), (5,140,GETDATE()), (1,100,DATEADD(day,-8,GETDATE())), (3,120,DATEADD(day,-8,GETDATE())), (4,130,DATEADD(day,-8,GETDATE())), (5,140,DATEADD(day,-8,GETDATE())) SELECT * FROM ( SELECT t.equipmentID, t.vehicleNumber, CONVERT(date,DateTimeCommunication) as dateCom, CASE WHEN t.DateTimeCommunication BETWEEN DATEADD(day,-7,GETDATE()) AND GETDATE() THEN N'YES' ELSE N'NO' END as communicated FROM #temp t ) as dat PIVOT( MAX(communicated) FOR dateCom IN([2015-06-23]) --needs to be changed on different date! ) as pvt DROP TABLE #temp 
  • SQL-сервер: преобразовывать строки в столбцы
  • SQL Server Pivot с использованием нескольких наборов столбцов
  • Запрос с строками в столбцы
  • Сопоставление и объединение значений из столбца в SQL Server
  • Как использовать предложение WHERE с PIVOT
  • Как вставить Результат запроса в PIVOT
  • Как написать SQL Pivot Query для этого сценария?
  • Вызов SQL Server pivot
  • Образец с динамическими столбцами
  • Как преобразовать значения строк в столбцы с подсчетом динамических столбцов?
  • Преобразование строк в столбцы после внутреннего соединения в SQL Server
  • Interesting Posts

    Как подключиться к удаленной базе данных с помощью ASP.NET MVC?

    SqlDataAdapter.Fill () в SqlTransaction – это плохая практика?

    Поиск "не соответствует" против запроса "NOT IN"

    Как изменить столбец в столбце IDENTITY и сохранить данные?

    SQL Server – шифрование данных в столбце таблицы базы данных

    Преобразование пользовательских единиц SQL

    Порядок выполнения SQL-запроса

    Не стоит ли использовать метод SqlParameterCollection.AddWithValue?

    Почему мои индексы Azure SQL Database все еще фрагментированы?

    Экспорт данных из SQL Server в Excel

    Как сделать 2 sql запросов 1

    Пользовательское поколение PrimaryKey с автоинкрементацией

    Использует встроенный провайдер аутентификации asp.net плохую идею из-за того, что guid используется как первичный ключ и т. Д.

    SQL-запрос для подсчета всех (неизвестный, известный), неизвестный, известный клиент, однозначно идентифицируется по MAC-адресу телефона

    Как восстановить резервную копию, как отключить все активные соединения?

    Давайте будем гением компьютера.