На главную Наши проекты:
Журнал   ·   Discuz!ML   ·   Wiki   ·   DRKB   ·   Помощь проекту
ПРАВИЛА FAQ Помощь Участники Календарь Избранное RSS
msm.ru
! информация о разделе
user posted imageДанный раздел предназначается исключительно для обсуждения вопросов использования языка запросов SQL. Обсуждение общих вопросов, связанных с тематикой баз данных - обсуждаем в разделе "Базы данных: общие вопросы". Убедительная просьба - соблюдать "Правила форума" и не пренебрегать "Правильным оформлением своих тем". Прежде, чем создавать тему, имеет смысл заглянуть в раздел "Базы данных: FAQ", возможно там уже есть ответ.

Модераторы: Akina
  
> Помогите с запросом
    SQL server 2014 + SQL management studio.

    Запрос:
    user posted image
    ExpandedWrap disabled
      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 -
      ExpandedWrap disabled
         
      LEFT JOIN DocumentCategory
        Бинго!!!

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

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


        суммировались в
        4СИНИЙ
        2КРАСНЫЙ
          Цитата ^D^ima @
          как называется функция объединения(суммирования) строк?

          Это шутка такая? у тебя суммируются числа, а не строки. SUM() ... А по строке выполняется группировка. GROUP BY ...
          Сообщение отредактировано: Akina -
            Ошибка:
            ExpandedWrap disabled
              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.

            Добавлено
            Мне что-то типа такого нужно:
            ExpandedWrap disabled
              GROUP BY Category.FileAs,sum(Payment.TotalWithVATBC)

            Чтобы для одинаковых Category.FileAs суммировались их Payment.TotalWithVATBC
              Так получилось, всем спасибо.
              ExpandedWrap disabled
                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
                Цитата ^D^ima @
                Так получилось

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

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

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

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


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

                  ДокументКатегория
                  1Категория 1, Категория 2
                    Цитата ^D^ima @
                    А можно так сделать, чтобы Category.FileAs AS Category объединяло все найденные варианты в 1 строку.

                    У Вас MS SQL. Если версия позволяет, то можете использовать STRING_AGG. Иначе разве что через FOR XML.
                    Сообщение отредактировано: Akina -
                      помогите отформатировать число.

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

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

                      Добавлено
                      или придется переводить в строку и делать replace?
                        Вместо
                        ExpandedWrap disabled
                          sum(PaymentDetail.Value) AS Сумма


                        Стало:
                        ExpandedWrap disabled
                          SELECT   replace( CAST(  sum(PaymentDetail.Value) as varchar(20) ) ,'.',',')AS Сумма


                        Так заработало, но как-то череопно выглядит. PaymentDetail.Value имеет тип данных money
                          Цитата ^D^ima @
                          SQL management studio выдает корректно, с запятой

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

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

                          Попробуй
                          ExpandedWrap disabled
                            Format( sum(PaymentDetail.Value) , N'#.00', N'ru-ru') AS Сумма
                            :thanks:
                              Цитата Akina @
                              N

                              Что означает N в формате?
                                Цитата ^D^ima @
                                Что означает N в формате?

                                Если посмотреть описание функции Format, то увидишь. что второй и третий параметры имеют тип Nvarchar, а не просто varchar.
                                  Можно я все глупые вопросы буду писать тут? :)

                                  ExpandedWrap disabled
                                    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 для каждого данного проекта.

                                  Добавлено
                                  А то получается сейчас берется как понимаю первое вхождение
                                    ^D^ima, а почему ты именно INNER JOIN делаешь, а не LEFT JOIN?
                                      Цитата kosten @
                                      LEFT JOIN

                                      Я пробовал разные варианты, проблему это не решает.
                                      Если была это 1с-ка было бы все легко. Тут как понимаю если делать встроенный запрос в запрос, то вначале выполняется 2-й встроенный, потом первый...
                                        ^D^ima, давай-ка структура таблиц.
                                          Цитата kosten @
                                          почему ты именно INNER JOIN делаешь, а не LEFT JOIN?

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

                                          У тебя в запросе выполняется группировка по Company.FileAs. Такая группа - один проект?
                                            Задача: Сделать отчет Клиент, СуммаПоступила, СуммаПотрачена

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

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

                                            1 платеж - 1 счет - 1 проект - много учетов времени
                                              Цитата ^D^ima @
                                              1 платеж - 1 счет - 1 проект - много учетов времени

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

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

                                                  Если оплата по проекту разбита по времени, то каждый раз надо платить на разные счета?
                                                    Цитата ^D^ima @
                                                    В 1 платеже -> 1 счет(не бывает счета связанного с несколькими платежами)

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

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


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

                                                      Возможно что к проекту привязан не 1 счет, но от этого цепочка не меняется
                                                        Цитата ^D^ima @
                                                        в счете ссылки на платеж нет

                                                        Пробегал мимо, суть задачи не уловил. Но осуждаю :lol:
                                                        Если связь A->B есть, а B->A нет, но нужна, то просто "склеивайте" нужный запрос с нужным порядком полей, и пользуйте его как "источник".
                                                        Сорри, если не в тему, и не впопад)
                                                          Моя задача не решает в принципе или я неверно изложил?
                                                            Да решается она, решается. Просто ты её неполно изложил, а догадываться тупо лень.
                                                            Если, ориентируясь на твой запрос, составить эскиз схемы данных, то получится что-то типа
                                                            Прикреплённый файлПрикреплённый файл1.png (15,13 Кбайт, скачиваний: 1400) .
                                                            Дорисуй, где один, а где много...
                                                              Akina
                                                              Все верно. везде 1, TimeEntry много.
                                                              Я имею в ввиду что в таблице TimeEntry много вхождений по одному ProjectID
                                                                Тогда почему надо суммировать PaymentDetail.Value?
                                                                  можно не суммировать
                                                                    :'(:'(:'(:'(:'(:'(:'(:'(:'(
                                                                      ^D^ima
                                                                      Не, давай так. Переноси структуры и пример наполнения в Access, и выкладывай полученную базу, с указанием, какой должен получиться результат на этом наполнении. Будем смотреть.
                                                                        Akina
                                                                        Прошу прощения, был недостаточно нагляден.

                                                                        С Access тяжело, сделал все наглядно:
                                                                        Запрос:
                                                                        ExpandedWrap disabled
                                                                          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
                                                                          Гм... А что получится на тех же данных, если обернуть PaymentDetail.Value в SUM() и убрать из выражения группировки?

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


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

                                                                          Что там тяжёлого? Открыть Access, создать новую БД, импортировать туда таблицы и почистить от лишних данных (если данных слишком дофига, то импортировать структуры, а потом запросами скопировать немного данных), сжать, зипануть - и всё.
                                                                          Сообщение отредактировано: Akina -
                                                                            Цитата Akina @
                                                                            Гм... А что получится на тех же данных, если обернуть PaymentDetail.Value в SUM() и убрать из выражения группировки?

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

                                                                            user posted image

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

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

                                                                            Добавлено
                                                                            Наверное последний вопрос:
                                                                            ExpandedWrap disabled
                                                                              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

                                                                            Как-то можно свернуть ещё по компаниям другим мпособом, кроме как ещё делать запрос в запросе, в котором запрос?
                                                                            ExpandedWrap disabled
                                                                              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
                                                                              "Средний" подзапрос - лишний.
                                                                              ExpandedWrap disabled
                                                                                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 Компания
                                                                                пришло в голову такое решение:
                                                                                Если и платеж и расход связан с клиентом, то можно взять одним запросом все расходы, в другом все платежи и в условии where связать через названия клиента, получится как-то лаконичней

                                                                                ExpandedWrap disabled
                                                                                  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.Компания
                                                                                Сообщение отредактировано: ^D^ima -
                                                                                  Цитата ^D^ima @
                                                                                  пришло в голову такое решение

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

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

                                                                                    ExpandedWrap disabled
                                                                                      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
                                                                                      Цитата ^D^ima @
                                                                                      несколько не понял

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

                                                                                      ExpandedWrap disabled
                                                                                        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.Компания
                                                                                        Akina
                                                                                        теперь понятно, :thanks::thanks::thanks:
                                                                                          Опять столкнулся с трудностью:
                                                                                          ExpandedWrap disabled
                                                                                            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.

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

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

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

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

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

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

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

                                                                                            ExpandedWrap disabled
                                                                                              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. Подумай, что ты будешь получать от этого запроса в ЯНВАРЕ...
                                                                                              Цитата Akina @
                                                                                              следует размещать в самОй секции связывания. То есть в секции ON.

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

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

                                                                                              Да, вижу...

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

                                                                                              :yes:

                                                                                              Akina, спасибо за ответы :thanks:
                                                                                                Все более тяжелые отчеты просят от меня :rolleyes:

                                                                                                Подскажите концептуально:
                                                                                                Есть 3 отдельных запроса, в каждом из которых результатом является 1 строка, 2 числа: СуммаПриход, СуммаРасход
                                                                                                Как мне получить отчет, где будет:
                                                                                                НазваниеЗапроса(произвольное название), СуммаПриход, СуммаРасход
                                                                                                  ExpandedWrap disabled
                                                                                                        SELECT 'Запрос 1' AS НазваниеЗапроса, СуммаПриход, СуммаРасход
                                                                                                        FROM [query1]
                                                                                                    UNION ALL
                                                                                                        SELECT 'Запрос 2'                   , СуммаПриход, СуммаРасход
                                                                                                        FROM [query2]
                                                                                                    UNION ALL
                                                                                                        SELECT 'Запрос 3'                   , СуммаПриход, СуммаРасход
                                                                                                        FROM [query3]
                                                                                                    Как-то можно оптимизировать это?
                                                                                                    ExpandedWrap disabled
                                                                                                      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


                                                                                                    например хочется условие по времени вынести куда-то(функция\процедура) и туда передовать дату. Возможно что-то ещё я сделал
                                                                                                      1) UNION ALL не оптимизируется. Можно оптимизировать только отдельные его подзапросы.
                                                                                                      2) Подзапросы независимы, так что параметры нужно передавать в каждый из них.
                                                                                                      3) Если хочется передавать параметр один раз - его можно передать через локальную переменную либо создать хранимую процедуру с параметрами.
                                                                                                      4) Объединить условия в подзапросах не получится - подзапросы независимы. Да и нет в том смысла (см. п. 1).
                                                                                                        В sqlcmd можно делать переменные, можно ли их потом использовать для всех запросов?
                                                                                                        например я использую везде between '01.08.2017' and '31.08.2017 23:59' и можно ли сделать 2 переменные для начальной даты и конечной даты и их распространить на все запросы?
                                                                                                          Цитата ^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'
                                                                                                            MIF
                                                                                                            MS SQL. Спасибо за совет
                                                                                                              Цитата Akina @
                                                                                                              1) UNION ALL не оптимизируется. Можно оптимизировать только отдельные его подзапросы.

                                                                                                              Как можно сделать итог суммаприхода, суммарасхода и разница между ними как на картинке?
                                                                                                              Прикреплённый файлПрикреплённый файлsql.png (7,26 Кбайт, скачиваний: 821)
                                                                                                                Цитата ^D^ima @
                                                                                                                Как можно сделать итог

                                                                                                                WITH ROLLUP

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

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

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

                                                                                                                  Добавлено
                                                                                                                  ExpandedWrap disabled
                                                                                                                    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 общий итог)
                                                                                                                    Цитата ^D^ima @
                                                                                                                    У меня 4 строки - 4 отдельных запроса, объединенных UNION ALL и в какой секции WITH ROLLUP нужно использовать?

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

                                                                                                                        А в поле select что писать, если там через union 3 отдельных запроса сделаны?
                                                                                                                          Цитата ^D^ima @
                                                                                                                          А в поле select что писать
                                                                                                                          :wall:
                                                                                                                          ExpandedWrap disabled
                                                                                                                            SELECT Вид, SUM(СуммаПриход) СуммаПриход, SUM(СуммаРасход) СуммаРасход
                                                                                                                            FROM (твой запрос с 4 подзапросами и 3 UNION-ами) dummy
                                                                                                                            GROUP BY Вид WITH ROLLUP
                                                                                                                            Цитата Akina @
                                                                                                                            dummy

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

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

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

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

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

                                                                                                                                      именно так.

                                                                                                                                      А то придется в этом-же запросе копировать его ещё раз с другими датами
                                                                                                                                        ExpandedWrap disabled
                                                                                                                                          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 таблицы выдает?
                                                                                                                                          https://msdn.microsoft.com/en-us/library/ms...v=sql.105).aspx

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

                                                                                                                                          2) Выполнение процедуры без EXECUTE (или сокращённой его формы EXEC) допускается лишь в случае, если её вызов является первым оператором (всего пакета, или отдельного блока пакета, т.е. непосредственно после GO). Во всех остальных случаях EXEC(UTE) обязателен.
                                                                                                                                          Сообщение отредактировано: Akina -
                                                                                                                                            ExpandedWrap disabled
                                                                                                                                               exec my1 'DA','DQ'
                                                                                                                                               
                                                                                                                                              union all
                                                                                                                                               exec my1 'DA','DQ'


                                                                                                                                            так все равно на union all ругается
                                                                                                                                              Ну ещё бы... ты хоть иногда в мануал-то заглядывай. UNION/EXCEPT/INTERSECT может объединять только запросы, а не любые конструкции, возвращающие набор записей.
                                                                                                                                                Печально, спасибо.

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

                                                                                                                                                Добавлено
                                                                                                                                                Последний день предыдущего месяца взял так:
                                                                                                                                                ExpandedWrap disabled
                                                                                                                                                  EOMONTH(GETDATE( ),-1)
                                                                                                                                                как туда 23:59:59 поставить?
                                                                                                                                                  Получилось 2 вариантами:
                                                                                                                                                  не знаю какой более правильный:
                                                                                                                                                  ExpandedWrap disabled
                                                                                                                                                    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')


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


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

                                                                                                                                                  Добавлено
                                                                                                                                                  Пока взял это:
                                                                                                                                                  ExpandedWrap disabled
                                                                                                                                                    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

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

                                                                                                                                                  А то получается что, действительно, в январе 2018 я буду получать данные не 12.2017 а 12.2018, хотя я мог бы чтото с IF'ом нагородить и отнимать год если месяц первый
                                                                                                                                                    Тебе нужно взять весь предыдущий месяц, верно? то есть больше или равно 0:00 первого числа и строго меньше 0:00 первого числа следующего (в данном конкретном случае текущего) месяца. Т.е.

                                                                                                                                                    ExpandedWrap disabled
                                                                                                                                                      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 @
                                                                                                                                                    Это я к чему всё, чтобы избавиться от этой конструкции

                                                                                                                                                    В первую очередь ты должен избавиться от любых действий, кроме сравнений, для полей таблицы. Для текущей даты значение вычислится один раз, каким бы ни было сложным выражение, а для значений из таблицы - для каждой записи. Что дольше и затратнее? Опять же - в каком случае можно использовать индекс?
                                                                                                                                                      Цитата Akina @
                                                                                                                                                      WHERE mydate >= DATEADD(day,1,EOMONTH(GETDATE(),-2))
                                                                                                                                                        AND mydate <  DATEADD(day,1,EOMONTH(GETDATE(),-1))

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

                                                                                                                                                        Изучи https://msdn.microsoft.com/en-us/library/ms...v=sql.105).aspx , особенно последний пример с пояснениями. Как я из них понял - нельзя, но вдруг я ошибаюсь...
                                                                                                                                                          Akina
                                                                                                                                                          Не подскажешь, как-то можно взять итог и подитог только по 2 столбцам?
                                                                                                                                                          Есть такой запрос:
                                                                                                                                                          ExpandedWrap disabled
                                                                                                                                                            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
                                                                                                                                                            Цитата ^D^ima @
                                                                                                                                                            я хочу чтобы rollup был только по Category.FileAs и Company.FileAs

                                                                                                                                                            Используй GROUPING SETS.
                                                                                                                                                              Получилось так:
                                                                                                                                                              Я поставил с скобки
                                                                                                                                                              ExpandedWrap disabled
                                                                                                                                                                GROUP BY
                                                                                                                                                                rollup (Category.FileAs,(Company.FileAs,Project.Comments))

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

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

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

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

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

                                                                                                                                                                    спасибо
                                                                                                                                                                      Цитата Akina @
                                                                                                                                                                      Группировка и COUNT(), как обычно.

                                                                                                                                                                      COUNT(*) или COUNT(поле)?
                                                                                                                                                                        Нет смысла указывать поле - нам не надо отсеивать NULL-значения.
                                                                                                                                                                          Если в таблице колонка имеет тип int, а мне нужно ее поделить и получить значение с запятой, как это организовать?
                                                                                                                                                                          ExpandedWrap disabled
                                                                                                                                                                            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)
                                                                                                                                                                            Друзья, так и не нашел ответа на вопрос.
                                                                                                                                                                              Ну типа
                                                                                                                                                                              ExpandedWrap disabled
                                                                                                                                                                                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 @
                                                                                                                                                                              ругается на

                                                                                                                                                                              А процититровать ругательства?
                                                                                                                                                                                Цитата Akina @
                                                                                                                                                                                SUM(TimeEntry.Duration)/60.0 AS Сумма

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

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

                                                                                                                                                                                Бинго!!!

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

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

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

                                                                                                                                                                                ) помечена красным пунктиром
                                                                                                                                                                                  Цитата ^D^ima @
                                                                                                                                                                                  как округлять?

                                                                                                                                                                                  ROUND(), вероятно...
                                                                                                                                                                                    :thanks:
                                                                                                                                                                                    Как можно упростить конструкцию?
                                                                                                                                                                                    Есть 1 селект из 3 запросов, у которых Query1.Проект=Query2.Проект=Query3.Проект, но такая конструкция недопустима. Пока сделал так:
                                                                                                                                                                                    ExpandedWrap disabled
                                                                                                                                                                                      where Query1.Проект=Query2.Проект and Query2.Проект=Query3.Проект


                                                                                                                                                                                    Как-то можно более грамотно написать?
                                                                                                                                                                                      Цитата ^D^ima @
                                                                                                                                                                                      Query1.Проект=Query2.Проект=Query3.Проект, но такая конструкция недопустима.

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

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

                                                                                                                                                                                      Вот как раз ЭТО - грамотно. При условии, что нет возможности сами запросы объединить...
                                                                                                                                                                                        Вот полный текст запроса:
                                                                                                                                                                                        ExpandedWrap disabled
                                                                                                                                                                                          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.Проект а именно на второе равно(Неправильный синтаксис около конструкции "=".)
                                                                                                                                                                                          По-моему, два последних подзапроса можно собрать в один. Будет что-то вроде

                                                                                                                                                                                          ExpandedWrap disabled
                                                                                                                                                                                            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
                                                                                                                                                                                            Опять нужна помощь:
                                                                                                                                                                                            ExpandedWrap disabled
                                                                                                                                                                                              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


                                                                                                                                                                                            ругается на
                                                                                                                                                                                            ExpandedWrap disabled
                                                                                                                                                                                              round(Сумма/Время,2)  as "Стоимость часа 5\2"
                                                                                                                                                                                            На Сумма/Время, т.к. выше они объявлены как суммы неких данных. Как их верно сюда запихнуть?
                                                                                                                                                                                              Вместо них поместить вычисляющие их выражения...
                                                                                                                                                                                              ExpandedWrap disabled
                                                                                                                                                                                                round(sum( PaymentDetail.Value)/CAST(SUM(TimeEntry.Duration) AS FLOAT)/60,2)  as "Стоимость часа 5\2"
                                                                                                                                                                                                Как-то странно считает:
                                                                                                                                                                                                ExpandedWrap disabled
                                                                                                                                                                                                  Сумма    Время    Стоимость часа 5\2
                                                                                                                                                                                                  160000,00      2    22,2222222222222
                                                                                                                                                                                                Сообщение отредактировано: ^D^ima -
                                                                                                                                                                                                  Скобки ещё нужны:
                                                                                                                                                                                                  ExpandedWrap disabled
                                                                                                                                                                                                    round( sum(PaymentDetail.Value) / (CAST(SUM(TimeEntry.Duration) AS FLOAT)/60) , 2) as "Стоимость часа 5\2"

                                                                                                                                                                                                  Или последнее деление заменить на умножение:
                                                                                                                                                                                                  ExpandedWrap disabled
                                                                                                                                                                                                    round( sum(PaymentDetail.Value) / CAST(SUM(TimeEntry.Duration) AS FLOAT) * 60 , 2) as "Стоимость часа 5\2"
                                                                                                                                                                                                  Сообщение отредактировано: Akina -
                                                                                                                                                                                                    :thanks:
                                                                                                                                                                                                      Опять не могу элементарное победить:
                                                                                                                                                                                                      ExpandedWrap disabled
                                                                                                                                                                                                        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


                                                                                                                                                                                                      ExpandedWrap disabled
                                                                                                                                                                                                        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)):
                                                                                                                                                                                                      ExpandedWrap disabled
                                                                                                                                                                                                        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.

                                                                                                                                                                                                      Что ему ещё нужно?
                                                                                                                                                                                                      Сообщение отредактировано: ^D^ima -
                                                                                                                                                                                                        Судя по показанному результату Employee.FileAs = "Курьер". Не подскажешь, что должно получиться после его декремента?
                                                                                                                                                                                                          Немного не тот столбец, смотри тот где
                                                                                                                                                                                                          Null, null, 0, 0.5, 135, 14, 220, 4, 5

                                                                                                                                                                                                          Если вместо float поставить int, ошибка невозмлжно преобразовать 0.5, а с типом float вообще такая ошибка как в прошлом посте
                                                                                                                                                                                                          Сообщение отредактировано: ^D^ima -
                                                                                                                                                                                                            Думаю, что акцесс ожидает точку разделителем целой и дробной части числа, а ты ему скармливаешь запятую.
                                                                                                                                                                                                              MIF
                                                                                                                                                                                                              Похоже на то если просто CAST('0,5' as float) сделать, то ошибку вываливает
                                                                                                                                                                                                                Попробую заиенить запятую точкой в Category.Fileas
                                                                                                                                                                                                                  Так работает, но как-то монструозно выглядит )
                                                                                                                                                                                                                  ExpandedWrap disabled
                                                                                                                                                                                                                    sum( CAST(isnull(REPLACE(Category.FileAs,',','.'),0) as float))
                                                                                                                                                                                                                    Может в самом аксессе можно сменить локаль с американской на русскую?
                                                                                                                                                                                                                      MIF
                                                                                                                                                                                                                      Может быть, только это не аксесс
                                                                                                                                                                                                                        Попробуй format(fileas, ‘N’, ,’en-US’)
                                                                                                                                                                                                                        Сообщение отредактировано: MIF -
                                                                                                                                                                                                                          Помогите с идеологией:
                                                                                                                                                                                                                          1 Есть проект, у него 2 записи о времени
                                                                                                                                                                                                                          2 У этого проекта 1 счет

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

                                                                                                                                                                                                                          То на выходе получаю 2 строки
                                                                                                                                                                                                                          ExpandedWrap disabled
                                                                                                                                                                                                                            Проект1,Запись о времени1, Счет1
                                                                                                                                                                                                                            Проект1,Запись о времени2, Счет1


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


                                                                                                                                                                                                                          Или мне делать 2 подзапроса и соединять по Проекту?
                                                                                                                                                                                                                            Цитата ^D^ima @
                                                                                                                                                                                                                            на выходе получаю 2 строки

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

                                                                                                                                                                                                                            надо использовать 2 копии таблицы времён и получать выборку
                                                                                                                                                                                                                            Проект1Запись о времени1Запись о времени2Счет1
                                                                                                                                                                                                                              Akina
                                                                                                                                                                                                                              А если их будет не 2 а 10 к примеру?
                                                                                                                                                                                                                              И как мне соединить как ты показал?
                                                                                                                                                                                                                                Цитата ^D^ima @
                                                                                                                                                                                                                                А если их будет не 2 а 10 к примеру?

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

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

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

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

                                                                                                                                                                                                                                Проект1Запись о времени1Null
                                                                                                                                                                                                                                Проект1Запись о времени2Счет1
                                                                                                                                                                                                                                  Цитата Akina @
                                                                                                                                                                                                                                  Давай опять отбросим "а если" и будем обсуждать конкретную структуру данных с конкретным наполнением и конкретным желаемым результатом.

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

                                                                                                                                                                                                                                  Вот и получается:
                                                                                                                                                                                                                                  ExpandedWrap disabled
                                                                                                                                                                                                                                    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 (11,43 Кбайт, скачиваний: 769)
                                                                                                                                                                                                                                    Цитата ^D^ima @
                                                                                                                                                                                                                                    Структура данных. По мере подключения через левое соединение:
                                                                                                                                                                                                                                    1 "Проект".
                                                                                                                                                                                                                                    2 "Записи о времени". Может быть несколько в проекте
                                                                                                                                                                                                                                    3 "Счет". Может быть несколько в проекте
                                                                                                                                                                                                                                    4 "Оплата". Может быть несколько по 1 счету.

                                                                                                                                                                                                                                    Я не понимаю одного - какая связь между каждым из нескольких времён и каждым из нескольких счетов. Точнее, я не понимаю, как между ними какая-то связь вообще может быть...
                                                                                                                                                                                                                                    Но ты хочешь получить и время, и сумму по каждому счёту в одном наборе данных - значит, связь есть?
                                                                                                                                                                                                                                      Нужно чтобы на выходе была 1 строка с суммой времени было 60, а суммой суммы 80000
                                                                                                                                                                                                                                        Цитата ^D^ima @
                                                                                                                                                                                                                                        Нужно чтобы на выходе была 1 строка с суммой времени было 60, а суммой суммы 80000

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

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

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

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

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

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

                                                                                                                                                                                                                                            Ну у тебя же MS SQL, который прекрасно умеет и FULL OUTER JOIN, и CROSS APPLY. Да и оконные функции у него есть, так что можно запросто использовать что-то вроде
                                                                                                                                                                                                                                            ExpandedWrap disabled
                                                                                                                                                                                                                                              SELECT SUM(payments.payment) OVER (PARTITION BY project.id) AS total_payment
                                                                                                                                                                                                                                              А как из этого рисунка( http://forum.sources.ru/index.php?act=Atta...attach_id=57837 ) получить
                                                                                                                                                                                                                                              Количество уникальных ИД платежа? Count как понимаю возвращает 4, а нужно 2
                                                                                                                                                                                                                                                Count(distinct(ид платежа))
                                                                                                                                                                                                                                                Сообщение отредактировано: MIF -
                                                                                                                                                                                                                                                  почему такая конструкция не прокатывает:
                                                                                                                                                                                                                                                  ExpandedWrap disabled
                                                                                                                                                                                                                                                    (CASE WHEN  count(distinct TimeEntry.id)<>0
                                                                                                                                                                                                                                                                         THEN count(distinct TimeEntry.id)
                                                                                                                                                                                                                                                                         ELSE 1 END)


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

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


                                                                                                                                                                                                                                                  Добавлено
                                                                                                                                                                                                                                                  семен-семеныч:
                                                                                                                                                                                                                                                  ExpandedWrap disabled
                                                                                                                                                                                                                                                     count(distinct (isnull (PaymentDetail.id,1) ) )

                                                                                                                                                                                                                                                  :facepalm: :facepalm: :facepalm:

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


                                                                                                                                                                                                                                                  сделал в 1 запросе:
                                                                                                                                                                                                                                                  ExpandedWrap disabled
                                                                                                                                                                                                                                                    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 Практика, [руководитель проекта]
                                                                                                                                                                                                                                                    Цитата ^D^ima @
                                                                                                                                                                                                                                                    Что-то монструозное получилось, но вродебы как рабочее.
                                                                                                                                                                                                                                                    так не понял как делать

                                                                                                                                                                                                                                                    Пять страниц на форуме? :scratch: И всего шесть таблиц связи, для отчета? Не проще(быстрее) функциями это сделать?
                                                                                                                                                                                                                                                    Сообщение отредактировано: Bas -
                                                                                                                                                                                                                                                      Bas
                                                                                                                                                                                                                                                      На самом деле 2 страницы. Я начал первую страницу с 0 знанием SQL. Постепенно я начал получать опыт. Если я сталкиваюсь с трудностью пытаюсь сделать сам, опираясь на опыт, ищу в интернете и только потом лезу на форум. Спасибо Akina и MIF что помогают разбираться в непростом для меня деле. В данной ветке я задавал вопросы трудностям с 6 запросам, так что не думай что 1 запрос обсасываем 9 страниц :)

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

                                                                                                                                                                                                                                                      Может быть. Но база не наша а стороннего ПО. Я просто оттуда дергаю данные. Не хочется ее захламлять своими поделиями, тем более она раз в 3 месяца обновляется.
                                                                                                                                                                                                                                                        ExpandedWrap disabled
                                                                                                                                                                                                                                                          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 (12,01 Кбайт, скачиваний: 718)
                                                                                                                                                                                                                                                          Так ближе:
                                                                                                                                                                                                                                                          ExpandedWrap disabled
                                                                                                                                                                                                                                                            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 (8,61 Кбайт, скачиваний: 688)

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

                                                                                                                                                                                                                                                          Добавлено
                                                                                                                                                                                                                                                          Так работает, но можно красивее сделать?
                                                                                                                                                                                                                                                          ExpandedWrap disabled
                                                                                                                                                                                                                                                            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 Проект
                                                                                                                                                                                                                                                            ExpandedWrap disabled
                                                                                                                                                                                                                                                              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
                                                                                                                                                                                                                                                              Цитата MIF @
                                                                                                                                                                                                                                                              group by case when project.FileAs not like '%АО ОКГ%' then project.FileAs else 'АО ОКГ' end

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

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

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

                                                                                                                                                                                                                                                                    И так далее - нестандарт везде свой.
                                                                                                                                                                                                                                                                      можно q1 вподзапросе вычислить.
                                                                                                                                                                                                                                                                        Как проще like и in совместить?
                                                                                                                                                                                                                                                                        ExpandedWrap disabled
                                                                                                                                                                                                                                                                          Project.ContractNumber like in('ЮК%','АО%','АС%','ЮКПРС%','Б%','Иное ПРС%','ЮК%','СОЮ%','Включение%','ПК%','И%','Л%','М%','Р%','Иное НР%')


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

                                                                                                                                                                                                                                                                            А разве в MS SQL это есть?
                                                                                                                                                                                                                                                                              В MS SQL тоже есть регэкспы.
                                                                                                                                                                                                                                                                                Запиши строки поиска в отдельную таблицу.
                                                                                                                                                                                                                                                                                Добавь JOIN в свой запрос:
                                                                                                                                                                                                                                                                                ExpandedWrap disabled
                                                                                                                                                                                                                                                                                  JOIN MyTable mt ON Project.ContractNumber like mt.MyField

                                                                                                                                                                                                                                                                                Ну и еше надо добавить DISTINCT или GROUP BY, чтобы исключить повторы.
                                                                                                                                                                                                                                                                                  MIF а эту таблицу придется удалять и создавать в запросе каждый раз?
                                                                                                                                                                                                                                                                                    Если строки живут долго, то лучше хранить их в постоянной таблице.
                                                                                                                                                                                                                                                                                    Если они меняются в каждом запросе, то лучше создавать временныйы таблицу в запросе.
                                                                                                                                                                                                                                                                                      Цитата ^D^ima @
                                                                                                                                                                                                                                                                                      а эту таблицу придется удалять и создавать в запросе каждый раз?

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

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

                                                                                                                                                                                                                                                                                                Цитата Akina @
                                                                                                                                                                                                                                                                                                :facepalm:

                                                                                                                                                                                                                                                                                                :lol:

                                                                                                                                                                                                                                                                                                Групповое вычитание = -(групповое сложение) :jokingly:
                                                                                                                                                                                                                                                                                                  JoeUser
                                                                                                                                                                                                                                                                                                  Не так. 2+2=4. Со знаком минус будет -4, а должно быть 0(2-2)


                                                                                                                                                                                                                                                                                                  Akina
                                                                                                                                                                                                                                                                                                  1-4-11-30
                                                                                                                                                                                                                                                                                                  Нужно сагрегировать результат, как ари SUM, только с вычитанием каждого, а не сложением.
                                                                                                                                                                                                                                                                                                  Ну или взять каждый элемент, и подставить минус. Типа Sum(- элемент) . Сами значения элементов положительные числа, нужно найти значение их разниц
                                                                                                                                                                                                                                                                                                    Почему начали вычитать именно из 1, а не из, скажем, 11?
                                                                                                                                                                                                                                                                                                      Суммируешь все записи кроме ... кроме той, которую ты считаешь первой. И из нее вычитаешь сумму.
                                                                                                                                                                                                                                                                                                        MIF, в терминах SQL лучше суммить всё, и вычитать из удвоенного "избранного". В любом случае это таки сложение, а не вычитание.
                                                                                                                                                                                                                                                                                                          Цитата ^D^ima @
                                                                                                                                                                                                                                                                                                          а должно быть 0(2-2)

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


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

                                                                                                                                                                                                                                                                                                          Вот! Это четко)
                                                                                                                                                                                                                                                                                                            Зайду с другой стороны.
                                                                                                                                                                                                                                                                                                            Коллеги, в общем ситуация такая. Есть таблица на выходе (всего по 2 цифры на первый столбец):
                                                                                                                                                                                                                                                                                                            Клиент1 100
                                                                                                                                                                                                                                                                                                            Клиент1 250
                                                                                                                                                                                                                                                                                                            Клиент2 300
                                                                                                                                                                                                                                                                                                            Клиент2 500

                                                                                                                                                                                                                                                                                                            Нужно получить:
                                                                                                                                                                                                                                                                                                            Клиент1 150 (по модулю 100-250 или 250-100 нет разницы)
                                                                                                                                                                                                                                                                                                            Клиент2 200 (по модулю 300-500 или 500-300 нет разницы)
                                                                                                                                                                                                                                                                                                              Цитата ^D^ima @
                                                                                                                                                                                                                                                                                                              всего по 2 цифры на первый столбец

                                                                                                                                                                                                                                                                                                              ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                MAX(field) - MIN(field) AS delta
                                                                                                                                                                                                                                                                                                                Цитата Akina @
                                                                                                                                                                                                                                                                                                                MAX(field) - MIN(field) AS delta

                                                                                                                                                                                                                                                                                                                Гениально!!!
                                                                                                                                                                                                                                                                                                                  Где найти описание шаблонов при PATINDEX ?

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

                                                                                                                                                                                                                                                                                                                  Добавлено
                                                                                                                                                                                                                                                                                                                  Можно так: REVERSE( SUBSTRING(REVERSE('Отгрузка май 2018 100%'),2, CHARINDEX(' ',REVERSE('Отгрузка май 2018 100%') )-1 ) )
                                                                                                                                                                                                                                                                                                                  Но это коряво как-то. :wacko:
                                                                                                                                                                                                                                                                                                                    А ещё лучше взять как-то последнюю группу цифр, т.е. варианты могут быть в теории - 50%, -50 %, - 50 % и надо как-то взять справа от первой цифры до последней цифры следовавшей подряд
                                                                                                                                                                                                                                                                                                                      Вот так сделал, но это ещё корявее. Ищем первую цифру. Берем от первой цифры до конца. Идем первую нецифру в найденной подстроке.
                                                                                                                                                                                                                                                                                                                      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))

                                                                                                                                                                                                                                                                                                                      Как-то можно оптимизировать?
                                                                                                                                                                                                                                                                                                                      Сообщение отредактировано: ^D^ima -
                                                                                                                                                                                                                                                                                                                        Цитата ^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)
                                                                                                                                                                                                                                                                                                                          Спасибо.

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

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

                                                                                                                                                                                                                                                                                                                          https://yadi.sk/i/qOGNrzvH3ZJnkK

                                                                                                                                                                                                                                                                                                                          Мне нужно Budget с Project связать
                                                                                                                                                                                                                                                                                                                            Database Diagrams в SSMS. Или запросить скрипт создания - там будет и FK.
                                                                                                                                                                                                                                                                                                                              Коллеги, подкиньте идею.

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


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



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

                                                                                                                                                                                                                                                                                                                              Через case лучше делать типа Case month(Дата)=1 then сумма as 'Январь' и так 12 раз или есть какая-то конструкция лучше?
                                                                                                                                                                                                                                                                                                                                  А можно ли как-то использовать переменную в select?
                                                                                                                                                                                                                                                                                                                                  Например Что_то_там это длинная формула расчета и что-бы ее не переписывать несколько раз в select

                                                                                                                                                                                                                                                                                                                                  Select Что_то_там as 'Значение1', 'Значение1'-5 as 'Значение5'
                                                                                                                                                                                                                                                                                                                                      Akina
                                                                                                                                                                                                                                                                                                                                      Спасибо за помощь
                                                                                                                                                                                                                                                                                                                                        А как передать параметр в подзапрос?
                                                                                                                                                                                                                                                                                                                                        например мне нужно А1 передать в подзапрос как условие к where
                                                                                                                                                                                                                                                                                                                                        ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                          Select A1, (select B1 from table2 where table2.c1=A1)
                                                                                                                                                                                                                                                                                                                                          From table1
                                                                                                                                                                                                                                                                                                                                          ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                            Select table1.A1, (select table2.B1 from table2 where table2.c1=table1.A1)
                                                                                                                                                                                                                                                                                                                                            From table1

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

                                                                                                                                                                                                                                                                                                                                            ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                              FIO,Number
                                                                                                                                                                                                                                                                                                                                              Иванов 4
                                                                                                                                                                                                                                                                                                                                              Иванов 3
                                                                                                                                                                                                                                                                                                                                              Иванов 2
                                                                                                                                                                                                                                                                                                                                              Иванов 1
                                                                                                                                                                                                                                                                                                                                              Сидоров 8
                                                                                                                                                                                                                                                                                                                                              Сидоров 7
                                                                                                                                                                                                                                                                                                                                              Сидоров 6
                                                                                                                                                                                                                                                                                                                                              Сидоров 5


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

                                                                                                                                                                                                                                                                                                                                            Т.е. на выходе должно быть это:
                                                                                                                                                                                                                                                                                                                                            ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                              FIO,Number
                                                                                                                                                                                                                                                                                                                                              Иванов 1
                                                                                                                                                                                                                                                                                                                                              Иванов 2
                                                                                                                                                                                                                                                                                                                                              Сидоров 5
                                                                                                                                                                                                                                                                                                                                              Сидоров 6
                                                                                                                                                                                                                                                                                                                                              ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                select fio, number
                                                                                                                                                                                                                                                                                                                                                from
                                                                                                                                                                                                                                                                                                                                                (select fio, number, row_number() over (partition by fio order by number asc) rn
                                                                                                                                                                                                                                                                                                                                                from table) x
                                                                                                                                                                                                                                                                                                                                                where rn < 3
                                                                                                                                                                                                                                                                                                                                              Сообщение отредактировано: JoeUser -
                                                                                                                                                                                                                                                                                                                                                Akina, Мощно!!!

                                                                                                                                                                                                                                                                                                                                                Как можно из этого запроса:
                                                                                                                                                                                                                                                                                                                                                ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                  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-х раз одной и той-же формулы
                                                                                                                                                                                                                                                                                                                                                ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                  Cast( REVERSE(substring(REVERSE(TaskRequest.FileAs), PatIndex('%[0-9]%', REVERSE(TaskRequest.FileAs)), PatIndex('%[^0-9]%', substring(REVERSE(TaskRequest.FileAs)
                                                                                                                                                                                                                                                                                                                                                  Ещё такой вопрос:
                                                                                                                                                                                                                                                                                                                                                  Результат отчета:

                                                                                                                                                                                                                                                                                                                                                  Прикреплённый файлПрикреплённый файлSQL2.png (43,54 Кбайт, скачиваний: 858)

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

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

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

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

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

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

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

                                                                                                                                                                                                                                                                                                                                                    ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                      CASE WHEN MAX(field) = MIN(field)
                                                                                                                                                                                                                                                                                                                                                           THEN MAX(field)
                                                                                                                                                                                                                                                                                                                                                           ELSE MAX(field) - MIN(field)
                                                                                                                                                                                                                                                                                                                                                      END
                                                                                                                                                                                                                                                                                                                                                      Цитата Akina @
                                                                                                                                                                                                                                                                                                                                                      CASE WHEN MAX(field) = MIN(field)
                                                                                                                                                                                                                                                                                                                                                           THEN MAX(field)
                                                                                                                                                                                                                                                                                                                                                           ELSE MAX(field) - MIN(field)
                                                                                                                                                                                                                                                                                                                                                      END

                                                                                                                                                                                                                                                                                                                                                      гениально! не додумался до такой простой вещи. Уже хотел ноль везде дописывать, респектую. :thanks:
                                                                                                                                                                                                                                                                                                                                                        Не пойму алгоритмически как решить задачу:
                                                                                                                                                                                                                                                                                                                                                        У нас есть таблица
                                                                                                                                                                                                                                                                                                                                                        ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                          Дата, Сумма
                                                                                                                                                                                                                                                                                                                                                          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 неясно что писать
                                                                                                                                                                                                                                                                                                                                                          ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                            AND SUM(CASE WHEN 'Дата'=@дата2 THEN 1 ELSE 0 END)>0
                                                                                                                                                                                                                                                                                                                                                            не заработало:
                                                                                                                                                                                                                                                                                                                                                            Цитата
                                                                                                                                                                                                                                                                                                                                                            Статистическое выражение не может использоваться в предложении WHERE, если оно не содержится во вложенном запросе предложения HAVING или в списке выбора, и столбец, подвергаемый статистической обработке, не является внешней ссылкой.


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

                                                                                                                                                                                                                                                                                                                                                              Такая конструкция либо помещается в HAVING, либо используется в коррелированном WHERE [NOT] EXISTS.
                                                                                                                                                                                                                                                                                                                                                                Спасибо. Некоторые конструкции мне тяжело переваривать :)
                                                                                                                                                                                                                                                                                                                                                                  ЕслИ "ничего не брать " означает ноль, то NULL надо заменить на 0.
                                                                                                                                                                                                                                                                                                                                                                  ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                    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 -
                                                                                                                                                                                                                                                                                                                                                                    ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                      Select 'Сумма' * CASE WHEN Max('Дата')=@дата2 THEN 1 ELSE 0 END
                                                                                                                                                                                                                                                                                                                                                                      From table1
                                                                                                                                                                                                                                                                                                                                                                      Where 'Дата'>=@дата1 and 'Дата'<=@дата2
                                                                                                                                                                                                                                                                                                                                                                      Вопрос про запись данных. Никогда этим не занимался.

                                                                                                                                                                                                                                                                                                                                                                      Есть такой запрос
                                                                                                                                                                                                                                                                                                                                                                      ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                        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 ее создать
                                                                                                                                                                                                                                                                                                                                                                        Цитата ^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
                                                                                                                                                                                                                                                                                                                                                                        Сообщение отредактировано: Akina -
                                                                                                                                                                                                                                                                                                                                                                          Akina
                                                                                                                                                                                                                                                                                                                                                                          Как-то так? Боюсь запускать :D
                                                                                                                                                                                                                                                                                                                                                                          ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                            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 - уникальное?

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

                                                                                                                                                                                                                                                                                                                                                                          Получается как-то так тогда?
                                                                                                                                                                                                                                                                                                                                                                          ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                            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)
                                                                                                                                                                                                                                                                                                                                                                            Цитата ^D^ima @
                                                                                                                                                                                                                                                                                                                                                                            как-то можно это выполнить в отладочном режиме без записи самих данных в таблицу?

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

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

                                                                                                                                                                                                                                                                                                                                                                            что вырождается в
                                                                                                                                                                                                                                                                                                                                                                            ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                              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 ] ), соответственно неверна ссылка на вставляемые значения.
                                                                                                                                                                                                                                                                                                                                                                            Т.е. должно быть что-то типа

                                                                                                                                                                                                                                                                                                                                                                            ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                              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) /* какие данные вставлять */
                                                                                                                                                                                                                                                                                                                                                                              Цитата Akina @
                                                                                                                                                                                                                                                                                                                                                                              ON (target.DocumentID = source.DocumentID) /* Слияние по условию */

                                                                                                                                                                                                                                                                                                                                                                              А остальные условия не должны там быть?
                                                                                                                                                                                                                                                                                                                                                                              ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                                ON (target.DocumentClass=24, target.DocumentID = source.DocumentID, target.CategoryID=12)
                                                                                                                                                                                                                                                                                                                                                                                Цитата ^D^ima @
                                                                                                                                                                                                                                                                                                                                                                                А остальные условия не должны там быть?

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

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

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

                                                                                                                                                                                                                                                                                                                                                                                    ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                                      ON (targetDocumentClass = 24 AND target.DocumentID = source.DocumentID AND target.CategoryID = 12)
                                                                                                                                                                                                                                                                                                                                                                                      На что ругается?
                                                                                                                                                                                                                                                                                                                                                                                      ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                                        Сообщение 547, уровень 16, состояние 0, строка 5
                                                                                                                                                                                                                                                                                                                                                                                        Конфликт инструкции MERGE с ограничением FOREIGN KEY "DocumentCategoriesFK". Конфликт произошел в базе данных "PJM10", таблица "dbo.Document".

                                                                                                                                                                                                                                                                                                                                                                                      Прикреплённый файлПрикреплённый файлsql.png (53,79 Кбайт, скачиваний: 1171)
                                                                                                                                                                                                                                                                                                                                                                                        В этой базе есть хранимая процедура, которая как я понимаю и отвечает за добавление категории в документ, может быть эту процедуру использовать? В том плане что вызвать?
                                                                                                                                                                                                                                                                                                                                                                                        ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                                          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, так лучше?

                                                                                                                                                                                                                                                                                                                                                                                        Добавлено
                                                                                                                                                                                                                                                                                                                                                                                        Он вот с таким запускается параметрами:
                                                                                                                                                                                                                                                                                                                                                                                        ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                                          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?
                                                                                                                                                                                                                                                                                                                                                                                        ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                                          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
                                                                                                                                                                                                                                                                                                                                                                                          Друзья, видимо пятница, не пойму как решить простую с виду задачу:
                                                                                                                                                                                                                                                                                                                                                                                          ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                                            Взять клиентов, проекты по которым были в 2016-2017 годах, но не были в 2018-2019

                                                                                                                                                                                                                                                                                                                                                                                          Сделал так вначале, но понял что это не сработает
                                                                                                                                                                                                                                                                                                                                                                                          ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                                            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-го запроса
                                                                                                                                                                                                                                                                                                                                                                                            А просто по-деревенски выбрать из Company клиентов, для которых where (Project.Date BETWEEN '01.01.2016' AND '01.01.2018' and Project.State=4/*Завершен*/) не рулит? Или я чего-то не понял?
                                                                                                                                                                                                                                                                                                                                                                                              LMM
                                                                                                                                                                                                                                                                                                                                                                                              А как ты поймешь что по ним не было проектов в 18 и 19 годах?
                                                                                                                                                                                                                                                                                                                                                                                                Цитата ^D^ima @
                                                                                                                                                                                                                                                                                                                                                                                                Взять клиентов, проекты по которым были в 2016-2017 годах, но не были в 2018-2019

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

                                                                                                                                                                                                                                                                                                                                                                                                ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                                                  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, сам понимаешь - без живой БД всё это пляски с бубном :( .
                                                                                                                                                                                                                                                                                                                                                                                                  Цитата ^D^ima @
                                                                                                                                                                                                                                                                                                                                                                                                  А как ты поймешь что по ним не было проектов в 18 и 19 годах?

                                                                                                                                                                                                                                                                                                                                                                                                  ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                                                    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
                                                                                                                                                                                                                                                                                                                                                                                                    Akina
                                                                                                                                                                                                                                                                                                                                                                                                    Гениально!
                                                                                                                                                                                                                                                                                                                                                                                                      10 месяцев не писал запросов и опять память подводит, час потратил, не решил.

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

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


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

                                                                                                                                                                                                                                                                                                                                                                                                      Все записи где есть ID_КорневойПроект должны схлопнуться в ID_Проект с суммированием денег. Название должно остаться корневого проекта
                                                                                                                                                                                                                                                                                                                                                                                                        Для SQL Server:
                                                                                                                                                                                                                                                                                                                                                                                                        ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                                                          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
                                                                                                                                                                                                                                                                                                                                                                                                            PS. На будущее - не ленись указывать СУБД и её версию в КАЖДОМ вопросе...
                                                                                                                                                                                                                                                                                                                                                                                                            PPS. Чего проверять-то - вон он, линк на fiddle. Но предупреждаю сразу - если попадётся кольцо ссылок, будет плохо. Проверку я не делал.
                                                                                                                                                                                                                                                                                                                                                                                                              Akina
                                                                                                                                                                                                                                                                                                                                                                                                              Твой пример сломал мне мозг :wall:
                                                                                                                                                                                                                                                                                                                                                                                                              ExpandedWrap disabled
                                                                                                                                                                                                                                                                                                                                                                                                                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?
                                                                                                                                                                                                                                                                                                                                                                                                              Сатанизм какой-то...
                                                                                                                                                                                                                                                                                                                                                                                                                Цитата ^D^ima @
                                                                                                                                                                                                                                                                                                                                                                                                                Это какая-то рекурсия?

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

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

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

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

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

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

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

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

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

                                                                                                                                                                                                                                                                                                                                                                                                                    Есть ещё какие известные тебе различия? Чисто из любопытства и для познания...
                                                                                                                                                                                                                                                                                                                                                                                                                      Цитата Akina @
                                                                                                                                                                                                                                                                                                                                                                                                                      Единственные известные мне различия - это:

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

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


                                                                                                                                                                                                                                                                                                                                                                                                                        Есть сотрудники, месяцы, суммы выручки. В какие-то месяцы сотрудники могут не иметь выручки. Как посчитать количество месяцев, в которых была выручка?
                                                                                                                                                                                                                                                                                                                                                                                                                        Это можно сделать в то-же самой таблице(столбец количество), или придется делать ещё один запрос
                                                                                                                                                                                                                                                                                                                                                                                                                          ^D^ima
                                                                                                                                                                                                                                                                                                                                                                                                                          Хотелось бы как минимум увидеть требуемый результат для именно показанных исходных данных. Ещё лучше - с подробными пояснениями. Совсем хорошо - если исходные данные будут показаны в виде online fiddle или скриптов CREATE TABLE + INSERT INTO.
                                                                                                                                                                                                                                                                                                                                                                                                                            Помогите в запросе, нужно перемножить поля 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 (21,81 Кбайт, скачиваний: 93)
                                                                                                                                                                                                                                                                                                                                                                                                                              Цитата Zhydkih @
                                                                                                                                                                                                                                                                                                                                                                                                                              поле Сумма получает текстовое значение

                                                                                                                                                                                                                                                                                                                                                                                                                              Умножение не может вернуть строковое значение.
                                                                                                                                                                                                                                                                                                                                                                                                                              Проверьте - если действительно возвращает строку, то дополнительное умножение IIF(...) на единицу должно привести к ошибке.
                                                                                                                                                                                                                                                                                                                                                                                                                                Если в данном запросе, умножение на единицу к ошибке не приводит, но в поле сумма число разделено не запятой, а точкой и если использовать результаты этого запроса в другом запросе, то любое арифметическое действие над полем Сумма приводит к ошибке.
                                                                                                                                                                                                                                                                                                                                                                                                                                Сообщение отредактировано: Zhydkih -
                                                                                                                                                                                                                                                                                                                                                                                                                                  Цитата Zhydkih @
                                                                                                                                                                                                                                                                                                                                                                                                                                  в поле сумма число разделено не запятой, а точкой

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

                                                                                                                                                                                                                                                                                                                                                                                                                                  Как конкретно использовать? этот запрос включается в источник данных как подзапрос или CTE?
                                                                                                                                                                                                                                                                                                                                                                                                                                    Это SQL запрос к серверу из Access, данные нужны для дальнейшей обработки, а так как, если я правильно понял, разделителем частей является точка, то Access воспринимает поле Сумма как текст и не дает далее производить арифметические действия.
                                                                                                                                                                                                                                                                                                                                                                                                                                      Цитата Zhydkih @
                                                                                                                                                                                                                                                                                                                                                                                                                                      Это SQL запрос к серверу из Access

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


                                                                                                                                                                                                                                                                                                                                                                                                                                          Рейтинг@Mail.ru
                                                                                                                                                                                                                                                                                                                                                                                                                                          [ Script execution time: 0,3817 ]   [ 31 queries used ]   [ Generated: 28.03.24, 23:08 GMT ]