#sql #sql-server #pivot #common-table-expression #unpivot
#sql #sql-server #сводная #common-table-expression #отменить
Вопрос:
У меня есть данные SQL Server, которые содержат доступную и выделенную информацию, и мне нужно показать отчет, например, доступные данные, выделенные данные и данные баланса для каждого ресурса.
Прямо сейчас у меня есть вся информация в одной строке, и необходимо рассчитать данные баланса. Как я могу восстановить ожидаемые данные?
Текущие данные и выходные данные
Доступно
--- ------------- ------------ --------------------- --------- ------------ ------------
| Id| ResourceName| EmployeeId | iGPMResourceGroupId | GroupId | Capacity01 | Capacity02 |
--- ------------- ------------ --------------------- --------- ------------ ------------
| 1 | Palanisamy | 24 | 1025135 | 15 | 0.70 | 0.70 |
| 2 | Anil | 20 | 1018707 | 15 | 1.00 | 1.00 |
| 3 | Ravi | 18 | 1025136 | 15 | 0.50 | 0.50 |
| 4 | Manikumar | 9 | 1025164 | 29 | 1.00 | 1.00 |
| 5 | Sakathi | 11 | 1020687 | 29 | 1.00 | 1.00 |
--- ------------- ------------ --------------------- --------- ------------ ------------
Спрос
------------ ------------- ---------------------
| Number | ProjectName | iGPMResourceGroupId |
------------ ------------- ---------------------
| BM-00000001| Project 1 | 1020687 |
| BM-00000002| Project 2 | 1020687 |
| BM-00000002| Project 2 | 1025136 |
| BM-00000003| Project 3 | 1025164 |
| BM-00000002| Project 2 | 1025135 |
| BM-00000003| Project 3 | 1025135 |
| BM-00000003| Project 3 | 1020687 |
| BM-00000002| Project 2 | 1025164 |
------------ ------------- ---------------------
Выделенный
---- --------------------- ---------------- ------------ --------------------- ------------ ----------- -----------
| Id | AvailableResourceId | AssociateName | EmployeeId | iGPMResourceGroupId | ProjectId | Staffed01 | Staffed02 |
---- --------------------- ---------------- ------------ --------------------- ------------ ----------- -----------
| 1 | 5 | Sakathi | 11 | 1020687 | BM-00000001| 0.30 | 0.30 |
| 2 | 5 | Sakathi | 11 | 1020687 | BM-00000003| 0.30 | 0.30 |
| 3 | 3 | Ravi | 18 | 1025136 | BM-00000002| 0.50 | 0.50 |
---- --------------------- ---------------- ------------ --------------------- ------------ ----------- -----------
Запрос
SELECT ResourceName, iGPMResourceGroupId, ProjectName, StaffedId, Capacity01, Capacity02, Staffed01, Staffed02
FROM
(
SELECT Distinct A. ResourceName, A.EmployeeId, A.[iGPMResourceGroupId], D.[ProjectName], S.[Id] AS StaffedId, A.[Capacity01], A.[Capacity02], S.[Staffed01], S.[Staffed02]
FROM [AvailableR] A JOIN [DemandR] D ON A.[iGPMResourceGroupId] = D.[iGPMResourceGroupId]
LEFT JOIN [dbo].[AllocatedR] S ON A.Id = S.[AvailableResourceId] AND S.Number = D.Number AND S.[iGPMResourceGroupId] = D.[iGPMResourceGroupId]
)X
ORDER BY EmployeeId
Вывод
-------------- --------------------- ------------- ----------- ------------ ------------ ----------- -----------
| ResourceName | iGPMResourceGroupId | ProjectName | StaffedId | Capacity01 | Capacity02 | Staffed01 | Staffed02 |
-------------- --------------------- ------------- ----------- ------------ ------------ ----------- -----------
| Sakathi | 1020687 | Project 1 | 1 | 1 | 1 | 0 | 0 |
| Sakathi | 1020687 | Project 2 | (null) | 1 | 1 | (null) | (null) |
| Sakathi | 1020687 | Project 3 | 2 | 1 | 1 | 0 | 0 |
| Ravi | 1025136 | Project 2 | 3 | 0 | 0 | 1 | 1 |
| Palanisamy | 1025135 | Project 2 | (null) | 1 | 1 | (null) | (null) |
| Palanisamy | 1025135 | Project 3 | (null) | 1 | 1 | (null) | (null) |
| Manikumar | 1025164 | Project 2 | (null) | 1 | 1 | (null) | (null) |
| Manikumar | 1025164 | Project 3 | (null) | 1 | 1 | (null) | (null) |
-------------- --------------------- ------------- ----------- ------------ ------------ ----------- -----------
Ожидаемый результат:
Осталось = Доступно — Сумма (выделено)
-------------- --------------------- ------------- ----------- ------------ ------ ------
| ResourceName | iGPMResourceGroupId | ProjectName | Status | StaffedId | 01 | 02 |
-------------- --------------------- ------------- ----------- ------------ ------ ------
| Sakathi | | | Available | (null) | 1 | 1 |
| Sakathi | 1020687 | Project 1 | Alloted | 1 | 0.30 | 0.30 |
| Sakathi | 1020687 | Project 2 | Alloted | (null) |(null)|(null)|
| Sakathi | 1020687 | Project 3 | Alloted | 2 | 0.30 | 0.30 |
| Sakathi | | | Left | (null) | 0.40 | 0.40 |
| Ravi | | | Available | (null) | 0.50 | 0.50 |
| Ravi | 1025136 | Project 2 | Alloted | 3 | 0.50 | 0.50 |
| Ravi | | | Left | (null) | 0 | 0 |
| Palanisamy | | | Available | (null) | 1 | 1 |
| Palanisamy | 1025135 | Project 2 | Alloted | (null) |(null)|(null)|
| Palanisamy | 1025135 | Project 3 | Alloted | (null) |(null)|(null)|
| Palanisamy | | | Left | (null) | 1 | 1 |
| Manikumar | | | Available | (null) | 1 | 1 |
| Manikumar | 1025164 | Project 2 | Alloted | (null) |(null)|(null)|
| Manikumar | 1025164 | Project 3 | Alloted | (null) |(null)|(null)|
| Manikumar | | | Left | (null) | 1 | 1 |
-------------- --------------------- ------------- ----------- ------------ ------ ------
Ответ №1:
Итак, если я вас правильно понял, вам нужен дополнительный столбец «Слева» с вычисленным результатом и новый столбец, в котором хранится это значение для каждого ресурса? Я думаю, вы можете добиться этого путем перепроектирования вашего запроса и использования двух подзапросов:
WITH cte AS(
SELECT Distinct A. ResourceName, A.EmployeeId, A.[iGPMResourceGroupId], NULL AS [ProjectName], N'Available' AS Status, NULL AS StaffedId, A.[Capacity01], A.[Capacity02], NULL AS [Staffed01], NULL AS [Staffed02]
FROM [AvailableR] A
JOIN [DemandR] D ON A.[iGPMResourceGroupId] = D.[iGPMResourceGroupId]
UNION ALL
SELECT Distinct A. ResourceName, A.EmployeeId, A.[iGPMResourceGroupId], D.[ProjectName], N'Alloted' AS Status, S.[Id] AS StaffedId, A.[Capacity01], A.[Capacity02], S.[Staffed01], S.[Staffed02]
FROM [AvailableR] A JOIN [DemandR] D ON A.[iGPMResourceGroupId] = D.[iGPMResourceGroupId]
LEFT JOIN [dbo].[AllocatedR] S ON A.Id = S.[AvailableResourceId] AND S.Number = D.Number AND S.[iGPMResourceGroupId] = D.[iGPMResourceGroupId]
),
cteLeft AS(
SELECT ResourceName, EmployeeID, iGPMResourceGroupId, SUM([Capacity01] [Capacity02]) - SUM(Staffed01 Staffed02) AS LeftTotal
FROM cte
WHERE Status = N'Alloted'
GROUP BY ResourceName, EmployeeID, iGPMResourceGroupId
)
SELECT *, NULL AS [Left]
FROM cte
UNION ALL
SELECT Distinct ResourceName, EmployeeId, [iGPMResourceGroupId], NULL AS [ProjectName], N'Left' AS Status, NULL AS StaffedId, NULL AS [Capacity01], NULL AS [Capacity02], NULL AS [Staffed01], NULL AS [Staffed02], [LeftTotal] AS [Left]
FROM cteLeft
ORDER BY 1
Подробности см. в fiddle: http://sqlfiddle.com /#!18/2a30d/16/0
Комментарии:
1. Да, вы все правильно поняли, но ресурс Anil не должен поступать, поскольку у него нет спроса.
2. Конечно, я добавил в запрос соединение по требованию — теперь Anil фильтруется из результата. Я также обновил скрипку
3. Тем не менее, это не ожидаемый результат
4. Хорошо, я только что изменил свой запрос (еще не обновил ответ), но при этом я задаюсь вопросом: в вашем ожидаемом результате (в вопросе) Manicumar выделяется в двух проектах, но, учитывая ваш пример скрипки, Manikumar вообще не выделяется — итак, неисправен ли скрипка из-заожидаемый результат? Вот моя обновленная скрипка: sqlfiddle.com /#!18/2a30d/25/0