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

[Akina]

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

[^D^ima]

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

[^D^ima]

[Akina]

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

[^D^ima]

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 строку в которой:

[Akina]

Гм... А что получится на тех же данных, если обернуть 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

[^D^ima]

Цитата 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

[Akina]

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

Select sum(СуммаПриход)as СуммаПриход, Компания, sum(СуммаРасхода) as СуммаРасхода
from (
         SELECT PaymentDetail.Value AS СуммаПриход, Company.FileAs AS Компания, sum(Expense.TotalPC) AS СуммаРасхода
         FROM PaymentDetail
         INNER JOIN Payment ON PaymentDetail.PaymentID = Payment.ID
         INNER JOIN   Company ON Payment.PayerCompanyID = Company.ID
         INNER JOIN Invoice ON PaymentDetail.InvoiceID = Invoice.ID
         INNER JOIN Project ON Invoice.ProjectID = Project.ID
         inner JOIN Expense ON Project.ID = Expense.ProjectID
         GROUP BY PaymentDetail.Value, Company.FileAs
     ) as dummy
GROUP BY  Компания
order by Компания

[^D^ima]

пришло в голову такое решение:
Если и платеж и расход связан с клиентом, то можно взять одним запросом все расходы, в другом все платежи и в условии 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

[Akina]

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

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

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

[^D^ima]

Цитата 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

[Akina]

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

[^D^ima]

Akina
теперь понятно,

[^D^ima]

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

SELECT   Company.FileAs AS Компания, ISNULL(sum(Expense.TotalPC),0) AS СуммаРасхода
FROM         Company
 
  left JOIN Project ON Company.ID = Project.CustomerCompanyID
  left join Expense  on Project.ID  = Expense.ProjectID
 
Where YEAR(Expense.Date) = YEAR( GETDATE( )) and    MONTH(Expense.Date) between 1 and MONTH(GETDATE( ))-1
GROUP BY Company.FileAs

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

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

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

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

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

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

[Akina]

Цитата ^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. Подумай, что ты будешь получать от этого запроса в ЯНВАРЕ...