Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
ef1
Member

Откуда: Москва
Сообщений: 203
Здравствуйте, помогите написать запрос
переношу базу Excel (СКУД учет рабочего времени) на клиент-сервер MSSQL (синтаксис для 2000-2005 версии), клиент на c#. Не могу понять как сделать один запрос (для datatable) для отчета вида принятого в компании

фрагмент исходные данных
таблица пользователей tbUsers (50 записей)
+--------+--------------+-------------+
+ userId + userFIO + userStatus +
+--------+--------------+-------------+
+ 1 + Иванов. И.И. + инженер +
+ 2 + Петров П.П. + менеджер +
+ 3 + Сидоров С.С. + лаборант +
...

таблица специальных отметок tbSpecialMarks (10 записей)
+--------+--------------+-------------+
+ spmId + spmShortName + spmFullName +
+--------+--------------+-------------+
+ 1 + Я + явка +
+ 2 + ОТ + отпуск +
...

таблица проходов tbPass (когда, кто, почему, сколько часов отработал) (55000 записей)
+-------------+---------+-------+----------+
+ pData + usersID + spmId + WorkTime +
+-------------+---------+-------+----------+
+ 01.10.2021 + 2 + 1 + 6,5 +
+ 01.10.2021 + 3 + 1 + 7,8 +
+ 02.10.2021 + 1 + 1 + 8 +
+ 02.10.2021 + 2 + 2 + 7,5 +
+ 02.10.2021 + 3 + 1 + 4 +
...

для итогового отчета (Excel) мне нужно получить набор данных по выбранным юзерам (tbUsers) (2 строки на запись) и диапазону дат из таблицы прохода (tbPass) - вида

вариант 1 (одна колонка на дату - две строки данных)
+---+--------------+------------+------------+-----
+ № + usersInfo + 01.10.2021 + 02.10.2021 + ...
+---+--------------+------------+------------+-----
+ 1 + Иванов И.И. + NULL + Я +
+ 1 + инженер + NULL + 8 +

+ 2 + Петров П.П. + Я + ОТ +
+ 2 + менеджер + 6,5 + 7,5 +

+ 3 + Сидоров С.С. + Я + Я +
+ 3 + лаборант + 7,8 + 4 +

как сделать запрос по юзерам (tbUsers) и одной дате (из tbPass) понятно - выбрать юзеров и через left join подогнать к ним данные на дату...
а вот как сделать запрос по диапазону дат и подогнать их слева к юзеру... - не могу придумать (да и ограничение по синтаксису 2000-2005 TransactSQL)

не подскажите как это можно разрулить?
итоги можно и в виде одной строки - я из C# раскидаю потом как нужно

вариант 2 (две колонки на дату - одна строка данных)
+---+--------------+-------------+-------------------------+---------------------+-------------------------+---------------------+--
+ № + userFIO + userStatus + spmShortName_01.10.2021 + WorkTime_01.10.2021 + spmShortName_01.10.2021 + WorkTime_01.10.2021 + ...
+---+--------------+-------------+-------------------------+---------------------+-------------------------+---------------------+--
+ 1 + Иванов И.И. + инженер + NULL + NULL + Я + 8 +
+ 2 + Петров П.П. + менеджер + Я + 6,5 + ОТ + 7,5 +
+ 3 + Сидоров С.С. + лаборант + Я + 7,8 + Я + 4 +

вообще не понимаю как сделать такую выборку одним запросом..., не подскажите?
20 ноя 21, 15:28    [22398328]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
aleks222
Member

Откуда:
Сообщений: 1846
ef1

как сделать запрос по юзерам (tbUsers) и одной дате (из tbPass) понятно - выбрать юзеров и через left join подогнать к ним данные на дату...
а вот как сделать запрос по диапазону дат и подогнать их слева к юзеру... - не могу придумать (да и ограничение по синтаксису 2000-2005 TransactSQL)


выбрать юзеров
и через left join подогнать к ним датЫ
и через left join подогнать к ним данные на дату...

ЗЫ. Ограничения не в синтаксисе - ограничения в голове.
20 ноя 21, 16:49    [22398343]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
ef1
Member

Откуда: Москва
Сообщений: 203
первая часть понятна
Select 
 u.usName,									--фио	
 u.usPost,									--должность
 e.pDate,									--дата прохода
 e.smName,									--имя специальных отметок
 e.ptimeScheduleFact						--количество отработанных минут
From 
  (Select 
     us.name usName,  						--фио
	 up.name usPost,						--должность
	 us.extId usId							--id фио 
     From Users us, UserPost up  
    Where us.postId = up.id 
	      and us.name like('%' + 'михаил' + '%') 
	      and us.uses = 1) as u 
  left join 
  (Select 
      ep.passDate pDate,					--дата прохода 
	  sm.letterCode smName,					--имя специальных отметок
	  ep.passId usId,						--id фио
	  ep.timeScheduleFact ptimeScheduleFact	--количество отработанных минут
     From EventsPass ep, SpecialMarks sm 
    Where sm.id=ep.specmarkId
	      and passDate between '20210102' and '20210115') as e 
  on u.usId = e.usId

результат
usName	usPost	pDate	smName	ptimeScheduleFact
Ломовцев Михаил Аркадьевич Руководитель 2021-01-11 00:00:00.000 РД 540
Ломовцев Михаил Аркадьевич Руководитель 2021-01-12 00:00:00.000 РД 540
Ломовцев Михаил Аркадьевич Руководитель 2021-01-13 00:00:00.000 РД 540
Ломовцев Михаил Аркадьевич Руководитель 2021-01-14 00:00:00.000 РД 540
Ломовцев Михаил Аркадьевич Руководитель 2021-01-15 00:00:00.000 РД 540
Потапов Михаил Аркадьевич Ведущий инженер 2021-01-11 00:00:00.000 Я 540
Потапов Михаил Аркадьевич Ведущий инженер 2021-01-12 00:00:00.000 Я 540
Потапов Михаил Аркадьевич Ведущий инженер 2021-01-13 00:00:00.000 Я 540
Потапов Михаил Аркадьевич Ведущий инженер 2021-01-14 00:00:00.000 Я 540
Потапов Михаил Аркадьевич Ведущий инженер 2021-01-15 00:00:00.000 ОТ 540

а вот как даты завернуть направо для этих двух человек (в примере)...
не понимаю...
20 ноя 21, 17:38    [22398349]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
aleks222
Member

Откуда:
Сообщений: 1846
Варианта ажно два:

1. Осознать, что это нафиг не надо. Ибо это не ексель.
2. Разучить pivot.
20 ноя 21, 17:42    [22398351]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
ef1
Member

Откуда: Москва
Сообщений: 203
я думал через обобщение with как то... но не понимаю как

но тут засада еще в том - что справа мне нужно показать весь диапазон исследуемых дат
в примере я запрашиваю 13 дат от '20210102' и до '20210115' и хочу забить забить их null если данных по проходам нет

т.е. строка вида (в общем случае)
фио+ данные фио + 13 колонок дат с результатами

...
так наверно вообще нельзя сделать?
20 ноя 21, 17:52    [22398352]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
aleks222
Member

Откуда:
Сообщений: 1846
ef1
я думал через обобщение with как то... но не понимаю как

но тут засада еще в том - что справа мне нужно показать весь диапазон исследуемых дат
в примере я запрашиваю 13 дат от '20210102' и до '20210115' и хочу забить забить их null если данных по проходам нет

т.е. строка вида (в общем случае)
фио+ данные фио + 13 колонок дат с результатами

...
так наверно вообще нельзя сделать?

Реляционные базы данных не допускают таблиц с переменным количеством колонок.
20 ноя 21, 17:58    [22398354]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
ef1
Member

Откуда: Москва
Сообщений: 203
ну хорошо - я могу запросить фиксированный диапазон дат - например 32 дня (с избытком)
для такого случая?

чувствую одним местом что можно сделать

ps
вся структура данных моя - исходники (данные проходов) Excel - аж с 2007 года (тоже моя структура - просто подтормаживать начала), клиента C# написал, базу создал все импортировал, осталось написать основной запрос для отчета - отчет естественно пойдет в Excel - руководство привыкло к шаблону
уже 2 месяца разработки - и хотелось завершить красивым и быстрым запросом для отчета...
а так костылей можно навставлять конечно - но это не эстетично ))
20 ноя 21, 18:06    [22398359]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
aleks222
Member

Откуда:
Сообщений: 1846
ef1
ну хорошо - я могу запросить фиксированный диапазон дат - например 32 дня (с избытком)
для такого случая?

чувствую одним местом что можно сделать

ps
вся структура данных моя - исходники (данные проходов) Excel - аж с 2007 года (тоже моя структура - просто подтормаживать начала), клиента C# написал, базу создал все импортировал, осталось написать основной запрос для отчета - отчет естественно пойдет в Excel - руководство привыкло к шаблону
уже 2 месяца разработки - и хотелось завершить красивым и быстрым запросом для отчета...
а так костылей можно навставлять конечно - но это не эстетично ))

Таблица с 32-я колонками.
+ 32 запроса обновления.

Ну, можно один запрос с 32-я join-ами.

Сообщение было отредактировано: 20 ноя 21, 19:06
20 ноя 21, 19:04    [22398391]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
ef1
Member

Откуда: Москва
Сообщений: 203
в принципе через временную таблицу можно замутить
вот здесь на этом сайте

т.е. написать udf
закинуть туда диапазон дат
создать с ними временную таблицу
пересечь ее с реальными данными
в колонку(ячейку) с датой вставлять два значения с разделителями или в формате xml для красоты
и удалить ее в конце функции
...
разбираюсь пока как красиво это оформить

Сообщение было отредактировано: 20 ноя 21, 19:18
20 ноя 21, 19:13    [22398399]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
ef1
Member

Откуда: Москва
Сообщений: 203
запутался короче
1. сделал функцию генерации дат календаря по запросу - getCalendar
+
USE [TimeWorkTracking]
GO
/****** Object:  UserDefinedFunction [dbo].[getCalendar]    Script Date: 21.11.2021 19:18:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Табличная функция для генерации последовательности дат (способ 2 – WITH)
-- https://info-comp.ru/generating-dates-t-sql
   ALTER FUNCTION [dbo].[getCalendar] (
	@DateStart DATEtime, -- Дата начала
	@DateEnd DATEtime	 -- Дата окончания
   )
   RETURNS @ListDates TABLE (dt DATEtime) 
   AS
   BEGIN

	--Рекурсивное обобщенное табличное выражение.
	WITH Dates AS
	(
		SELECT @DateStart AS DateStart -- Задаем якорь рекурсии
	
		UNION ALL

		SELECT DATEADD(DAY, 1, DateStart) AS DateStart -- Увеличиваем значение даты на 1 день
		FROM Dates
		WHERE DateStart < @DateEnd -- Прекращаем выполнение, когда дойдем до даты окончания
	)
	INSERT INTO @ListDates
		SELECT DateStart 
		FROM Dates
		OPTION (MAXRECURSION 0); 
		/*
			Значением 0 снимаем серверное ограничение на количество уровней рекурсии (которое по умолчанию равно 100), 
			чтобы иметь возможность формировать даты в большом диапазоне.
		*/
     RETURN
   END

2. сделал функцию заполнения календаря данными из таблицы проходов EventsPass - twt_uploadCalendar
+
USE [TimeWorkTracking]
GO
/****** Object:  UserDefinedFunction [dbo].[twt_uploadCalendar]    Script Date: 21.11.2021 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[twt_uploadCalendar]
	(@DateBegin datetime, 
	 @DateEnd datetime)
--'20210102' and '20210115'
Returns table as Return 
(
Select calendar.dt daysCalendar,
       pass.userId,
	   pass.passId,
	   pass.userName,
	   pass.userPost,
	   pass.specialMark,
	   pass.workTime
  From 
   (Select convert(nvarchar(30),dt,112) dt From getCalendar(@DateBegin, @DateEnd)) as calendar
  left join 
   (Select 
	  u.usId userId,                            --id фио 
	  u.usName userName,                        --фио
	  u.usPost userPost,                        --должность
	  e.smName specialMark,                     --специальные отметки
	  e.ptimeScheduleFact workTime,             --количество отработанных минут
	  e.pDate passDate,                         --дата прохода
	  e.pId passId                              --id записи
	 From 
	  (Select 
	     us.extId usId,                         --id фио 
             us.name usName,                        --фио
	     up.name usPost			    --должность
        From Users us, UserPost up  
       Where us.postId = up.id 
	      and us.name like('%' + 'михаил' + '%') 
	      and us.uses = 1) as u 
     left join 
      (Select 
	ep.id pId,                                  --id записи
        ep.passDate pDate,                          --дата прохода 
	ep.passId usId,                             --id фио
        sm.letterCode smName,			    --имя специальных отметок
	ep.timeScheduleFact ptimeScheduleFact	--количество отработанных минут
       From EventsPass ep, SpecialMarks sm 
       Where sm.id=ep.specmarkId
	      and passDate between @DateBegin and @DateEnd) as e 
  on u.usId = e.usId ) as pass

  on pass.passDate=calendar.dt
  )

3. добавил хп динамического pivot - SP_Dynamic_Pivot
+
USE [TimeWorkTracking]
GO
/****** Object:  StoredProcedure [dbo].[SP_Dynamic_Pivot]    Script Date: 21.11.2021 19:14:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 -- Создаем универсальную процедуру для динамического PIVOT   
 -- https://info-comp.ru/obucheniest/631-dynamic-pivot-in-t-sql.html
   ALTER PROCEDURE [dbo].[SP_Dynamic_Pivot]
   (
        @TableSRC NVARCHAR(100),   --Таблица источник (Представление)
        @ColumnName NVARCHAR(100), --Столбец, содержащий значения, которые станут именами столбцов
        @Field NVARCHAR(100),      --Столбец, над которым проводить агрегацию
        @FieldRows NVARCHAR(100),  --Столбец (столбцы) для группировки по строкам (Column1, Column2)
        @FunctionType NVARCHAR(20) = 'SUM',--Агрегатная функция (SUM, COUNT, MAX, MIN, AVG), по умолчанию SUM
        @Condition NVARCHAR(200) = '' --Условие (WHERE и т.д.). По умолчанию без условия
   )
   AS 
   BEGIN
        /*
                Универсальная процедура формирования динамического запроса PIVOT.
                Разработчик Info-Comp.ru
        */
        
        --Отключаем вывод количества строк
        SET NOCOUNT ON;
        
        --Переменная для хранения строки запроса
        DECLARE @Query NVARCHAR(MAX);                     
         --Переменная для хранения имен столбцов
        DECLARE @ColumnNames NVARCHAR(MAX);              
        --Переменная для хранения заголовков результирующего набора данных
        DECLARE @ColumnNamesHeader NVARCHAR(MAX); 

        --Обработчик ошибок
        BEGIN TRY
                --Таблица для хранения уникальных значений, 
                --которые будут использоваться в качестве столбцов      
                CREATE TABLE #ColumnNames(ColumnName NVARCHAR(100) NOT NULL PRIMARY KEY);
        
                --Формируем строку запроса для получения уникальных значений для имен столбцов
                SET @Query = N'INSERT INTO #ColumnNames (ColumnName)
                                                  SELECT DISTINCT COALESCE(' + @ColumnName + ', ''Пусто'') 
                                                  FROM ' + @TableSRC + ' ' + @Condition + ';'
                
                --Выполняем строку запроса
                EXEC (@Query);

                --Формируем строку с именами столбцов
                SELECT @ColumnNames = ISNULL(@ColumnNames + ', ','') + QUOTENAME(ColumnName) 
                FROM #ColumnNames;
                
                --Формируем строку для заголовка динамического перекрестного запроса (PIVOT)
                SELECT @ColumnNamesHeader = ISNULL(@ColumnNamesHeader + ', ','') 
                                                                        + 'COALESCE('
                                                                        + QUOTENAME(ColumnName) 
                                                                        + ', 0) AS '
                                                                        + QUOTENAME(ColumnName)
                FROM #ColumnNames;
        
                --Формируем строку с запросом PIVOT
                SET @Query = N'SELECT ' + @FieldRows + ' , ' + @ColumnNamesHeader + ' 
                                           FROM (SELECT ' + @FieldRows + ', ' + @ColumnName + ', ' + @Field 
                                                         + ' FROM ' + @TableSRC  + ' ' + @Condition + ') AS SRC
                                           PIVOT ( ' + @FunctionType + '(' + @Field + ')' +' FOR ' +  
                                                                   @ColumnName + ' IN (' + @ColumnNames + ')) AS PVT
                                           ORDER BY ' + @FieldRows + ';'
                
                --Удаляем временную таблицу
                DROP TABLE #ColumnNames;

                --Выполняем строку запроса с PIVOT
                EXEC (@Query);
                
                --Включаем обратно вывод количества строк
                SET NOCOUNT OFF;
                
        END TRY
        BEGIN CATCH
                --В случае ошибки, возвращаем номер и описание этой ошибки
                SELECT ERROR_NUMBER() AS [Номер ошибки], 
                           ERROR_MESSAGE() AS [Описание ошибки]
        END CATCH
   END

4. Развернул и казалось бы добавить это к списку юзеров слева но
Select convert(nvarchar(30),dt,112) dt From getCalendar('20210102', '20210115')
select * from twt_uploadCalendar('20210102', '20210115')
EXEC SP_Dynamic_Pivot @TableSRC = '(select * from twt_uploadCalendar(''20210102'', ''20210115'')) pivotSrc',  --Таблица источник (Представление)
                                          @ColumnName = 'daysCalendar',--Столбец, содержащий значения для столбцов в PIVOT
                                          @Field = 'passId',           --Столбец, над которым проводить агрегацию
                                          @FieldRows = 'userId',       --Столбец для группировки по строкам
                                          @FunctionType = 'MAX'        --Агрегатная функция, по умолчанию SUM


pivot агрегирует только одно значение, а мне нужно два - specialMark & workTime (строку и число), поэтому в агрегатор положил id записи проходов passId (таблицы EventsPass ) чтобы потом его разобрать подзапросами или курсором (в общем не знаю как, это нужно пройти по каждой колонке даты а сколько их - заранее не известно... диапазон я запрашиваю на вызове)
но наверно это путь в никуда...

может действительно формировать динамический запрос с '32' left join на каждую дату...
или в клиенте просто по факту выполнить все '32' запроса на каждую отдельную дату...

в общем не получается сводная таблица в лоб

К сообщению приложен файл. Размер - 20Kb


Сообщение было отредактировано: 21 ноя 21, 19:51
21 ноя 21, 19:42    [22398761]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9155
ef1,

колонки календаря лучше формировать на клиенте, а на сервере использовать string_agg вместо pivot.
22 ноя 21, 00:57    [22398847]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
ef1
Member

Откуда: Москва
Сообщений: 203
можно и на клиенте создать строки с колонками календаря и бросить их в параметры pivot...
но поскольку все равно динамика, и pivot не в цикле - проще на сервере все оформить - в данном случае
пока думаю как в агрегатор pivot MAX передать строку или функцию которая отобразит нужные мне данные
ps
string_agg - не пройдет - у меня заявлено в поддержке проекта - минимальная версия 2005, а эта функция для 2017
22 ноя 21, 08:37    [22398885]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
ef1
Member

Откуда: Москва
Сообщений: 203
осталось чуть чуть
1. Переписал функцию загрузки календаря данными добавив туда строки для агрегатора pivot
+
USE [TimeWorkTracking]
GO
/****** Object:  UserDefinedFunction [dbo].[twt_uploadCalendar]    Script Date: 22.11.2021 11:02:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[twt_uploadCalendar]
	(@DateBegin datetime, 
	 @DateEnd datetime)
--'20210102' and '20210115'
Returns table as Return 
(
Select calendar.dt daysCalendar,
       pass.userId,
	   pass.passId,
	   pass.userName,
	   pass.userPost,
	   pass.specialMark,
	   pass.workTime,
	   pass.pivotStr,
	   pass.pivotXML
  From 
   (Select convert(nvarchar(30),dt,112) dt From getCalendar(@DateBegin, @DateEnd)) as calendar
  left join 
   (Select 
	  u.usId userId,                            --id фио 
	  u.usName userName,                        --фио
	  u.usPost userPost,                        --должность
	  e.smName specialMark,                     --специальные отметки
	  e.ptimeScheduleFact workTime,             --количество отработанных минут
	  e.smName + '||' + CONVERT(varchar(10), e.ptimeScheduleFact) pivotStr,
	  '<inf><sm>' + e.smName + '</sm><time>' + CONVERT(varchar(10), e.ptimeScheduleFact) + '</time></inf>' pivotXML,
	  e.pDate passDate,                         --дата прохода
	  e.pId passId                              --id записи
	 From 
	  (Select 
	     us.extId usId,                         --id фио 
         us.name usName,  						--фио
	     up.name usPost 						--должность
        From Users us, UserPost up  
       Where us.postId = up.id 
	      and us.name like('%' + 'михаил' + '%') 
	      and us.uses = 1) as u 
     left join 
      (Select 
	    ep.id pId,  							--id записи
        ep.passDate pDate,					    --дата прохода 
	    ep.passId usId,                         --id фио
        sm.letterCode smName,					--имя специальных отметок
		ep.timeScheduleFact ptimeScheduleFact	--количество отработанных минут
        From EventsPass ep, SpecialMarks sm 
       Where sm.id=ep.specmarkId
	      and passDate between @DateBegin and @DateEnd) as e 
  on u.usId = e.usId ) as pass

  on pass.passDate=calendar.dt
  )

2. исправил хп динамической генерации pivot - значение по умолчанию для агрегатора заменил с 0 на NULL
+
USE [TimeWorkTracking]
GO
/****** Object:  StoredProcedure [dbo].[SP_Dynamic_Pivot]    Script Date: 22.11.2021 11:03:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 -- Создаем универсальную процедуру для динамического PIVOT   
 -- https://info-comp.ru/obucheniest/631-dynamic-pivot-in-t-sql.html
   ALTER PROCEDURE [dbo].[SP_Dynamic_Pivot]
   (
        @TableSRC NVARCHAR(100),   --Таблица источник (Представление)
        @ColumnName NVARCHAR(100), --Столбец, содержащий значения, которые станут именами столбцов
        @Field NVARCHAR(100),      --Столбец, над которым проводить агрегацию
        @FieldRows NVARCHAR(100),  --Столбец (столбцы) для группировки по строкам (Column1, Column2)
        @FunctionType NVARCHAR(20) = 'SUM',--Агрегатная функция (SUM, COUNT, MAX, MIN, AVG), по умолчанию SUM
        @Condition NVARCHAR(200) = '' --Условие (WHERE и т.д.). По умолчанию без условия
   )
   AS 
   BEGIN
        /*
                Универсальная процедура формирования динамического запроса PIVOT.
                Разработчик Info-Comp.ru
        */
        
        --Отключаем вывод количества строк
        SET NOCOUNT ON;
        
        --Переменная для хранения строки запроса
        DECLARE @Query NVARCHAR(MAX);                     
         --Переменная для хранения имен столбцов
        DECLARE @ColumnNames NVARCHAR(MAX);              
        --Переменная для хранения заголовков результирующего набора данных
        DECLARE @ColumnNamesHeader NVARCHAR(MAX); 

        --Обработчик ошибок
        BEGIN TRY
                --Таблица для хранения уникальных значений, 
                --которые будут использоваться в качестве столбцов      
                CREATE TABLE #ColumnNames(ColumnName NVARCHAR(100) NOT NULL PRIMARY KEY);
        
                --Формируем строку запроса для получения уникальных значений для имен столбцов
                SET @Query = N'INSERT INTO #ColumnNames (ColumnName)
                                                  SELECT DISTINCT COALESCE(' + @ColumnName + ', ''Пусто'') 
                                                  FROM ' + @TableSRC + ' ' + @Condition + ';'
                
                --Выполняем строку запроса
                EXEC (@Query);

                --Формируем строку с именами столбцов
                SELECT @ColumnNames = ISNULL(@ColumnNames + ', ','') + QUOTENAME(ColumnName) 
                FROM #ColumnNames;
                
                --Формируем строку для заголовка динамического перекрестного запроса (PIVOT)
                SELECT @ColumnNamesHeader = ISNULL(@ColumnNamesHeader + ', ','') 
                                                                        + 'COALESCE('
                                                                        + QUOTENAME(ColumnName) 
                                                                        + ', NULL ) AS '         --значение по умолчанию NULL
                                                                        + QUOTENAME(ColumnName)
                FROM #ColumnNames;
        
                --Формируем строку с запросом PIVOT
                SET @Query = N'SELECT ' + @FieldRows + ' , ' + @ColumnNamesHeader + ' 
                                           FROM (SELECT ' + @FieldRows + ', ' + @ColumnName + ', ' + @Field 
                                                         + ' FROM ' + @TableSRC  + ' ' + @Condition + ') AS SRC
                                           PIVOT ( ' + @FunctionType + '(' + @Field + ')' +' FOR ' +  
                                                                   @ColumnName + ' IN (' + @ColumnNames + ')) AS PVT
                                           ORDER BY ' + @FieldRows + ';'
                
                --Удаляем временную таблицу
                DROP TABLE #ColumnNames;

                --Выполняем строку запроса с PIVOT
                EXEC (@Query);
                
                --Включаем обратно вывод количества строк
                SET NOCOUNT OFF;
                
        END TRY
        BEGIN CATCH
                --В случае ошибки, возвращаем номер и описание этой ошибки
                SELECT ERROR_NUMBER() AS [Номер ошибки], 
                           ERROR_MESSAGE() AS [Описание ошибки]
        END CATCH
   END

3. развернул получил то что хотел по правой части см. картинку
4. остался последний вопрос самый простой )) мне нужно
- в своей хп получить таблицу из хп п.п.3
- прикрепить ее слева к таблице пользователей (users) - и отдать клиенту C#

но подскажите как сделать...
я в своей хп должен создать #временную таблицу для приема данных из хп п.п.3 - но я не знаю ее реального размера (диапазон заказываемых дат - разный - не знаю заранее количества столбцов)
?

--------
вызов и картинка к хп п.п.3
select * from twt_uploadCalendar('20210102', '20210115')

EXEC SP_Dynamic_Pivot @TableSRC = '(select * from twt_uploadCalendar(''20210102'', ''20210115'')) pivotSrc',  --Таблица источник (Представление)
                      @ColumnName = 'daysCalendar',--Столбец, содержащий значения для столбцов в PIVOT
                      @Field = 'pivotXML',         --Столбец, над которым проводить агрегацию
                      @FieldRows = 'userId',       --Столбец для группировки по строкам
                      @FunctionType = 'MAX'        --Агрегатная функция, по умолчанию SUM


К сообщению приложен файл. Размер - 23Kb
22 ноя 21, 11:13    [22398969]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
ef1
Member

Откуда: Москва
Сообщений: 203
финал - все получилось
пользователи +динамический (диапазон дат) календарь событий
(две udf, одна sp в ней один динамический запрос) синтаксис для MSSQL2005

основная sp для отчета
+
USE [TimeWorkTracking]
GO
/****** Object:  StoredProcedure [dbo].[SP_twt_TotalReport]    Script Date: 23.11.2021 10:59:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

   ALTER PROCEDURE [dbo].[SP_twt_TotalReport]
   (
		@DateBegin NVARCHAR(100),       --Дата начала периода 
	    @DateEnd NVARCHAR(100)	       --Дата окончания периода   
	)
   AS 
   BEGIN
        /*
                Процедура формирования данных для отчета (использует динамический PIVOT).
        */
        
	--Обработчик ошибок
    BEGIN TRY
        SET NOCOUNT ON;																	--Отключаем вывод количества строк

	    --таблица для формирования имен колонок (для расширененя вренной таблицы и динамического pivot)
		DECLARE @daysRange TABLE(colName NVARCHAR(30), colId int identity(1,1));		--табличная переменная (имя колонки и id колонки)		
		INSERT INTO @daysRange (colName)												--заполним диапазоном дат
			SELECT top 500 convert(NVARCHAR(30),dt,112) dt								--вставим ограничение 500 (2000 столбцов ограничение SQL)
			  FROM getCalendar(@DateBegin, @DateEnd);
--select * from @daysRange

		--столбцы pivot
		--заголовки колонок Pivot
		DECLARE @colNamesHeaderPivot NVARCHAR(MAX);										--sql строка заголовков столбцов для pivot
	    SELECT @colNamesHeaderPivot = ISNULL(@colNamesHeaderPivot + ', ','') 
										+ 'COALESCE('
			                            + QUOTENAME(colName) 
				                        + ', NULL ) AS '								--значение по умолчанию NULL для агрегатной функции MAX
					                    + QUOTENAME(colName)
		  FROM @daysRange;
--select @colNamesHeaderPivot

		--перечень колонок Pivot
		DECLARE @colNamesPivot NVARCHAR(MAX);											--sql строка столбцов для pivot
	    SELECT @colNamesPivot = ISNULL(@colNamesPivot + ', ','') 
										+ QUOTENAME(colName)	--сформируем ее
		  FROM @daysRange;
--select @colNamesPivot

	    --временная таблица Report
		DECLARE @max int,																--переменная для цикла			
				@id int,																--переменная для цикла
				@Query NVARCHAR(MAX),   												--переменная для хранения строки запроса
				@colName NVARCHAR(50)													--наименование заголовка столбца

		SET @id = 1
		SELECT @max = MAX(colId) FROM @daysRange
		CREATE TABLE #twt_Report(userID NVARCHAR(30));				--создадим временную таблицу для отчета
		WHILE (@id <= @max)																--расширим ее нужным количеством столбцов
			BEGIN																		--**возможно не самое красивое решение
				SELECT @colName = colName FROM @daysRange WHERE colId = @id
				SET @Query = 'ALTER TABLE #twt_Report ADD '+ QUOTENAME(@colName) +' NVARCHAR(50);'
				EXEC(@Query)																
				SET @id = @id +1
			END
--select * from #twt_Report

        --Формируем строку с запросом PIVOT и вставкой в таблицу Report
		DECLARE @TableSRC NVARCHAR(100),   --Таблица источник (Представление)
				@ColumnName NVARCHAR(100), --Столбец, содержащий значения, которые станут именами столбцов
				@Field NVARCHAR(100),      --Столбец, над которым проводить агрегацию
				@FieldRows NVARCHAR(100),  --Столбец (столбцы) для группировки по строкам (Column1, Column2)
				@FunctionType NVARCHAR(20),--Агрегатная функция (SUM, COUNT, MAX, MIN, AVG), по умолчанию SUM
				@Condition NVARCHAR(200)   --Условие (WHERE и т.д.). По умолчанию без условия


        SET @TableSRC = '(select * from twt_uploadCalendar(''' + @DateBegin + ''', ''' + @DateEnd + ''')) pivotSrc';
        SET @ColumnName = 'daysCalendar';
        SET @Field = 'pivotXML';         
        SET @FieldRows = 'userId';       
        SET @FunctionType = 'MAX';        
		SET @Condition = '';
        SET @Query = N'SELECT ' + @FieldRows + ' , ' + @colNamesHeaderPivot + ' 
                         FROM (SELECT ' + @FieldRows + ', ' + @ColumnName + ', ' + @Field 
                             + ' FROM ' + @TableSRC  + ' ' + @Condition + ') AS SRC
                       PIVOT ( ' + @FunctionType + '(' + @Field + ')' +' FOR ' +  
                                   @ColumnName + ' IN (' + @colNamesPivot + ')) AS PVT
                       ORDER BY ' + @FieldRows + ';'
--select @Query

		INSERT INTO #twt_Report EXEC(@Query)						--вставим результаты запроса во временную таблицу Отчета

		--то ради чего...
		SELECT *
		  FROM 
			(SELECT 
				u.name fio, 
				p.name post, 
				u.uses access, 
				u.extId extId 
			  FROM Users u, UserDepartment d, UserPost p--, UserWorkScheme w 
             WHERE u.departmentId = d.Id 
	 	        AND u.name like('%' + 'михаил' + '%') 
			    AND u.postId = p.id 
				AND	u.uses = 1) as u
		left join
		  (SELECT * 
		     FROM #twt_Report 
		  ) as r 
		on u.ExtId = r.userID

		DROP TABLE #twt_Report;							--удалим временную таблицу отчета
		                
		SET NOCOUNT OFF;								--Включаем обратно вывод количества строк
	END TRY
    BEGIN CATCH
		--В случае ошибки, возвращаем номер и описание этой ошибки
        SELECT ERROR_NUMBER() AS [Номер ошибки], 
               ERROR_MESSAGE() AS [Описание ошибки]
	END CATCH
	END

вызов (из клиента C#)
EXEC SP_twt_TotalReport @DateBegin='20210110', @DateEnd= '20210115'  

результат
(код и отчет не оптимизировал... - все в динамике... - проще на клиенте пропустить служебные столбцы)

К сообщению приложен файл. Размер - 11Kb
23 ноя 21, 11:13    [22399562]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
ef1
Member

Откуда: Москва
Сообщений: 203
off
функцию генерации дат календаря заменил на более скорострельную (+форматирование дат для наименований столбцов потребителям)
+
USE [TimeWorkTracking]
GO
/****** Object:  UserDefinedFunction [dbo].[getCalendar]    Script Date: 23.11.2021 12:56:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
   ALTER FUNCTION [dbo].[getCalendar] (
	@fromdate DATEtime, -- Дата начала
	@todate DATEtime	-- Дата окончания
   )
   RETURNS @tcaldate TABLE (dt nvarchar(30)) 
   AS
   BEGIN
	 
	 INSERT INTO @tcaldate 
       SELECT TOP (DATEDIFF(DAY, @fromdate, @todate) + 1)
              convert(nvarchar(30), DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @fromdate),112)
         FROM sys.all_objects a
              CROSS JOIN sys.all_objects b;

     RETURN 
   END


Сообщение было отредактировано: 23 ноя 21, 12:58
23 ноя 21, 12:57    [22399633]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь с запросом по базовой таблице с левым добавлением данных из диапазона дат  [new]
ef1
Member

Откуда: Москва
Сообщений: 203
off
из замеченных ограничений (для mssql2005 + win server2003)
1. агрегатор pivot MAX - не обрабатывает (у меня во всяком случае) строки длиной более 50 символов (заменил формат xml на разделители для split в дальнейшем на клиенте)(сейчас передаю 6 параметров ~25 символов с разделителями)
2. количество динамически сформированных столбцов календаря/отчета не более 2000 шт - но и не нужно )) (поставил ограничитель на 500)

по скорости
выборка на 500 дат ~5-10 сек
рабочая выборка на месяц (30 дат) ~ 0,5-1 сек - вполне нормально

Сообщение было отредактировано: 23 ноя 21, 15:02
23 ноя 21, 15:00    [22399703]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить