Using the SalesOrderHeader and SalesTerritory tables, write a query to calculate the number of transactions and total monthly amount per territory for each of the states based on the Status field. Status values ​​are: 1 = In process ; 2 = Approved ; 3 = Backordered ; 4 = Rejected ; 5 = Shipped ; 6 = Canceled The result table must contain the following fields:

1. Month : month in YYYY-MM format
2. NameTerritory : Name of the Territory
3. TrProcess : Transactions in process (Quantity)
4. TrApproved : Approved Transactions (Quantity)
5. TrBackordered : Overdue Transactions (Quantity)
6. TrRejected : Rejected Transactions (Quantity)
7. TrShipped : Transactions Sent (Amount)
8. TrCanceled : Transactions Canceled (Quantity)
9. MntProcess: Total amount in process
10. MntApproved: Total amount Approved
11. MntBackordered: Total Amount Arrears
12. MntRejected: Total Rejected amount
13. MntShipped: Total amount sent
14. MntCanceled: Total amount Canceled

SELECT 
    FORMAT(A.ShipDate, 'yyyy-MM') as Mes,
    B.Name,
    (SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 1) as TrProcess,
    (SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 2) as TrApproved,
    (SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 3) as TrBackordered,
    (SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 4) as TrRejected,
    (SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 5) as TrShipped,
    (SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 6) as TrCanceled,
    (SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 1) as MntProcess,
    (SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 2) as MntApproved,
    (SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 3) as MntBackordered,
    (SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 4) as MntRejected,
    (SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 5) as MntShipped,
    (SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 6) as MntCanceled
FROM [dbo].[SalesOrderHeader] A
INNER JOIN [dbo].[SalesTerritory] B on A.TerritoryID = B.TerritoryID
GROUP BY FORMAT(A.ShipDate,'yyyy/MM')

I have this query set up but I can't get my query to return the amounts. I would really appreciate if someone could help me.

CodePudding user response：

Your logic is flawed there is no correlation between the sub queries and the branch/date in the main query so they total everything. Using conditional aggregation demonstrates the difference

SELECT 
    FORMAT(A.ShipDate, 'yyyy-MM') as mes,
    B.Name,
    (SELECT count(Status) FROM [sales].[SalesOrderHeader] where Status = 5) as TrProcess,
     sum(case when status = 5 then 1 else 0 end)  as trtprocesstrue

FROM [sales].[SalesOrderHeader] A
INNER JOIN [sales].[SalesTerritory] B on A.TerritoryID = B.TerritoryID
where b.name = 'northwest'
GROUP BY b.name,FORMAT(A.ShipDate, 'yyyy-MM') ;

mes        Name                                               TrProcess   trtprocesstrue
---------- -------------------------------------------------- ----------- --------------
2005-07    Northwest                                          31465       20
2005-08    Northwest                                          31465       28
2005-09    Northwest                                          31465       21
2005-10    Northwest                                          31465       22