SQL - Číselník zboží
Contents
===================================================
FAQ - Cena02 v kartě zboží - necelé halíře
===================================================
SELECT
*
FROM
(
SELECT
Cislo,
Nazev,
Cena02,
(Cena02*100.0) AS Cena02_100x,
FLOOR(Cena02*100.0) AS Cena02_100x_Cele,
FLOOR(Cena02*100.0)-(Cena02*100.0) AS C02_Rozdil_halire
FROM
XX__Z201
) AS X
WHERE
ABS(C02_Rozdil_halire)>0.000099
===================================================
===================================================
FAQ - SQL pro duplicitu vět v číselníku zboží
===================================================
SELECT
*
FROM
(
SELECT
Cislo,
Count(Cislo) AS Pocet
FROM
XX__Z201
GROUP BY
Cislo
) AS X
WHERE
Pocet>1
===================================================
SQL - Číselník zboží
SELECT
Cislo,
Nazev,
DPH,
MJ,
Cena02 AS ProdejniCenaMOsDPH,
Cena03 AS ProdejniCenaMOsDPHPoLomitku,
HmotnostMJg,
Rabat AS KoeficientMerneCeny,
"AutoInc",
AkcniCenaBezDPH,
AkcniCenaSDPH,
Barva,
BlobMEMO,
BlobMemoPoznamka,
BlobMemoStav,
BruttoVahaSObalemKg,
CarkovyKod,
CelniSazebnik,
Cena00,
Cena01 AS ProdejniCenaVObezDPH,
Cena04,
Cena05,
Cena06,
Cena07,
Cena08,
Cena09 AS Akce1,
Cena10,
Cena11,
Cena12,
Cena13,
Cena14,
Cena15,
Cena16,
Cena17,
Cena18,
Cena19 AS Akce2,
Cena20,
Cena21,
Cena22,
Cena23,
Cena24,
Cena25,
Cena26,
Cena27,
Cena28,
Cena29 AS Akce3,
CenaPLC,
CenovaSkupina,
Cislo32,
Currency_01 AS PosledniNakupniCena,
Currency_02 AS HruskaInvK1KCMJ,
Currency_03 AS HruskaInvK1CenaCelkem,
Currency_04,
Currency_05,
Currency_06,
Currency_07,
Currency_08,
Currency_09 AS PosledniProdejniCena,
Currency_10 AS SpotrebniDanKcMJ,
Currency_11 AS InventuraPuvodniKc,
Currency_12 AS InventuraNoveKc,
DesetinnychMist AS HlidatZasoby, // 1=HlidatZasoby
Dodavatel AS RozborMOzaDodavatele,
Dodavatel1,
Dodavatel2,
Dodavatel3,
Dodavatel4,
Dodavatel5,
Dodavatel6,
Dodavatel7,
Dodavatel8,
Dodavatel9,
Dodavatel10,
Double_01 AS MaximalniCena,
Double_02 AS HruskaInvK1MJ,
Double_03 AS HruskaInvPFMJ,
Double_04 AS HruskaInvZAMJ,
Double_05 AS HruskaInvRozdil, // Double_05 kladné = prebytek zasob, Double_05 záporné = manko zasob
Double_06 AS HruskaInvMankaSeZapoctenymPrebytkem,
Double_07 AS HruskaInvZustatekMank, // Double_07 = zustatek mank ===> c : \ manka.txt
Double_08,
CAST(Double_09 AS DateTime) AS DatumPoslednihoNakupu,
CAST(Double_10 AS DateTime) AS DatumPoslednihoProdeje,
Double_11 AS InventuraPuvodniMJ,
Double_12 AS InventuraNoveMJ,
DPCAkcniCenaBezDPH,
DPCAkcniCenaSDPH,
DPCTypCenoveSkupiny00,
DPCTypCenoveSkupiny01,
DPCTypCenoveSkupiny02,
DPCTypCenoveSkupiny03,
DPCTypCenoveSkupiny04,
DPCTypCenoveSkupiny05,
DPCTypCenoveSkupiny06,
DPCTypCenoveSkupiny07,
DPCTypCenoveSkupiny08,
DPCTypCenoveSkupiny09,
DPCTypCenoveSkupiny10,
DPCTypCenoveSkupiny11,
DPCTypCenoveSkupiny12,
DPCTypCenoveSkupiny13,
DPCTypCenoveSkupiny14,
DPCTypCenoveSkupiny15,
DPCTypCenoveSkupiny16,
DPCTypCenoveSkupiny17,
DPCTypCenoveSkupiny18,
DPCTypCenoveSkupiny19,
DPCTypCenoveSkupiny20,
DPCTypCenoveSkupiny21,
DPCTypCenoveSkupiny22 AS ZakazatProdej, // 1 = zakazat prodej
DPCTypCenoveSkupiny23 AS NehlidatDPH, // 1 = nehlidat DPH
DPCTypCenoveSkupiny24 AS NoveZboziPocetEANu, // Nove zbozi - pocet EANu
DPCTypCenoveSkupiny25 AS CenaRecepturyA, // Cena receptury A
DPCTypCenoveSkupiny26 AS CenaRecepturyB, // Cena receptury B
DPCTypCenoveSkupiny27 AS PocetPolozekVRecepture, // Pocet polozek v recepture
DPCTypCenoveSkupiny28 AS PouzeCelaEgalizace, // Pouze cela egalizace
DPCTypCenoveSkupiny29 AS RedukceZboziNEW, // redukce zbozi NEW
DPCVychoziCena AS BlokovaniRadkuDokladu_NehlidatNulovouCenu, // 1=BlokovaniRadkuDokladu
DPHNakup,
Egalizace,
Egalizace2,
Int32_01 AS CisloPOS,
Int32_02 AS PosledniOdberatel,
Int32_03 AS KlavesaPOS,
Int32_04 AS TypPOS,
Int32_05 AS Mlecka, // 1=Mlecka
Int32_06 AS CistHmotnostZVahy, // 1=CistHmotnostZVahy
Int32_07 AS IngredienceProDigiRM40,
Int32_08 AS CisloLicence,
Int32_09 AS PEKPoradiZboziVObjednavce, // PEK - poradi zbozi v objednavce
Int32_10 AS PEKHromadnyVyrobek, // PEK - Hromadny vyrobek
Int32_11 AS HlidatNuloveAZaporneZasoby, // 1=HlidatNuloveAZaporneZasoby
Int32_12 AS TestCK, // 1=TestCK
Inventura0KC,
Inventura22KC,
Inventura5KC,
InventuraDatum,
InventuraMJ,
JKPOV,
Katalog AS VerzeProgramu,
Marze,
MaxZasoba,
MinZasoba,
NazevASCII,
NazevDodavatele,
Obal,
Obal2,
ObjednanoMJ,
ObjednanoUDodavateleMJ,
ObjemM3 AS Nasobek, // nasobek - rozlevany alkohol, pouzito dale jen na pekarne
PKoef AS KoeficientCeny,
PLU,
PocatecniStavDatum,
PocatecniStavMJ,
PosledniDodavatel,
Poznamka,
Precislovani,
Priznak AS AKCE,
ProtokolOShode,
Receptura,
RezervovanoMJ,
SKP,
Skupina,
SlevaOK,
Stav,
StavArchivace,
StavPrenosu AS BlokaceZbozi99, // 99 - blokace zbozi
TrvanlivostDni,
TypCenoveSkupiny00,
TypCenoveSkupiny01,
TypCenoveSkupiny02,
TypCenoveSkupiny03,
TypCenoveSkupiny04,
TypCenoveSkupiny05,
TypCenoveSkupiny06,
TypCenoveSkupiny07,
TypCenoveSkupiny08,
TypCenoveSkupiny09,
TypCenoveSkupiny10,
TypCenoveSkupiny11,
TypCenoveSkupiny12,
TypCenoveSkupiny13,
TypCenoveSkupiny14,
TypCenoveSkupiny15,
TypCenoveSkupiny16,
TypCenoveSkupiny17,
TypCenoveSkupiny18,
TypCenoveSkupiny19,
TypCenoveSkupiny20,
TypCenoveSkupiny21,
TypCenoveSkupiny22,
TypCenoveSkupiny23,
TypCenoveSkupiny24,
TypCenoveSkupiny25,
TypCenoveSkupiny26,
TypCenoveSkupiny27,
TypCenoveSkupiny28,
TypCenoveSkupiny29,
TypStroje,
TypZbozi AS PRMarket, // 1=PRMarket, 0 - obal, 1 - zbozi, 2 - sluzba, 3 - sada, 4 - komplet, 5 - vyrobek
UcetNaklad,
UcetSklad,
UcetVynos,
Umisteni,
Umisteni2,
VychoziCena AS NeskladovaPolozka, // 1=NeskladovaPolozka
ZarukaMesicu,
ZasobaDni,
ZasobaKc,
ZasobaMJ,
ZasobaNaPocetDni,
ZdrojovaCena,
Zkratka,
ZkratkaASCII,
ZmenaDatum,
ZmenaDatumServer,
ZmenaKdo,
ZmenaKod,
ZmenaPC,
ZmenaPocet
FROM
XX__Z201
FAQ - SQL pro číselník zboží:
SELECT
Cislo,
Nazev,
MJ,
Skupina,
DPH,
Cena00, // HR
Cena01 AS ProdejniCenaVObezDPH, // HD, HR, XX
Cena02 AS ProdejniCenaMOsDPH, // HR, XX
Cena03, // HR, XX
Cena04, // -
Cena05, // HD
Cena06, // HD, HR
Cena07, // HR
Cena08, // -
Cena09, // HD, HR - akční DPC
Cena10, // HR
Cena11, // HR
Cena12, // HR
Cena13, // HR
Cena14, // -
Cena15, // HD, HR
Cena16, // HR
Cena17, // -
Cena18, // -
Cena19, // HR
Cena20, // HR
Cena21, // -
Cena22, // HR
Cena23, // -
Cena24, // -
Cena25, // -
Cena26, // -
Cena27, // -
Cena28, // HD, HR
Cena29, // -
ZdrojovaCena,
ZasobaKc,
ZasobaMJ,
HmotnostMJg,
Rabat AS KoeficientMerneCeny,
Poznamka,
Obal, // HR
Egalizace,
Obal2, // HR, XX
Egalizace2, // XX
Umisteni, // cislo, HD, HR
Umisteni2, // retezec 32, HR
PosledniDodavatel,
NazevDodavatele,
Dodavatel1,
Dodavatel2,
Dodavatel3,
Dodavatel4,
Dodavatel5,
Dodavatel6,
Dodavatel7,
Dodavatel8,
Dodavatel9,
Dodavatel10,
Dodavatel, // HD, pro rozbor MO za dodavatele
CarkovyKod,
CelniSazebnik,
JKPOV,
PLU,
SKP,
TypStroje,
CenovaSkupina,
Cislo32,
Currency_01 AS PosledniNakupniCena,
Currency_02 AS HruskaInvK1KCMJ,
Currency_03 AS HruskaInvK1CenaCelkem,
Currency_09 AS PosledniProdejniCena,
Currency_10 AS SpotrebniDanKcMJ,
Currency_11 AS InventuraPuvodniKc,
Currency_12 AS InventuraNoveKc,
Double_01 AS MaximalniCena,
Double_02 AS HruskaInvK1MJ,
Double_03 AS HruskaInvPFMJ,
Double_04 AS HruskaInvZAMJ,
Double_05 AS HruskaInvRozdil,
Double_06 AS HruskaInvMankaSeZapoctenymPrebytkem,
Double_07 AS HruskaInvZustatekMank,
CAST(Double_09 AS DateTime) AS DatumPoslednihoNakupu,
CAST(Double_10 AS DateTime) AS DatumPoslednihoProdeje,
Double_11 AS InventuraPuvodniMJ,
Double_12 AS InventuraNoveMJ,
Int32_01 AS CisloPOS,
Int32_02 AS PosledniOdberatel,
Int32_03 AS KlavesaPOS,
Int32_04 AS TypPOS,
Int32_05 AS Mlecka, // 1=Mlecka
Int32_06 AS CistHmotnostZVahy, // 1=CistHmotnostZVahy
Int32_07 AS IngredienceProDigiRM40,
Int32_11 AS HlidatNuloveAZaporneZasoby, // 1=HlidatNuloveAZaporneZasoby
Int32_12 AS TestCK, // 1=TestCK
PocatecniStavMJ,
Inventura0KC,
Inventura22KC,
Inventura5KC,
InventuraDatum,
InventuraMJ,
TrvanlivostDni,
ZarukaMesicu,
ZasobaNaPocetDni,
MaxZasoba,
MinZasoba,
SlevaOK,
Receptura,
ProtokolOShode,
UcetNaklad,
UcetSklad,
Stav, // HD, HR
Priznak AS AKCE, // HR
Marze,
PKoef AS KoeficientCeny,
Precislovani,
CenaPLC,
Zkratka,
ZkratkaASCII,
DPCVychoziCena AS BlokovaniRadkuDokladu, // 1=BlokovaniRadkuDokladu
VychoziCena AS NeskladovaPolozka, // 1=NeskladovaPolozka
NazevASCII, // pro fulltext
DesetinnychMist AS HlidatZasoby, // 1=HlidatZasoby
TypZbozi AS PRMarket, // 1=PRMarket
Barva, // HR - mlecka
DPHNakup, // HD, HR
BlobMemoStav,
StavArchivace,
StavPrenosu,
AkcniCenaBezDPH,
Katalog AS VerzeProgramu,
ZmenaDatum,
ZmenaDatumServer,
ZmenaKdo,
ZmenaKod,
ZmenaPC,
ZmenaPocet,
"AutoInc" // musi byt v uvozovkach
FROM
HR__Z201