/* Paragrafy + odstavce */
;with a as (select distinct num, val from tresty.dbo.seznam where a_name='ns2_paragrafCisel' and b_name='ns2_paragrafCislo'),
aa as (select distinct num, val from tresty.dbo.seznam where a_name='ns2_paragrafCisel' and b_name='ns2_odstavecPismeno'),
b as (
select b.*,a.val aval, aa.val aaval
from tresty.dbo.seznam b
inner join a on a.num=b.num
left join aa on aa.num=b.num
where b_name='ico'
)
select count(b_name), aval, aaval
from b
group by aval, aaval
order by 2,3

/* Ptvni trest */
;with a as (select top 1 num, (cast(val as date)) val from tresty.dbo.seznam where a_name='ns3_odsouzeni' and b_name='ns3_datumPM' order by 2)
select * from tresty.dbo.seznam where num in (select num from a)

/* Paragrafy VS datum PM */
;with a as (select distinct num, val from tresty.dbo.seznam where a_name='ns2_paragrafCisel' and b_name='ns2_paragrafCislo'),
aa as (select num, cast(val as date) val from tresty.dbo.seznam where a_name='ns3_odsouzeni' and b_name='ns3_datumPM'),
b as (select b.*,a.val aval, aa.val aaval
from tresty.dbo.seznam b
inner join a on a.num=b.num
left join aa on aa.num=b.num
where b_name='ico')
select count(b_name), /*aval,*/ year(aaval)
from b
group by /*aval, */year(aaval)
order by /*3,*/2

/* Soud 1.stupne VS datum Rozhodnuti */
;with a as (select distinct num, val from tresty.dbo.seznam where a_name='ns2_paragrafCisel' and b_name='ns2_paragrafCislo'),
aa as (select num, val from tresty.dbo.seznam where a_name='ns3_odsouzeni' and b_name='ns3_organizace'),
aaa as (select distinct num, val from tresty.dbo.seznam where a_name='ns2_paragrafCisel' and b_name='ns2_odstavecPismeno'),
aaaa as (select num, cast(val as date) val from tresty.dbo.seznam where a_name='ns3_odsouzeni' and b_name='ns3_datumRozhodnuti'),
b as (select x.*,a.val aval, aa.val aaval, aaa.val aaaval, aaaa.val aaaaval
from tresty.dbo.seznam x
inner join a on a.num=x.num
left join aa on aa.num=x.num
left join aaa on aaa.num=x.num
left join aaaa on aaaa.num=x.num
where b_name='ico')
--select * from b
select num, val, aval, aaaval, aaval, year(aaaaval)
from b
order by year(aaaaval), aval, aaaval, aaval

select * from tresty.dbo.seznam where num=537