|
Помогите составить запрос
#38364877
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
сомневаюсь, что "это" можно использовать в "боевой ситуации" :),
но всё-таки - "работает"
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
insert into @t (service_id, user_id, payment_date, days)
select 1, 1, '20130101', 10 union all
select 1, 1, '20130102', 1 union all
select 1, 1, '20130105', 1 union all
select 1, 1, '20130120', 10 union all
select 1, 1, '20130501', 1 union all
select 1, 1, '20130502', 2 union all
select 1, 1, '20130510', 10
--insert into @t (service_id, user_id, payment_date, days)
--select 1, 2, '20130101', 10 union all
--select 1, 2, '20130102', 1 union all
--select 1, 3, '20130105', 20 union all
--select 1, 3, '20130120', 10 union all
--select 1, 3, '20130501', 7 union all
--select 1, 3, '20130502', 2
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
select * from @t
select t.service_id, t.user_id, isnull(b.st, t.payment_date) as payment_date, max(isnull(b.sumdays, t.days)) as days
from @t t
left join
(select a.service_id, a.user_id, min(a.st) as st, a.fn, max(a.sumdays+a.days) as sumdays
from
(select t1.service_id, t1.user_id, t1.payment_date as st, t2.payment_date as fn, t2.days
,(select sum(t3.days) from @t t3 where t1.service_id=t3.service_id and t1.user_id=t3.user_id and t3.payment_date>=t1.payment_date and t3.payment_date<t2.payment_date) as sumdays
from @t t1 inner join @t t2
on t1.payment_date < t2.payment_date
and t1.service_id = t2.service_id
and t1.user_id = t2.user_id) a
where a.fn between a.st and a.st+a.sumdays
group by a.service_id, a.user_id, a.fn ) b
on t.service_id=b.service_id
and t.user_id=b.user_id
and t.payment_date=b.fn
group by t.service_id, t.user_id, isnull(b.st, t.payment_date)
order by 1,2,3
@t
idservice_iduser_idpayment_datedays1112013-01-01 00:00:00.000102112013-01-02 00:00:00.00013112013-01-05 00:00:00.00014112013-01-20 00:00:00.000105112013-05-01 00:00:00.00016112013-05-02 00:00:00.00027112013-05-10 00:00:00.00010
result
service_iduser_idpayment_datedays112013-01-01 00:00:00.00012112013-01-20 00:00:00.00010112013-05-01 00:00:00.0003112013-05-10 00:00:00.00010
и проверка сводится к
1.
exists (select 1 from result where service_id=@service_id and user_id=@user_id and @check_date between payment_date and payment_date+days)
зы
на МС СКЛ, сори |
|
|
