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
I want this to be returned result set (only update MIN(woID))
What do I need to change in my query so that I get the desired result set returned?
Best Answer
Try this: