jk
BAN USERThe question seems incomplete, and I would prefer to log the session id for ease of request.
However...
with userlog as (select ul.*,
rank() over (partition by ul.name, ul.event order by ul.name, ul.event, ul.event_time asc) as rn
from user_log ul
)
select u1.name
,u1.event_time login_time
,isnull(u2.event_time, getdate()) out_time
,case when u2.event_time is null then 'online' else '-' end status
,datediff(minute, u1.event_time, isnull(u2.event_time, getdate())) spend_time_in_m
,datediff(minute, u1.event_time, isnull(u2.event_time, getdate()))/60 spend_time_in_h
,(sum(datediff(minute, u1.event_time, isnull(u2.event_time, getdate()))) over (partition by u1.name order by u1.name))/60 as sum_spend_time
from userlog u1 left join userlog u2 on u1.name = u2.name and u1.rn = u2.rn and u2.event = 'out'
where u1.event = 'login'
order by 1
with disp_rec as(
select ds.*,
row_number()over(order by dispute_id) as row
from dispute_records ds
),
disp_rec_2 as(
select d1.*,
isnull(d2.created, current_timestamp) as end_date
from disp_rec d1 left join disp_rec d2 on d1.row=d2.row-1
)
select rep.charge_id,
rep.created,
rep.amount,
rep.seller_id,
rep.customer_id,
count(distinct rep.dispute_id) as cnt_dispute,
count(rep.evidence_id) as cnt_evidence,
min(rep.is_evidence*rep.win) as win
from (
select ch.*,
disp_rec_2.dispute_id,
disp_rec_2.created as beg_date,
disp_rec_2.end_date,
es.evidence_id,
es.created as evidence_date,
datediff(day, disp_rec_2.created, isnull(es.created, current_timestamp)) diff,
case when datediff(day, disp_rec_2.created, isnull(es.created, current_timestamp)) < 31 then 1 else 0 end as win,
case when es.evidence_id is null then 0 else 1 end is_evidence
from charges ch inner join disp_rec_2 on ch.charge_id=disp_rec_2.charge_id
left join evidence_submission es on ch.charge_id=es.charge_id
and disp_rec_2.created <= es.created
and disp_rec_2.end_date > es.created
) rep
group by rep.charge_id, rep.created, rep.amount, rep.seller_id, rep.customer_id
I think it is impossible to avoid duplicate results by writing only a query.
- jk September 04, 2019You can implement this by writing a stored procedure and using two temporary tables in it (although you can use one)