1

Topic: The help with advanced query

Good afternoon!
There are 2 tables. In tab. #x there is an information on a time interval when the goods (id) cost cheaper.
In tab. #y the information on sales per every day. It is necessary to write request which would deduce the total qty for all time intervals when the goods (id) cost cheaper also the total qty with an equal time interval for the last dates and these dates should not be intersected with dates from table #x and if are intersected then again to take away an amount of days equal to an interval of these dates until then while any of them will not be in table #x.

create table #x (id int, startdate date, enddate date)
insert #x
select 1, ' 2018-01-11 ', ' 2018-01-13 ' union all
select 1, ' 2018-01-14 ', ' 2018-01-16 ' union all
select 1, ' 2018-01-20 ', ' 2018-01-22
create table #y (id int, idate date, qty int)
insert #y
select 1, ' 2018-01-08 ', 3 union all
select 1, ' 2018-01-09 ', 4 union all
select 1, ' 2018-01-10 ', 5 union all
select 1, ' 2018-01-11 ', 2 union all
select 1, ' 2018-01-12 ', 3 union all
select 1, ' 2018-01-13 ', 1 union all
select 1, ' 2018-01-14 ', 1 union all
select 1, ' 2018-01-15 ', 3 union all
select 1, ' 2018-01-16 ', 6 union all
select 1, ' 2018-01-17 ', 5 union all
select 1, ' 2018-01-18 ', 3 union all
select 1, ' 2018-01-19 ', 2 union all
select 1, ' 2018-01-20 ', 3 union all
select 1, ' 2018-01-21 ', 3 union all
select 1, ' 2018-01-22 ', 5

I.e. with 11/01 on 13/01 it is 3 days, means it is taken away this amount of days = with 08/01 on 10/01 (these dates are not present in table #x);
c 14/01 on 16/01 too 3 days, we take away them = with 11/01 on 13/01 (these dates are in table #x) means it is necessary to select others which are not present
In table #x then from dates that received with 11/01 on 13/01 we take away an amount of days = with 08/01 on 10/01 (these dates are not present in table #x);
Result:
6 - 12
10 - 12
11 - 10

2

Re: The help with advanced query

1. It is not necessary to bring down all in a heap.
2. At first generate intervals "with an equal time interval for the last dates and these dates should not be intersected with dates from table #x".
3. Well and to count "the total qty for all time intervals" is it is trivial.

3

Re: The help with advanced query

aleks222;
With 2 point also there are difficulties.