Как передать параметры в запрос в SQL (Excel). Sql запрос в excel
SQL в Excel -- надстройка для Экселя
0
Скачать надстройку! Надстройка в формате *.xlam. Тестировалась в версиях Экселя: 2013.SQL в Excel позволяет создавать пользовательские запросы SQL из таблиц Экселя. Надстройка является уникальным в своем роде продуктом, она позволяет любому создать и запустить свой запрос в 3 простых шага:
- Открыть файл и выбрать лист с таблицей
- Выбрать поля для запроса
- Выбрать условия отбора и запустить запрос.
1. Внешний вид надстройки
Внешне надстройка реализована в виде пошагового мастера SQL-запроса:Для формирования таблицы используется текст запроса, типа SELECT... Самый простой пример текста запроса: SELECT * FROM [Исходная$]. Для выполнения запроса программе так же необходима ссылка на книгу. Обе эти переменные выводятся на итоговый лист и могут быть изменены под нужды пользователя.
2. Запуск надстройки
Преимущество состоит в том, что тебе не требуются права администратора для установки надстройки SQL в Excel. Она устанавливается в папку с надстройками, либо в любую папку на твоем компьютере. Подробные инструкции по установке можно найти здесь.Надстройка добавит 2 кнопки на панели инструментов:
| ↑ Рис.3. Новые кнопки на панели инструментов |
- [A1] -- текст запроса
- [A2] -- ссылка на файл
3. Сформируй свой запрос
В спецификации к надстройке мы перечисляем различные варианты запросов. Данные примеры запросов действительны и могут быть применены на практике. С исходным файлом можно ознакомиться, перейдя по ссылке.↑ перечень еще неполный. Будем его, по возможности, пополнять новыми интересными примерами запросов.
Как выбрать Да в запросах SQL для MailMerge? MS Excel онлайн
Я пытался «обойти» предупреждение Select From Sheet1$ которое появляется, когда вы отправляете MailMerge в Word с документом Excel.
Я попытался сохранить каждый документ «без подключенного источника данных, но изменил код VBA в каждом документе, чтобы сделать соответствующий OpenDataSource», как указано в Excel VBA, для автоматического выбора «Да» при появлении запроса во время слияния
Но все же Word требует выбора Sheet1$ .
Кроме того, DisplayAlerts=0 не работает для меня. Я предполагаю, что так же, как @Ashton Sheets, «у меня проблема с размещением. Когда у меня есть wdDoc.DisplayAlerts = 0 установите перед тем, как установить wdDoc = GetObject(wdInputName, "Word.document") очевидно, потому что wdDoc не установлен, но если я сразу же поставлю его по строке, то слишком поздно, потому что слово только ТОГДА открывается, и это именно так, когда появляется сообщение – так что «слишком поздно», как указано в « Как использовать VBA для сказать «Да» на любые подсказки SQL в слове?
Идея этого кода заключается в том, чтобы избежать возможных ошибок пользователей. Поэтому минимальное количество «кликов» от оператора является обязательным. В том числе MsgBox s …
Также важно упомянуть, что я использую Office 2007 для программирования этого. И HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Word\Options не является вариантом из-за нескольких пользователей и компьютеров, которые будут использовать код.
Вот пример кода:
Option Explicit Sub wrd() Dim name As String Dim wrdapp As Word.Application Dim wrddoc As Word.Document Set wrdapp = CreateObject("word.application") wrdapp.Visible = True 'wrdapp.DisplayAlerts = False Word.Application.DisplayAlerts = False Set wrddoc = wrdapp.Documents.Open("C:\............\3.2_CTO. A.FIN..docx") With wrddoc '.Application.DisplayAlerts = wdAlertsNone .MailMerge.OpenDataSource ("C:\.......\3.1_Base de datos_FIN.xlsx") .MailMerge.HighlightMergeFields = True .MailMerge.ViewMailMergeFieldCodes = False .MailMerge.DataSource.ActiveRecord = wdLastDataSourceRecord name = Hoja14.Range("a2") .SaveAs ("C:\...........\Clientes\Persona Física\Cto. " & (tipo) & "# " & (name) + ".docx") wrdapp.Quit Set wrddoc = Nothing Set wrdapp = Nothing Application.DisplayAlerts = True End With End Sub
Solutions Collecting From Web of "Как выбрать Да в запросах SQL для MailMerge?"
я согласен что
.DisplayAlerts = Falseлегче читать, чем
.DisplayAlerts = 0но оба означают одинаковые
.DisplayAlerts = FalseDisplayAlerts является логическим выражением, а не номером один. Кроме того, если вы предоставите фрагмент кода, это поможет всем нам справиться с проблемой SO.
Вы также можете попробовать .EnableEvents = False . Обычно это работает для приглашения «Сохранить изменения», но может также работать и на вашу ситуацию. Опять же, ссылка на код поможет в этой ситуации.
EDIT: увидев ваш код, я бы попытался добавить этот SQLStatement к вашему .MailMerge.OpenDataSource Object:
SQLStatement:="SELECT * FROM Sheet1$"
Пример:
.MailMerge.OpenDataSource "C:\.......\3.1_Base de datos_FIN.xlsx",_ SQLStatement:="SELECT * FROM 'Sheet1$'"Кредит, в котором должен быть предоставлен кредит.
Наконец сделал.
Код оказался следующим:
'After defining variables and opening word app and word doc: .MailMerge.OpenDataSource "C:\...\3.1_Base de datos.xlsx",_ LinkToSource:=True, _ SQLStatement:="SELECT * FROM `Sheet1$`" .MailMerge.ViewMailMergeFieldCodes = False .MailMerge.DataSource.ActiveRecord = wdLastDataSourceRecord .MailMerge.MainDocumentType = wdNotAMergeDocument .SaveAs ("E:\_...\Cto. " & (tipo) & "# " & (name) + ".docx") .Close wrdapp.Quit Set wrddoc = Nothing Set wrdapp = Nothing 'Remember that you need to establish in the VisualBasic Editos ' /Tools/References/Microsoft Word Object Library in order to access 'Word VBA's references.Надеюсь, это может пригодиться.
Спасибо @SalvadorVayshun за вашу помощь.
excel.bilee.com
Обновление ячеек на листе в excel через sql-запрос
Вопрос: Sql запрос к листам excell
Здравствуйте! Пытаюсь решить задачу сравнения данных на двух листах эксель(на каждом порядка 150 000 записей). Вцелом задача: найти только записи листа1 которых нет на листе2. Нет на листе2 значит, что нет совпадений 11 из 20 полей записи. Использую sql запрос к листам книги, вот такой примерно(очень его упростил уже пытаясь разобраться, но самостоятельно не вышло)| Visual Basic | ||
| ||
Кликните здесь для просмотра всего текста
| Visual Basic | ||
| ||
ядро используется ms.jet и все хорошо выполняется, все как мне нужно Однако в результате получаем таблицу с огромной кучей колонок, которые вообщем-то не нужны Однако если я пишу запрос определяя поля для вывода
| Visual Basic | ||
| ||
честно, я не понимаю, что за хрень, помогите пожалуйста разобраться....
еще странные вещи вот в чем: select * выдает мои несколько строчек разницы, а select count(*) считает, что результатов запроса 0
Возможно это надо в ветку про sql однако чутье мне подсказывает, что есть какая-то особенность именно в экселе, с sql запросами к которому я относительно недавно познакомился
Так же, если есть идеи по альтернативному решению задачи, буду рад! Спасибо!
Ответ:Сообщение от snipe
и если HDR вдруг стало No а вы в запросе указываете имена конкретных столбцов
Сообщение от LEonardo_
Сообщение от snipe
то можно воспользоваться Not In()
да, я рассматривал вариант этот, только оно не правильно работает если появляются значения null. ну и реальный запрос он намного сложнее... вроде бы Join с ms jet работает быстрей...опытным путем установлено, что проблема была в работе cdbl(). это одно из важнейших полей было, но в одной таблице это строка типа 0000123456789 а в другой целое число 123456789. заменил на странную конструкцию типа cstr(t1.номер*1) и заработало(может есть еще варианты?)...
на самом деле забавно... но ответ искать не хочется, тк я не знаю способа отладить sql запрос из vba...в целом меня устраивает, обрабатывает мои листы с 150 000 строк, 20 полями по 11 из которых сравнение меньше минуты
Дополнительно, если можно,Можете посоветовать где почитать про sql запросы из vba(интересует запуск pl/sql конструкций для бд oracle 11 из vba а так же впринципе возможности работы с книгой эксель как с таблицами бд(ну вот примерно как в вопросе топика))
за ответы Спасибо
sql - Переменный SQL-запрос в Excel
Я ищу, чтобы создать запрос в excel, чтобы запросы, чтобы найти, где строка соответствует значению, которое находится в colummn. выберите несколько определенных столбцов.
Итак, у меня есть данные в форме
668406 668406-1 668406-2 670589 671259 671259-1 671259-2 671261 671261-1я хочу создать запрос, который говорит
SELECT JobHeaderID, Job,ProofApproved,SleeveLabel, MasterLabel FROM JobHeader WHERE JobHeader.Job = '668406' or JobHeader.Job = '668406-1' or JobHeader.Job = '668406-2' or JobHeader.Job = '670589' or JobHeader.Job = '671259' or JobHeader.Job = '671259-1' or JobHeader.Job = '671259-2' or JobHeader.Job = '671261' or JobHeader.Job = '671261-1'
Я могу построить запрос и выбрать целые столы для записей или одну или две записи, когда я вручную скажу, чтобы они соответствовали этим заданиям, но я хочу, чтобы он запросил все это сразу, а не меня вручную, набрав каждый из них.
Если бы была функция SQL, я мог бы просто позвонить
=SQLQuery(SQLServer,CONCATENATE("SELECT JobHeaderID, Job,ProofApproved,SleeveLabel, MasterLabel FROM JobHeader WHERE JobHeader.Job = '",C3,"'"))'EDIT: У меня нет проблем с построением запроса, будь то только по значению или для всего диапазона сразу. Я просто не знаю, как сделать запрос из ячейки или макроса, только импортируя выделение один раз в качестве источника данных SQL или используя запрос Microsoft для выбора значений (даже для всех значений), но никак не ссылаться на текущие значения в этих запросах, Я должен забрать их вручную. Если кто-то может помочь мне создать функцию SQLSquery, как показано выше, тогда я настроен, иначе: мне не нужна помощь при построении запроса, если вы не можете помочь мне ссылаться на меняющийся объект из одного из типов источников, перечисленных выше,
qaru.site
как передать параметры в запрос в SQL (Excel) MS Excel онлайн
Это зависит от базы данных, к которой вы пытаетесь подключиться, метода, с помощью которого вы создали соединение, и версии Excel, которую вы используете. (Также, скорее всего, версия соответствующего драйвера ODBC на вашем компьютере.)
В следующих примерах используются SQL Server 2008 и Excel 2007, как на моей локальной машине.
Когда я использовал Мастер подключения данных (на вкладке «Данные» ленты в разделе «Получить внешние данные» в разделе «Из других источников»), я увидел то же самое, что и вы: кнопка «Параметры» была отключена и добавила параметр в запрос, что-то вроде select field from table where field2 = ? , заставил Excel жаловаться на то, что значение параметра не было указано, и изменения не были сохранены.
Когда я использовал Microsoft Query (то же самое место, что и Мастер подключения к данным), я смог создавать параметры, указывать отображаемое имя для них и вводить значения каждый раз, когда запрос выполнялся. При добавлении свойств подключения для этого соединения кнопка «Параметры …» включена, и параметры могут быть изменены и использованы, как я думаю, вы хотите.
Я также смог сделать это с помощью базы данных Access. Представляется разумным, что Microsoft Query может использоваться для создания параметризованных запросов, ударяющих по другим типам баз данных, но я не могу легко проверить это прямо сейчас.
Этот пост достаточно стар, что этот ответ, вероятно, будет мало полезен для OP, но я навсегда пытался ответить на этот же вопрос, поэтому я решил обновить его своими выводами.
Этот ответ предполагает, что у вас уже есть рабочий SQL-запрос в вашем документе Excel. Существует множество учебных пособий, показывающих вам, как это сделать в Интернете, и много, которые объясняют, как добавить параметризованный запрос к одному, за исключением того, что ни один из них не работает для существующего запроса OLE DB .
Итак, если вы, как и я, получили документ Excel с рабочим запросом, но пользователь хочет иметь возможность фильтровать результаты на основе одного из полей базы данных, и если вы, как и я, не являетесь ни Excel, ни SQL-гуру, это может помочь вам.
Большинство веб-ответов на этот вопрос, похоже, говорят, что вы должны добавить «?» В свой запрос, чтобы заставить Excel запросить у вас специальный параметр или поместить приглашение или ссылку на ячейку в [скобки], где должен быть параметр. Это может работать для запроса ODBC, но он не работает для OLE DB, возвращая «Нет значения для одного или нескольких требуемых параметров» в первом экземпляре и «Недопустимое имя столбца« xxxx »или« Неизвестный объект » 'xxxx' "в последних двух. Точно так же использование мифических кнопок «Параметры …» или «Редактировать запрос …» также не является вариантом, поскольку в этом случае они, как представляется, постоянно серые. (Для справки, я использую Excel 2010, но с Excel 97-2003 Workbook (* .xls))
Однако мы можем добавить ячейку параметра и кнопку с простой подпрограммой, чтобы программно обновлять текст запроса.
Во-первых, добавьте строку над вашей внешней таблицей данных (или где угодно), где вы можете поместить подсказку параметров рядом с пустой ячейкой и кнопкой (Developer-> Insert-> Button (Form Control). Возможно, вам потребуется включить вкладку «Разработчик» , но вы можете узнать, как это сделать в другом месте), например:
Затем выберите ячейку в области внешних данных (синий), затем откройте Data-> Refresh All (выпадающее меню) -> Свойства подключения …, чтобы просмотреть ваш запрос. Код в следующем разделе предполагает, что у вас уже есть параметр в вашем запросе (Connection Properties-> Definition-> Command Text) в форме «WHERE (DB_TABLE_NAME.Field_Name =« Параметры запроса по умолчанию »)» (включая круглые скобки). Очевидно, что «DB_TABLE_NAME.Field_Name» и «Default Query Parameter» должны быть разными в вашем коде на основе имени таблицы базы данных, имени поля базы данных (столбца) и некоторого значения по умолчанию для поиска, когда документ открывается (если у вас установлен автообновление). Обратите внимание на значение «DB_TABLE_NAME.Field_Name», которое вам понадобится в следующем разделе, а также «Имя подключения» вашего запроса, которое можно найти в верхней части диалогового окна.
Закройте свойства подключения и нажмите Alt + F11, чтобы открыть редактор VBA. Если вы еще не на нем, щелкните правой кнопкой мыши на имени листа, содержащего вашу кнопку, в окне «Проект» и выберите «Просмотреть код». Вставьте следующий код в окно кода (рекомендуется копирование, поскольку одиночные / двойные кавычки являются рискованными и необходимыми).
Sub RefreshQuery() Dim queryPreText As String Dim queryPostText As String Dim valueToFilter As String Dim paramPosition As Integer valueToFilter = "DB_TABLE_NAME.Field_Name =" With ActiveWorkbook.Connections("Connection name").OLEDBConnection queryPreText = .CommandText paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) - 1 queryPreText = Left(queryPreText, paramPosition) queryPostText = .CommandText queryPostText = Right(queryPostText, Len(queryPostText) - paramPosition) queryPostText = Right(queryPostText, Len(queryPostText) - InStr(queryPostText, ")") + 1) .CommandText = queryPreText & " '" & Range("Cell reference").Value & "'" & queryPostText End With ActiveWorkbook.Connections("Connection name").Refresh End SubЗамените «DB_TABLE_NAME.Field_Name» и «Имя соединения» (в двух местах) с вашими значениями (необходимо включить двойные кавычки, а также пробел и знак равенства).
Замените «Ссылка на ячейку» на ячейку, где будет идти ваш параметр (пустая ячейка с начала) – моя была второй ячейкой в первой строке, поэтому я положил «B1» (опять же, нужны двойные кавычки).
Сохраните и закройте редактор VBA.
Введите свой параметр в соответствующую ячейку.
Щелкните правой кнопкой мыши, чтобы назначить Sub RefreshQuery в качестве макроса, затем нажмите кнопку. Запрос должен обновлять и отображать правильные данные!
Примечания. Использование всего имени параметра фильтра («DB_TABLE_NAME.Field_Name =») необходимо только в том случае, если в вашем запросе есть соединения или другие вхождения знаков равенства, иначе просто знак равенства будет достаточным, и вычисление Len () будет излишний. Если ваш параметр содержится в поле, которое также используется для объединения таблиц, вам нужно будет изменить строку «paramPosition = InStr (queryPreText, valueToFilter) + Len (valueToFilter) – 1» в коде на «paramPosition = InStr ( Right (.CommandText, Len (.CommandText) – InStrRev (.CommandText, "WHERE")), valueToFilter) + Len (valueToFilter) – 1 + InStr (.CommandText, "WHERE") ", так что он ищет только значениеToFilter после «ГДЕ».
Этот ответ был создан с помощью «BaconBits» datapig, где я нашел базовый код для обновления запроса.
excel.bilee.com
parameter-passing - как передать параметры в запрос в SQL (Excel)
Этот пост достаточно стар, что этот ответ, вероятно, будет мало полезен для OP, но я навсегда пытался ответить на этот же вопрос, поэтому я подумал, что обновляю его своими выводами.
Этот ответ предполагает, что у вас уже есть рабочий SQL-запрос в вашем документе Excel. Существует множество учебных пособий, в которых показано, как это сделать в Интернете, и много, которые объясняют, как добавить параметризованный запрос к одному, за исключением того, что ни один из них не работает для существующего запроса OLE DB.
Итак, если вы, как и я, получили документ Excel с рабочим запросом, но пользователь хочет иметь возможность фильтровать результаты на основе одного из полей базы данных, и если вы, как и я, не являетесь ни тем, ни другим Excel или SQL-гуру, это может помочь вам.
Большинство веб-ответов на этот вопрос, похоже, говорят, что вы должны добавить "?" в запросе, чтобы заставить Excel запросить у вас специальный параметр или поместить подсказку или ссылку на ячейку в [скобки], где должен быть параметр. Это может работать для запроса ODBC, но он не работает для OLE DB, возвращая "Нет значения для одного или нескольких требуемых параметров" в первом экземпляре и "Недопустимое имя столбца" xxxx "или" Неизвестный объект ", xxxx" в последних двух. Точно так же использование мифических кнопок "Параметры..." или "Редактировать запрос..." также не является вариантом, поскольку в этом случае они, как представляется, постоянно серые. (Для справки, я использую Excel 2010, но с Excel 97-2003 Workbook (*.xls))
Однако мы можем добавить ячейку параметра и кнопку с простой подпрограммой для программного обновления текста запроса.
Сначала добавьте строку над вашей внешней таблицей данных (или где угодно), где вы можете поместить приглашение параметра рядом с пустой ячейкой и кнопкой (кнопка "Разработчик- > Вставка- > (управление формой) - вам может потребоваться включить вкладку" Разработчик", но вы можете узнать, как это сделать в другом месте), например:
Затем выберите ячейку в области внешних данных (синий), затем откройте Data- > Refresh All (выпадающее меню) → Свойства подключения..., чтобы просмотреть ваш запрос. Код в следующем разделе предполагает, что у вас уже есть параметр в вашем запросе (Connection Properties- > Definition- > Command Text) в форме "WHERE (DB_TABLE_NAME.Field_Name =" Параметры запроса по умолчанию ")" (включая круглые скобки). Очевидно, что "DB_TABLE_NAME.Field_Name" и "Default Query Parameter" должны быть разными в вашем коде на основе имени таблицы базы данных, имени поля базы данных (столбца) и некоторого значения по умолчанию для поиска, когда документ открывается (если у вас установлен автообновление). Обратите внимание на значение "DB_TABLE_NAME.Field_Name" , которое вам понадобится в следующем разделе, а также "Имя подключения" вашего запроса, которое можно найти в верхней части диалогового окна.
Закройте свойства подключения и нажмите Alt + F11, чтобы открыть редактор VBA. Если вы еще не на нем, щелкните правой кнопкой мыши на имени листа, содержащего вашу кнопку, в окне "Проект" и выберите "Просмотреть код". Вставьте следующий код в окно кода (рекомендуется копирование, поскольку одиночные/двойные кавычки являются рискованными и необходимыми).
Sub RefreshQuery() Dim queryPreText As String Dim queryPostText As String Dim valueToFilter As String Dim paramPosition As Integer valueToFilter = "DB_TABLE_NAME.Field_Name =" With ActiveWorkbook.Connections("Connection name").OLEDBConnection queryPreText = .CommandText paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) - 1 queryPreText = Left(queryPreText, paramPosition) queryPostText = .CommandText queryPostText = Right(queryPostText, Len(queryPostText) - paramPosition) queryPostText = Right(queryPostText, Len(queryPostText) - InStr(queryPostText, ")") + 1) .CommandText = queryPreText & " '" & Range("Cell reference").Value & "'" & queryPostText End With ActiveWorkbook.Connections("Connection name").Refresh End SubЗамените "DB_TABLE_NAME.Field_Name" и "Имя соединения" (в двух местах) с вашими значениями (необходимо включить двойные кавычки, а также пробел и знак равенства).
Замените "Ссылка на ячейку" на ячейку, где будет идти ваш параметр (пустая ячейка с начала) - моя была второй ячейкой в первой строке, поэтому я положил "B1" (опять же, нужны двойные кавычки).
Сохраните и закройте редактор VBA.
Введите свой параметр в соответствующую ячейку.
Щелкните правой кнопкой мыши по вашей кнопке, чтобы назначить опцию RefreshQuery в качестве макроса, затем нажмите кнопку. Запрос должен обновлять и отображать правильные данные!
Примечания: Использование всего имени параметра фильтра ( "DB_TABLE_NAME.Field_Name =" ) необходимо только в том случае, если в вашем запросе есть соединения или другие вхождения знаков равенства, иначе просто знак равенства будет достаточным, и вычисление Len() будет излишним. Если ваш параметр содержится в поле, которое также используется для объединения таблиц, вам нужно будет изменить строку "paramPosition = InStr (queryPreText, valueToFilter) + Len (valueToFilter) - 1" в коде на "paramPosition = InStr ( Right (.CommandText, Len (.CommandText) - InStrRev (.CommandText," WHERE ")), valueToFilter) + Len (valueToFilter) - 1 + InStr (.CommandText," WHERE ")", так что он ищет только значениеToFilter после "ГДЕ".
Этот ответ был создан с помощью datapigs "BaconBits", где я нашел базовый код для обновления запроса.
qaru.site
SQL-запрос для посещения в течение месяца MS Excel онлайн
Это довольно сложная задача для решения SQL с учетом необходимости поиска переходов и диапазонов в данных, что не является тривиальным. Я перепутал проблему с целым рядом шагов, сделанных из последовательных cte, которые основываются друг на друге и приводят к окончательному рабочему решению:
Во-первых, я добавляю индекс строки к данным, чтобы предоставить простой PK для идентификации уникальной строки:
with NumberedAtt as ( select row_number() over (partition by [user] order by date, time, reader) as ix, att.[user], att.[department], att.[date] + att.[time] as dt, att.[reader] from att )Затем я беру первое и последнее значение индекса для каждого пользователя, которое будет использоваться для самых внешних границ каждого диапазона ввода / выхода:
, MinMax as ( select [user], min(ix) ixMin, max(ix) ixMax from NumberedAtt N group by [user] )Затем я собрал их вместе для создания списка всех диапазонов выхода / ввода, которые являются точками, где значение Reader изменяется с 2 на 1 . Это конкретные точки, которые точно определяют, когда заканчивается предыдущий временной диапазон, и начинается следующий временной диапазон (и четко обрабатывает последовательные повторяющиеся записи / выходы). Объединив это с первой записью и последним временем выхода для каждого пользователя, создается список всех переходов ввода / вывода:
, Transitions as ( select N.[User], 0 as exitIx, M.ixMin as entryIx from NumberedAtt N join MinMax M on N.[User] = M.[User] where N.ix = M.ixMin union select N.[User], M.ixMax as exitIx, 0 as entryIx from NumberedAtt N join MinMax M on N.[User] = M.[User] where N.ix = M.ixMax union select A1.[User], A1.ix as exitIx, A2.ix as entryIx from NumberedAtt A1 join NumberedAtt A2 on A1.ix + 1 = A2.ix and A1.[user] = A2.[user] where A1.[reader] = 2 and A2.[reader] = 1 )Вот результат:
| USER | EXITIX | ENTRYIX | |------|--------|---------| | A1 | 0 | 1 | | A1 | 2 | 3 | | A1 | 4 | 0 | | B1 | 0 | 1 | | B1 | 3 | 0 |Обратите внимание, что мы аккуратно захватили все индексы строк, где начинается и заканчивается диапазон времени. Однако они смещены на один – это время входа в одну строку соответствует времени выхода в следующей строке. Поэтому нам нужно еще одно преобразование, чтобы объединить диапазоны, добавив индекс строки в эту таблицу и объединив каждую строку со следующей строкой:
, NumberedTransitions as ( select row_number() over (partition by [User] order by exitIx) tix, T.* from Transitions T ), EntryExit as ( select aEntry.ix as ixEntry, aExit.ix as ixExit, aEntry.[user], aEntry.[department], aEntry.[dt] as entryDT, aExit.[dt] as exitDT from NumberedTransitions tEntry join NumberedAtt aEntry on tEntry.entryIx = aEntry.ix and tEntry.[user] = aEntry.[user] join NumberedTransitions tExit on tEntry.tix + 1 = tExit.tix and tEntry.[user] = tExit.[user] join NumberedAtt aExit on tExit.exitIx = aExit.ix and tExit.[user] = aExit.[user] )Присоединившись к последовательному диапазону вместе, я также вернусь к исходным подробным данным, поскольку я работал только с значениями индекса строки до сих пор. В конце этого подзапроса мы определили все диапазоны входа / выхода для каждого пользователя и «проглотили» любые множественные чтения:
| IXENTRY | IXEXIT | USER | DEPARTMENT | ENTRYDT | EXITDT | |---------|--------|------|------------|------------------------------|------------------------------| | 1 | 2 | A1 | IT | March, 01 2014 11:12:00+0000 | March, 01 2014 13:12:00+0000 | | 3 | 4 | A1 | IT | March, 02 2014 07:42:15+0000 | March, 02 2014 16:16:15+0000 | | 1 | 3 | B1 | IT | March, 01 2014 12:28:06+0000 | March, 01 2014 13:28:06+0000 |Теперь осталось только собрать данные вместе, чтобы сообщать об общем количестве часов на пользователя в месяц. Немного сложно вычислить общее количество часов, но это можно сделать, взяв сумму минут между диапазонами, а затем преобразуя результат обратно в значение времени:
, Hours as ( select [User], [Department], Year(EntryDT) Year, Month(EntryDT) Month, RIGHT('0' + CAST(SUM(DATEDIFF(Minute, EntryDT, ExitDT)) / 60 as varchar(10)), 2) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(Minute, EntryDT, ExitDT)) % 60 as varchar(2)), 2) as TotalHours from EntryExit EE group by [User], [Department], Year(EntryDT), Month(EntryDT) )Это дает конечный результат, который очень близок к желаемому результату:
| USER | DEPARTMENT | YEAR | MONTH | TOTALHOURS | |------|------------|------|-------|------------| | A1 | IT | 2014 | 3 | 10:34:00 | | B1 | IT | 2014 | 3 | 01:00:00 |Несколько изменений можно было бы внести в форматирование по желанию, но это должно быть легко создать поверх этой структуры.
Вот рабочая демонстрация: http://www.sqlfiddle.com/#!3/f3f37/7
excel.bilee.com
