CODEWITHSUNDEEP
reporting-services
BartM
BartM

Reputation: 23

Sum of all values within two date fields

I try to make a sum of all values within two date fields (StartDate and EndDat of Dataset1).

The date field are only in a different dataset then the number field (Dataset 2)

Here is an example of the data:

Dataset 1

Name        StartDate        EndDate   
-----------------------------------
1234        01-01-2018       31-12-2018

Dateset2

Name    Date         Weight
-----------------------------------
1234    31-12-2017   10000
1234    4-1-2018     20000
1234    8-1-2018     44000
1234    17-8-2018    50000

In the above example, the weight of 31-12-2017 should not be included in the sum

Thanks in advance for your help!

Upvotes: 0

Views: 48

Answers (2)

JonTout
JonTout

Reputation: 640

You might need to include the name from Dataset 2 and add Grouping

SELECT Name, SUM(Weight) as Weight
FROM Dateset2 
WHERE Date >= (SELECT StartDate FROM DataSet1)
AND Date <= (SELECT EndDate FROM DataSet1)
Group By Name

What does the SSRS Report look like?

Upvotes: 0

Abhishek_Itachi
Abhishek_Itachi

Reputation: 159

Try This :-

SELECT SUM(Weight) FROM Dateset2 WHERE Date >= (SELECT StartDate FROM DataSet1)
AND Date <= (SELECT EndDate FROM DataSet1)

Please let me know if this helps.

PS- DataSet1 contains 1 row. Else filter out data from dateset1 to 1 row

Upvotes: 0

Related Questions