Sql-server – Only Show Data For Min(woID)

sql serversql-server-2008-r2t-sql

With my data I am only wanting to show the SUM(ISNULL(dolamt,0)) for the MIN(woID) – however my query shows the SUM(ISNULL(dolamt,0)) for every row. Below is sample DDL –

Declare @Info Table
(
    customer varchar(500)
    ,dolamt decimal(10,2)
    ,pddate date
)

Declare @Custs Table
(
    customer varchar(500)
    ,active varchar(10)
)

Declare @WorkOrders Table
(
    customer varchar(500)
    ,woID int
    ,sitename varchar(250)
    ,companycost decimal(10,2)
)

Insert Into @WorkOrders (customer, woID, sitename, companycost) Values
('Customer A', 1, 'Site 1', '10.23'), ('Customer A', 2, 'Site 1', '23.33'),
('Customer A', 3, 'Site 1', '44.12'), ('Customer B', 4, 'Site 2', '44.33'),
('Customer B', 5, 'Site 2', '12.12'), ('Customer C', 6, 'Site3', '22.11'),
('Customer D', 7, 'Site 4', '22.11'), ('Customer D', 8, 'Site 5', '100.12'),
('Customer D', 9, 'Site 6', '23.14')

INSERT INTO @Custs (customer, active) Values
('Customer A', 'Yes'), ('Customer B', 'Yes')
,('Customer C', 'Yes'), ('Customer D', 'Yes')
,('Customer E', 'No'), ('Customer F', 'No')

Insert Into @Info (customer, dolamt, pddate) Values
('Customer A',  '3286.12',  '7/17/2016')
,('Customer A', '9665', '11/25/2016')
,('Customer A', '154.13',   '6/29/2016')
,('Customer B', '73575.37', '12/30/2016')
,('Customer A', '134491.15',    '9/30/2016')
,('Customer A', '112.5',    '11/30/2016')
,('Customer C', '1140.9',   '8/31/2016')
,('Customer C', '5512', '2/8/2016')
,('Customer C', '82323',    '7/31/2016')
,('Customer C', '450',  '10/23/2016')
,('Customer D', '6050.91',  '3/13/2016')
,('Customer B', '520.84',   '8/19/2016')
,('Customer D', '1554.35',  '8/31/2016')

Select
cs.Customer
,[Work Order ID] = COALESCE(wo.woID,0)
,[Company Cost] = SUM(ISNULL(wo.companycost,0))
,[Total Amt] = COALESCE((Select Top 1 SUM(ISNULL(dolamt,0)) FROM @Info nf WHERE nf.Customer = wo.Customer),0)
FROM @WorkOrders wo
RIGHT JOIN @Custs cs
ON cs.Customer = wo.customer
GROUP BY cs.Customer, wo.woID, wo.Customer
ORDER BY cs.Customer ASC

Which produces this result set
Current Results

I want this to be returned result set (only update MIN(woID))
Desired Results

What do I need to change in my query so that I get the desired result set returned?

Best Answer

Try this:

SELECT cs.Customer
    ,[Work Order ID] = COALESCE(wo.woID, 0)
    ,[Company Cost] = SUM(ISNULL(wo.companycost, 0))
    ,[Total Amt] = CASE 
        WHEN wo.woid = (
                SELECT min(woid)
                FROM @WorkOrders
                WHERE customer = cs.customer
                )
            THEN COALESCE((
                        SELECT TOP 1 SUM(ISNULL(dolamt, 0))
                        FROM @Info nf
                        WHERE nf.Customer = wo.Customer
                        ), 0)
        ELSE 0
        END
FROM @WorkOrders wo
RIGHT JOIN @Custs cs ON cs.Customer = wo.customer
GROUP BY cs.Customer
    ,wo.woID
    ,wo.Customer
ORDER BY cs.Customer ASC