|
|
| Řádek 1: |
Řádek 1: |
|
| |
|
|
| |
| [[Image: Sestava pro ucto-cc2.png]]
| |
|
| |
|
| |
| 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
| |
