SQL sum data between 2 dates in one column

David Graig

I have a table with orders that is looks like:

t1

+-----------+---------+------------+
| client ID | order q | order date |
+-----------+---------+------------+
|     01    |  100    | 01-02-2016 |
|     01    |  350    | 03-05-2016 |
+-----------+---------+------------+

And I have second table with sales:

t2

+-----------+-------+------------+
| client ID | sales | sales date |
+-----------+-------+------------+
|   01      | 50    | 03-02-2016 |
|   01      | 50    | 10-02-2016 |
|   01      | 300   | 04-05-2016 |
|   01      | 50    | 15-05-201  |
+-----------+-------+------------+  

The aim is to get SUM of sales MORE than first order date and LESS than second order date:

Result

+-----------+---------+-------+
| cliend ID | order q | sales |
+-----------+---------+-------+
|  01       | 100     | 100   |
|  01       | 350     | 350   |
+-----------+---------+-------+

First idea is to make order dates rating like

DENSE_RANK() OVER(PARTITION BY [client ID] ORDER BY [order date] ASC) AS R

then do something like this:

select
    client ID,
    order q,
    sum (sales) as sales
from 
    t2  
left outer join
    t2.client ID = t1.client ID
where 
    [sales date] >= [order date] 
    and [sales date] <= [order date] in (select [order date] 
                                         from t2 
                                         where (R < (R+1)))
group by 
    client ID, order q

I know this is wrong but this is the logic in my head.

Can you please give me some ideas how to do this?

Serg

Looks like sum between dates. I assume including current order date excluding next order date.

select t.clientID, fromdate, sum(sales) 
from (
    select clientID,orderq
        , fromdate = orderdate
        , todate = dateadd(dd, -1, lead(orderdate,1,cast('2100-01-01' as date)) over(partition by clientID order by orderdate))
    from(
            values
            (01,100,cast('2016-02-01' as date)),
            (01,350,cast('2016-05-03' as date))
        ) orders(clientID,orderq,orderdate)
    ) t
join( 
        values  
        (01,50  ,cast('2016-02-03' as date)),
        (01,50  ,cast('2016-02-10' as date)),
        (01,300 ,cast('2016-05-04' as date)),
        (01,50  ,cast('2016-05-15' as date))
    ) sales(clientID, sales,salesdate)
    on sales.salesdate between fromdate and todate
group by t.clientID, fromdate   

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集
0

コメントを追加

0

関連記事

分類Dev

Select dates between date range and sum over a column

分類Dev

sum the number of seconds between dates of the same stationid sql server

分類Dev

differences between dates in sql

分類Dev

Show One Column Data as Two Columns in SQL

分類Dev

Merge data into one column - sql server 2012

分類Dev

Filter between dates and SUM with LEFT JOIN and GROUP BY

分類Dev

SQL column sum and difference

分類Dev

Oracle SQL Between Dates Query

分類Dev

Difference between dates in PySpark SQL

分類Dev

SQL select between two dates

分類Dev

SQL Access, Sum one column's values only when ALL values in another column in are in specified range

分類Dev

R: how to use the aggregate()-function to sum data from one column if another column has a distinct value?

分類Dev

SQL create new column based on the one-to-one or one-to-many relationship between two original columns

分類Dev

MySQL SUM of one column, DISTINCT of ID column

分類Dev

Retrieve sorted data based on one numeric column in SQL

分類Dev

T SQL Group column with accumulating data in one record

分類Dev

Sql need to sum column several times and break result in one row horizontally

分類Dev

subsetting data frame on sum of column

分類Dev

SQL : Two Columns Count Third Column Sum of 1st and 2nd Column

分類Dev

SQL: select distinct on column1 and sum up column2 values of merged rows

分類Dev

merge 2 data frames in a loop for each column in one of them

分類Dev

Sum between a range of dates from two different worksheets

分類Dev

For loop sum of numbers between specific dates in array of objects

分類Dev

How to check Time between 2 Dates

分類Dev

Reading logs with the word 'ERROR' between 2 dates

分類Dev

SQL Server : 2 Stored Procedure 1 for XML & one for data

分類Dev

Writing dates to SQL Server data from R

分類Dev

How to select column with greatest difference between dates in MySQL

分類Dev

SUM() function differences between SQL Server and MySQL

Related 関連記事

  1. 1

    Select dates between date range and sum over a column

  2. 2

    sum the number of seconds between dates of the same stationid sql server

  3. 3

    differences between dates in sql

  4. 4

    Show One Column Data as Two Columns in SQL

  5. 5

    Merge data into one column - sql server 2012

  6. 6

    Filter between dates and SUM with LEFT JOIN and GROUP BY

  7. 7

    SQL column sum and difference

  8. 8

    Oracle SQL Between Dates Query

  9. 9

    Difference between dates in PySpark SQL

  10. 10

    SQL select between two dates

  11. 11

    SQL Access, Sum one column's values only when ALL values in another column in are in specified range

  12. 12

    R: how to use the aggregate()-function to sum data from one column if another column has a distinct value?

  13. 13

    SQL create new column based on the one-to-one or one-to-many relationship between two original columns

  14. 14

    MySQL SUM of one column, DISTINCT of ID column

  15. 15

    Retrieve sorted data based on one numeric column in SQL

  16. 16

    T SQL Group column with accumulating data in one record

  17. 17

    Sql need to sum column several times and break result in one row horizontally

  18. 18

    subsetting data frame on sum of column

  19. 19

    SQL : Two Columns Count Third Column Sum of 1st and 2nd Column

  20. 20

    SQL: select distinct on column1 and sum up column2 values of merged rows

  21. 21

    merge 2 data frames in a loop for each column in one of them

  22. 22

    Sum between a range of dates from two different worksheets

  23. 23

    For loop sum of numbers between specific dates in array of objects

  24. 24

    How to check Time between 2 Dates

  25. 25

    Reading logs with the word 'ERROR' between 2 dates

  26. 26

    SQL Server : 2 Stored Procedure 1 for XML & one for data

  27. 27

    Writing dates to SQL Server data from R

  28. 28

    How to select column with greatest difference between dates in MySQL

  29. 29

    SUM() function differences between SQL Server and MySQL

ホットタグ

アーカイブ