Помогите с запросом

SQL server 2014 + SQL management studio.

Запрос:

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 - 16.07.18, 12:06

 

LEFT JOIN DocumentCategory

Бинго!!!

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

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

суммировались в

4	СИНИЙ
2	КРАСНЫЙ

Цитата ^D^ima @ 06.09.17, 17:28

как называется функция объединения(суммирования) строк?

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

Сообщение отредактировано: Akina - 06.09.17, 17:37

Ошибка:

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.

Добавлено 06.09.17, 18:46
Мне что-то типа такого нужно:

GROUP BY Category.FileAs,sum(Payment.TotalWithVATBC)

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

Так получилось, всем спасибо.

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 @ 06.09.17, 20:28

Так получилось

Мне неизвестна физическая сущность хранящихся в таблицах сведений, но создаётся впечатление, что запрос избыточен. Как я понимаю, не может быть такого, чтобы в 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 @ 07.09.17, 10:02

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

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

Сообщение отредактировано: Akina - 07.09.17, 11:04

помогите отформатировать число.

Я хочу чтобы сотые отделялись запятой(12345,67)
А то получается что SQL management studio выдает корректно, с запятой, а при обработке того-же запроса через sqlcmd получается точка
Я написал так:

Format( sum(PaymentDetail.Value) , '#,00') AS Сумма

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

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

Вместо

sum(PaymentDetail.Value) AS Сумма

Стало:

SELECT   replace( CAST(  sum(PaymentDetail.Value) as varchar(20) ) ,'.',',')AS Сумма

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

Цитата ^D^ima @ 12.09.17, 14:42

SQL management studio выдает корректно, с запятой

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

Цитата ^D^ima @ 12.09.17, 14:42

при обработке того-же запроса через sqlcmd получается точка

А sqlcmd отдаёт результат as is, используя стандартный разделитель.

Цитата ^D^ima @ 12.09.17, 18:12

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

Попробуй

Format( sum(PaymentDetail.Value) , N'#.00', N'ru-ru') AS Сумма

Цитата Akina @ 12.09.17, 18:29

N

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

Цитата ^D^ima @ 13.09.17, 10:41

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

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

Можно я все глупые вопросы буду писать тут?

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

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

^D^ima, а почему ты именно INNER JOIN делаешь, а не LEFT JOIN?

Цитата kosten @ 19.09.17, 06:51

LEFT JOIN

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

^D^ima, давай-ка структура таблиц.

Цитата kosten @ 19.09.17, 06:51

почему ты именно INNER JOIN делаешь, а не LEFT JOIN?

А что в запросе могло натолкнуть на мысль о необходимости LEFT JOIN???

Цитата ^D^ima @ 18.09.17, 17:54

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

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

Задача: Сделать отчет Клиент, СуммаПоступила, СуммаПотрачена

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

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

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

Цитата ^D^ima @ 19.09.17, 09:24

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

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

Akina

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

Цитата ^D^ima @ 19.09.17, 10:39

не бывает счета связанного с несколькими платежами

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

Цитата ^D^ima @ 19.09.17, 10:39

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

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

Цитата Akina @ 19.09.17, 10:48

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

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

Цитата kosten @ 19.09.17, 10:43

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

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

Цитата ^D^ima @ 19.09.17, 11:16

в счете ссылки на платеж нет

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

Моя задача не решает в принципе или я неверно изложил?

Да решается она, решается. Просто ты её неполно изложил, а догадываться тупо лень.
Если, ориентируясь на твой запрос, составить эскиз схемы данных, то получится что-то типа
Прикреплённый файл1.png (15,13 Кбайт, скачиваний: 1412) .
Дорисуй, где один, а где много...

Akina
Все верно. везде 1, TimeEntry много.
Я имею в ввиду что в таблице TimeEntry много вхождений по одному ProjectID

Тогда почему надо суммировать PaymentDetail.Value?

можно не суммировать

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

Akina
Прошу прощения, был недостаточно нагляден.

С Access тяжело, сделал все наглядно:
Запрос:

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

Результат:


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

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

В самом крайнем случае

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

Цитата ^D^ima @ 21.09.17, 08:20

С Access тяжело

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

Сообщение отредактировано: Akina - 21.09.17, 08:36

Цитата Akina @ 21.09.17, 08:33

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

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



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

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

Добавлено 21.09.17, 09:26
Наверное последний вопрос:

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  Компания, СуммаРасхода

дает


Как-то можно свернуть ещё по компаниям другим мпособом, кроме как ещё делать запрос в запросе, в котором запрос?

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 Компания

Добавлено 21.09.17, 09:30

"Средний" подзапрос - лишний.

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 связать через названия клиента, получится как-то лаконичней

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 - 21.09.17, 14:55

Цитата ^D^ima @ 21.09.17, 14:49

пришло в голову такое решение

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

Цитата Akina @ 21.09.17, 16:18

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

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

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 @ 22.09.17, 09:01

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

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

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
теперь понятно,

Опять столкнулся с трудностью:

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 @ 27.09.17, 09:05

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

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

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

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

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

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

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

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

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

Цитата Akina @ 27.09.17, 10:08

следует размещать в самОй секции связывания. То есть в секции ON.

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

Цитата Akina @ 27.09.17, 10:08

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

Да, вижу...

Цитата Akina @ 27.09.17, 10:08

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

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

Все более тяжелые отчеты просят от меня

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

    SELECT 'Запрос 1' AS НазваниеЗапроса, СуммаПриход, СуммаРасход
    FROM [query1]
UNION ALL
    SELECT 'Запрос 2'                   , СуммаПриход, СуммаРасход
    FROM [query2]
UNION ALL
    SELECT 'Запрос 3'                   , СуммаПриход, СуммаРасход
    FROM [query3]

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

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 @ 28.09.17, 14:01

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 @ 28.09.17, 12:55

1) UNION ALL не оптимизируется. Можно оптимизировать только отдельные его подзапросы.

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

Цитата ^D^ima @ 10.10.17, 14:56

Как можно сделать итог

WITH ROLLUP

Цитата ^D^ima @ 10.10.17, 14:56

и разница между ними

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

Цитата Akina @ 10.10.17, 17:09

WITH ROLLUP

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

Добавлено 11.10.17, 07:13

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

Добавлено 11.10.17, 07:20

Цитата ^D^ima @ 11.10.17, 07:07

WITH ROLLUP

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

Цитата ^D^ima @ 11.10.17, 07:07

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

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

Akina

спасибо

Цитата Akina @ 11.10.17, 07:38

Обернуть всё это внешним запросом

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

Цитата ^D^ima @ 11.10.17, 14:44

А в поле select что писать

SELECT Вид, SUM(СуммаПриход) СуммаПриход, SUM(СуммаРасход) СуммаРасход
FROM (твой запрос с 4 подзапросами и 3 UNION-ами) dummy
GROUP BY Вид WITH ROLLUP

Цитата Akina @ 11.10.17, 19:40

dummy

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

Цитата ^D^ima @ 12.10.17, 10:34

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

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

Ясно

Akina
Теоретический вопрос:

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

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

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

Akina
Временная таблица должна быть создана в таблицах базы данных?

Добавлено 16.10.17, 12:36

Цитата Akina @ 16.10.17, 11:15

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

именно так.

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

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) Прежде чем создавать процедуру, надо проверить существование предыдущей версии, и если она есть - удалить её.

IF OBJECT_ID ( 'SchemaName.procedureName', 'P' ) IS NOT NULL
    DROP PROCEDURE SchemaName.procedureName;
GO

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

Сообщение отредактировано: Akina - 16.10.17, 13:45

 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'?
Первый день взял так

select convert(datetime,   '01.'+convert(char(2), month(getdate())-1)+'.'+convert(char(4), year(getdate())) )

но как-то длинно получилось, можно ли короче? А последний вообще не пойму как взять...

Добавлено 17.10.17, 07:53
Последний день предыдущего месяца взял так:

EOMONTH(GETDATE( ),-1)

как туда 23:59:59 поставить?

Получилось 2 вариантами:
не знаю какой более правильный:

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')

Добавлено 17.10.17, 09:14
Первый день прошлого месяца:

select convert(date, dateadd(month,-1,  dateadd(day,1-day(GETDATE( )),GETDATE( )) ) )

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

Добавлено 17.10.17, 09:26
Пока взял это:

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

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

( YEAR(ExpenditureEntry.Date) = YEAR( GETDATE( )) and    MONTH(ExpenditureEntry.Date)=MONTH(GETDATE( ))-1)

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

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

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

Цитата ^D^ima @ 17.10.17, 08:55

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

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

Цитата ^D^ima @ 17.10.17, 08:55

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

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

Цитата Akina @ 17.10.17, 09:56

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

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

Цитата ^D^ima @ 17.10.17, 10:04

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

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

Akina
Не подскажешь, как-то можно взять итог и подитог только по 2 столбцам?
Есть такой запрос:

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 @ 15.11.17, 11:58

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

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

Получилось так:
Я поставил с скобки

GROUP BY
rollup (Category.FileAs,(Company.FileAs,Project.Comments))

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

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

Цитата ^D^ima @ 15.11.17, 11:58

LEFT OUTER JOIN

Цитата ^D^ima @ 20.11.17, 11:13

У некоторых компаний несколько отраслей(список с множественным выбором)

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

Цитата ^D^ima @ 20.11.17, 11:13

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

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

Цитата Akina @ 20.11.17, 11:46

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

спасибо

Цитата Akina @ 20.11.17, 11:46

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

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

Нет смысла указывать поле - нам не надо отсеивать NULL-значения.

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

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)

Друзья, так и не нашел ответа на вопрос.

Ну типа

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 @ 05.12.17, 09:39

ругается на

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

Цитата Akina @ 12.12.17, 07:02

SUM(TimeEntry.Duration)/60.0 AS Сумма

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

Цитата Akina @ 12.12.17, 07:02

CAST(SUM(TimeEntry.Duration) AS FLOAT)/60 AS Сумма

Бинго!!!

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

Цитата Akina @ 12.12.17, 07:02

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

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

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

Цитата ^D^ima @ 12.12.17, 07:11

как округлять?

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

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

where Query1.Проект=Query2.Проект and Query2.Проект=Query3.Проект

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

Цитата ^D^ima @ 12.12.17, 11:48

Query1.Проект=Query2.Проект=Query3.Проект, но такая конструкция недопустима.

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

Цитата ^D^ima @ 12.12.17, 11:48

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

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

Вот полный текст запроса:

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.Проект а именно на второе равно(Неправильный синтаксис около конструкции "=".)

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

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

Опять нужна помощь:

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

ругается на

round(Сумма/Время,2)  as "Стоимость часа 5\2"

На Сумма/Время, т.к. выше они объявлены как суммы неких данных. Как их верно сюда запихнуть?

Вместо них поместить вычисляющие их выражения...

round(sum( PaymentDetail.Value)/CAST(SUM(TimeEntry.Duration) AS FLOAT)/60,2)  as "Стоимость часа 5\2"

Как-то странно считает:

Сумма    Время    Стоимость часа 5\2
160000,00      2    22,2222222222222

Сообщение отредактировано: ^D^ima - 16.01.18, 12:53

Скобки ещё нужны:

round( sum(PaymentDetail.Value) / (CAST(SUM(TimeEntry.Duration) AS FLOAT)/60) , 2) as "Стоимость часа 5\2"

Или последнее деление заменить на умножение:

round( sum(PaymentDetail.Value) / CAST(SUM(TimeEntry.Duration) AS FLOAT) * 60 , 2) as "Стоимость часа 5\2"

Сообщение отредактировано: Akina - 16.01.18, 13:12

Опять не могу элементарное победить:

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

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)):

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 - 16.01.18, 14:48

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

Немного не тот столбец, смотри тот где
Null, null, 0, 0.5, 135, 14, 220, 4, 5

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

Сообщение отредактировано: ^D^ima - 16.01.18, 16:57

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

MIF
Похоже на то если просто CAST('0,5' as float) сделать, то ошибку вываливает

Попробую заиенить запятую точкой в Category.Fileas

Так работает, но как-то монструозно выглядит )

sum( CAST(isnull(REPLACE(Category.FileAs,',','.'),0) as float))

Может в самом аксессе можно сменить локаль с американской на русскую?

MIF
Может быть, только это не аксесс

Попробуй format(fileas, ‘N’, ,’en-US’)

Сообщение отредактировано: MIF - 16.01.18, 19:49

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

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

То на выходе получаю 2 строки

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

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

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

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

Цитата ^D^ima @ 25.01.18, 10:21

на выходе получаю 2 строки

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

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

Проект1

Запись о времени1

Запись о времени2

Счет1

Akina
А если их будет не 2 а 10 к примеру?
И как мне соединить как ты показал?

Цитата ^D^ima @ 25.01.18, 13:37

А если их будет не 2 а 10 к примеру?

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

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

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

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

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

Цитата Akina @ 25.01.18, 13:42

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

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

Вот и получается:

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 Кбайт, скачиваний: 782)

Цитата ^D^ima @ 25.01.18, 14:42

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

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

Нужно чтобы на выходе была 1 строка с суммой времени было 60, а суммой суммы 80000

Цитата ^D^ima @ 25.01.18, 16:41

Нужно чтобы на выходе была 1 строка с суммой времени было 60, а суммой суммы 80000

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

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

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

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

Цитата Akina @ 25.01.18, 16:39

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

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

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

Цитата ^D^ima @ 25.01.18, 17:26

Я не знаю по какому условию соединить подзапросы. Могут быть варианты когда у проекта нет поступлений или нет записей о времени

Ну у тебя же MS SQL, который прекрасно умеет и FULL OUTER JOIN, и CROSS APPLY. Да и оконные функции у него есть, так что можно запросто использовать что-то вроде

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 - 26.01.18, 11:11

почему такая конструкция не прокатывает:

(CASE WHEN  count(distinct TimeEntry.id)<>0
                     THEN count(distinct TimeEntry.id)
                     ELSE 1 END)

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

Добавлено 26.01.18, 17:48
Вот так помоему тоже не прокатывает, если в запросе при левом соединении нет PaymentDetail.id, говорит деление на ноль:

count(distinct (case when PaymentDetail.id > 0 then PaymentDetail.id end) )

Добавлено 26.01.18, 17:58
семен-семеныч:

 count(distinct (isnull (PaymentDetail.id,1) ) )

Добавлено 26.01.18, 18:14
Что-то монструозное получилось, но вродебы как рабочее.
так не понял как делать:

Цитата Akina @ 25.01.18, 17:15

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

сделал в 1 запросе:

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 @ 26.01.18, 17:24

Что-то монструозное получилось, но вродебы как рабочее.
так не понял как делать

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

Сообщение отредактировано: Bas - 30.01.18, 23:56

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

Добавлено 31.01.18, 06:14

Цитата Bas @ 30.01.18, 23:55

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

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

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 Кбайт, скачиваний: 730)

Так ближе:

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 Кбайт, скачиваний: 698)

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

Добавлено 08.02.18, 14:14
Так работает, но можно красивее сделать?

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 Проект

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 @ 11.02.18, 07:11

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 @ 13.04.18, 13:28

в поле select, выражение присвоенное через AS ещё раз использовать в том-же Select?

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

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

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

можно q1 вподзапросе вычислить.

Как проще like и in совместить?

Project.ContractNumber like in('ЮК%','АО%','АС%','ЮКПРС%','Б%','Иное ПРС%','ЮК%','СОЮ%','Включение%','ПК%','И%','Л%','М%','Р%','Иное НР%')

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

Используй RLike и собери всё в один шаблон.

Цитата Akina @ 15.05.18, 16:01

Используй RLike и собери всё в один шаблон.

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

В MS SQL тоже есть регэкспы.

Запиши строки поиска в отдельную таблицу.
Добавь JOIN в свой запрос:

JOIN MyTable mt ON Project.ContractNumber like mt.MyField

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

MIF а эту таблицу придется удалять и создавать в запросе каждый раз?

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

Цитата ^D^ima @ 16.05.18, 18:23

а эту таблицу придется удалять и создавать в запросе каждый раз?

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

Друзья, какая агрегатная функция не суммирует а вычитает?

А что из чего вычитать-то?

Есть числовой столбец, нужно его не суммировать, а отнять,сгрупировав по 2му полю

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

Цитата ^D^ima @ 06.07.18, 18:58

Есть числовой столбец, нужно его не суммировать, а отнять

Цитата Akina @ 06.07.18, 20:03

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

JoeUser
Не так. 2+2=4. Со знаком минус будет -4, а должно быть 0(2-2)


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

Почему начали вычитать именно из 1, а не из, скажем, 11?

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

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

Цитата ^D^ima @ 06.07.18, 21:02

а должно быть 0(2-2)

Это с какого перепугу?
Суммируем мы же с нуля, не??? 0+значение записи1+значение записи2+ ... значение записиN
А чем вычитание "лучше"???

Скрытый текст

Просто есть подозрение что ты развиваешь тему "Как с помощью SQL зОхватить мир?"

Добавлено 07.07.18, 18:26

Цитата MIF @ 07.07.18, 10:36

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

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

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

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

Цитата ^D^ima @ 09.07.18, 07:32

всего по 2 цифры на первый столбец

MAX(field) - MIN(field) AS delta

Цитата Akina @ 09.07.18, 08:13

MAX(field) - MIN(field) AS delta

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

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

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

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

А ещё лучше взять как-то последнюю группу цифр, т.е. варианты могут быть в теории - 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 - 16.07.18, 12:01

Цитата ^D^ima @ 11.07.18, 13:42

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

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

Цитата ^D^ima @ 11.07.18, 14:50

варианты могут быть в теории - 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 таблицы.

ID, ФИО
1 Иванов
2 Петров
3 Сидоров
 
ID, Дата, Сумма
1 01.01.2018 100руб
1 01.02.2018 200руб
2 01.05.2018 200руб

Как на выходе получить Таблицу такого плана:

ФИО, Январь, Февраль, март, Апрель, Май
Иванов 100р.   200р.
Петров                              200р.

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

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

PIVOT

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

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

WITH common_table_expression (Transact-SQL)

Akina
Спасибо за помощь

А как передать параметр в подзапрос?
например мне нужно А1 передать в подзапрос как условие к where

Select A1, (select B1 from table2 where table2.c1=A1)
From table1

Select table1.A1, (select table2.B1 from table2 where table2.c1=table1.A1)
From table1

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

Ещё такой вопрос:
Есть таблица:

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

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

Т.е. на выходе должно быть это:

FIO,Number
Иванов 1
Иванов 2
Сидоров 5
Сидоров 6

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 - 20.09.18, 16:38

Akina, Мощно!!!

Как можно из этого запроса:

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-х раз одной и той-же формулы

Cast( REVERSE(substring(REVERSE(TaskRequest.FileAs), PatIndex('%[0-9]%', REVERSE(TaskRequest.FileAs)), PatIndex('%[^0-9]%', substring(REVERSE(TaskRequest.FileAs)

Ещё такой вопрос:
Результат отчета:

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

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

В прошлые разы было предложено решение сделать

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 @ 21.09.18, 07:07

избавиться от записи 2-х раз одной и той-же формулы

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

Цитата ^D^ima @ 21.09.18, 08:15

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

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

CASE WHEN MAX(field) = MIN(field)
     THEN MAX(field)
     ELSE MAX(field) - MIN(field)
END

Цитата Akina @ 21.09.18, 08:42

CASE WHEN MAX(field) = MIN(field)
     THEN MAX(field)
     ELSE MAX(field) - MIN(field)
END

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

Не пойму алгоритмически как решить задачу:
У нас есть таблица

Дата, Сумма
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 неясно что писать

AND SUM(CASE WHEN 'Дата'=@дата2 THEN 1 ELSE 0 END)>0

не заработало:

Цитата

Статистическое выражение не может использоваться в предложении WHERE, если оно не содержится во вложенном запросе предложения HAVING или в списке выбора, и столбец, подвергаемый статистической обработке, не является внешней ссылкой.

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

Цитата ^D^ima @ 21.09.18, 11:41

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

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

Спасибо. Некоторые конструкции мне тяжело переваривать

ЕслИ "ничего не брать " означает ноль, то NULL надо заменить на 0.

SELECT a.Summa * b.Cnt
FROM
(
Select 'Summa'
From table1
Where 'Дата'>=@дата1 and 'Дата'<=@дата2
) as a
CROSS APPLY
(SELECT CASE WHEN
 COUNT(*) >0 THEN 1 ELSE NULL END) AS Cnt
FROM Tabe1
Where 'Дата'=@дата2
)as b

Сообщение отредактировано: MIF - 25.09.18, 09:47

Select 'Сумма' * CASE WHEN Max('Дата')=@дата2 THEN 1 ELSE 0 END
From table1
Where 'Дата'>=@дата1 and 'Дата'<=@дата2

Вопрос про запись данных. Никогда этим не занимался.

Есть такой запрос

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 @ 16.01.19, 15:13

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

Тупо зачеркнуть в SELECT одно имя поля и вставить другое.

Цитата ^D^ima @ 16.01.19, 15:13

DocumentClass=24,DocumentID=Company.ID,CategoryID=12

Соответственно добавить в список выбираемых полей два INTEGER литерала.

Цитата ^D^ima @ 16.01.19, 15:13

если в таблице DocumentCategory нет записи

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

Сообщение отредактировано: Akina - 16.01.19, 17:52

Akina
Как-то так? Боюсь запускать

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

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

Добавлено 16.01.19, 21:45

Цитата Akina @ 16.01.19, 17:51

Поле DocumentID - уникальное?

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

Получается как-то так тогда?

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 @ 16.01.19, 21:08

как-то можно это выполнить в отладочном режиме без записи самих данных в таблицу?

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

Вернее, не очень нормальный. У тебя:

from Project
left JOIN Company ON Company.ID = Project.CustomerCompanyID
where (что-то) and Company.FileAs is not null

что вырождается в

from Project
inner JOIN Company ON Company.ID = Project.CustomerCompanyID
where (что-то)

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

Цитата ^D^ima @ 16.01.19, 21:08

Получается как-то так тогда?

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

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 @ 17.01.19, 04:35

ON (target.DocumentID = source.DocumentID) /* Слияние по условию */

А остальные условия не должны там быть?

ON (target.DocumentClass=24, target.DocumentID = source.DocumentID, target.CategoryID=12)

Цитата ^D^ima @ 17.01.19, 06:30

А остальные условия не должны там быть?

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

Цитата Akina @ 17.01.19, 11:31

У тебя дубликат - по одному полю.

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

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

ааа, вон оно как... тогда да, все три поля...

ON (targetDocumentClass = 24 AND target.DocumentID = source.DocumentID AND target.CategoryID = 12)

На что ругается?

Сообщение 547, уровень 16, состояние 0, строка 5
Конфликт инструкции MERGE с ограничением FOREIGN KEY "DocumentCategoriesFK". Конфликт произошел в базе данных "PJM10", таблица "dbo.Document".

Прикреплённый файлsql.png (53,79 Кбайт, скачиваний: 1183)

В этой базе есть хранимая процедура, которая как я понимаю и отвечает за добавление категории в документ, может быть эту процедуру использовать? В том плане что вызвать?

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, так лучше?

Добавлено 23.01.19, 15:15
Он вот с таким запускается параметрами:

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?

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

Друзья, видимо пятница, не пойму как решить простую с виду задачу:

Взять клиентов, проекты по которым были в 2016-2017 годах, но не были в 2018-2019

Сделал так вначале, но понял что это не сработает

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 @ 22.03.19, 10:25

Взять клиентов, проекты по которым были в 2016-2017 годах, но не были в 2018-2019

Цитата ^D^ima @ 22.03.19, 19:41

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

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 @ 22.03.19, 19:41

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

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_Проект	Проект	Деньги
2	1	1	Проект 1	500
2	3	3	Проект 3	200
null	null	2	Проект 2	300
null	null	4	Проект 4	2000

На выходе должно быть:

ID_КорневойПроект	ID_ПодчиненныйПроект	ID_Проект	Проект	Деньги
null	null	2	Проект 2	1000
null	null	4	Проект 4	2000

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

Для SQL Server:

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
Твой пример сломал мне мозг

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 @ 17.01.20, 06:43

Это какая-то рекурсия?

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

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

Цитата ^D^ima @ 17.01.20, 06:43

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

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

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

Цитата Wound @ 17.01.20, 15:15

синтаксис в разных БД может немного отличаться

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

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

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

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

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

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

Цитата Akina @ 17.01.20, 19:01

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

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

Коллеги, помогите посчитать.

Есть таблица:

Сотрудник	Месяц	Сумма	Количество
Иванов	Январь	100	2
Иванов	Февраль	200	2
Иванов	март		2
Петров	Январь		1
Петров	Февраль	200	1
Петров	Март		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 Кбайт, скачиваний: 102)

Цитата Zhydkih @ 29.04.22, 08:23

поле Сумма получает текстовое значение

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

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

Сообщение отредактировано: Zhydkih - 29.04.22, 13:59

Цитата Zhydkih @ 29.04.22, 13:52

в поле сумма число разделено не запятой, а точкой

Ась? какая нафиг запятая??? разделитель целой и дробной части - это точка.

Цитата Zhydkih @ 29.04.22, 13:52

если использовать результаты этого запроса в другом запросе, то любое арифметическое действие над полем Сумма приводит к ошибке.

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

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

Цитата Zhydkih @ 29.04.22, 15:55

Это SQL запрос к серверу из Access

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

И?

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