1

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.

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.