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

[^D^ima]

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

[MIF]

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

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

[^D^ima]

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

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

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

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

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

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

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

[Akina]

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

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

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

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

Проект1

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

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

Счет1

[^D^ima]

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

[Akina]

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

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

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

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

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

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

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

[^D^ima]

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

[Akina]

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

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

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

[^D^ima]

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

[Akina]

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

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

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

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

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

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

[^D^ima]

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

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

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

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

[Akina]

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

[^D^ima]

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

[MIF]

Count(distinct(ид платежа))

Сообщение отредактировано: MIF - 26.01.18, 11:11

[^D^ima]

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

(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 Практика, [руководитель проекта]