Search code examples
sqldatesumrangeansi-sql

sql sum sales between date

The image explains everything. My aim is to the last table using sql.

I will explain the logic with an example.

Table 1 shows the expected sales. For instance, article 22 should be sold only between 1 janv and 4 jan. To make it simple we assume that shops are open every day.

Table 2 shows the daily sales for each article. For instance article was sold 1 janv 2 janv 3 janv 4 janv. However, for article 22 4 janv is not in the range [1 janv to 3 janv]. Thus to get the sum of sales of article 22, we should omit the 4 jan. Therefore the calculation for article 22 is 2+4+5=11.

enter image description here

DataType

Table 1

  • artNo: int
  • from: date
  • planned_to: date

Table 2

  • artNo: int
  • day: date
  • sales: float
Solution

  • Something like this:

    SELECT
    t1.artNo
    , t1.[from]
    , t1.planned_to
    , SUM(t2.sales) total
FROM
    table1 t1
    JOIN table2 t2 ON
        t1.artNo = t2.artNo
        AND t2.day >= t1.[from]
        AND t2.day <= t1.planned_to
GROUP BY
    t1.artNo
    , t1.[from]
    , t1.planned_to