WITH amount AS
(
SELECT buy.nomenclature_id, sum(buy.summa) as summa
FROM buy
GROUP BY buy.nomenclature_id
UNION ALL
SELECT sale.nomenclature_id, -1*sum(sale.summa) as summa
FROM sale
GROUP BY sale.nomenclature_id
)
SELECT nomenclature_id, sum(summa) as summa
FROM amount
GROUP BY nomenclature_id
WITH rates as
(SELECT rate, type, route_id, weight, volume
FROM test_rates rs
WHERE rs.dt_del IS NULL
AND (rs.volume >= 300 OR rs.weight >= 50000))
SELECT rate, type, route_id, weight, volume
FROM rates
where rate = (select max(rate) from rates)
select views, image_url, (select avg(votes.value))
from images
inner join votes
on votes.image_id = images.id
group by views, image_url
order by views asc
select top 1 c1.code_number as c1, c2.code_number as c2, min(c2.code_number - c1.code_number) as delta
from code as c1
join code as c2
on c2.code_number > c1.code_number
group by c1.code_number, c2.code_number
having min(c2.code_number - c1.code_number) > 0
order by 3
SELECT ISNULL(i2.title, i1.title) as title, ISNULL(i2.descr, i1.descr) as descr, gg.*
FROM game_text_info as i1
INNER JOIN games as gg
ON gg.id = i1.game_id
LEFT JOIN game_text_info as i2
ON i2.game_id = i1.game_id
AND i2.game_text_info.local = :local
WHERE i1.title LIKE ':search'
and i1.game_text_info.local = "en"
DELETE FROM b_iblock_section WHERE IBLOCK_ID=4 LIMIT 100;
SELECT purc.client_id, purc.action_date as DatePrch, srv.action_date as DateSrv
FROM [action_log] purc
INNER JOIN [action_log] srv
ON srv.client_id = purc.client_id
AND srv.action_date > purc.action_date
AND srv.action_type = 'service'
WHERE action_type = 'purchase'
AND action_date >= DATEADD(hour, -24, GETDATE())
ORDER BY action_date DESC