Версия для печати
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум на Исходниках.RU > Базы данных: SQL > Помогите с запросом


Автор: ^D^ima 06.09.17, 14:29
SQL server 2014 + SQL management studio.

Запрос:
user posted image
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT     Payment.Number, Payment.Date, PaymentDetail.ID AS PaymentDetailID, PaymentDetail.Value, Invoice.FileAs, Invoice.Number AS Expr1, Project.FileAs AS Expr2
    FROM         PaymentDetail INNER JOIN
                          Payment ON PaymentDetail.PaymentID = Payment.ID INNER JOIN
                          Invoice ON PaymentDetail.InvoiceID = Invoice.ID INNER JOIN
                          Project ON Invoice.ProjectID = Project.ID INNER JOIN
                          DocumentCategory ON Project.ID = DocumentCategory.DocumentID INNER JOIN
                          Category ON DocumentCategory.CategoryID = Category.ID
    ORDER BY Payment.Date


Проблема:
В таблице DocumentCategory присутствуют не все значения из таблицы Project, следовательно итоговая таблица усекается до не пустых вхождений(с 1000 до 700)

Вопрос:
Как запрос построить таким образом, что если DocumentCategory.DocumentID не найден, то вместо него(ну и дальше в Category) просто писалось null и все(т.е. число записей осталось 1000).

Автор: Akina 06.09.17, 16:06
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
     
LEFT JOIN DocumentCategory

Автор: ^D^ima 06.09.17, 17:28
Бинго!!!

Akina
Не напомнишь, как называется функция объединения(суммирования) строк?

1СИНИЙ
2КРАСНЫЙ
3СИНИЙ


суммировались в
4СИНИЙ
2КРАСНЫЙ

Автор: Akina 06.09.17, 17:36
Цитата ^D^ima @
как называется функция объединения(суммирования) строк?

Это шутка такая? у тебя суммируются числа, а не строки. SUM() ... А по строке выполняется группировка. GROUP BY ...

Автор: ^D^ima 06.09.17, 18:38
Ошибка:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT     /*Payment.Date, Payment.FileAs AS Payment,*/ Payment.TotalWithVATBC, /*Project.FileAs AS Project,*/ Category.FileAs AS Category
    FROM         PaymentDetail INNER JOIN
                          Payment ON PaymentDetail.PaymentID = Payment.ID INNER JOIN
                          Invoice ON PaymentDetail.InvoiceID = Invoice.ID INNER JOIN
                          Project ON Invoice.ProjectID = Project.ID LEFT OUTER JOIN
                          DocumentCategory ON Project.ID = DocumentCategory.DocumentID LEFT OUTER JOIN
                          Category ON DocumentCategory.CategoryID = Category.ID
    WHERE     (Payment.FileAs NOT LIKE N'%-1%') AND (Payment.FileAs NOT LIKE N'%-3%') and (Payment.Date between '01.01.2017' and '31.12.2017' )
    /*ORDER BY Payment.Date*/
    GROUP BY Payment.TotalWithVATBC


Сообщение 8120, уровень 16, состояние 1, строка 1
Столбец "Category.FileAs" недопустим в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY.

Добавлено
Мне что-то типа такого нужно:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    GROUP BY Category.FileAs,sum(Payment.TotalWithVATBC)

Чтобы для одинаковых Category.FileAs суммировались их Payment.TotalWithVATBC

Автор: ^D^ima 06.09.17, 20:28
Так получилось, всем спасибо.
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT     /*Payment.Date, Payment.FileAs AS Payment,*/ sum(Payment.TotalWithVATBC), /*Project.FileAs AS Project,*/ Category.FileAs AS Category
    FROM         PaymentDetail INNER JOIN
                          Payment ON PaymentDetail.PaymentID = Payment.ID INNER JOIN
                          Invoice ON PaymentDetail.InvoiceID = Invoice.ID INNER JOIN
                          Project ON Invoice.ProjectID = Project.ID LEFT OUTER JOIN
                          DocumentCategory ON Project.ID = DocumentCategory.DocumentID LEFT OUTER JOIN
                          Category ON DocumentCategory.CategoryID = Category.ID
    WHERE     (Payment.FileAs NOT LIKE N'%-1%') AND (Payment.FileAs NOT LIKE N'%-3%') and (Payment.Date between '01.01.2017' and '31.12.2017' )
     
    /*ORDER BY Payment.Date*/
    GROUP BY Category.FileAs

Автор: Akina 07.09.17, 04:33
Цитата ^D^ima @
Так получилось

Мне неизвестна физическая сущность хранящихся в таблицах сведений, но создаётся впечатление, что запрос избыточен. Как я понимаю, не может быть такого, чтобы в DocumentCategory присутствовала запись о CategoryID, к которой нет соответствующей записи в таблице Category. Но если так, то нет смысла связывать таблицу Category левым связыванием, достаточно внутреннего. А если поле Category.ID уникально (а на показанной схеме это вообще первичный ключ) - то таблицу Category вообще можно удалить из списка таблиц-источников запроса.

Автор: ^D^ima 07.09.17, 10:02
Akina
Спасибо за совет.

А можно так сделать, чтобы Category.FileAs AS Category объединяло все найденные варианты в 1 строку.

Я имею в виду что если для Project.ID найдется несколько DocumentCategory.DocumentID и Category.ID чтобы эти строки в 1 объединились?

ДокументКатегория
1Категория 1
1Категория 2


переделалось в

ДокументКатегория
1Категория 1, Категория 2

Автор: Akina 07.09.17, 10:57
Цитата ^D^ima @
А можно так сделать, чтобы Category.FileAs AS Category объединяло все найденные варианты в 1 строку.

У Вас MS SQL. Если версия позволяет, то можете использовать STRING_AGG. Иначе разве что через FOR XML.

Автор: ^D^ima 12.09.17, 14:42
помогите отформатировать число.

Я хочу чтобы сотые отделялись запятой(12345,67)
А то получается что SQL management studio выдает корректно, с запятой, а при обработке того-же запроса через sqlcmd получается точка
Я написал так:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Format( sum(PaymentDetail.Value) , '#,00') AS Сумма

но почему-то дробную часть вообще отсекает

Добавлено
или придется переводить в строку и делать replace?

Автор: ^D^ima 12.09.17, 18:12
Вместо
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    sum(PaymentDetail.Value) AS Сумма


Стало:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT   replace( CAST(  sum(PaymentDetail.Value) as varchar(20) ) ,'.',',')AS Сумма


Так заработало, но как-то череопно выглядит. PaymentDetail.Value имеет тип данных money

Автор: Akina 12.09.17, 18:29
Цитата ^D^ima @
SQL management studio выдает корректно, с запятой

Потому что SSMS - это средств отображения, которое форматирует результат, используя установленный системный или настраиваемый разделитель/формат.
Цитата ^D^ima @
при обработке того-же запроса через sqlcmd получается точка

А sqlcmd отдаёт результат as is, используя стандартный разделитель.
Цитата ^D^ima @
Так заработало, но как-то череопно выглядит.

Попробуй
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Format( sum(PaymentDetail.Value) , N'#.00', N'ru-ru') AS Сумма

Автор: ^D^ima 12.09.17, 19:08
:thanks:

Автор: ^D^ima 13.09.17, 10:41
Цитата Akina @
N

Что означает N в формате?

Автор: Akina 13.09.17, 13:04
Цитата ^D^ima @
Что означает N в формате?

Если посмотреть описание функции Format, то увидишь. что второй и третий параметры имеют тип Nvarchar, а не просто varchar.

Автор: ^D^ima 18.09.17, 17:54
Можно я все глупые вопросы буду писать тут? :)

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
    SELECT   sum(PaymentDetail.Value) AS СуммаПриход, QUOTENAME(Company.FileAs,'"') AS Компания,  sum(TimeEntry.Rate) AS СуммаРасхода
    FROM         PaymentDetail
                            INNER JOIN Payment ON PaymentDetail.PaymentID = Payment.ID
                            INNER JOIN   Company ON Payment.PayerCompanyID = Company.ID
                            INNER JOIN Invoice ON PaymentDetail.InvoiceID = Invoice.ID
                          
                          INNER JOIN Project ON Invoice.ProjectID = Project.ID
                         INNER JOIN TimeEntry ON Project.ID = TimeEntry.ProjectID
                          
    WHERE     (Payment.FileAs LIKE N'%-1%') AND (Payment.Date BETWEEN '01.01.2017' AND '31.08.2017')
    GROUP BY Company.FileAs
    ORDER BY Company.FileAs

Project - таблица проектов
TimeEntry - таблица учета времени\затрат по проектам

Не могу понять как мне сделать так, чтобы SQL суммировал все TimeEntry.Rate для каждого данного проекта.

Добавлено
А то получается сейчас берется как понимаю первое вхождение

Автор: kosten 19.09.17, 06:51
^D^ima, а почему ты именно INNER JOIN делаешь, а не LEFT JOIN?

Автор: ^D^ima 19.09.17, 06:56
Цитата kosten @
LEFT JOIN

Я пробовал разные варианты, проблему это не решает.
Если была это 1с-ка было бы все легко. Тут как понимаю если делать встроенный запрос в запрос, то вначале выполняется 2-й встроенный, потом первый...

Автор: kosten 19.09.17, 07:08
^D^ima, давай-ка структура таблиц.

Автор: Akina 19.09.17, 07:41
Цитата kosten @
почему ты именно INNER JOIN делаешь, а не LEFT JOIN?

А что в запросе могло натолкнуть на мысль о необходимости LEFT JOIN???
Цитата ^D^ima @
как мне сделать так, чтобы SQL суммировал все TimeEntry.Rate для каждого данного проекта.

У тебя в запросе выполняется группировка по Company.FileAs. Такая группа - один проект?

Автор: ^D^ima 19.09.17, 09:24
Задача: Сделать отчет Клиент, СуммаПоступила, СуммаПотрачена

Связанность таблиц:
Payment и PaymentDetail это платежи денег, которые поступают на счет, из них берется Клиент и СуммаПоступила

далее сложнее.
В каждом платеже есть только 1 счет. Таблица Invoice.
Каждый счет связан с конкретным проектом. Таблица Project.
В каждом проекте есть учет времени. Таблица TimeEntry, из которой берется TimeEntry.Rate СуммаПотрачена. Трудность в том что в каждом проекте может быть много учетов времени, которые нужно суммировать.

1 платеж - 1 счет - 1 проект - много учетов времени

Автор: Akina 19.09.17, 10:23
Цитата ^D^ima @
1 платеж - 1 счет - 1 проект - много учетов времени

У одного проекта строго один счёт - допускаю.
У одного счёта - один проект или много?
У одного счёта строго один платёж - не верю.

Автор: ^D^ima 19.09.17, 10:39
Akina

В 1 платеже -> 1 счет(не бывает счета связанного с несколькими платежами) -> каждый счет связан с 1 проектом (не бывает одного счета связанного с 2 проектами) -> каждый проект связан со многими записями о времени.

Автор: kosten 19.09.17, 10:43
Цитата ^D^ima @
не бывает счета связанного с несколькими платежами

Если оплата по проекту разбита по времени, то каждый раз надо платить на разные счета?

Автор: Akina 19.09.17, 10:48
Цитата ^D^ima @
В 1 платеже -> 1 счет(не бывает счета связанного с несколькими платежами)

Т.е. не только "в 1 платеже -> 1 счет", но и "в 1 счёте - 1 платёж". Я верно понял?
Ибо в схеме-то, что наверху, я вижу как бы иное...

Автор: ^D^ima 19.09.17, 11:16
Цитата Akina @
Т.е. не только "в 1 платеже -> 1 счет", но и "в 1 счёте - 1 платёж". Я верно понял?

В таблице платежа есть ссылка на счет, в счете ссылки на платеж нет.


Цитата kosten @
Если оплата по проекту разбита по времени, то каждый раз надо платить на разные счета?

Возможно что к проекту привязан не 1 счет, но от этого цепочка не меняется

Автор: JoeUser 19.09.17, 15:04
Цитата ^D^ima @
в счете ссылки на платеж нет

Пробегал мимо, суть задачи не уловил. Но осуждаю :lol:
Если связь A->B есть, а B->A нет, но нужна, то просто "склеивайте" нужный запрос с нужным порядком полей, и пользуйте его как "источник".
Сорри, если не в тему, и не впопад)

Автор: ^D^ima 20.09.17, 12:13
Моя задача не решает в принципе или я неверно изложил?

Автор: Akina 20.09.17, 12:32
Да решается она, решается. Просто ты её неполно изложил, а догадываться тупо лень.
Если, ориентируясь на твой запрос, составить эскиз схемы данных, то получится что-то типа
1.png (, : 1402) .
Дорисуй, где один, а где много...

Автор: ^D^ima 20.09.17, 12:48
Akina
Все верно. везде 1, TimeEntry много.
Я имею в ввиду что в таблице TimeEntry много вхождений по одному ProjectID

Автор: Akina 20.09.17, 15:41
Тогда почему надо суммировать PaymentDetail.Value?

Автор: ^D^ima 20.09.17, 16:14
можно не суммировать

Автор: ^D^ima 21.09.17, 06:09
:'(:'(:'(:'(:'(:'(:'(:'(:'(

Автор: Akina 21.09.17, 06:46
^D^ima
Не, давай так. Переноси структуры и пример наполнения в Access, и выкладывай полученную базу, с указанием, какой должен получиться результат на этом наполнении. Будем смотреть.

Автор: ^D^ima 21.09.17, 08:20
Akina
Прошу прощения, был недостаточно нагляден.

С Access тяжело, сделал все наглядно:
Запрос:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
    SELECT   (PaymentDetail.Value) AS СуммаПриход,Company.FileAs AS Компания,   Project.FileAs as Проект, sum(Expense.TotalPC) AS СуммаРасхода
     
    FROM         PaymentDetail
                            INNER JOIN Payment ON PaymentDetail.PaymentID = Payment.ID
                            INNER JOIN   Company ON Payment.PayerCompanyID = Company.ID
                            INNER JOIN Invoice ON PaymentDetail.InvoiceID = Invoice.ID
                          
                          INNER JOIN Project ON Invoice.ProjectID = Project.ID
                        inner JOIN Expense ON Project.ID = Expense.ProjectID
     
    where Company.FileAs ='Идиллия'
    GROUP BY PaymentDetail.Value, Company.FileAs,    Project.FileAs

Результат:
user posted image

Что хочу:
Получить только 1 строку в которой:
user posted image

Автор: Akina 21.09.17, 08:33
Гм... А что получится на тех же данных, если обернуть PaymentDetail.Value в SUM() и убрать из выражения группировки?

В самом крайнем случае
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT SUM(СуммаПриход) AS СуммаПриход, Компания, Проект, СуммаРасхода
    FROM (твой запрос) AS dummy
    GROUP BY Компания, Проект


Цитата ^D^ima @
С Access тяжело

Что там тяжёлого? Открыть Access, создать новую БД, импортировать туда таблицы и почистить от лишних данных (если данных слишком дофига, то импортировать структуры, а потом запросами скопировать немного данных), сжать, зипануть - и всё.

Автор: ^D^ima 21.09.17, 08:44
Цитата Akina @
Гм... А что получится на тех же данных, если обернуть PaymentDetail.Value в SUM() и убрать из выражения группировки?

Вопрос верный, я так уже делал, он СуммаРасхода суммирует а СуммаПриход вообще треш непонятный:

user posted image

По поводу аксеса, там вообще не принципиально, достаточно 2 таблицы связанные сделать, накидаю сейчас

Добавлено
Akina
ДАААААААААААА, через запрос в запросе все срослась!!!!!!:dance::dance::dance:

Добавлено
Наверное последний вопрос:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT SUM(СуммаПриход) AS СуммаПриход, Компания, СуммаРасхода
     
    from
     
    (SELECT   (PaymentDetail.Value) AS СуммаПриход,Company.FileAs AS Компания,    sum(Expense.TotalPC) AS СуммаРасхода
     
    FROM         PaymentDetail
                            INNER JOIN Payment ON PaymentDetail.PaymentID = Payment.ID
                            INNER JOIN   Company ON Payment.PayerCompanyID = Company.ID
                            INNER JOIN Invoice ON PaymentDetail.InvoiceID = Invoice.ID
                          
                          INNER JOIN Project ON Invoice.ProjectID = Project.ID
                        inner JOIN Expense ON Project.ID = Expense.ProjectID
     
    GROUP BY PaymentDetail.Value, Company.FileAs
    ) as dummy
    where компания ='Идиллия' or компания ='Авангард ОАО'
    GROUP BY  Компания, СуммаРасхода

дает
user posted image

Как-то можно свернуть ещё по компаниям другим мпособом, кроме как ещё делать запрос в запросе, в котором запрос?
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
     
    Select sum(СуммаПриход)as СуммаПриход, Компания, sum(СуммаРасхода) as СуммаРасхода
    from(
     
    SELECT SUM(СуммаПриход) AS СуммаПриход, Компания, СуммаРасхода
     
    from
     
    (SELECT   (PaymentDetail.Value) AS СуммаПриход,Company.FileAs AS Компания,    sum(Expense.TotalPC) AS СуммаРасхода
     
    FROM         PaymentDetail
                            INNER JOIN Payment ON PaymentDetail.PaymentID = Payment.ID
                            INNER JOIN   Company ON Payment.PayerCompanyID = Company.ID
                            INNER JOIN Invoice ON PaymentDetail.InvoiceID = Invoice.ID
                          
                          INNER JOIN Project ON Invoice.ProjectID = Project.ID
                        inner JOIN Expense ON Project.ID = Expense.ProjectID
     
    GROUP BY PaymentDetail.Value, Company.FileAs
    ) as dummy
    where компания ='Идиллия' or компания ='Авангард ОАО'
    GROUP BY  Компания, СуммаРасхода) as dummy2
     
    GROUP BY  Компания
    order by Компания


Добавлено
user posted image

Автор: Akina 21.09.17, 12:57
"Средний" подзапрос - лишний.
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Select sum(СуммаПриход)as СуммаПриход, Компания, sum(СуммаРасхода) as СуммаРасхода
    from (
             SELECT PaymentDetail.Value AS СуммаПриход, Company.FileAs AS Компания, sum(Expense.TotalPC) AS СуммаРасхода
             FROM PaymentDetail
             INNER JOIN Payment ON PaymentDetail.PaymentID = Payment.ID
             INNER JOIN   Company ON Payment.PayerCompanyID = Company.ID
             INNER JOIN Invoice ON PaymentDetail.InvoiceID = Invoice.ID
             INNER JOIN Project ON Invoice.ProjectID = Project.ID
             inner JOIN Expense ON Project.ID = Expense.ProjectID
             GROUP BY PaymentDetail.Value, Company.FileAs
         ) as dummy
    GROUP BY  Компания
    order by Компания

Автор: ^D^ima 21.09.17, 14:49
пришло в голову такое решение:
Если и платеж и расход связан с клиентом, то можно взять одним запросом все расходы, в другом все платежи и в условии where связать через названия клиента, получится как-то лаконичней

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
    Select sum(PaymentDetail.Value) AS СуммаПриход, innerQuery.Компания, innerQuery.СуммаРасхода
     
    from PaymentDetail
                            INNER JOIN Payment ON PaymentDetail.PaymentID = Payment.ID
                            INNER JOIN   Company ON Payment.PayerCompanyID = Company.ID,
     
     
    (SELECT   Company.FileAs AS Компания,    sum(Expense.TotalPC) AS СуммаРасхода
     
    FROM         Expense            
              
                          INNER JOIN Project ON Expense.ProjectID = Project.ID
                          INNER JOIN   Company ON Project.CustomerCompanyID = Company.ID
                        
     
    GROUP BY Company.FileAs) as innerQuery
    where Company.FileAs=innerQuery.Компания and (Payment.FileAs LIKE N'%-1%')
    group by innerQuery.Компания, innerQuery.СуммаРасхода
    order by innerQuery.Компания

Автор: Akina 21.09.17, 16:18
Цитата ^D^ima @
пришло в голову такое решение

Подход правильный. Реализация - кривая.
Сделай два запроса, в каждом посчитай свою сумму. Оба их используй как подзапросы в секции FROM, связав по полю Компания. Само это поле возьми из любого из подзапросов, всё одно равны.

Автор: ^D^ima 22.09.17, 09:01
Цитата Akina @
делай два запроса, в каждом посчитай свою сумму. Оба их используй как подзапросы в секции FROM, связав по полю Компания.

несколько не понял

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
     
    Select  innerQuery1.Компания, innerQuery1.СуммаПриход, innerQuery2.СуммаРасхода
     
    From
    Company on Company.FileAs =innerQuery1.Компания=innerQuery2.Компания, -- Тут что-то не вяжется
     
    (Select  Company.FileAs as Компания, sum(PaymentDetail.Value) AS СуммаПриход
     
    from Company
            left JOIN Payment ON  Company.ID=Payment.PayerCompanyID
           left JOIN PaymentDetail ON Payment.ID  = PaymentDetail.PaymentID
    GROUP BY Company.FileAs) as innerQuery1,          
     
    (
    SELECT   Company.FileAs AS Компания,    sum(Expense.TotalPC) AS СуммаРасхода
    FROM         Company
     
      left JOIN Project ON Company.ID = Project.CustomerCompanyID
      left join Expense  on Project.ID  = Expense.ProjectID
    GROUP BY Company.FileAs) as innerQuery2

Автор: Akina 22.09.17, 09:54
Цитата ^D^ima @
несколько не понял

Ну что ты всё заумь какую-то выдумываешь?

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT innerQuery1.Компания
         , innerQuery1.СуммаПрихода
         , innerQuery2.СуммаРасхода
     
    FROM (   SELECT Company.FileAs as Компания
                  , SUM(PaymentDetail.Value) AS СуммаПрихода
             FROM Company
             JOIN Payment ON Company.ID=Payment.PayerCompanyID
             JOIN PaymentDetail ON Payment.ID = PaymentDetail.PaymentID
             GROUP BY Company.FileAs
         ) AS innerQuery1,
            
         (   SELECT Company.FileAs AS Компания
                  , SUM(Expense.TotalPC) AS СуммаРасхода
             FROM Company
             JOIN Project ON Company.ID = Project.CustomerCompanyID
             JOIN Expense ON Project.ID = Expense.ProjectID
             GROUP BY Company.FileAs
         ) as innerQuery2
     
    WHERE innerQuery1.Компания = innerQuery2.Компания

Автор: ^D^ima 22.09.17, 10:16
Akina
теперь понятно, :thanks::thanks::thanks:

Автор: ^D^ima 27.09.17, 09:05
Опять столкнулся с трудностью:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT   Company.FileAs AS Компания, ISNULL(sum(Expense.TotalPC),0) AS СуммаРасхода
    FROM         Company
     
      left JOIN Project ON Company.ID = Project.CustomerCompanyID
      left join Expense  on Project.ID  = Expense.ProjectID
     
    Where YEAR(Expense.Date) = YEAR( GETDATE( )) and    MONTH(Expense.Date) between 1 and MONTH(GETDATE( ))-1
    GROUP BY Company.FileAs

Тут для 100 компаний формируется расход.

Проблема в том, что если нет расхода, то через условие Where строчка будет отброшена, т.к. нет даты.
А как сделать так чтобы туда попадал 0?

нужно что-то этого:
Условие Where выполняется только если Expense через Project.ID = Expense.ProjectID существует.

Как-то так:
If Record_exist(Expense) then
Where YEAR(Expense.Date) = YEAR( GETDATE( )) and MONTH(Expense.Date) between 1 and MONTH(GETDATE( ))-1
End if

В общем для 100 клиентов нужно сделать таблицу из 100 записей и если расхода нет, записать 0.

надеюсь понятно написал...

Автор: Akina 27.09.17, 10:08
Цитата ^D^ima @
Проблема в том, что если нет расхода, то через условие Where строчка будет отброшена, т.к. нет даты.
А как сделать так чтобы туда попадал 0?

Типичная проблема ошибочной логики. Смотри.

Тебе надо сделать две вещи.
Первая - это объединить записи из Expense с соотв. записью из Company+Project.
Вторая - это отобрать записи из Expense, которые отвечают твоим требованиям.

А теперь - внимание, вопрос! Что нужно делать сначала, а что потом?

Ответ очевиден - сначала надо отобрать, и только потом объединять отобранное. Что делает твой запрос? да с точностью до наоборот!

Для того, чтобы в правильном порядке выполнить эти два действия, условие отбора, которое должно выполняться ДО связывания (или, точнее, во время связывания - какая собственно разница? лишь бы не после), следует размещать в самОй секции связывания. То есть в секции ON.

Правильный вопрос будет выглядеть так:

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT Company.FileAs AS Компания, ISNULL(sum(Expense.TotalPC),0) AS СуммаРасхода
    FROM      Company
    LEFT JOIN Project ON Company.ID = Project.CustomerCompanyID
    LEFT JOIN Expense ON Project.ID  = Expense.ProjectID
                     AND YEAR(Expense.Date) = YEAR( GETDATE( ))
                     AND MONTH(Expense.Date) between 1 and MONTH(GETDATE( ))-1
    GROUP BY Company.FileAs


Добавлено
PS. Подумай, что ты будешь получать от этого запроса в ЯНВАРЕ...

Автор: ^D^ima 27.09.17, 10:44
Цитата Akina @
следует размещать в самОй секции связывания. То есть в секции ON.

Гениально!!!

Цитата Akina @
Подумай, что ты будешь получать от этого запроса в ЯНВАРЕ...

Да, вижу...

Цитата Akina @
Ответ очевиден - сначала надо отобрать, и только потом объединять отобранное.

:yes:

Akina, спасибо за ответы :thanks:

Автор: ^D^ima 28.09.17, 08:42
Все более тяжелые отчеты просят от меня :rolleyes:

Подскажите концептуально:
Есть 3 отдельных запроса, в каждом из которых результатом является 1 строка, 2 числа: СуммаПриход, СуммаРасход
Как мне получить отчет, где будет:
НазваниеЗапроса(произвольное название), СуммаПриход, СуммаРасход

Автор: Akina 28.09.17, 08:50
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
        SELECT 'Запрос 1' AS НазваниеЗапроса, СуммаПриход, СуммаРасход
        FROM [query1]
    UNION ALL
        SELECT 'Запрос 2'                   , СуммаПриход, СуммаРасход
        FROM [query2]
    UNION ALL
        SELECT 'Запрос 3'                   , СуммаПриход, СуммаРасход
        FROM [query3]

Автор: ^D^ima 28.09.17, 11:51
Как-то можно оптимизировать это?
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Select 'Выручка консультантов' as Вид, СуммаПриход, СуммаРасход
    from (
    Select sum(PaymentDetail.Value) AS СуммаПриход, СуммаРасход = 0
    From    Payment
           left JOIN PaymentDetail ON Payment.ID  = PaymentDetail.PaymentID
     
    where (Payment.FileAs not LIKE N'%-1%') and YEAR(Payment.Date) = YEAR( GETDATE( )) and    MONTH(Payment.Date) between 1 and MONTH(GETDATE( ))-1
    ) as запрос1
     
     
    Union all
     
    Select 'Проектные расходы' , Запрос2.СуммаПриход, Запрос3.СуммаРасход
    from (
    Select sum(PaymentDetail.Value) AS СуммаПриход
    From    Payment
           left JOIN PaymentDetail ON Payment.ID  = PaymentDetail.PaymentID
     
    where (Payment.FileAs LIKE N'%-1%') and YEAR(Payment.Date) = YEAR( GETDATE( )) and    MONTH(Payment.Date) between 1 and MONTH(GETDATE( ))-1
    ) as Запрос2,
    (
    Select sum(Expense.TotalPC) AS СуммаРасход
    From    Expense
          
    where Expense.EmployeeID=1386 and YEAR(Expense.Date) = YEAR( GETDATE( )) and    MONTH(Expense.Date) between 1 and MONTH(GETDATE( ))-1
    ) as Запрос3
     
     
    Union all
     
    Select 'РКО\ПКО' , Запрос4.СуммаПриход, Запрос5.СуммаРасход
    from (
    select sum(ExpenditureEntry.Total) AS СуммаПриход
    from ExpenditureEntry
    where (ExpenditureEntry.ExpenditureTypeID = 1 or ExpenditureEntry.ExpenditureTypeID =5 or ExpenditureEntry.ExpenditureTypeID =6 or ExpenditureEntry.ExpenditureTypeID =7) and ExpenditureEntry.CostCenterID = 20/*РКО*/
    and YEAR(ExpenditureEntry.Date) = YEAR( GETDATE( )) and    MONTH(ExpenditureEntry.Date) between 1 and MONTH(GETDATE( ))-1
       ) as Запрос4,
     
    (select sum(ExpenditureEntry.Total) AS СуммаРасход
    from ExpenditureEntry
    where ExpenditureEntry.CostCenterID = 21 /*ПКО*/ and YEAR(ExpenditureEntry.Date) = YEAR( GETDATE( )) and    MONTH(ExpenditureEntry.Date) between 1 and MONTH(GETDATE( ))-1
       ) as Запрос5


например хочется условие по времени вынести куда-то(функция\процедура) и туда передовать дату. Возможно что-то ещё я сделал

Автор: Akina 28.09.17, 12:55
1) UNION ALL не оптимизируется. Можно оптимизировать только отдельные его подзапросы.
2) Подзапросы независимы, так что параметры нужно передавать в каждый из них.
3) Если хочется передавать параметр один раз - его можно передать через локальную переменную либо создать хранимую процедуру с параметрами.
4) Объединить условия в подзапросах не получится - подзапросы независимы. Да и нет в том смысла (см. п. 1).

Автор: ^D^ima 28.09.17, 14:01
В sqlcmd можно делать переменные, можно ли их потом использовать для всех запросов?
например я использую везде between '01.08.2017' and '31.08.2017 23:59' и можно ли сделать 2 переменные для начальной даты и конечной даты и их распространить на все запросы?

Автор: MIF 29.09.17, 07:20
Цитата ^D^ima @
between '01.08.2017' and '31.08.2017 23:59'

Не знаю, какую БД ты используешь. В MS SQL в условии есть баг. Запись, созданная в 23:59:59.100 в результат не попадет. Лучше использовать ...>= '01.08.2017' and ...<'01.09.2017'

Автор: ^D^ima 29.09.17, 07:52
MIF
MS SQL. Спасибо за совет

Автор: ^D^ima 10.10.17, 14:56
Цитата Akina @
1) UNION ALL не оптимизируется. Можно оптимизировать только отдельные его подзапросы.

Как можно сделать итог суммаприхода, суммарасхода и разница между ними как на картинке?
sql.png (, : 823)

Автор: Akina 10.10.17, 17:09
Цитата ^D^ima @
Как можно сделать итог

WITH ROLLUP

Цитата ^D^ima @
и разница между ними

Ну это уже пусть клиентская часть попыхтит...

Автор: ^D^ima 11.10.17, 07:07
Цитата Akina @
WITH ROLLUP

У меня 4 строки - 4 отдельных запроса, объединенных UNION ALL и в какой секции WITH ROLLUP нужно использовать?

Добавлено
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
    Select QUOTENAME('Выручка консультантов','"') as Вид, СуммаПриход, СуммаРасход
    from (
    Select sum(PaymentDetail.Value) AS СуммаПриход, СуммаРасход = 0
    From    Payment
           left JOIN PaymentDetail ON Payment.ID  = PaymentDetail.PaymentID
     
    where (Payment.FileAs not LIKE N'%-1%') and Payment.PaymentTypeID = 2 and (YEAR(Payment.Date) = YEAR( GETDATE( )) and    MONTH(Payment.Date)=MONTH(GETDATE( ))-1)
    ) as запрос1
     
     
    Union all
     
    Select QUOTENAME('Проектные расходы','"') ,Запрос2.СуммаПриход, Запрос3.СуммаРасход
    from (
    Select sum(PaymentDetail.Value) AS СуммаПриход
    From    Payment
           left JOIN PaymentDetail ON Payment.ID  = PaymentDetail.PaymentID
     
    where (Payment.FileAs LIKE N'%-1%') and Payment.PaymentTypeID = 2 and (YEAR(Payment.Date) = YEAR( GETDATE( )) and    MONTH(Payment.Date)=MONTH(GETDATE( ))-1)
    ) as Запрос2,
    (
    Select sum(Expense.TotalPC) AS СуммаРасход
    From    Expense
          
    where Expense.EmployeeID=1386 and (YEAR(Expense.Date) = YEAR( GETDATE( )) and    MONTH(Expense.Date)=9)
    ) as Запрос3
     
     
    Union all
     
    Select QUOTENAME('Расходы иное','"') ,СуммаПриход=0 , Запрос5.СуммаРасход
    from
    (select sum(ExpenditureEntry.Total) AS СуммаРасход
    from ExpenditureEntry
    where (ExpenditureEntry.ExpenditureTypeID = 1 or ExpenditureEntry.ExpenditureTypeID =6 or ExpenditureEntry.ExpenditureTypeID =7) and ExpenditureEntry.CostCenterID = 20/*РКО*/
    and ( YEAR(ExpenditureEntry.Date) = YEAR( GETDATE( )) and    MONTH(ExpenditureEntry.Date)=MONTH(GETDATE( ))-1)
      ) as Запрос5
      
     
    Union all
     
    Select QUOTENAME('Касса РО','"') ,Запрос6.СуммаПриход , Запрос7.СуммаРасход
    from (
    select sum(ExpenditureEntry.Total) AS СуммаПриход
    from ExpenditureEntry
    where ExpenditureEntry.CostCenterID = 21 /*ПКО*/ and ( YEAR(ExpenditureEntry.Date) = YEAR( GETDATE( )) and    MONTH(ExpenditureEntry.Date)=MONTH(GETDATE( ))-1 )
      
     ) as Запрос6,
     
    (select sum(ExpenditureEntry.Total) AS СуммаРасход
    from ExpenditureEntry
    where ExpenditureEntry.ExpenditureTypeID =5  and ExpenditureEntry.CostCenterID = 20/*РКО*/
    and ( YEAR(ExpenditureEntry.Date) = YEAR( GETDATE( )) and    MONTH(ExpenditureEntry.Date)=MONTH(GETDATE( ))-1)
      ) as Запрос7


Добавлено
Цитата ^D^ima @
WITH ROLLUP

и ещё как понимаю ROLLUP покажет подитоги для разных столбцов "вид", т.е. вместо 5 строки он добавит ещё 5(4 было + 4 подитого + 1 общий итог)

Автор: Akina 11.10.17, 07:38
Цитата ^D^ima @
У меня 4 строки - 4 отдельных запроса, объединенных UNION ALL и в какой секции WITH ROLLUP нужно использовать?

Обернуть всё это внешним запросом, добавить группировку по полю "Вид" и обернуть в SUM() остальные поля.

Автор: ^D^ima 11.10.17, 08:44
Akina
:wacko: :wacko: :wacko:
спасибо

Автор: ^D^ima 11.10.17, 14:44
Цитата Akina @
Обернуть всё это внешним запросом

А в поле select что писать, если там через union 3 отдельных запроса сделаны?

Автор: Akina 11.10.17, 19:40
Цитата ^D^ima @
А в поле select что писать
:wall:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT Вид, SUM(СуммаПриход) СуммаПриход, SUM(СуммаРасход) СуммаРасход
    FROM (твой запрос с 4 подзапросами и 3 UNION-ами) dummy
    GROUP BY Вид WITH ROLLUP

Автор: ^D^ima 12.10.17, 10:34
Цитата Akina @
dummy

вот из-за этой мелочи у меня выдавало ошибку, р-р-р-р-р-р :wall:

Автор: Akina 12.10.17, 11:19
Цитата ^D^ima @
вот из-за этой мелочи у меня выдавало ошибку

ВСЁ должно иметь алиасы. Абсолютно всё. Или должна быть стопроцентная убеждённость, что в данной конкретной точке алиас не обязателен.

Автор: ^D^ima 12.10.17, 11:26
Ясно :)

Автор: ^D^ima 16.10.17, 10:34
Akina
Теоретический вопрос:

если мне в этом-же запрос( Помогите с запросом (сообщение #3744524) )нужно получить результаты его-же но за другой период(некое сальдо), та как это лучше оформить? Как хранимую процедуру и ей передавать даты, или ещё существуют способы? Аля процедура\функция в самом запросе?

Автор: Akina 16.10.17, 11:15
Если безотносительно к конкретному тексту запроса, я верно понимаю, что задача такова: для некоей сложной выборки, в которой в качестве одного из условий вводится некий параметр (дата) нужно получить одновременно результаты этой выборки для некоего набора значений этого параметра?

Если так, я бы, наверное, пошёл именно по пути создания хранимой процедуры, которая в некотором формате примет набор значений этого параметра, для каждого значения получит требуемые результаты, и вернёт весь массив полученных данных во временной таблице с предопределённым именем, после чего он уже будет обрабатываться в соответствии с требуемой логикой. Само собой, в этой временной таблице кроме собственно ответных значений (в запросе по ссылке это поля Вид, СуммаПриход, СуммаРасход) должно быть предусмотрено поле, хранящее значение параметра, для которого получена данная конкретная запись (например, поле Дата, в которую литерально копируется значение даты, на которую рассчитываются заданные значения полей Вид, СуммаПриход и СуммаРасход).

Автор: ^D^ima 16.10.17, 12:21
Akina
Временная таблица должна быть создана в таблицах базы данных?

Добавлено
Цитата Akina @
для некоей сложной выборки, в которой в качестве одного из условий вводится некий параметр (дата) нужно получить одновременно результаты этой выборки для некоего набора значений этого параметра?

именно так.

А то придется в этом-же запросе копировать его ещё раз с другими датами

Автор: ^D^ima 16.10.17, 13:26
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use [PJM10]
    go
    create procedure my1
    @Day char(2), @month char(2)
    as
    begin
    (
      select top 10 Employee.FileAs
     from Employee)
    end
     
     
    go
     
     
     
     my1 'DA','DQ'
     
    /* union all
     
    my1 'DA','DQ'
    */


1 почему предупреждение: "В базе данных уже существует объект с именем "my1"."
2 почему на union all ругается если раскоментить, хотя если вместо него поставить go, то работает, но 2 таблицы выдает?

Автор: Akina 16.10.17, 13:45
https://msdn.microsoft.com/en-us/library/ms...v=sql.105).aspx

1) Прежде чем создавать процедуру, надо проверить существование предыдущей версии, и если она есть - удалить её.
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    IF OBJECT_ID ( 'SchemaName.procedureName', 'P' ) IS NOT NULL
        DROP PROCEDURE SchemaName.procedureName;
    GO

2) Выполнение процедуры без EXECUTE (или сокращённой его формы EXEC) допускается лишь в случае, если её вызов является первым оператором (всего пакета, или отдельного блока пакета, т.е. непосредственно после GO). Во всех остальных случаях EXEC(UTE) обязателен.

Автор: ^D^ima 16.10.17, 13:52
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
     exec my1 'DA','DQ'
     
    union all
     exec my1 'DA','DQ'


так все равно на union all ругается

Автор: Akina 16.10.17, 18:33
Ну ещё бы... ты хоть иногда в мануал-то заглядывай. UNION/EXCEPT/INTERSECT может объединять только запросы, а не любые конструкции, возвращающие набор записей.

Автор: ^D^ima 17.10.17, 07:19
Печально, спасибо.

А как можно взять дату: Первый день прошлого месяца и Последний день прошлого месяца '01.09.2017 00:00:00' - '30.09.2017 23:59:59'?
Первый день взял так
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    select convert(datetime,   '01.'+convert(char(2), month(getdate())-1)+'.'+convert(char(4), year(getdate())) )
но как-то длинно получилось, можно ли короче? А последний вообще не пойму как взять...

Добавлено
Последний день предыдущего месяца взял так:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    EOMONTH(GETDATE( ),-1)
как туда 23:59:59 поставить?

Автор: ^D^ima 17.10.17, 08:55
Получилось 2 вариантами:
не знаю какой более правильный:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    select SMALLDATETIMEFROMPARTS(year(EOMONTH(GETDATE( ),-1)),  month(EOMONTH(GETDATE( ),-1)),day(EOMONTH(GETDATE( ),-1)),'23','59')
     
    select convert(datetime, convert(char(10), EOMONTH(GETDATE( ),-1))+'T23:59:59')


Добавлено
Первый день прошлого месяца:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    select convert(date, dateadd(month,-1,  dateadd(day,1-day(GETDATE( )),GETDATE( )) ) )


Я правильно понимаю если date сравнивают с datetime, то подразумевают что в date время '00:00:00' ??? Т.е. если в date и datetime будет одинаковая дата, то в не зависимости от времени они будут равны?

Добавлено
Пока взял это:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    declare @d1 datetime
    declare @d2 datetime
    set @d1=convert(date, dateadd(month,-1,  dateadd(day,1-day(GETDATE( )),GETDATE( )) ) )  
    set @d2=convert(datetime, convert(char(10), EOMONTH(GETDATE( ),-1))+'T23:59:59')
     
    select @d1
    select @d2

Это я к чему всё, чтобы избавиться от этой конструкции:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    ( YEAR(ExpenditureEntry.Date) = YEAR( GETDATE( )) and    MONTH(ExpenditureEntry.Date)=MONTH(GETDATE( ))-1)

А то получается что, действительно, в январе 2018 я буду получать данные не 12.2017 а 12.2018, хотя я мог бы чтото с IF'ом нагородить и отнимать год если месяц первый

Автор: Akina 17.10.17, 09:56
Тебе нужно взять весь предыдущий месяц, верно? то есть больше или равно 0:00 первого числа и строго меньше 0:00 первого числа следующего (в данном конкретном случае текущего) месяца. Т.е.

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    WHERE mydate >= DATEADD(day,1,EOMONTH(GETDATE(),-2))
      AND mydate <  DATEADD(day,1,EOMONTH(GETDATE(),-1))


Цитата ^D^ima @
Я правильно понимаю если date сравнивают с datetime, то подразумевают что в date время '00:00:00' ???

Тип date не содержит компоненты времени. При сравнении данные приводятся к общему формату, не теряющему точность, т.е. к datetime. Т.е. к date добавляется компонента времени, а раз для неё нет значение, то нулевая. Так что всё правильно.

Цитата ^D^ima @
Это я к чему всё, чтобы избавиться от этой конструкции

В первую очередь ты должен избавиться от любых действий, кроме сравнений, для полей таблицы. Для текущей даты значение вычислится один раз, каким бы ни было сложным выражение, а для значений из таблицы - для каждой записи. Что дольше и затратнее? Опять же - в каком случае можно использовать индекс?

Автор: ^D^ima 17.10.17, 10:04
Цитата Akina @
WHERE mydate >= DATEADD(day,1,EOMONTH(GETDATE(),-2))
  AND mydate <  DATEADD(day,1,EOMONTH(GETDATE(),-1))

Прикольно. Это как-то через between передать можно?

Автор: Akina 17.10.17, 12:44
Цитата ^D^ima @
Это как-то через between передать можно?

Изучи https://msdn.microsoft.com/en-us/library/ms...v=sql.105).aspx , особенно последний пример с пояснениями. Как я из них понял - нельзя, но вдруг я ошибаюсь...

Автор: ^D^ima 15.11.17, 11:58
Akina
Не подскажешь, как-то можно взять итог и подитог только по 2 столбцам?
Есть такой запрос:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
    declare @d1 date
    declare @d2 date
    set @d1=DATEADD(day,1,EOMONTH(GETDATE(),-2))
    set @d2=DATEADD(day,1,EOMONTH(GETDATE(),-1))
     
     
    SELECT   isnull(QUOTENAME(Category.FileAs,'"'),'"Отсутствует"') AS Источник, QUOTENAME(Company.FileAs,'"') AS Компания, Format( sum(PaymentDetail.Value) , N'#.00', N'ru-ru') AS Сумма, isnull(QUOTENAME(Project.Comments,'"'),'""') AS Комментарий
    FROM         PaymentDetail INNER JOIN
                          Payment ON PaymentDetail.PaymentID = Payment.ID INNER JOIN
                          Invoice ON PaymentDetail.InvoiceID = Invoice.ID INNER JOIN
                          Project ON Invoice.ProjectID = Project.ID
                          INNER JOIN Company ON Payment.PayerCompanyID = Company.ID
                          
                           LEFT OUTER JOIN
                          DocumentCategory ON Project.ID = DocumentCategory.DocumentID LEFT OUTER JOIN
                          Category ON DocumentCategory.CategoryID = Category.ID
    WHERE     (Payment.FileAs NOT LIKE N'%-1%') AND (Payment.FileAs NOT LIKE N'%-3%') AND     Payment.Date >=@d1 and  Payment.Date< @d2
    GROUP BY
    rollup(Category.FileAs,Company.FileAs,Project.Comments )


я хочу чтобы rollup был только по Category.FileAs и Company.FileAs

Автор: Akina 15.11.17, 12:20
Цитата ^D^ima @
я хочу чтобы rollup был только по Category.FileAs и Company.FileAs

Используй GROUPING SETS.

Автор: ^D^ima 16.11.17, 07:18
Получилось так:
Я поставил с скобки
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    GROUP BY
    rollup (Category.FileAs,(Company.FileAs,Project.Comments))

:)

Автор: ^D^ima 20.11.17, 11:13
У некоторых компаний несколько отраслей(список с множественным выбором), мне нужно найти компании у который несколько отраслей. Как это сделать?
Я подумал что можно как-то получить количество записей. возвращенных соединением, только как?

Как можно узнать сколько вернуло соединение записей?
Цитата ^D^ima @
LEFT OUTER JOIN

Автор: Akina 20.11.17, 11:46
Цитата ^D^ima @
У некоторых компаний несколько отраслей(список с множественным выбором)

Это - отображение данных. На кое чхать с высокой колокольни. Важно, как это хранится в таблицах.

Цитата ^D^ima @
Как можно узнать сколько вернуло соединение записей?

Группировка и COUNT(), как обычно.

Автор: ^D^ima 20.11.17, 13:04
Цитата Akina @
Группировка и COUNT(), как обычно.

спасибо

Автор: Bas 21.11.17, 15:10
Цитата Akina @
Группировка и COUNT(), как обычно.

COUNT(*) или COUNT(поле)?

Автор: Akina 21.11.17, 17:02
Нет смысла указывать поле - нам не надо отсеивать NULL-значения.

Автор: ^D^ima 05.12.17, 09:39
Если в таблице колонка имеет тип int, а мне нужно ее поделить и получить значение с запятой, как это организовать?
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
    declare @d1 date
    declare @d2 date
    set @d1='01.01.2017'
    set @d2=GETDATE()+1
    declare @sum float
     
    SELECT  Project.FileAs as Проект, sum(@sum=TimeEntry.Duration/60) AS Сумма
    FROM         project
                 inner JOIN  TimeEntry ON   Project.ID = TimeEntry.ProjectID            
    WHERE  Project.Date >=@d1 and  Project.Date< @d2
    Group by  Project.FileAs

TimeEntry.Duration тип int.
ругается на sum(@sum=TimeEntry.Duration/60)

Автор: ^D^ima 12.12.17, 06:15
Друзья, так и не нашел ответа на вопрос.

Автор: Akina 12.12.17, 07:02
Ну типа
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT  Project.FileAs AS Проект, SUM(TimeEntry.Duration)/60.0 AS Сумма
                      /* ну или там   CAST(SUM(TimeEntry.Duration) AS FLOAT)/60 AS Сумма */
    FROM project
    INNER JOIN  TimeEntry ON Project.ID = TimeEntry.ProjectID            
    WHERE Project.Date >=@d1 and  Project.Date< @d2
    GROUP BY Project.FileAs


Цитата ^D^ima @
ругается на

А процититровать ругательства?

Автор: ^D^ima 12.12.17, 07:11
Цитата Akina @
SUM(TimeEntry.Duration)/60.0 AS Сумма

Так возвращает только целые

Цитата Akina @
CAST(SUM(TimeEntry.Duration) AS FLOAT)/60 AS Сумма

Бинго!!!

Только остался вопрос, как округлять?

Цитата Akina @
А процититровать ругательства?

sum(@sum=TimeEntry.Duration/60)
Неправильный синтаксис около конструкции ")".

) помечена красным пунктиром

Автор: Akina 12.12.17, 07:56
Цитата ^D^ima @
как округлять?

ROUND(), вероятно...

Автор: ^D^ima 12.12.17, 11:48
:thanks:
Как можно упростить конструкцию?
Есть 1 селект из 3 запросов, у которых Query1.Проект=Query2.Проект=Query3.Проект, но такая конструкция недопустима. Пока сделал так:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    where Query1.Проект=Query2.Проект and Query2.Проект=Query3.Проект


Как-то можно более грамотно написать?

Автор: Akina 12.12.17, 11:51
Цитата ^D^ima @
Query1.Проект=Query2.Проект=Query3.Проект, но такая конструкция недопустима.

Это почему же? вполне допустима. Просто надо понимать, ЧТО ИМЕННО будет вычислено.

Цитата ^D^ima @
Как-то можно более грамотно написать?

Вот как раз ЭТО - грамотно. При условии, что нет возможности сами запросы объединить...

Автор: ^D^ima 12.12.17, 11:54
Вот полный текст запроса:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
    declare @d1 date
    declare @d2 date
    set @d1='01.01.2017'
    set @d2=GETDATE()+1
     
    Select query1.Проект, query1.[руководитель проекта], round(Query2.Время,2) as Время, Round(Query3.Время_не_рук,2) as "Время не руководителя", round(query1.Сумма/Query2.Время,2)  as "Стоимость часа 5\2"
    from
    (SELECT  Project.FileAs as Проект, Employee.FileAs as "руководитель проекта", sum(PaymentDetail.Value) AS Сумма
    FROM         project
                 left JOIN Invoice ON   Project.ID = Invoice.ProjectID
                 left JOIN PaymentDetail ON PaymentDetail.InvoiceID = Invoice.ID
                 left JOIN Payment ON PaymentDetail.PaymentID = Payment.ID
                 left JOIN Employee on Project.ProjectManagerEmployeeID=Employee.ID
    WHERE    (Payment.FileAs NOT LIKE N'%-1%') AND (Payment.FileAs NOT LIKE N'%-3%') AND Project.Date >=@d1 and  Project.Date< @d2
    Group by  Project.FileAs,Employee.FileAs) as Query1,
     
    (SELECT  Project.FileAs as Проект, CAST(SUM(TimeEntry.Duration) AS FLOAT)/60 AS Время
    FROM         project
                 left JOIN  TimeEntry ON   (Project.ID = TimeEntry.ProjectID)
    WHERE  Project.Date >=@d1 and  Project.Date< @d2
    Group by  Project.FileAs) as Query2,
     
     
    (SELECT  Project.FileAs as Проект, CAST(SUM(TimeEntry.Duration) AS FLOAT)/60 AS Время_не_рук
    FROM         project
                 left JOIN  TimeEntry ON   (Project.ID = TimeEntry.ProjectID) and TimeEntry.EmployeeID<>Project.ProjectManagerEmployeeID
    WHERE  Project.Date >=@d1 and  Project.Date< @d2
    Group by  Project.FileAs) as Query3
     
    where Query1.Проект=Query2.Проект=Query3.Проект
    Order by query1.Проект

на это ругается where Query1.Проект=Query2.Проект=Query3.Проект а именно на второе равно(Неправильный синтаксис около конструкции "=".)

Автор: Akina 12.12.17, 13:31
По-моему, два последних подзапроса можно собрать в один. Будет что-то вроде

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT Project.FileAs as Проект
         , CAST(SUM(TimeEntry.Duration) AS FLOAT)/60 AS Время
         , CAST(SUM(CASE WHEN TimeEntry.EmployeeID<>Project.ProjectManagerEmployeeID
                         THEN TimeEntry.Duration
                         ELSE 0 END) AS FLOAT)/60 AS "Время не руководителя"
    FROM project
    left JOIN  TimeEntry ON Project.ID = TimeEntry.ProjectID
    WHERE  Project.Date >=@d1 and  Project.Date< @d2
    Group by  Project.FileAs

Автор: ^D^ima 16.01.18, 12:22
Опять нужна помощь:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
    declare @d1 date
    declare @d2 date
    set @d1='01.01.2017'
    set @d2=GETDATE()+1
     
     
     
    SELECT  Project.FileAs as Проект, Employee.FileAs as "руководитель проекта",Division.FileAs as Практика ,sum( PaymentDetail.Value) AS Сумма, CostCenter.FileAs as [Центр затрат],  CAST(SUM(TimeEntry.Duration) AS FLOAT)/60 AS Время,
     CAST(SUM(CASE WHEN TimeEntry.EmployeeID<>Project.ProjectManagerEmployeeID
                         THEN TimeEntry.Duration
                         ELSE 0 END) AS FLOAT)/60 AS "Время не руководителя",
    CAST(SUM(CASE WHEN TimeEntry.DivisionID = 7 THEN TimeEntry.Duration
                         ELSE 0 END) AS FLOAT)/60 AS "Время курьеров" ,
    round(Сумма/Время,2)  as "Стоимость часа 5\2"
     
    FROM         TimeEntry
                 left JOIN  Project on TimeEntry.ProjectID=Project.ID
                 left JOIN Invoice ON   Project.ID = Invoice.ProjectID
                 left JOIN PaymentDetail ON PaymentDetail.InvoiceID = Invoice.ID
                 left JOIN Payment ON PaymentDetail.PaymentID = Payment.ID and (Payment.FileAs NOT LIKE N'%-1%') AND (Payment.FileAs NOT LIKE N'%-3%')
                 left JOIN Employee on Project.ProjectManagerEmployeeID=Employee.ID
                 left JOIN Division on Employee.DivisionID=Division.ID
                 left JOIN CostCenter on Project.CostCenterID=CostCenter.ID
    WHERE  TimeEntry.StartTime >=@d1 and  TimeEntry.StartTime< @d2
    group by  Project.FileAs, Employee.FileAs, Division.FileAs , CostCenter.FileAs


ругается на
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    round(Сумма/Время,2)  as "Стоимость часа 5\2"
На Сумма/Время, т.к. выше они объявлены как суммы неких данных. Как их верно сюда запихнуть?

Автор: Akina 16.01.18, 12:35
Вместо них поместить вычисляющие их выражения...
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    round(sum( PaymentDetail.Value)/CAST(SUM(TimeEntry.Duration) AS FLOAT)/60,2)  as "Стоимость часа 5\2"

Автор: ^D^ima 16.01.18, 12:52
Как-то странно считает:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Сумма    Время    Стоимость часа 5\2
    160000,00      2    22,2222222222222

Автор: Akina 16.01.18, 13:11
Скобки ещё нужны:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    round( sum(PaymentDetail.Value) / (CAST(SUM(TimeEntry.Duration) AS FLOAT)/60) , 2) as "Стоимость часа 5\2"

Или последнее деление заменить на умножение:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    round( sum(PaymentDetail.Value) / CAST(SUM(TimeEntry.Duration) AS FLOAT) * 60 , 2) as "Стоимость часа 5\2"

Автор: ^D^ima 16.01.18, 13:20
:thanks:

Автор: ^D^ima 16.01.18, 14:46
Опять не могу элементарное победить:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
    declare @d1 date
    declare @d2 date
    set @d1='01.01.2018'
    set @d2='15.01.2018'
     
     
     
    select Employee.FileAs, count(TimeEntry.FileAs) as "Количество задач", sum(CASE WHEN TimeEntry.Overtime=1 THEN 1  ELSE 0 END) as "Сложных задач",/*sum( CAST(isnull(Category.FileAs,0) as float))--,*/Category.FileAs
    from TimeEntry
    left join Employee on TimeEntry.EmployeeID = Employee.ID
    LEFT JOIN DocumentCategory ON TimeEntry.DocumentID = DocumentCategory.DocumentID
    LEFT JOIN Category ON DocumentCategory.CategoryID = Category.ID
     
    where TimeEntry.DivisionID = 7 and TimeEntry.StartTime >=@d1 and  TimeEntry.StartTime< @d2
    group by Employee.FileAs, Category.FileAs


<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    FileAs  Количество задач Сложных задач Category.FileAs
    Курьер Белогаров 21 0 NULL
    Курьер Костин 4 0 NULL
    Курьер Белогаров 4 0 0
    Курьер Белогаров 2 0 0,5
    Курьер Белогаров 1 1 135
    Курьер Белогаров 1 0 14
    Курьер Белогаров 1 1 220
    Курьер Белогаров 5 0 4
    Курьер Белогаров 2 0 5


Я хочу Category.FileAs конвертировать в число и сложить через CAST(isnull(Category.FileAs,0) as float)):
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
    declare @d1 date
    declare @d2 date
    set @d1='01.01.2018'
    set @d2='15.01.2018'
     
     
     
    select Employee.FileAs, count(TimeEntry.FileAs) as "Количество задач", sum(CASE WHEN TimeEntry.Overtime=1 THEN 1  ELSE 0 END) as "Сложных задач",sum( CAST(isnull(Category.FileAs,0) as float))--,*/Category.FileAs
    from TimeEntry
    left join Employee on TimeEntry.EmployeeID = Employee.ID
    LEFT JOIN DocumentCategory ON TimeEntry.DocumentID = DocumentCategory.DocumentID
    LEFT JOIN Category ON DocumentCategory.CategoryID = Category.ID
     
    where TimeEntry.DivisionID = 7 and TimeEntry.StartTime >=@d1 and  TimeEntry.StartTime< @d2
    group by Employee.FileAs--, Category.FileAs

Сообщение 8114, уровень 16, состояние 5, строка 9
Ошибка при преобразовании типа данных nvarchar к float.

Что ему ещё нужно?

Автор: Akina 16.01.18, 15:35
Судя по показанному результату Employee.FileAs = "Курьер". Не подскажешь, что должно получиться после его декремента?

Автор: ^D^ima 16.01.18, 16:56
Немного не тот столбец, смотри тот где
Null, null, 0, 0.5, 135, 14, 220, 4, 5

Если вместо float поставить int, ошибка невозмлжно преобразовать 0.5, а с типом float вообще такая ошибка как в прошлом посте

Автор: MIF 16.01.18, 18:51
Думаю, что акцесс ожидает точку разделителем целой и дробной части числа, а ты ему скармливаешь запятую.

Автор: ^D^ima 16.01.18, 18:53
MIF
Похоже на то если просто CAST('0,5' as float) сделать, то ошибку вываливает

Автор: MIF 16.01.18, 18:55
Попробую заиенить запятую точкой в Category.Fileas

Автор: ^D^ima 16.01.18, 18:56
Так работает, но как-то монструозно выглядит )
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    sum( CAST(isnull(REPLACE(Category.FileAs,',','.'),0) as float))

Автор: MIF 16.01.18, 18:59
Может в самом аксессе можно сменить локаль с американской на русскую?

Автор: ^D^ima 16.01.18, 19:26
MIF
Может быть, только это не аксесс

Автор: MIF 16.01.18, 19:49
Попробуй format(fileas, ‘N’, ,’en-US’)

Автор: ^D^ima 25.01.18, 10:21
Помогите с идеологией:
1 Есть проект, у него 2 записи о времени
2 У этого проекта 1 счет

если я делаю это 1 запросом
Проект
левое соединение записи о времени
левое соединение счет

То на выходе получаю 2 строки
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Проект1,Запись о времени1, Счет1
    Проект1,Запись о времени2, Счет1


далее если я усложняю, ищу по счету платы, у плат суммы, потом суммирую и т.д. то в результирующей таблице суммы все в 2 раза выше, т.к. изначально 2 строчки было. Как бы мне сделать так 1 запросом чтобы на выходе изначально было:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Проект1,Запись о времени1, Счет1
    Проект1,Запись о времени2, null


Или мне делать 2 подзапроса и соединять по Проекту?

Автор: Akina 25.01.18, 11:57
Цитата ^D^ima @
на выходе получаю 2 строки

Проект1Запись о времени1Счет1
Проект1Запись о времени2Счет1

надо использовать 2 копии таблицы времён и получать выборку
Проект1Запись о времени1Запись о времени2Счет1

Автор: ^D^ima 25.01.18, 13:37
Akina
А если их будет не 2 а 10 к примеру?
И как мне соединить как ты показал?

Автор: Akina 25.01.18, 13:42
Цитата ^D^ima @
А если их будет не 2 а 10 к примеру?

Давай опять отбросим "а если" и будем обсуждать конкретную структуру данных с конкретным наполнением и конкретным желаемым результатом.

Да, про вывод типа

Проект1Запись о времени1Счет1
Проект1Запись о времени2Null

лучше сразу забыть. Просто потому что возникает вполне логичный вопрос - а почему не

Проект1Запись о времени1Null
Проект1Запись о времени2Счет1

Автор: ^D^ima 25.01.18, 14:42
Цитата Akina @
Давай опять отбросим "а если" и будем обсуждать конкретную структуру данных с конкретным наполнением и конкретным желаемым результатом.

Структура данных. По мере подключения через левое соединение:
1 "Проект".
2 "Записи о времени". Может быть несколько в проекте
3 "Счет". Может быть несколько в проекте
4 "Оплата". Может быть несколько по 1 счету.

Вот и получается:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT  Project.ID as 'ИД проекта', Project.FileAs as Проект, (TimeEntry.Duration) AS Время,Invoice.FileAs as 'счет', Invoice.ID as 'ИД счета' , PaymentDetail.ID as 'ИД платежа', PaymentDetail.Value as 'сумма платежа'
     
    FROM       Project
               left JOIN      TimeEntry on  TimeEntry.ProjectID=Project.ID
               left JOIN Invoice ON   Project.ID = Invoice.ProjectID
               left JOIN PaymentDetail ON PaymentDetail.InvoiceID = Invoice.ID
     
    WHERE  TimeEntry.StartTime >=@d1 and  TimeEntry.StartTime< @d2 and Project.FileAs='разблокировка счетов ООО Лети с нами, ООО мой билет'

1 проект - 2 записи о времени - 1 счет - 2 платежа по счету.
Если суммировать столбец сумма платежа по счету он покажет неверную, задвоенную сумму
sql.png (, : 771)

Автор: Akina 25.01.18, 16:39
Цитата ^D^ima @
Структура данных. По мере подключения через левое соединение:
1 "Проект".
2 "Записи о времени". Может быть несколько в проекте
3 "Счет". Может быть несколько в проекте
4 "Оплата". Может быть несколько по 1 счету.

Я не понимаю одного - какая связь между каждым из нескольких времён и каждым из нескольких счетов. Точнее, я не понимаю, как между ними какая-то связь вообще может быть...
Но ты хочешь получить и время, и сумму по каждому счёту в одном наборе данных - значит, связь есть?

Автор: ^D^ima 25.01.18, 16:41
Нужно чтобы на выходе была 1 строка с суммой времени было 60, а суммой суммы 80000

Автор: Akina 25.01.18, 17:15
Цитата ^D^ima @
Нужно чтобы на выходе была 1 строка с суммой времени было 60, а суммой суммы 80000

Т.е. отдельно суммировать время, отдельно деньги...

А теперь повторяю, но с акцентами:

Т.е. отдельно суммировать время, отдельно деньги.

Всё понимаешь? если нет, перевожу: в одном подзапросе суммировать деньги, во втором время, а потом связать их и получить итог.

Автор: ^D^ima 25.01.18, 17:26
Цитата Akina @
Я не понимаю одного - какая связь между каждым из нескольких времён и каждым из нескольких счетов. Точнее, я не понимаю, как между ними какая-то связь вообще может быть...

У меня задача получить отчет по проектам, затраченному времени и полученным деньгам за проект.
Вот и получается что нужно суммировать все время потраченное сотрудникам по проекту и все полученные деньги. Как ещё объяснить?

Я раньше делал через 3 запроса делал(Помогите с запросом (сообщение #3753073) ) потом по твоему совету в 1 соединил. Я могу обратно сделать несколько запросов, только проблема в периоде для отчета.
Требуется взять "записи о времени" за определенный период и из них уже найти проект и все остальное. Я не знаю по какому условию соединить подзапросы. Могут быть варианты когда у проекта нет поступлений или нет записей о времени

Автор: Akina 25.01.18, 17:31
Цитата ^D^ima @
Я не знаю по какому условию соединить подзапросы. Могут быть варианты когда у проекта нет поступлений или нет записей о времени

Ну у тебя же MS SQL, который прекрасно умеет и FULL OUTER JOIN, и CROSS APPLY. Да и оконные функции у него есть, так что можно запросто использовать что-то вроде
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT SUM(payments.payment) OVER (PARTITION BY project.id) AS total_payment

Автор: ^D^ima 26.01.18, 11:06
А как из этого рисунка( http://forum.sources.ru/index.php?act=Atta...attach_id=57837 ) получить
Количество уникальных ИД платежа? Count как понимаю возвращает 4, а нужно 2

Автор: MIF 26.01.18, 11:09
Count(distinct(ид платежа))

Автор: ^D^ima 26.01.18, 17:24
почему такая конструкция не прокатывает:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    (CASE WHEN  count(distinct TimeEntry.id)<>0
                         THEN count(distinct TimeEntry.id)
                         ELSE 1 END)


Добавлено
Я просто хочу в select'е исключить случаи когда count(distinct TimeEntry.id) равно 0. И если оно равно, ставить 1

Добавлено
Вот так помоему тоже не прокатывает, если в запросе при левом соединении нет PaymentDetail.id, говорит деление на ноль:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    count(distinct (case when PaymentDetail.id > 0 then PaymentDetail.id end) )


Добавлено
семен-семеныч:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
     count(distinct (isnull (PaymentDetail.id,1) ) )

:facepalm: :facepalm: :facepalm:

Добавлено
Что-то монструозное получилось, но вродебы как рабочее.
так не понял как делать:
Цитата Akina @
Т.е. отдельно суммировать время, отдельно деньги.


сделал в 1 запросе:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
    declare @d1 date
    declare @d2 date
    set @d1='01.01.2017'
    set @d2=GETDATE()+1
     
     
     
    SELECT  Project.FileAs as Проект, Employee.FileAs as "руководитель проекта",isnull(Division.FileAs,'') as Практика ,
     
     isnull(sum( PaymentDetail.Value)/count(distinct (isnull (TimeEntry.id,1) ) )  ,0 )AS Сумма,
     isnull(CostCenter.FileAs,'')as [Центр затрат],
     
     round( CAST(SUM(TimeEntry.Duration)/ count(distinct (isnull (PaymentDetail.id,1) ) ) AS FLOAT)/60,2) AS Время,
     
     round(CAST(SUM(CASE WHEN TimeEntry.EmployeeID<>Project.ProjectManagerEmployeeID
                         THEN TimeEntry.Duration
                         ELSE 0 END) / count(distinct (isnull (PaymentDetail.id,1) ) ) AS FLOAT)/60,2) AS "Время не руководителя",
    round(CAST(SUM(CASE WHEN TimeEntry.DivisionID = 7 THEN TimeEntry.Duration
                         ELSE 0 END)/ count(distinct (isnull (PaymentDetail.id,1) ) )  AS FLOAT)/60,2) AS "Время курьеров"
    ,round( sum(PaymentDetail.Value) / (CAST(SUM(TimeEntry.Duration) AS FLOAT)/60) , 2) as "Стоимость часа 5\2"
     
    FROM         TimeEntry
                 left JOIN  Project on TimeEntry.ProjectID=Project.ID
                 left JOIN Invoice ON   Project.ID = Invoice.ProjectID
                 left JOIN PaymentDetail ON PaymentDetail.InvoiceID = Invoice.ID
                 left JOIN Payment ON PaymentDetail.PaymentID = Payment.ID and (Payment.FileAs NOT LIKE N'%-1%') AND (Payment.FileAs NOT LIKE N'%-3%')
                 left JOIN Employee on Project.ProjectManagerEmployeeID=Employee.ID
                 left JOIN Division on Employee.DivisionID=Division.ID
                 left JOIN CostCenter on Project.CostCenterID=CostCenter.ID
    WHERE  TimeEntry.StartTime >=@d1 and  TimeEntry.StartTime< @d2 --and Project.FileAs='Ликвидация ООО "Метека"'
    group by  Project.FileAs, Employee.FileAs, Division.FileAs , CostCenter.FileAs
    Order by Практика, [руководитель проекта]

Автор: Bas 30.01.18, 23:55
Цитата ^D^ima @
Что-то монструозное получилось, но вродебы как рабочее.
так не понял как делать

Пять страниц на форуме? :scratch: И всего шесть таблиц связи, для отчета? Не проще(быстрее) функциями это сделать?

Автор: ^D^ima 31.01.18, 06:13
Bas
На самом деле 2 страницы. Я начал первую страницу с 0 знанием SQL. Постепенно я начал получать опыт. Если я сталкиваюсь с трудностью пытаюсь сделать сам, опираясь на опыт, ищу в интернете и только потом лезу на форум. Спасибо Akina и MIF что помогают разбираться в непростом для меня деле. В данной ветке я задавал вопросы трудностям с 6 запросам, так что не думай что 1 запрос обсасываем 9 страниц :)

Добавлено
Цитата Bas @
Не проще(быстрее) функциями это сделать?

Может быть. Но база не наша а стороннего ПО. Я просто оттуда дергаю данные. Не хочется ее захламлять своими поделиями, тем более она раз в 3 месяца обновляется.

Автор: ^D^ima 08.02.18, 13:54
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
    declare @d1 date
    declare @d2 date
    set @d1='01.01.2018'
    set @d2='01.02.2018'--GETDATE()+1
     
    Select project.FileAs, count(TaskRequest.id) as 'Количество выполненных задач'
    From
    TaskRequest
    left join Project on Project.ID = TaskRequest.ProjectID
     
    where TaskRequest.DueTime >=@d1 and  TaskRequest.DueTime<@d2 and (Project.FileAs = 'Задачи ОАиФ' or Project.FileAs ='Задачи Оценка' or Project.FileAs ='Задачи  ОКГ' or Project.FileAs like '%АО ОКГ%')
    and TaskRequest.PercentComplete=1
    group by project.FileAs

Результат в файле

Как-то можно объединить все Project.FileAs like '%АО ОКГ%' в 1 строчку? Или придется делать подзапрос или union all?
sql.png (, : 720)

Автор: ^D^ima 08.02.18, 14:01
Так ближе:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
    declare @d1 date
    declare @d2 date
    set @d1='01.01.2018'
    set @d2='01.02.2018'--GETDATE()+1
     
    Select
    case when project.FileAs not like '%АО ОКГ%' then project.FileAs else 'АО ОКГ' end , count(TaskRequest.id) as 'Количество выполненных задач'
    From
    TaskRequest
    left join Project on Project.ID = TaskRequest.ProjectID
     
    where TaskRequest.DueTime >=@d1 and  TaskRequest.DueTime<@d2 and (Project.FileAs = 'Задачи ОАиФ' or Project.FileAs ='Задачи Оценка' or Project.FileAs ='Задачи  ОКГ' or Project.FileAs like '%АО ОКГ%')
    and TaskRequest.PercentComplete=1
    group by project.FileAs

sql.png (, : 691)

Добавлено
Он group by project.FileAs не выполняет потому что select самым последним выполняется после сбора таблицы? А нельзя ли как-то прямо в from где-то менять названия? Т.е. тот-же case как в select сделать?

Добавлено
Так работает, но можно красивее сделать?
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    use PJM10
    declare @d1 date
    declare @d2 date
    set @d1='01.01.2018'
    set @d2='01.02.2018'--GETDATE()+1
     
    Select
    query1.Проект , sum(query1.[Количество выполненных задач])
     
    From(
    select case when project.FileAs not like '%АО ОКГ%' then project.FileAs else 'АО ОКГ' end  as Проект, count(TaskRequest.id) as 'Количество выполненных задач'
    From
    TaskRequest
    left join Project on Project.ID = TaskRequest.ProjectID
     
    where TaskRequest.DueTime >=@d1 and  TaskRequest.DueTime<@d2 and
    (Project.FileAs = 'Задачи ОАиФ' or Project.FileAs ='Задачи Оценка' or Project.FileAs ='Задачи  ОКГ' or Project.FileAs like '%АО ОКГ%')
    and TaskRequest.PercentComplete=1
    group by project.FileAs) as query1
    group by Проект

Автор: MIF 11.02.18, 07:11
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Select
    case when project.FileAs not like '%АО ОКГ%' then project.FileAs else 'АО ОКГ' end , count(TaskRequest.id) as 'Количество выполненных задач'
    ..;
    group by case when project.FileAs not like '%АО ОКГ%' then project.FileAs else 'АО ОКГ' end

Автор: ^D^ima 11.02.18, 07:49
Цитата MIF @
group by case when project.FileAs not like '%АО ОКГ%' then project.FileAs else 'АО ОКГ' end

шикарно :)

Автор: ^D^ima 13.04.18, 13:28
Как-то можно в поле select, выражение присвоенное через AS ещё раз использовать в том-же Select?
Типа этого:
Select Что_то_там As q1,
q1*2 as q2
А то у меня выдает ошибку:
https://yadi.sk/i/b2aDszRH3UPKaw

Автор: MIF 13.04.18, 15:38
Нельзя. Поле не существует, пока рекордсет не создан.

Автор: Akina 13.04.18, 18:13
Цитата ^D^ima @
в поле select, выражение присвоенное через AS ещё раз использовать в том-же Select?

Зависит от диалекта.

Например, в MS Access это делается именно так, как ты пишешь.
В MySQL это будет так: Select @q1:=Что_то_там as q1, @q1*2 as q2
В MS SQL... не знаю, можно или нет.

И так далее - нестандарт везде свой.

Автор: MIF 14.04.18, 01:22
можно q1 вподзапросе вычислить.

Автор: ^D^ima 15.05.18, 14:53
Как проще like и in совместить?
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Project.ContractNumber like in('ЮК%','АО%','АС%','ЮКПРС%','Б%','Иное ПРС%','ЮК%','СОЮ%','Включение%','ПК%','И%','Л%','М%','Р%','Иное НР%')


а то выдает ошибку. Не хочется через or повторять бесконечно Project.ContractNumber like 'ЮК%' or Project.ContractNumber like 'АО%' и.т.д.

Автор: Akina 15.05.18, 16:01
Используй RLike и собери всё в один шаблон.

Автор: ^D^ima 15.05.18, 19:44
Цитата Akina @
Используй RLike и собери всё в один шаблон.

А разве в MS SQL это есть?

Автор: Akina 15.05.18, 20:02
В MS SQL тоже есть регэкспы.

Автор: MIF 16.05.18, 11:17
Запиши строки поиска в отдельную таблицу.
Добавь JOIN в свой запрос:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    JOIN MyTable mt ON Project.ContractNumber like mt.MyField

Ну и еше надо добавить DISTINCT или GROUP BY, чтобы исключить повторы.

Автор: ^D^ima 16.05.18, 18:23
MIF а эту таблицу придется удалять и создавать в запросе каждый раз?

Автор: MIF 16.05.18, 21:41
Если строки живут долго, то лучше хранить их в постоянной таблице.
Если они меняются в каждом запросе, то лучше создавать временныйы таблицу в запросе.

Автор: Akina 17.05.18, 04:59
Цитата ^D^ima @
а эту таблицу придется удалять и создавать в запросе каждый раз?

Ответ зависит от двух факторов. Первый - изменчивость исходных данных и соответственно данных в этой таблице. Второй - расход ресурсов на получение данных для такой таблицы. На основании этих данных следует сделать выбор либо в пользу скорости ценой точности/актуальности, либо наоборот.
Если решите создавать каждый раз - используйте CTE (WITH Clause).

Автор: ^D^ima 06.07.18, 14:06
Друзья, какая агрегатная функция не суммирует а вычитает?

Автор: Akina 06.07.18, 15:23
А что из чего вычитать-то?

Автор: ^D^ima 06.07.18, 18:58
Есть числовой столбец, нужно его не суммировать, а отнять,сгрупировав по 2му полю

Автор: Akina 06.07.18, 20:03
:facepalm:

В группе по некоему значению "второго поля" есть 4 записи со следующими значениями "числового столбца": 1,4,11,30. Каким должно быть значение, возвращаемое "функцией группового вычитания"? Как оно получено? И почему именно так?

Автор: JoeUser 06.07.18, 20:40
Цитата ^D^ima @
Есть числовой столбец, нужно его не суммировать, а отнять

Цитата Akina @
:facepalm:

:lol:

Групповое вычитание = -(групповое сложение) :jokingly:

Автор: ^D^ima 06.07.18, 21:02
JoeUser
Не так. 2+2=4. Со знаком минус будет -4, а должно быть 0(2-2)


Akina
1-4-11-30
Нужно сагрегировать результат, как ари SUM, только с вычитанием каждого, а не сложением.
Ну или взять каждый элемент, и подставить минус. Типа Sum(- элемент) . Сами значения элементов положительные числа, нужно найти значение их разниц

Автор: Akina 07.07.18, 09:07
Почему начали вычитать именно из 1, а не из, скажем, 11?

Автор: MIF 07.07.18, 10:36
Суммируешь все записи кроме ... кроме той, которую ты считаешь первой. И из нее вычитаешь сумму.

Автор: Akina 07.07.18, 15:27
MIF, в терминах SQL лучше суммить всё, и вычитать из удвоенного "избранного". В любом случае это таки сложение, а не вычитание.

Автор: JoeUser 07.07.18, 18:24
Цитата ^D^ima @
а должно быть 0(2-2)

Это с какого перепугу?
Суммируем мы же с нуля, не??? 0+значение записи1+значение записи2+ ... значение записиN
А чем вычитание "лучше"???
Скрытый текст
Просто есть подозрение что ты развиваешь тему "Как с помощью SQL зОхватить мир?" 8-)


Добавлено
Цитата MIF @
Суммируешь все записи кроме ... кроме той, которую ты считаешь первой. И из нее вычитаешь сумму.

Вот! Это четко)

Автор: ^D^ima 09.07.18, 07:32
Зайду с другой стороны.
Коллеги, в общем ситуация такая. Есть таблица на выходе (всего по 2 цифры на первый столбец):
Клиент1 100
Клиент1 250
Клиент2 300
Клиент2 500

Нужно получить:
Клиент1 150 (по модулю 100-250 или 250-100 нет разницы)
Клиент2 200 (по модулю 300-500 или 500-300 нет разницы)

Автор: Akina 09.07.18, 08:13
Цитата ^D^ima @
всего по 2 цифры на первый столбец

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    MAX(field) - MIN(field) AS delta

Автор: ^D^ima 09.07.18, 10:28
Цитата Akina @
MAX(field) - MIN(field) AS delta

Гениально!!!

Автор: ^D^ima 11.07.18, 13:42
Где найти описание шаблонов при PATINDEX ?

У меня такая строка: "Отгрузка апрель 2018 - 50%" нужно вычленить проценты. Проценты могут быть от 0 до 100, т.е. как 1 символ, так и 3. Я думаю через PATINDEX найти вхождение этих чисел от первого пробела с права до %. Какой шаблон мне применить при поиске?

Добавлено
Можно так: REVERSE( SUBSTRING(REVERSE('Отгрузка май 2018 100%'),2, CHARINDEX(' ',REVERSE('Отгрузка май 2018 100%') )-1 ) )
Но это коряво как-то. :wacko:

Автор: ^D^ima 11.07.18, 14:50
А ещё лучше взять как-то последнюю группу цифр, т.е. варианты могут быть в теории - 50%, -50 %, - 50 % и надо как-то взять справа от первой цифры до последней цифры следовавшей подряд

Автор: ^D^ima 16.07.18, 11:17
Вот так сделал, но это ещё корявее. Ищем первую цифру. Берем от первой цифры до конца. Идем первую нецифру в найденной подстроке.
REVERSE(substring(REVERSE(TaskRequest.FileAs), PatIndex('%[0-9]%', REVERSE(TaskRequest.FileAs)), PatIndex('%[^0-9]%', substring(REVERSE(TaskRequest.FileAs), PatIndex('%[0-9]%', REVERSE(TaskRequest.FileAs)), len(TaskRequest.FileAs)))-1))

Как-то можно оптимизировать?

Автор: Akina 16.07.18, 12:03
Цитата ^D^ima @
Где найти описание шаблонов при PATINDEX ?

https://docs.microsoft.com/ru-ru/sql/t-sql/...sql-server-2017

Цитата ^D^ima @
варианты могут быть в теории - 50%, -50 %, - 50 % и надо как-то взять справа от первой цифры до последней цифры следовавшей подряд


pos = charindex(str, '%')
str = left(str, pos-1)
str = trim(str)
str1 = reverse(str)
pos = patindex(str1, '[^0-9]')
str = right(str, pos-1)

Автор: ^D^ima 18.07.18, 11:38
Спасибо.

Как мне узнать каким ключом связаны 2 таблицы?

Делаю зависимость через Studio и не понимаю:

https://yadi.sk/i/qOGNrzvH3ZJnkK

Мне нужно Budget с Project связать

Автор: Akina 18.07.18, 12:29
Database Diagrams в SSMS. Или запросить скрипт создания - там будет и FK.

Автор: ^D^ima 18.09.18, 10:26
Коллеги, подкиньте идею.

Есть например 2 таблицы.
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    ID, ФИО
    1 Иванов
    2 Петров
    3 Сидоров
     
    ID, Дата, Сумма
    1 01.01.2018 100руб
    1 01.02.2018 200руб
    2 01.05.2018 200руб


Как на выходе получить Таблицу такого плана:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    ФИО, Январь, Февраль, март, Апрель, Май
    Иванов 100р.   200р.
    Петров                              200р.



Т.е. в зависимости от даты разносить суммы в столбцы месяцев.

Через case лучше делать типа Case month(Дата)=1 then сумма as 'Январь' и так 12 раз или есть какая-то конструкция лучше?

Автор: Akina 18.09.18, 11:00
PIVOT

Автор: ^D^ima 18.09.18, 11:39
А можно ли как-то использовать переменную в select?
Например Что_то_там это длинная формула расчета и что-бы ее не переписывать несколько раз в select

Select Что_то_там as 'Значение1', 'Значение1'-5 as 'Значение5'

Автор: Akina 18.09.18, 12:48
WITH common_table_expression (Transact-SQL)

Автор: ^D^ima 20.09.18, 06:19
Akina
Спасибо за помощь

Автор: ^D^ima 20.09.18, 12:13
А как передать параметр в подзапрос?
например мне нужно А1 передать в подзапрос как условие к where
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Select A1, (select B1 from table2 where table2.c1=A1)
    From table1

Автор: Akina 20.09.18, 13:37
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Select table1.A1, (select table2.B1 from table2 where table2.c1=table1.A1)
    From table1

Но с большой вероятностью запрос приведёт к ошибке, если на table2.c1 не наложено требование уникальности.

Автор: ^D^ima 20.09.18, 13:46
Ещё такой вопрос:
Есть таблица:

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    FIO,Number
    Иванов 4
    Иванов 3
    Иванов 2
    Иванов 1
    Сидоров 8
    Сидоров 7
    Сидоров 6
    Сидоров 5


Нужно сгруппировать по FIO, по возрастанию Number и взять первые 2 выборки в каждой

Т.е. на выходе должно быть это:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    FIO,Number
    Иванов 1
    Иванов 2
    Сидоров 5
    Сидоров 6

Автор: Akina 20.09.18, 16:26
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    select fio, number
    from
    (select fio, number, row_number() over (partition by fio order by number asc) rn
    from table) x
    where rn < 3

Автор: ^D^ima 21.09.18, 07:07
Akina, Мощно!!!

Как можно из этого запроса:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    select   Employee.FileAs, project.FileAs, TaskRequest.FileAs
    ,Cast( REVERSE(substring(REVERSE(TaskRequest.FileAs), PatIndex('%[0-9]%', REVERSE(TaskRequest.FileAs)), PatIndex('%[^0-9]%', substring(REVERSE(TaskRequest.FileAs), PatIndex('%[0-9]%', REVERSE(TaskRequest.FileAs)), len(TaskRequest.FileAs)))-1))as int)
    ,row_number() over (partition by Employee.FileAs, project.FileAs order by Cast( REVERSE(substring(REVERSE(TaskRequest.FileAs), PatIndex('%[0-9]%', REVERSE(TaskRequest.FileAs)), PatIndex('%[^0-9]%', substring(REVERSE(TaskRequest.FileAs), PatIndex('%[0-9]%', REVERSE(TaskRequest.FileAs)), len(TaskRequest.FileAs)))-1))as int)  DESC) as rn
     
     
    from
    TaskRequest
    left join Project on TaskRequest.ProjectID=Project.ID
    Left join Employee on Employee.ID=TaskRequest.EmployeeID
    Left join CostCenter on CostCenter.id= Project.CostCenterID
     
    Where TaskRequest.EmployeeID is not null and TaskRequest.FileAs like N'%Отгрузка%' and TaskRequest.FileAs like N'%[%]' and year(TaskRequest.DueTime)=year(@d1)


избавиться от записи 2-х раз одной и той-же формулы
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Cast( REVERSE(substring(REVERSE(TaskRequest.FileAs), PatIndex('%[0-9]%', REVERSE(TaskRequest.FileAs)), PatIndex('%[^0-9]%', substring(REVERSE(TaskRequest.FileAs)

Автор: ^D^ima 21.09.18, 08:15
Ещё такой вопрос:
Результат отчета:

SQL2.png (, : 861)

Как мне сделать группировку по 'сотрудник' и 'проект' и при этом найти разницу процентов?
Может быть как одна выдача процентов группы 'сотрудник' и 'проект', так и две.

В прошлые разы было предложено решение сделать
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    max(query1.Процент)-min(query1.Процент)
    Group by query1.Сотрудник,query1.Проект

Но если у группы 'сотрудник' и 'проект' одна выдача, то max(query1.Процент)-min(query1.Процент) даст 0, т.к. max(query1.Процент) и min(query1.Процент) будут совпадать.

Нужно как-то найти количество записей в группах и сделать условие что если кол-во таких групп 2 то берется max(query1.Процент)-min(query1.Процент) в противном случаи только query1.Процент

Автор: Akina 21.09.18, 08:42
Цитата ^D^ima @
избавиться от записи 2-х раз одной и той-же формулы

Ну я же уже давал ссылку на CTE...

Цитата ^D^ima @
если у группы 'сотрудник' и 'проект' одна выдача, то max(query1.Процент)-min(query1.Процент) даст 0, т.к. max(query1.Процент) и min(query1.Процент) будут совпадать.

Нужно как-то найти количество записей в группах и сделать условие что если кол-во таких групп 2 то берется max(query1.Процент)-min(query1.Процент) в противном случаи только query1.Процент

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    CASE WHEN MAX(field) = MIN(field)
         THEN MAX(field)
         ELSE MAX(field) - MIN(field)
    END

Автор: ^D^ima 21.09.18, 08:56
Цитата Akina @
CASE WHEN MAX(field) = MIN(field)
     THEN MAX(field)
     ELSE MAX(field) - MIN(field)
END

гениально! не додумался до такой простой вещи. Уже хотел ноль везде дописывать, респектую. :thanks:

Автор: ^D^ima 21.09.18, 10:53
Не пойму алгоритмически как решить задачу:
У нас есть таблица
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Дата, Сумма
    01.01.2018 100руб
    01.02.2018 100руб
    01.03.2018 100руб
    01.04.2018 100руб
    01.05.2018 100руб


Мы хотим взять все суммы с даты1 по дату2, но при условии что есть запись на дату2. Т.е. если записи на дату 2 нет, то не нужно вообще ничего брать

Select 'Сумма'
From table1
Where 'Дата'>=@дата1 and 'Дата'<=@дата2 and ???

Где-то нужно case впихнуть, не пойму где

Можно было бы в Select впихнуть, но хотелось бы что-бы вообще не было строк выдачи, да и в select неясно что писать

Автор: Akina 21.09.18, 11:30
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    AND SUM(CASE WHEN 'Дата'=@дата2 THEN 1 ELSE 0 END)>0

Автор: ^D^ima 21.09.18, 11:41
не заработало:
Цитата
Статистическое выражение не может использоваться в предложении WHERE, если оно не содержится во вложенном запросе предложения HAVING или в списке выбора, и столбец, подвергаемый статистической обработке, не является внешней ссылкой.


Добавлено
Но в Select такая конструкция приемлема, спасибо.

Автор: Akina 24.09.18, 10:49
Цитата ^D^ima @
Но в Select такая конструкция приемлема, спасибо.

Такая конструкция либо помещается в HAVING, либо используется в коррелированном WHERE [NOT] EXISTS.

Автор: ^D^ima 24.09.18, 11:23
Спасибо. Некоторые конструкции мне тяжело переваривать :)

Автор: MIF 25.09.18, 09:46
ЕслИ "ничего не брать " означает ноль, то NULL надо заменить на 0.
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT a.Summa * b.Cnt
    FROM
    (
    Select 'Summa'
    From table1
    Where 'Дата'>=@дата1 and 'Дата'<=@дата2
    ) as a
    CROSS APPLY
    (SELECT CASE WHEN
     COUNT(*) >0 THEN 1 ELSE NULL END) AS Cnt
    FROM Tabe1
    Where 'Дата'=@дата2
    )as b

Автор: MIF 25.09.18, 09:52
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Select 'Сумма' * CASE WHEN Max('Дата')=@дата2 THEN 1 ELSE 0 END
    From table1
    Where 'Дата'>=@дата1 and 'Дата'<=@дата2

Автор: ^D^ima 16.01.19, 15:13
Вопрос про запись данных. Никогда этим не занимался.

Есть такой запрос
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    select Company.FileAs
    from
    Project
    left JOIN Company ON Company.ID = Project.CustomerCompanyID
     
    where ((Project. EndDate BETWEEN '01.01.2017' AND '01.01.2019')or (Project.ContractDate<'01.01.2017'and Project.State<>4)) and Company.FileAs is not null


нужно для всех Company.FileAs которые попали в отбор сделать следующее:
Взять Company.ID

И если в таблице DocumentCategory нет записи(таблица из 3 реквизитов)

DocumentClass=24,DocumentID=Company.ID,CategoryID=12 ее создать

Автор: Akina 16.01.19, 17:51
Цитата ^D^ima @
для всех Company.FileAs которые попали в отбор сделать следующее:
Взять Company.ID
Тупо зачеркнуть в SELECT одно имя поля и вставить другое.

Цитата ^D^ima @
DocumentClass=24,DocumentID=Company.ID,CategoryID=12
Соответственно добавить в список выбираемых полей два INTEGER литерала.

Цитата ^D^ima @
если в таблице DocumentCategory нет записи
Поле DocumentID - уникальное? тогда просто вставляй. Если запись есть - будет ошибка вставки, и хрен бы с ею.
Иначе MERGE ... WHEN NOT MATCHED THEN INSERT

Автор: ^D^ima 16.01.19, 21:08
Akina
Как-то так? Боюсь запускать :D
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    insert into  DocumentCategory(DocumentClass,DocumentID,CategoryID)
    select 24,Company.ID,12
    from
    Project
    left JOIN Company ON Company.ID = Project.CustomerCompanyID
     
     
    where ((Project. EndDate BETWEEN '01.01.2017' AND '01.01.2019')or (Project.ContractDate<'01.01.2017'and Project.State<>4)) and Company.FileAs is not null
    group by Company.id


Добавлено
Кстати как-то можно это выполнить в отладочном режиме без записи самих данных в таблицу? Т.е. чтобы просто увидеть что он на выходе захочет написать, а то каждый раз создавать тестовую базу ради 1 прогона не очень

Добавлено
Цитата Akina @
Поле DocumentID - уникальное?

А вот и нет к сожалению :(

Получается как-то так тогда?
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    MERGE DocumentCategory
    USING (select 24,Company.ID,12
    from
    Project
    left JOIN Company ON Company.ID = Project.CustomerCompanyID
     
     
    where ((Project. EndDate BETWEEN '01.01.2017' AND '01.01.2019')or (Project.ContractDate<'01.01.2017'and Project.State<>4)) and Company.FileAs is not null
    group by Company.id)
    WHEN NOT MATCHED THEN INSERT VALUES(DocumentClass,DocumentID,CategoryID)

Автор: Akina 17.01.19, 04:35
Цитата ^D^ima @
как-то можно это выполнить в отладочном режиме без записи самих данных в таблицу?

Конечно... комментируешь первую строку - остаётся нормальный SELECT.

Вернее, не очень нормальный. У тебя:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    from Project
    left JOIN Company ON Company.ID = Project.CustomerCompanyID
    where (что-то) and Company.FileAs is not null

что вырождается в
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    from Project
    inner JOIN Company ON Company.ID = Project.CustomerCompanyID
    where (что-то)

Зачем заставлять сервер делать ненужную работу?

Цитата ^D^ima @
Получается как-то так тогда?

Нет. У тебя отсутствует:
- класс совмещения данных ON <merge_search_condition> - т.е. собственно то условие, по которому будет определяться, MATCHED текущая запись или NOT MATCHED.
- описание источника AS table_alias ( column_alias [ ,...n ] ), соответственно неверна ссылка на вставляемые значения.
Т.е. должно быть что-то типа

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    MERGE DocumentCategory AS target /* Слияние с чем */
    USING ( select 24,Company.ID,12
            from Project
            inner JOIN Company ON Company.ID = Project.CustomerCompanyID
            where ((Project. EndDate BETWEEN '01.01.2017' AND '01.01.2019')or (Project.ContractDate<'01.01.2017'and Project.State<>4))
            group by Company.id) AS source (DocumentClass,DocumentID,CategoryID) /* Слияние откуда */
    ON (target.DocumentID = source.DocumentID) /* Слияние по условию */
    WHEN NOT MATCHED THEN /* действие при невыполнении условия */
        INSERT (DocumentClass,DocumentID,CategoryID) /* в какие поля вставлять */
        VALUES (source.DocumentClass,source.DocumentID,source.CategoryID) /* какие данные вставлять */

Автор: ^D^ima 17.01.19, 06:30
Цитата Akina @
ON (target.DocumentID = source.DocumentID) /* Слияние по условию */

А остальные условия не должны там быть?
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    ON (target.DocumentClass=24, target.DocumentID = source.DocumentID, target.CategoryID=12)

Автор: Akina 17.01.19, 11:31
Цитата ^D^ima @
А остальные условия не должны там быть?

Там должно быть выражение, по которому выявляется дубликат. У тебя дубликат - по одному полю.

Автор: ^D^ima 17.01.19, 12:15
Цитата Akina @
У тебя дубликат - по одному полю.

По 3 получается.

Т.е. если в таблице нет записи 24,1023,12 то она создается, если есть не создается. Но если смотреть просто по 2 полю, например есть ли такое число 1023, то это не верно, т.к. уже писал что поле не уникально

Автор: Akina 18.01.19, 04:43
ааа, вон оно как... тогда да, все три поля...

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    ON (targetDocumentClass = 24 AND target.DocumentID = source.DocumentID AND target.CategoryID = 12)

Автор: ^D^ima 23.01.19, 14:02
На что ругается?
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Сообщение 547, уровень 16, состояние 0, строка 5
    Конфликт инструкции MERGE с ограничением FOREIGN KEY "DocumentCategoriesFK". Конфликт произошел в базе данных "PJM10", таблица "dbo.Document".

sql.png (, : 1174)

Автор: ^D^ima 23.01.19, 15:04
В этой базе есть хранимая процедура, которая как я понимаю и отвечает за добавление категории в документ, может быть эту процедуру использовать? В том плане что вызвать?
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    USE [PJM10]
    GO
    /****** Object:  StoredProcedure [dbo].[adfDocumentCategoryUpdate]    Script Date: 23.01.2019 17:47:34 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    /******************************************************************************
    **      File:
    **      Name: adfDocumentCategoryUpdate
    **      Desc:
    **
    **      This template can be customized:
    **              
    **      Return values:
    **
    **      Called by:  
    **              
    **      Parameters:
    **      Input                           Output
    **      ----------                      -----------
    **
    **      Auth:
    **      Date:
    *******************************************************************************
    **      Change History
    *******************************************************************************
    **      Date:       Author:         Description:
    **      --------    --------        -------------------------------------------
    **      19.02.13    SAP             Отказ от OPENXML
    *******************************************************************************/
    ALTER Procedure [dbo].[adfDocumentCategoryUpdate]
        @DocumentID TIdentifier,
        @DocumentClass TClass,
        @Xml XML
    AS
        DECLARE @ID TIdentifier
        SET NOCOUNT ON
     
        DECLARE categories CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
        SELECT
            tab.col.value('Name[1]','nvarchar(256)'),
            tab.col.value('State[1]','int')
        FROM @Xml.nodes('//Item') tab(col)
     
        OPEN categories
        DECLARE @name TName, @state INT
        
        FETCH NEXT FROM categories INTO @name, @state
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @ID = dbo.adfGetCategoryID(@name, @DocumentClass)
            PRINT @name
            PRINT @ID
            IF @state = 2 -- added
            BEGIN
                IF NOT EXISTS(SELECT * FROM [DocumentCategory] WHERE [DocumentID] = @DocumentID AND [DocumentClass] = @DocumentClass AND [CategoryID] = @ID)
                BEGIN
                    INSERT [DocumentCategory] ([DocumentID], [DocumentClass], [CategoryID])
                    VALUES(@DocumentID, @DocumentClass, @ID)
                END
            END
            ELSE IF @state = 3 -- deleted
            BEGIN
                IF EXISTS(SELECT * FROM [DocumentCategory] WHERE [DocumentID] = @DocumentID AND [DocumentClass] = @DocumentClass AND [CategoryID] = @ID)
                BEGIN
                    DELETE [DocumentCategory] WHERE [DocumentID] = @DocumentID AND [DocumentClass] = @DocumentClass AND [CategoryID] = @ID
                END
            END
            IF @@error != 0 GOTO ERROR_HANDLER
            FETCH NEXT FROM categories INTO @name, @state
        END
        
        CLOSE categories
        DEALLOCATE categories
        
        RETURN 0
    ERROR_HANDLER:
        CLOSE categories
        DEALLOCATE categories
        
        RETURN 1


Кстати там не MARGE используется почему-то а INSERT при IF EXISTS, так лучше?

Добавлено
Он вот с таким запускается параметрами:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    declare @p3 xml
    set @p3=convert(xml,N'<List><Item><Name>Новый Клиент</Name><State>2</State></Item></List>')
    exec adfDocumentCategoryUpdate @DocumentID=1042,@DocumentClass=9,@Xml=@p3


Я правильно понимаю что можно тупо этот код напрямую вызывать, как-то обернув через select? Не подскажешь как?
Я имею в виду что вызвать эту процедуру для каждой выборки select?
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    select 24,Company.ID,12
            from Project
            inner JOIN Company ON Company.ID = Project.CustomerCompanyID
            where ((Project. EndDate BETWEEN '01.01.2017' AND '01.01.2019')or (Project.ContractDate<'01.01.2017'and Project.State<>4))
            group by Company.id

Автор: ^D^ima 22.03.19, 10:25
Друзья, видимо пятница, не пойму как решить простую с виду задачу:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    Взять клиентов, проекты по которым были в 2016-2017 годах, но не были в 2018-2019

Сделал так вначале, но понял что это не сработает
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    select query1.ID,query1.FileAs
    from
     
    (select Company.ID, Project.FileAs
    from Company
    inner JOIN Project ON Company.ID = Project.CustomerCompanyID
    where (Project.Date BETWEEN '01.01.2016' AND '01.01.2018' and Project.State=4/*Завершен*/)
    group by Company.id,Project.FileAs) as query1,
     
    (select Company.ID
    from Company
    inner JOIN Project ON Company.ID = Project.CustomerCompanyID
    where not (Project.Date BETWEEN '02.01.2018' AND '01.01.2019')
    group by Company.id) as query2
     
    where query1.ID=query2.ID

Т.е. идея была взять компании с проектами в 2016-2017 и отдельно без проектов в 2018-2019 и соединить по ID, но получается что 2-й подзапрос не работает, т.к. там будут те-же клиенты что и из 1-го запроса

Автор: LMM 22.03.19, 19:18
А просто по-деревенски выбрать из Company клиентов, для которых where (Project.Date BETWEEN '01.01.2016' AND '01.01.2018' and Project.State=4/*Завершен*/) не рулит? Или я чего-то не понял?

Автор: ^D^ima 22.03.19, 19:41
LMM
А как ты поймешь что по ним не было проектов в 18 и 19 годах?

Автор: LMM 23.03.19, 06:32
Цитата ^D^ima @
Взять клиентов, проекты по которым были в 2016-2017 годах, но не были в 2018-2019

Цитата ^D^ima @
А как ты поймешь что по ним не было проектов в 18 и 19 годах?

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    select c.Id, p.FileAs
      from company c
     inner join projects p on p.CustomerCompanyID = c.Id
       and ((p.Date between '01.01.2016' and '01.01.2018' and p.State = 4) and (p.Date not between '01.01.2016' and '01.01.2018'))
     order by c.Id, p.FileAs

^D^ima, сам понимаешь - без живой БД всё это пляски с бубном :( .

Автор: Akina 23.03.19, 17:24
Цитата ^D^ima @
А как ты поймешь что по ним не было проектов в 18 и 19 годах?

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    GROUP BY проект
    HAVING SUM(CASE WHEN год IN (2016, 2017) THEN 1 ELSE 0 END) > 0
       AND SUM(CASE WHEN год IN (2018, 2019) THEN 1 ELSE 0 END) = 0

Автор: ^D^ima 25.03.19, 06:17
Akina
Гениально!

Автор: ^D^ima 16.01.20, 22:24
10 месяцев не писал запросов и опять память подводит, час потратил, не решил.

Есть дерево проектов.
Как мне его свернуть до корневых проектов, суммировав деньги?

Есть таблица
ID_КорневойПроектID_ПодчиненныйПроектID_ПроектПроектДеньги
211Проект 1500
233Проект 3200
nullnull2Проект 2300
nullnull4Проект 42000


На выходе должно быть:
ID_КорневойПроектID_ПодчиненныйПроектID_ПроектПроектДеньги
nullnull2Проект 21000
nullnull4Проект 42000

Все записи где есть ID_КорневойПроект должны схлопнуться в ID_Проект с суммированием денег. Название должно остаться корневого проекта

Автор: Akina 17.01.20, 04:40
Для SQL Server:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    WITH cte AS ( SELECT id id_k, id_p, id, p, money
                  FROM test
                  WHERE id_k IS NULL
                UNION ALL
                  SELECT cte.id_k, test.id_p, test.id, test.p, test.money
                  FROM test, cte
                  WHERE cte.id = test.id_k )
     
    SELECT test.id, test.id_p, test.id, test.p, SUM(cte.money) money
    FROM cte, test
    WHERE cte.id_k = test.id
    GROUP BY test.id, test.id_p, test.id, test.p

fiddle

Для MySQL/MariaDB/SQLite/PosttgreSQL добавить RECURSIVE.

Автор: ^D^ima 17.01.20, 04:50
Спасибо!!! Проверю

Автор: Akina 17.01.20, 05:23
^D^ima
PS. На будущее - не ленись указывать СУБД и её версию в КАЖДОМ вопросе...
PPS. Чего проверять-то - вон он, линк на fiddle. Но предупреждаю сразу - если попадётся кольцо ссылок, будет плохо. Проверку я не делал.

Автор: ^D^ima 17.01.20, 06:43
Akina
Твой пример сломал мне мозг :wall:
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    WITH cte AS ( SELECT id id_k, id_p, id, p, money
                  FROM test
                  WHERE id_k IS NULL
                UNION ALL
                  SELECT cte.id_k, test.id_p, test.id, test.p, test.money
                  FROM test, cte
                  WHERE cte.id = test.id_k )


Это как вообще?

До Union all понятно, а SELECT cte.id_k и FROM test, cte не ясно. Это какая-то рекурсия? Т.е. он доходит до FROM test, cte и что что вернет в cte? что было до UNION ALL?
Сатанизм какой-то...

Автор: Akina 17.01.20, 14:16
Цитата ^D^ima @
Это какая-то рекурсия?

Точно. Таблица CTE накапливает записи, поставляемые запросом, и в то же время используется как источник данных для его второй части. Та часть запроса, что до UNION, выполняется один раз и в самом начале, выбирая некие записи в CTE. Затем выполняется вторая часть, много раз, при этом используются те записи из накопленных уже в CTE, которые ранее во второй части не использовались (т.е. при первом выполнении - вставленные первой частью, при втором - вставленные первой частью при первом выполнении, при третьем - ... и так продолжается до тех пор, пока на очередном витке ни одной записи второй частью не будет вставлено).

Т.е. в данном случае первая часть выберет в СТЕ записи 2 и 4.
Вторая часть, используя эти две записи, выберет записи 1 и 3 (по связи с записью 2).
Следующее выполнение второй части ни одной записи не выберет (она использует в таблице CTE записи 1 и 3, но в таблице нет записей с таким значением поля ID_КорневойПроект).
На этом накопление закончится. В CTE будут записи 2,4,1,3.

Автор: Wound 17.01.20, 15:15
Цитата ^D^ima @
Это как вообще?

До Union all понятно, а SELECT cte.id_k и FROM test, cte не ясно. Это какая-то рекурсия? Т.е. он доходит до FROM test, cte и что что вернет в cte? что было до UNION ALL?
Сатанизм какой-то...

Это обычный деревянный(древовидный) запрос, рекурсивный. Причем синтаксис в разных БД может немного отличаться.

Автор: Akina 17.01.20, 19:01
Цитата Wound @
синтаксис в разных БД может немного отличаться

Единственные известные мне различия - это:

- одни СУБД требуют явного указания рекурсивности (WITH RECURSIVE), другим это не требуется;

- одни СУБД допускают только два подзапроса в рекурсивном CTE, другие не ограничивают их количество;

- одни СУБД допускают только UNION ALL, другие - и UNION DISTINCT (а при количестве подзапросов более 2 - и то, и другое в любом порядке);

- одни СУБД требуют явной спецификации выходного набора, другие не против динамического формирования структуры.

Есть ещё какие известные тебе различия? Чисто из любопытства и для познания...

Автор: Wound 18.01.20, 20:19
Цитата Akina @
Единственные известные мне различия - это:

Возможно, я просто сталкивался с ними довольно давненько уже, ньюансы подзабыл, если честно, помню только что пример с PostgresSQL у меня не пошел на MSSQL, пришлось немного модифицировать, чтоб завелся.

Автор: ^D^ima 28.03.22, 08:30
Коллеги, помогите посчитать.

Есть таблица:
СотрудникМесяцСуммаКоличество
ИвановЯнварь1002
ИвановФевраль2002
Ивановмарт2
ПетровЯнварь1
ПетровФевраль2001
ПетровМарт1


Есть сотрудники, месяцы, суммы выручки. В какие-то месяцы сотрудники могут не иметь выручки. Как посчитать количество месяцев, в которых была выручка?
Это можно сделать в то-же самой таблице(столбец количество), или придется делать ещё один запрос

Автор: Akina 28.03.22, 16:05
^D^ima
Хотелось бы как минимум увидеть требуемый результат для именно показанных исходных данных. Ещё лучше - с подробными пояснениями. Совсем хорошо - если исходные данные будут показаны в виде online fiddle или скриптов CREATE TABLE + INSERT INTO.

Автор: Zhydkih 29.04.22, 08:23
Помогите в запросе, нужно перемножить поля Price и Quantity, но поле Сумма получает текстовое значение, как получить числовое значение в поле Сумма?


SELECT YEAR(dbo.Documents.DocDate) AS Год, MONTH(dbo.Documents.DocDate) AS Месяц,dbo.DocumentRows.OfficeID, dbo.DocumentRows.Quantity, dbo.DocumentRows.Price, iif(dbo.DocumentRows.CrID=1 AND dbo.DocumentRows.FlagCr=1, [Quantity] * [Price] , -1 * [Quantity] * [Price]) AS Сумма

FROM dbo.Documents INNER JOIN dbo.DocumentRows ON dbo.Documents.ID = dbo.DocumentRows.DocID

WHERE dbo.Documents.DocDate>='01.01.2020' AND dbo.DocumentRows.DbID=1 AND dbo.DocumentRows.FlagDb=1 AND dbo.Documents.State=1 OR dbo.Documents.DocDate>='01.01.2020' AND dbo.Documents.State=1 AND dbo.DocumentRows.CrID=1 AND dbo.DocumentRows.FlagCr=1;


____________.png (, : 94)

Автор: Akina 29.04.22, 12:53
Цитата Zhydkih @
поле Сумма получает текстовое значение

Умножение не может вернуть строковое значение.
Проверьте - если действительно возвращает строку, то дополнительное умножение IIF(...) на единицу должно привести к ошибке.

Автор: Zhydkih 29.04.22, 13:52
Если в данном запросе, умножение на единицу к ошибке не приводит, но в поле сумма число разделено не запятой, а точкой и если использовать результаты этого запроса в другом запросе, то любое арифметическое действие над полем Сумма приводит к ошибке.

Автор: Akina 29.04.22, 14:10
Цитата Zhydkih @
в поле сумма число разделено не запятой, а точкой

Ась? какая нафиг запятая??? разделитель целой и дробной части - это точка.
Цитата Zhydkih @
если использовать результаты этого запроса в другом запросе, то любое арифметическое действие над полем Сумма приводит к ошибке.

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

Автор: Zhydkih 29.04.22, 15:55
Это SQL запрос к серверу из Access, данные нужны для дальнейшей обработки, а так как, если я правильно понял, разделителем частей является точка, то Access воспринимает поле Сумма как текст и не дает далее производить арифметические действия.

Автор: Akina 29.04.22, 18:21
Цитата Zhydkih @
Это SQL запрос к серверу из Access

А курсор, конечно, клиентский. А Аксесс русифицированный. Ну-ну...

Автор: Zhydkih 29.04.22, 18:51
И?

Автор: Akina 02.05.22, 16:01
Что - и? Клиентский курсор тащит к себе всё с сервера, а потом на полученных данных выполняет запрос. Сам. Естественно, возникает конфликт между стандартным представлением данных сервером и их национальной интерпретацией Аксессом. Или заменяй точку на запятую, или меняй региональные установки, или (как по мне - наиболее верный подход) переходи на использование серверного курсора.

Powered by Invision Power Board (https://www.invisionboard.com)
© Invision Power Services (https://www.invisionpower.com)