需要帮助将第二个查询合并到第一个查询中(Need help incorporating a second query into first query)

需要帮助将第二个查询合并到第一个查询中(Need help incorporating a second query into first query)

我有第一个查询根据需要运行。 此查询按周将用户的活动级别聚合到相应的BusinessIds中。

出于报告目的，我需要创建一个新列，以捕获每个Grouped Week和BusinessId的活动级别大于0的UserId数。 第二个查询自己给了我正确的值（虽然它可能有比合并后所需的数据点更多的数据点），但是我很难将它合并到我的第一个查询中。

这可能是一个相当直接的努力，但我一直无法从其他问题的样本中找出它。 任何帮助表示赞赏。

/* First Query */ SET DATEFIRST 2; SELECT SUM( fua.[RisksAffected] +fua.[IssuesAffected] +fua.[ChangesAffected]) AS RIC ,SUM( fua.[ProjectsCreated] +fua.[ProjectsAffected]) AS Projects ,CASE WHEN SUM(fua.[ProjectsCreated]) >0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS ProjectCreated ,SUM(fua.[TimesheetsCreated]) AS Timesheets ,SUM(fua.[ReportsAffected]) AS Reports ,SUM(fua.[FilesAffected]) AS Files ,ud.[BusinessId] ,COUNT ( DISTINCT fua.UserId ) AS [UserCount] ,DateAdd(day, -1 * datepart(dw,fua.[QueryDate]),fua.[QueryDate]) AS [ActivityDate] FROM [client_projectmanager].[whs].[FactUserActivity] fua Left Join [client_projectmanager].[whs].[DimUserDataV2] ud ON fua.[UserId] = ud.[UserId] GROUP BY DateAdd(day, -1 * datepart(dw,fua.[QueryDate]),fua.[QueryDate]),ud.[BusinessId] /* Second Query */ SET DATEFIRST 2; SELECT count (distinct sela.UserId) ,sela.BusinessId ,DateAdd(day, -1 * datepart(dw,sela.[Date]),sela.Date) FROM (SELECT fua.UserId ,ud.BusinessId ,DateAdd(day, -1 * datepart(dw,fua.[QueryDate]),fua.[QueryDate]) AS [Date] ,SUM( fua.[RisksAffected] +fua.[IssuesAffected] +fua.[ChangesAffected] +fua.[ProjectsCreated] +[ProjectsAffected]) AS Acttotal FROM [client_projectmanager].[whs].[FactUserActivity] fua Left Join [client_projectmanager].[whs].[DimUserDataV2] ud ON fua.[UserId] = ud.[UserId] GROUP BY DateAdd(day, -1 * datepart(dw,fua.[QueryDate]),fua.[QueryDate]) ,fua.[UserId] ,ud.BusinessId HAVING SUM(fua.[RisksAffected] +fua.[IssuesAffected] +fua.[ChangesAffected] +fua.[ProjectsCreated] +fua.[ProjectsAffected]) >0) sela GROUP BY DateAdd(day, -1 * datepart(dw,sela.[Date]),sela.Date) ,sela.BusinessId ORDER BY DateAdd(day, -1 * datepart(dw,sela.[Date]),sela.Date)

I have the first query running as needed. This query aggregates activity levels for Users into the corresponding BusinessIds, by week.

For reporting purposes, I needed to create a new column that captures the number of UserIds that have activity levels above 0 for each Grouped Week and BusinessId. The second query gives me the correct values on its own (although it may have more data points than needed for after the merge), but I am having a difficult time merging this into my first query.

This is likely a pretty straight forward effort, but I have been unable to figure it out from samples in other questions. Any help is appreciated.

/* First Query */ SET DATEFIRST 2; SELECT SUM( fua.[RisksAffected] +fua.[IssuesAffected] +fua.[ChangesAffected]) AS RIC ,SUM( fua.[ProjectsCreated] +fua.[ProjectsAffected]) AS Projects ,CASE WHEN SUM(fua.[ProjectsCreated]) >0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS ProjectCreated ,SUM(fua.[TimesheetsCreated]) AS Timesheets ,SUM(fua.[ReportsAffected]) AS Reports ,SUM(fua.[FilesAffected]) AS Files ,ud.[BusinessId] ,COUNT ( DISTINCT fua.UserId ) AS [UserCount] ,DateAdd(day, -1 * datepart(dw,fua.[QueryDate]),fua.[QueryDate]) AS [ActivityDate] FROM [client_projectmanager].[whs].[FactUserActivity] fua Left Join [client_projectmanager].[whs].[DimUserDataV2] ud ON fua.[UserId] = ud.[UserId] GROUP BY DateAdd(day, -1 * datepart(dw,fua.[QueryDate]),fua.[QueryDate]),ud.[BusinessId] /* Second Query */ SET DATEFIRST 2; SELECT count (distinct sela.UserId) ,sela.BusinessId ,DateAdd(day, -1 * datepart(dw,sela.[Date]),sela.Date) FROM (SELECT fua.UserId ,ud.BusinessId ,DateAdd(day, -1 * datepart(dw,fua.[QueryDate]),fua.[QueryDate]) AS [Date] ,SUM( fua.[RisksAffected] +fua.[IssuesAffected] +fua.[ChangesAffected] +fua.[ProjectsCreated] +[ProjectsAffected]) AS Acttotal FROM [client_projectmanager].[whs].[FactUserActivity] fua Left Join [client_projectmanager].[whs].[DimUserDataV2] ud ON fua.[UserId] = ud.[UserId] GROUP BY DateAdd(day, -1 * datepart(dw,fua.[QueryDate]),fua.[QueryDate]) ,fua.[UserId] ,ud.BusinessId HAVING SUM(fua.[RisksAffected] +fua.[IssuesAffected] +fua.[ChangesAffected] +fua.[ProjectsCreated] +fua.[ProjectsAffected]) >0) sela GROUP BY DateAdd(day, -1 * datepart(dw,sela.[Date]),sela.Date) ,sela.BusinessId ORDER BY DateAdd(day, -1 * datepart(dw,sela.[Date]),sela.Date)

最满意答案

如果要在一个语句中执行此操作，可以使用CTE。

SET DATEFIRST 2; WITH cte1 AS ( /* First Query */ SELECT SUM(fua.[RisksAffected] + fua.[IssuesAffected] + fua.[ChangesAffected]) AS RIC, SUM(fua.[ProjectsCreated] + fua.[ProjectsAffected]) AS Projects, CASE WHEN SUM(fua.[ProjectsCreated]) > 0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS ProjectCreated, SUM(fua.[TimesheetsCreated]) AS Timesheets, SUM(fua.[ReportsAffected]) AS Reports, SUM(fua.[FilesAffected]) AS Files, ud.[BusinessId], COUNT(DISTINCT fua.UserId) AS [UserCount], DATEADD(day,-1 * DATEPART(dw,fua.[QueryDate]),fua.[QueryDate]) AS [ActivityDate] FROM [client_projectmanager].[whs].[FactUserActivity] fua LEFT JOIN [client_projectmanager].[whs].[DimUserDataV2] ud ON fua.[UserId] = ud.[UserId] GROUP BY DATEADD(day,-1 * DATEPART(dw,fua.[QueryDate]),fua.[QueryDate]), ud.[BusinessId] ), cte2 AS ( /* Second Query */ SELECT COUNT(DISTINCT sela.UserId), sela.BusinessId, DATEADD(day,-1 * DATEPART(dw,sela.[Date]),sela.Date) FROM (SELECT fua.UserId, ud.BusinessId, DATEADD(day,-1 * DATEPART(dw,fua.[QueryDate]),fua.[QueryDate]) AS [Date], SUM(fua.[RisksAffected] + fua.[IssuesAffected] + fua.[ChangesAffected] + fua.[ProjectsCreated] + [ProjectsAffected]) AS Acttotal FROM [client_projectmanager].[whs].[FactUserActivity] fua LEFT JOIN [client_projectmanager].[whs].[DimUserDataV2] ud ON fua.[UserId] = ud.[UserId] GROUP BY DATEADD(day,-1 * DATEPART(dw,fua.[QueryDate]),fua.[QueryDate]), fua.[UserId], ud.BusinessId HAVING SUM(fua.[RisksAffected] + fua.[IssuesAffected] + fua.[ChangesAffected] + fua.[ProjectsCreated] + fua.[ProjectsAffected]) > 0 ) sela GROUP BY DATEADD(day,-1 * DATEPART(dw,sela.[Date]),sela.Date), sela.BusinessId ORDER BY DATEADD(day,-1 * DATEPART(dw,sela.[Date]),sela.Date) ) SELECT * FROM cte1 -- USE INNER JOIN IF YOU WANT ONLY RECORDS THAT EXIST IN BOTH LEFT JOIN cte2 ON cte1.BusinessId = cte2.BusinessId AND cte1.ActivityDate = cte2.GroupedByDateName

您的第二个查询缺少列别名..假设您正在加入BusinessId的2个查询和您要分组的日期，则需要添加列别名

You can use CTEs if you want to do this in one statement.

SET DATEFIRST 2; WITH cte1 AS ( /* First Query */ SELECT SUM(fua.[RisksAffected] + fua.[IssuesAffected] + fua.[ChangesAffected]) AS RIC, SUM(fua.[ProjectsCreated] + fua.[ProjectsAffected]) AS Projects, CASE WHEN SUM(fua.[ProjectsCreated]) > 0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS ProjectCreated, SUM(fua.[TimesheetsCreated]) AS Timesheets, SUM(fua.[ReportsAffected]) AS Reports, SUM(fua.[FilesAffected]) AS Files, ud.[BusinessId], COUNT(DISTINCT fua.UserId) AS [UserCount], DATEADD(day,-1 * DATEPART(dw,fua.[QueryDate]),fua.[QueryDate]) AS [ActivityDate] FROM [client_projectmanager].[whs].[FactUserActivity] fua LEFT JOIN [client_projectmanager].[whs].[DimUserDataV2] ud ON fua.[UserId] = ud.[UserId] GROUP BY DATEADD(day,-1 * DATEPART(dw,fua.[QueryDate]),fua.[QueryDate]), ud.[BusinessId] ), cte2 AS ( /* Second Query */ SELECT COUNT(DISTINCT sela.UserId), sela.BusinessId, DATEADD(day,-1 * DATEPART(dw,sela.[Date]),sela.Date) FROM (SELECT fua.UserId, ud.BusinessId, DATEADD(day,-1 * DATEPART(dw,fua.[QueryDate]),fua.[QueryDate]) AS [Date], SUM(fua.[RisksAffected] + fua.[IssuesAffected] + fua.[ChangesAffected] + fua.[ProjectsCreated] + [ProjectsAffected]) AS Acttotal FROM [client_projectmanager].[whs].[FactUserActivity] fua LEFT JOIN [client_projectmanager].[whs].[DimUserDataV2] ud ON fua.[UserId] = ud.[UserId] GROUP BY DATEADD(day,-1 * DATEPART(dw,fua.[QueryDate]),fua.[QueryDate]), fua.[UserId], ud.BusinessId HAVING SUM(fua.[RisksAffected] + fua.[IssuesAffected] + fua.[ChangesAffected] + fua.[ProjectsCreated] + fua.[ProjectsAffected]) > 0 ) sela GROUP BY DATEADD(day,-1 * DATEPART(dw,sela.[Date]),sela.Date), sela.BusinessId ORDER BY DATEADD(day,-1 * DATEPART(dw,sela.[Date]),sela.Date) ) SELECT * FROM cte1 -- USE INNER JOIN IF YOU WANT ONLY RECORDS THAT EXIST IN BOTH LEFT JOIN cte2 ON cte1.BusinessId = cte2.BusinessId AND cte1.ActivityDate = cte2.GroupedByDateName

your second query is missing column aliases.. assuming you're joining the 2 queries by BusinessId and the Date you're grouping by, you need to add column aliases