CODEWITHSUNDEEP
mysqlsqloracle-database
Claes
Claes

Reputation: 13

Given a table with time periods, query for a list of sum per day

Let's say I have a table that says how many items of something are valid between two dates. Additionally, there may be multiple such periods.

For example, given a table:

itemtype  | count | start       | end
A         | 10    | 2014-01-01  | 2014-01-10
A         | 10    | 2014-01-05  | 2014-01-08

This means that there are 10 items of type A valid 2014-01-01 - 2014-01-10 and additionally, there are 10 valid 2014-01-05 - 2014-01-08. So for example, the sum of valid items at 2014-01-06 are 20. How can I query the table to get the sum per day? I would like a result such as

2014-01-01  10
2014-01-02  10
2014-01-03  10
2014-01-04  10
2014-01-05  20
2014-01-06  20
2014-01-07  20
2014-01-08  20
2014-01-09  10
2014-01-10  10

Can this be done with SQL? Either Oracle or MySQL would be fine

Upvotes: 1

Views: 688

Answers (2)

WoMo
WoMo

Reputation: 7266

There are several ways to go about this. First, you need a list of dense dates to query. Using a row generator statement can provide that:

select date '2014-01-01' + level -1 d 
from dual 
connect by level <= 15;

Then for each date, select the sum of inventory:

with 
  sample_data as
    (select 'A' itemtype, 10 item_count, date '2014-01-01' start_date, date '2014-01-10' end_date from dual union all
     select 'A', 10, date '2014-01-05', date '2014-01-08' from dual),
  periods as (select date '2014-01-01' + level -1 d from dual connect by level <= 15)
select 
  periods.d,
  (select sum(item_count) from sample_data where periods.d between start_date and end_date) available
from periods
where periods.d = date '2014-01-06';

You would need to dynamically set the number of date rows to generate.

If you only needed a single row, then a query like this would work:

with 
  sample_data as
    (select 'A' itemtype, 10 item_count, date '2014-01-01' start_date, date '2014-01-10' end_date from dual union all
     select 'A', 10, date '2014-01-05', date '2014-01-08' from dual)
select sum(item_count)
from sample_data
where date '2014-01-06' between start_date and end_date;

Upvotes: 0

talegna
talegna

Reputation: 2403

The basic syntax you are looking for is as follows:

For my example below I've defined a new table called DateTimePeriods which has a column for StartDate and EndDate both of which are DATE columns.

SELECT 
    SUM(NumericColumnName)
    , DateTimePeriods.StartDate
    , DateTimePeriods.EndDate
FROM 
    TableName
    INNER JOIN DateTimePeriods ON TableName.dateColumnName BETWEEN DateTimePeriods.StartDate and DateTimePeriods.EndDate
GROUP BY 
    DateTimePeriods.StartDate
    , DateTimePeriods.EndDate

Obviously the above code won't work on your database but should give you a reasonable place to start. You should look into GROUP BY and Aggregate Functions. I'm also not certain of how universal BETWEEN is for each database type, but you could do it using other comparisons such as <= and >=.

Upvotes: 1

Related Questions