Решение 64 задачи (обучающий этап) сайта sql-ex.ru

[MN-xnin]

мля, нихера не понятно...почему is null((

[ikomicin]

Крутое решение 👍.Очень классно, что оно короткое, и его не сложно понять. Альтернативное решение с FULL JOIN:

SELECT COALESCE(incs.point, outs.point) point,
COALESCE(incs.date, outs.date) date,
CASE WHEN incs.date IS NULL THEN 'out' ELSE 'inc' END operation,
COALESCE(inc, out) money_sum
FROM (SELECT point, date, COALESCE(SUM(inc), 0) inc
FROM Income
GROUP BY point, date) incs
FULL JOIN
(SELECT point, date, COALESCE(SUM(out), 0) out
FROM Outcome
GROUP BY point, date) outs
ON incs.point = outs.point AND incs.date = outs.date
WHERE incs.date IS NULL OR outs.date IS NULL

[Game_Collider]

Мое решение:
WITH
data AS (SELECT *
FROM income
FULL JOIN outcome using(point, date)
), -- get all data in one table
only_income AS (
SELECT point, date, 'inc' as type, inc
FROM data
WHERE inc IS NOT NULL AND out IS NULL
), -- find points with only income
only_outcome AS (
SELECT point, date, 'out' as type, out
FROM data
WHERE inc IS NULL AND out IS NOT NULL
), --find points with only outcomes
sum_inc_by_day AS (
SELECT point, date, type, SUM(inc) as daily_sum
FROM only_income
GROUP BY point, date, type
), -- get daily sum by point
sum_out_by_day AS (
SELECT point, date, type, SUM(out) as daily_sum
FROM only_outcome
GROUP BY point, date, type
)-- get daily outcome by point
SELECT * FROM sum_inc_by_day
UNION
SELECT * FROM sum_out_by_day
ORDER BY point, date

[Svetonpiton]

with pivot as(

select
i.point, i.date, 'inc' as operation, sum(inc) as income, sum(out) as outcome
from
income i left join outcome o ON i.point=o.point and i.date=o.date
group by
i.point, i.date

union

select
o.point, o.date, 'out', sum(inc) as income, sum(out) as outcome
from
income i right join outcome o ON i.point=o.point and i.date=o.date
group by
o.point, o.date)


select point, date, operation, money_sum

from

(select point, date, operation,
case
when income is NULL then outcome
when outcome is NULL then income
end money_sum

from pivot)Table_1

where money_sum is not null

[sarion]

select point, date, case when inc is null then 'out' else 'inc' end optype, isnull(inc, 0)+isnull(out, 0) as opsum from(
select point, date, sum(inc) as inc, sum(out)as out from(
select a.point, a. date, b.inc, c.out from (select point, date from Income
union
select point, date from Outcome) a
left join Income b on a.point=b.point and b.date=a.date
left join Outcome c on a.point=c.point and c.date=a.date
where b.inc is null or c.out is null
) tab1 group by point, date) fintable

[alexross]

select point, date, oper, sum(coalesce(inc, out)) msum
from (
Select coalesce(i.point, o.point) point, coalesce(i.date, o.date) date, (case when inc is null then 'out' else 'inc' end) oper, inc, out
from income i full join outcome o on i.point=o.point and i.date=o.date
) t
where inc is null or out is null
group by point, date, oper