SQL Server – Loop in Stored Procedure to Calculate Sum

sql servert-sql

I'm trying to write a stored procedure that does the sum calculation on a field using parameters ID and date.

For this purpose I have two tables. My table that contains data

ID      CAF     SDATE
1       12345   15/06/2018
1       04564   16/06/2018
1       03589   30/06/2018
1       00099   01/07/2018
3       00056   15/06/2018
3       00325   20/06/2018
4       00123   01/06/2018
5       04445   12/06/2018
5       16676   23/06/2018

And the table that contains parameters

Prameter table

ID  SDATE1      SDATE2
1   01/06/2018  30/06/2018
3   15/06/2018  30/06/2018
4   01/06/2018  30/06/2018
5   15/06/2018  01/07/2018

My query that I want to use in my stored procedure: 

select T1.ID,SUM(T1.CAF) From 
(SELECT *
  FROM TABLE
  where ID = @ID
  and SDate between @SDATE1 and @SDATE2) T1
Group By ID

The result I'm expecting is the following:

ID  SUM(CAF)    
1   20498   
3   381 
4   123 
5   16676

I believe that I need to run some kind of loop to run my query for every ID, but I'm struggling a little bit.

Best Answer

No loop needed, you can calculate the sums simply by joining by ID and filtering the date with a BETWEEN.

SELECT
    P.ID,
    SUM(D.CAF)
FROM
    Data AS D
    INNER JOIN Parameter AS P ON D.ID = P.ID
WHERE
    D.SDATE BETWEEN P.SDATE1 AND P.SDATE2
GROUP BY
    P.ID
Related Question