SQL复杂筛选器+数据透视

Question

我有以下表格。

用户

userID     fullName
5          Mr. Joe
7          Mr. Bean

供应品

id     supplyDesc     isActive
1      Ballpen         1
2      Adhesive Tape   1
3      White Paper     1

Supply请求

id  requestCode     forFrom      forTo        userID
1   SPR-2017-1      12-01-2017   02-28-2018   5
2   SPR-2017-2      12-01-2017   02-28-2018   7
3   SPR-2017-3      01-01-2018   03-31-2018   7

供应商请求详细信息

id  rqID   supplyID  storeID    qty   isActive
1   1      1         1          1000  1
2   1      2         1          2000  1
3   1      1         12         2000  1
4   1      2         12         3000  1
5   1      1         13         3000  1
6   1      2         13         4000  1
7   2      1         10         100   1
8   2      2         10         200   1
9   2      1         11         200   1
10  2      2         11         300   1
11  3      1         10         1     1
12  3      2         10         2     1
13  3      1         11         2     1
14  3      2         11         3     1

要求：

在给定日期范围内，获取所有用户及其每个供应项目的相应请求总数，以及他们请求的唯一分支的总数。必须是请求详细信息和供应表中的活动供应项目

注意供应可能因而异

到目前为止我尝试了什么：

declare @forFrom varchar(50) = '12/01/2017'
declare @forTo varchar(50) = '02/28/2018'
declare @cols nvarchar(max), @cols2 nvarchar(max), @query NVARCHAR(MAX)
select @cols = STUFF((SELECT DISTINCT ', MAX(' + QUOTENAME(supplyDesc) + ')' + '''' + supplyDesc + ''''
FROM (select a.id'supplyID',
a.supplyDesc,
SUBSTRING(d.fullName, 5, LEN(MAX(d.fullName)))fullName,
SUM(CASE WHEN ((CAST(c.forFrom as datetime) >= @forFrom and CAST(c.forTo as datetime) <= @forTo)) THEN b.qty ELSE 0 END)totalQty
from Supplies a
left join SupplyRequestDetails b
on a.id = b.supplyID
left join SupplyRequests c
on b.rqID = c.id
left join Users d
on c.userID = d.id
where a.isActive = 1
or b.isActive = 1
group by a.id,
a.supplyDesc,
fullName
) as x
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'');
select @cols2 = STUFF((SELECT DISTINCT ',' + QUOTENAME(supplyDesc)
FROM (select a.id'supplyID',
a.supplyDesc,
SUBSTRING(d.fullName, 5, LEN(MAX(d.fullName)))fullName,
SUM(CASE WHEN ((CAST(c.forFrom as datetime) >= @forFrom and CAST(c.forTo as datetime) <= @forTo)) THEN b.qty ELSE 0 END)totalQty
from Supplies a
left join SupplyRequestDetails b
on a.id = b.supplyID
left join SupplyRequests c
on b.rqID = c.id
left join Users d
on c.userID = d.id
where a.isActive = 1
or b.isActive = 1
group by a.id,
a.supplyDesc,
fullName
) as x
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
SET @query = N'
declare @forFrom varchar(50) = ''12/01/2017''
declare @forTo varchar(50) = ''02/28/2018''
SELECT SUBSTRING(fullName, 5, LEN(fullName))fullName, ' + @cols + '
FROM (SELECT SUBSTRING(fullName, 5, LEN(fullName))fullName, ' + @cols + '
FROM (select a.id''supplyID'',
a.supplyDesc,
SUBSTRING(d.fullName, 5, LEN(MAX(d.fullName)))fullName,
SUM(CASE WHEN ((CAST(c.forFrom as datetime) >= @forFrom and CAST(c.forTo as datetime) <= @forTo)) THEN b.qty ELSE 0 END)totalQty
from Supplies a
left join SupplyRequestDetails b
on a.id = b.supplyID
left join SupplyRequests c
on b.rqID = c.id
left join Users d
on c.userID = d.id
where a.isActive = 1
or b.isActive = 1
group by a.id,
a.supplyDesc,
fullName
) as j
PIVOT (
SUM(totalQty) FOR supplyDesc IN ('
+ @cols2
+ ')
) AS a
WHERE fullName is not null
GROUP BY fullName
ORDER BY fullName;';

PRINT @query
EXEC sp_executesql @query

结果就像：

fullName     Ballpen        Adhesive Tape     White Paper
Mr.Joe       6303           9505              NULL

预期结果集：

fullName  uqStores  Ballpen        Adhesive Tape     White Paper
Mr. Joe   3         6000           4000              NULL
Mr. Bean  2         300            500               NULL

正如您可能注意到的，我首先尝试从耗材表中获取项目列表，并尝试(但失败了)将每个项目标记给在给定条件(日期范围和活动状态)内分别请求每个项目的用户。然后，创建了动态列，并准备了一个供应轴心。此外，我还没有弄清楚如何从这些查询中计算出所有唯一的storeID。

我认为第一个问题是我如何尝试为每个项目的用户添加标签。不过，我不知道如何把它们和一堆物品同时放在一起。

我们将非常感谢你的帮助。

编辑：

我不得不解决一些问题。我想我抄错了项目。

更新：

fullName      Ballpen      Adhesive Tape      White Paper
Mr. Joe       6000         9000               NULL
Mr. Bean      300          500                NULL

PS：不知怎么的，我一点一点地找到了解决方案。现在我只需要获得每个请求的唯一存储。

更新：

这看起来很有效，但确实很混乱。

DECLARE @forFrom varchar(50) = '12/01/2017'
DECLARE @forTo varchar(50) = '02/28/2018'
declare @cols nvarchar(max), @cols2 nvarchar(max), @query NVARCHAR(MAX)
select @cols = STUFF((SELECT DISTINCT ', MAX(' + QUOTENAME(supplyDesc) + ')' + '''' + supplyDesc + ''''
FROM (select DISTINCT a.id'supplyID',
a.supplyDesc,
COUNT(CASE WHEN ((CAST(c.forFrom as datetime) >= @forFrom and CAST(c.forTo as datetime) <= @forTo)) THEN b.storeID ELSE NULL END)numberOfStores,
SUBSTRING(d.fullName, 5, LEN(MAX(CASE WHEN (CAST(c.forFrom as datetime) >= @forFrom and CAST(c.forTo as datetime) <= @forTo) THEN d.fullName ELSE NULL END)))fullName,
SUM(CASE WHEN ((CAST(c.forFrom as datetime) >= @forFrom and CAST(c.forTo as datetime) <= @forTo)) THEN b.qty ELSE 0 END)totalQty
from Supplies a
left join SupplyRequestDetails b
on a.id = b.supplyID
left join SupplyRequests c
on b.rqID = c.id
left join Users d
on c.userID = d.id
where a.isActive = 1
or b.isActive = 1
group by a.id,
a.supplyDesc,
fullName
) as x
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'');
select @cols2 = STUFF((SELECT DISTINCT ',' + QUOTENAME(supplyDesc)
FROM (select DISTINCT a.id'supplyID',
a.supplyDesc,
COUNT(CASE WHEN ((CAST(c.forFrom as datetime) >= @forFrom and CAST(c.forTo as datetime) <= @forTo)) THEN b.storeID ELSE NULL END)numberOfStores,
SUBSTRING(d.fullName, 5, LEN(MAX(CASE WHEN (CAST(c.forFrom as datetime) >= @forFrom and CAST(c.forTo as datetime) <= @forTo) THEN d.fullName ELSE NULL END)))fullName,
SUM(CASE WHEN ((CAST(c.forFrom as datetime) >= @forFrom and CAST(c.forTo as datetime) <= @forTo)) THEN b.qty ELSE 0 END)totalQty
from Supplies a
left join SupplyRequestDetails b
on a.id = b.supplyID
left join SupplyRequests c
on b.rqID = c.id
left join Users d
on c.userID = d.id
where a.isActive = 1
or b.isActive = 1
group by a.id,
a.supplyDesc,
fullName
) as x
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
SET @query = N'
declare @forFrom varchar(50) = ''' + @forFrom + '''
declare @forTo varchar(50) = ''' + @forTo + '''
SELECT SUBSTRING(fullName, 5, LEN(fullName))fullName, COUNT(numberOfStores)numOfStores,' + @cols + '
FROM (select DISTINCT a.id''supplyID'',
a.supplyDesc,
COUNT(CASE WHEN ((CAST(c.forFrom as datetime) >= @forFrom and CAST(c.forTo as datetime) <= @forTo)) THEN b.storeID ELSE NULL END)numberOfStores,
SUBSTRING(d.fullName, 5, LEN(MAX(CASE WHEN (CAST(c.forFrom as datetime) >= @forFrom and CAST(c.forTo as datetime) <= @forTo) THEN d.fullName ELSE NULL END)))fullName,
SUM(CASE WHEN ((CAST(c.forFrom as datetime) >= @forFrom and CAST(c.forTo as datetime) <= @forTo)) THEN b.qty ELSE 0 END)totalQty
from Supplies a
left join SupplyRequestDetails b
on a.id = b.supplyID
left join SupplyRequests c
on b.rqID = c.id
left join Users d
on c.userID = d.id
where a.isActive = 1
or b.isActive = 1
group by a.id,
a.supplyDesc,
fullName
) as j
PIVOT (
SUM(totalQty) FOR supplyDesc IN ('
+ @cols2
+ ')
) AS a
WHERE fullName is not null
GROUP BY fullName
ORDER BY fullName;';
EXEC sp_executesql @query

当然还有更好的方法。我真的很喜欢你的建议。

更新

来源于@sarslan的回答并编辑：

DECLARE @Query NVARCHAR(MAX) = '
SELECT * FROM (
SELECT 
S.supplyDesc 
, fullName
, COUNT(DISTINCT storeID) [uqStores]
, SUM(qty) SumQty   
FROM 
Supplies S
LEFT JOIN ( SELECT U.fullName, supplyID, storeID, qty, SRD.isActive FROM SupplyRequestDetails SRD 
INNER JOIN SupplyRequests SR ON SRD.rqID = SR.id AND ( SR.forFrom >= @forFrom AND SR.forTo <= @forTo ) 
INNER JOIN Users U ON SR.userID = U.userID ) AS ST ON ST.supplyID = S.id
WHERE ST.isActive = 1
GROUP BY fullName, supplyDesc 
HAVING COUNT(DISTINCT storeID) > 1
) SRC
PIVOT ( SUM (SumQty) FOR supplyDesc IN ( ' + @ColNames + ')) PVT '

例如，假设SupplyRequestDetails中id为1的项目将变为非活动状态。

结果是：

fullName      Ballpen      Adhesive Tape      White Paper
Mr. Bean      5000         NULL               NULL
Mr. Bean      NULL         9000               NULL
Mr. Joe       300          500                NULL

Answer 1

您可以使用此脚本。

declare @forFrom datetime = '12/01/2017'
declare @forTo datetime = '02/28/2018'
DECLARE @ColNames NVARCHAR(MAX)= ''
SELECT @ColNames = @ColNames + ',' + QUOTENAME (supplyDesc) FROM Supplies WHERE isActive = 1
SET @ColNames = STUFF(@ColNames,1,1,'')
DECLARE @Query NVARCHAR(MAX) = '
SELECT * FROM (
SELECT 
S.supplyDesc 
, fullName
, COUNT(DISTINCT storeID) [uqStores]
, SUM(qty) SumQty   
FROM 
Supplies S
LEFT JOIN ( SELECT U.fullName, supplyID, storeID, qty, SRD.isActive FROM SupplyRequestDetails SRD 
INNER JOIN SupplyRequests SR ON SRD.rqID = SR.id AND ( SR.forFrom >= @forFrom AND SR.forTo <= @forTo ) 
INNER JOIN Users U ON SR.userID = U.userID 
WHERE SRD.isActive = 1 ) AS ST ON ST.supplyID = S.id
WHERE S.isActive = 1 
GROUP BY fullName, supplyDesc 
HAVING COUNT(DISTINCT storeID) > 1
) SRC
PIVOT ( SUM (SumQty) FOR supplyDesc IN ( ' + @ColNames + ')) PVT '

EXEC sp_executesql @Query, N'@forFrom  DATETIME, @forTo DATETIME' , @forFrom = @forFrom, @forTo = @forTo

结果：

fullName             uqStores    Ballpen     Adhesive Tape White Paper
-------------------- ----------- ----------- ------------- -----------
Mr. Bean             2           300         500           NULL
Mr. Joe              3           6000        9000          NULL