Pom15: Porovnání verzí

Z Prirucky
Řádek 1: Řádek 1:
select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
P.id_uc_dim1,  
P.id_uc_dim1,  
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
P.id_uc_dim2,  
P.id_uc_dim2,  
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
P.id_uc_dim3,  
P.id_uc_dim3,  
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
P.misto, null as kod, null as jmeno,
P.misto, null as kod, null as jmeno,
sum (castka) as uhrn_kc
sum (castka) as uhrn_kc
from par_ucty_tyco P, kody_uc A, ciselnik C where
from par_ucty_tyco P, kody_uc A, ciselnik C where
P.kod = C.kod and C.kod_uc = A.kod_uc and  
P.kod = C.kod and C.kod_uc = A.kod_uc and  
substring (A.ucet_md, 1,1) = '5' and P.kod not in (select distinct kod from kody_uc_zp) and P.kod not in (921,922,923,924,925,932) and  
substring (A.ucet_md, 1,1) = '5' and P.kod not in (select distinct kod from kody_uc_zp) and P.kod not in (921,922,923,924,925,932) and  
P.datum >=%DATUMOD% and P.datum <=%DATUMDO%
P.datum >=%DATUMOD% and P.datum <=%DATUMDO%
group by  
group by  
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3, P.misto
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3, P.misto
 
union all
union all
 
select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
P.id_uc_dim1,  
P.id_uc_dim1,  
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
P.id_uc_dim2,  
P.id_uc_dim2,  
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
P.id_uc_dim3,  
P.id_uc_dim3,  
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
null, null, null,
null, null, null,
sum (castka) as uhrn_kc
sum (castka) as uhrn_kc
from par_ucty_tyco P, kody_uc A, ciselnik C where
from par_ucty_tyco P, kody_uc A, ciselnik C where
P.kod = C.kod and C.kod_uc = A.kod_uc and  
P.kod = C.kod and C.kod_uc = A.kod_uc and  
substring (A.ucet_md, 1,1) <> '5' and P.kod not in (select distinct kod from kody_uc_zp) and P.kod not in (921,922,923,924,925,932) and  
substring (A.ucet_md, 1,1) <> '5' and P.kod not in (select distinct kod from kody_uc_zp) and P.kod not in (921,922,923,924,925,932) and  
P.datum >=%DATUMOD% and P.datum <=%DATUMDO%
P.datum >=%DATUMOD% and P.datum <=%DATUMDO%
group by  
group by  
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3
 
 
union all
 
union all
select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
 
P.id_uc_dim1,  
select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
(select nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
P.id_uc_dim1,  
P.id_uc_dim2,  
(select nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
(select nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
P.id_uc_dim2,  
P.id_uc_dim3,  
(select nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
(select nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
P.id_uc_dim3,  
P.misto, null, null,
(select nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
sum (castka) as uhrn_kc
P.misto, null, null,
from par_ucty_tyco P, kody_uc A, kody_uc_zp B, pracovnici PR where
sum (castka) as uhrn_kc
P.kod = B.kod and B.kod_uc = A.kod_uc and B.cis_poj = PR.zdrav_poj and PR.os_cislo = P.os_cislo and  
from par_ucty_tyco P, kody_uc A, kody_uc_zp B, pracovnici PR where
substring (A.ucet_md, 1,1) = '5' and P.kod in (select distinct kod from kody_uc_zp) and
P.kod = B.kod and B.kod_uc = A.kod_uc and B.cis_poj = PR.zdrav_poj and PR.os_cislo = P.os_cislo and  
P.datum >=%DATUMOD% and P.datum <=%DATUMDO%
substring (A.ucet_md, 1,1) = '5' and P.kod in (select distinct kod from kody_uc_zp) and
group by  
P.datum >=%DATUMOD% and P.datum <=%DATUMDO%
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
 
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3, P.misto
group by  
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
union all
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3, P.misto
 
select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
 
P.id_uc_dim1,  
union all
(select nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
 
P.id_uc_dim2,  
select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
(select nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
P.id_uc_dim1,  
P.id_uc_dim3,  
(select nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
(select nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
P.id_uc_dim2,  
null, null, null,
(select nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
sum (castka) as uhrn_kc
P.id_uc_dim3,  
from par_ucty_tyco P, kody_uc A, kody_uc_zp B, pracovnici PR where
(select nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
P.kod = B.kod and B.kod_uc = A.kod_uc and B.cis_poj = PR.zdrav_poj and PR.os_cislo = P.os_cislo and  
null, null, null,
substring (A.ucet_md, 1,1) <> '5' and P.kod in (select distinct kod from kody_uc_zp)
sum (castka) as uhrn_kc
and P.datum >=%DATUMOD% and P.datum <=%DATUMDO%
from par_ucty_tyco P, kody_uc A, kody_uc_zp B, pracovnici PR where
group by  
P.kod = B.kod and B.kod_uc = A.kod_uc and B.cis_poj = PR.zdrav_poj and PR.os_cislo = P.os_cislo and  
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
substring (A.ucet_md, 1,1) <> '5' and P.kod in (select distinct kod from kody_uc_zp)
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3, P.misto
and P.datum >=%DATUMOD% and P.datum <=%DATUMDO%
 
union all
group by  
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3, P.misto
P.id_uc_dim1,  
 
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
union all
P.id_uc_dim2,  
 
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
P.id_uc_dim3,  
P.id_uc_dim1,  
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
P.misto, P.kod, ltrim(rtrim(PR.prijmeni)) + ' ' + ltrim(rtrim(PR.jmeno)),  
P.id_uc_dim2,  
castka as uhrn_kc
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
from par_ucty_tyco P, kody_uc A, ciselnik C, pracovnici PR where
P.id_uc_dim3,  
P.os_cislo = PR.os_cislo and
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
P.kod = C.kod and C.kod_uc = A.kod_uc and  
P.misto, P.kod, ltrim(rtrim(PR.prijmeni)) + ' ' + ltrim(rtrim(PR.jmeno)),  
P.kod in (921,922,923,924,925,932) and  
castka as uhrn_kc
P.datum >=%DATUMOD% and P.datum <=%DATUMDO%
from par_ucty_tyco P, kody_uc A, ciselnik C, pracovnici PR where
P.os_cislo = PR.os_cislo and
order by  
P.kod = C.kod and C.kod_uc = A.kod_uc and  
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
P.kod in (921,922,923,924,925,932) and  
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3, P.misto, P.kod, PR.prijmeni
P.datum >=%DATUMOD% and P.datum <=%DATUMDO%
 
 
 
 
order by  
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev,  
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3

Verze z 3. 12. 2019, 11:50

select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev, 
P.id_uc_dim1, 
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
P.id_uc_dim2, 
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
P.id_uc_dim3, 
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
P.misto, null as kod, null as jmeno,
sum (castka) as uhrn_kc
from par_ucty_tyco P, kody_uc A, ciselnik C where
P.kod = C.kod and C.kod_uc = A.kod_uc and 
substring (A.ucet_md, 1,1) = '5' and P.kod not in (select distinct kod from kody_uc_zp) and P.kod not in (921,922,923,924,925,932) and 
P.datum >=%DATUMOD% and P.datum <=%DATUMDO%
group by 
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev, 
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3, P.misto

union all

select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev, 
P.id_uc_dim1, 
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
P.id_uc_dim2, 
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
P.id_uc_dim3, 
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
null, null, null,
sum (castka) as uhrn_kc
from par_ucty_tyco P, kody_uc A, ciselnik C where
P.kod = C.kod and C.kod_uc = A.kod_uc and 
substring (A.ucet_md, 1,1) <> '5' and P.kod not in (select distinct kod from kody_uc_zp) and P.kod not in (921,922,923,924,925,932) and 
P.datum >=%DATUMOD% and P.datum <=%DATUMDO%
group by 
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev, 
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3

union all

select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev, 
P.id_uc_dim1, 
(select nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
P.id_uc_dim2, 
(select nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
P.id_uc_dim3, 
(select nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
P.misto, null, null,
sum (castka) as uhrn_kc
from par_ucty_tyco P, kody_uc A, kody_uc_zp B, pracovnici PR where
P.kod = B.kod and B.kod_uc = A.kod_uc and B.cis_poj = PR.zdrav_poj and PR.os_cislo = P.os_cislo and 
substring (A.ucet_md, 1,1) = '5' and P.kod in (select distinct kod from kody_uc_zp) and
P.datum >=%DATUMOD% and P.datum <=%DATUMDO%
group by 
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev, 
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3, P.misto

union all

select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev, 
P.id_uc_dim1, 
(select nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
P.id_uc_dim2, 
(select nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
P.id_uc_dim3, 
(select nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
null, null, null,
sum (castka) as uhrn_kc
from par_ucty_tyco P, kody_uc A, kody_uc_zp B, pracovnici PR where
P.kod = B.kod and B.kod_uc = A.kod_uc and B.cis_poj = PR.zdrav_poj and PR.os_cislo = P.os_cislo and 
substring (A.ucet_md, 1,1) <> '5' and P.kod in (select distinct kod from kody_uc_zp)
and P.datum >=%DATUMOD% and P.datum <=%DATUMDO%
group by 
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev, 
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3, P.misto

union all

select A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev, 
P.id_uc_dim1, 
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = P.id_uc_dim1) as nazev1,
P.id_uc_dim2, 
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = P.id_uc_dim2) as nazev2,
P.id_uc_dim3, 
(select DIM.nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = P.id_uc_dim3) as nazev3,
P.misto, P.kod, ltrim(rtrim(PR.prijmeni)) + ' ' + ltrim(rtrim(PR.jmeno)), 
castka as uhrn_kc
from par_ucty_tyco P, kody_uc A, ciselnik C, pracovnici PR where
P.os_cislo = PR.os_cislo and
P.kod = C.kod and C.kod_uc = A.kod_uc and 
P.kod in (921,922,923,924,925,932) and 
P.datum >=%DATUMOD% and P.datum <=%DATUMDO%

order by 
A.kod_uc, A.ucet_md, A.ucet_dal, A.nazev, 
P.id_uc_dim1, P.id_uc_dim2, P.id_uc_dim3, P.misto, P.kod, PR.prijmeni
Citováno z „http://prirucky.ksprogram.cz/index.php?title=Pom15&oldid=37576“