CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user1130511
user1130511

Reputation: 253

Stored Procedure to Sum values based on multiple conditions and a table join

I am trying to write a stored procedure for the following set of data:

Table A:

ReconID | service    | customer   | date
1       | Service A  | Customer A | July 2017
2       | Service A  | Customer A | July 2017
3       | Service A  | Customer B | July 2017
4       | Service A  | Customer B | Aug 2017

Table B:

ReconID | actual_duration
1       | 10
2       | 20
3       | 5
4       | 2

I would like to:

1) Join the two tables,

2) Group by: Service, Customer, and Month+Year and then sum the actual duration.

So, in this case my new table would have the following:

service    | customer | date | sum_actual_duration
Service A  | Customer A | July 2017  | 30    *(First two records were summed to make 30)*
Service A  | Customer B | July 2017  | 5
Service A  | Customer B | Aug 2017   | 2

**Attempted but failed Stored procedure :( **

create PROCEDURE sum_ActualDuration
    @service varchar(50),
    @customer varchar(50),
    @date varchar(10),
    @sum_value int OUTPUT

SET @sum_value = (SELECT SUM( Z.actual_duration)
    FROM B
    LEFT OUTER JOIN A
    ON (A.ReconID = B.ReconID
WHERE A.service = @service AND A.customer = @customer AND A.date = @date
    )
AS
Begin
-- INSERT the new record
INSERT INTO sum_ActualDuration (service, customer, date, sum_value)
VALUES(@service, @customer, @date, @sum_value
    FROM B
    LEFT OUTER JOIN A
    ON (A.ReconID = B.ReconID)
    WHERE service = @service AND customer = @customer AND date = @date
)
end

Any help is much appreciated!

--- UPDATED with answer ---

/* --- CREATE TABLE --- */

--DROP TABLE A;
--DROP TABLE B;

CREATE TABLE A(reconID integer, service text, customer text, date text);

INSERT INTO A VALUES(1, 'Service A', 'Customer A', 'Jul-17');
INSERT INTO A VALUES(2, 'Service A', 'Customer A', 'Jul-17');
INSERT INTO A VALUES(3, 'Service A', 'Customer B', 'Jul-17');
INSERT INTO A VALUES(4, 'Service A', 'Customer B', 'Aug-17');
COMMIT;

SELECT * FROM A;

CREATE TABLE B(reconID integer, actual_duration integer);

INSERT INTO B VALUES(1, '10');
INSERT INTO B VALUES(2, '20');
INSERT INTO B VALUES(3, '5');
INSERT INTO B VALUES(4, '2');
COMMIT;

SELECT * FROM B;

/* --- STORED PROCEDURE --- */

--CREATE PROC sp_actualDuration
--AS


--DROP TABLE sum_ActualDuration;
CREATE TABLE sum_ActualDuration(service text, customer text, date text, sum_value integer);

INSERT INTO sum_ActualDuration (service, customer, date, sum_value)
SELECT a.service, a.customer, a.date, SUM(b.actual_duration)
FROM A a LEFT OUTER JOIN
    B b
    ON a.ReconID = b.ReconID
    GROUP BY a.service, a.customer, a.date;
    --WHERE a.service="Service A" AND a.customer="Customer A" AND a.date="Jul-17";

--END

--EXEC sp_actualDuration

SELECT * FROM sum_ActualDuration;

Upvotes: 1

Views: 2303

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you can do this all in one statement, an INSERT . . . SELECT:

INSERT INTO sum_ActualDuration (service, customer, date, sum_value)
    SELECT a.service, a.customer, a.date, SUM(b.actual_duration)
    FROM A a LEFT OUTER JOIN
         B b
         ON a.ReconID = b.ReconID
    WHERE a.service = @service AND a.customer = @customer AND a.date = @date;

You would seem to want the A table to be the first table for the outer join. Also note that I qualified all the column names.

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use group by and sum as below:

Select B.Service, B.Customer, B.[Date], Sum(A.Actual_duration)
FROM B
LEFT OUTER JOIN A
ON (A.ReconID = B.ReconID)
group by B.Service, B.Customer, B.[Date]

Upvotes: 1

Related Questions