SQL Server How to SUM two SUM columns in SQL that share a similar field value

olmpazwi 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(128)

I have a SQL query:

SELECT R.ResourceID,
          R.EmailAddr,
          CAL.Comapny_Year_Week,
          CAL.Company_AP,
          T.StatusCode,
          SUM(T.StandardHours) AS "Total Hours"
   FROM PMGB.PM.CompanyAPCalendar AS CAL
   JOIN E4GB.dbo.Time AS T ON CAST(CAL.full_date AS DATE) = CAST(T.TimeEntryDate AS DATE)
   JOIN E4GB.dbo.Resource AS R ON T.ResourceID = R.ResourceID
   JOIN E4GB.dbo.ResourceWorkHourGroup AS WHG ON WHG.ResourceID = R.ResourceID
   WHERE T.StatusCode != 'V'
     AND R.EmailAddr IS NOT NULL
   GROUP BY R.ResourceID,
            R.EmailAddr,
            CAL.Company_Year_Week,
            CAL.Company_AP,
            T.StatusCode;

The result of the query shows rows of data displaying user information, calendar information and SUM of Hours booked for a given Week.

When the StatusCode = 'T': I would like to SUM the SUM(T.StandardHours) WITH SUM(T.StandardHours) WHERE StatusCode = 'A' For any rows that share the same Company_Year_Week.

For example: If the Company_Year_Week = '2021 Wk24' has 2 rows of data that share the same value for this field then the Total Hours column should be the SUM(SUM(T.StandardHours) WHERE T.StatusCode = 'T', SUM(T.StandardHours) WHERE T.StatusCode = 'A').

How can I achieve this?

I tried removing the SUM T.StandardHours and using subqueries. Nothing I try seems to provide a solution to the problem I have.

sql-server

来源：https://stackoverflow.com/questions/77281751/how-to-sum-two-sum-columns-in-sql-that-share-a-similar-field-value

1条答案

按热度按时间

gorkyyrv1#

I would start by writing a query that uses just the CompanyAPCalendar and Time tables, to get the sum you are looking for at a high level (essentially what will go inside an inner query, or a cross / outer apply) and then build it up from there

Without understanding / knowing anything about your tables / data, I would guess something like this is what you might be looking for:

SELECT
    R.ResourceID,
    R.EmailAddr,
    A.Company_Year_Week,
    A.Company_AP,
    A.StatusCode,
    A.TotalHours

FROM E4GB.dbo.Resource AS R
OUTER APPLY (
    SELECT
        CAL.Company_Year_Week,
        CAL.Company_AP,
        T.StatusCode,
        SUM(T.StandardHours) AS TotalHours
    FROM E4GB.dbo.Time AS T
    LEFT JOIN PMGB.PM.CompanyAPCalendar AS CAL ON CAL.full_date = T.TimeEntryDate
    WHERE
        T.ResourceID = R.ResourceID
        AND T.StatusCode != 'V'
    GROUP BY
        T.ResourceID,
        T.StatusCode,
        CAL.Company_Year_Week,
        CAL.Company_AP
) A

WHERE
    R.EmailAddr IS NOT NULL

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server How to SUM two SUM columns in SQL that share a similar field value

1条答案

相关问题

热门标签

最新问答