Comments (...)

SQL - SQL pro rozbory inventury POS (NexusDB)

 

Inventurni soupiska

 

SELECT

   i.CisloZbozi,

   i.NazevZbozi,

   i.DPH,

   i.MnozstviMJ,

   i.CenaMJ,

   i.ObjednanoCena,

// pole ObjednanoCena obsahuje obsah pole Cena02 pri porizeni

   z.Cena02,

   (CASE WHEN i.DPH=1 THEN z.Currency_04 ELSE z.Cena02 END) AS Cena02PredPrecenenimDPH

FROM

   XX81Y_PD i, XX__Z201 z

WHERE

   (i.CisloZbozi=z.Cislo)

ORDER BY

   i.CisloZbozi

 

 

 

SQL - SQL pro rozbory inventury POS (NexusDB)

 

Inventura podle mnozstvi MJ

 

SELECT

   Cislo,

   Nazev,

   DPH,

   Double_12 AS InventuraMJ,

   Currency_12 AS InventuraNakupKcCelkem,

   Cena02*Double_12 AS InventuraProdejKcCelkemPredPrecenenim,

   Currency_04*Double_12 AS InventuraProdejKcCelkemPoPreceneni

FROM

   XX__Z201

WHERE

   (ABS(Currency_12)>0.0099) or (ABS(Double_12)>0.00099)   

ORDER BY  

   InventuraMJ DESC

 

Inventura podle celkove nakupni ceny

 

SELECT

   Cislo,

   Nazev,

   DPH,

   Double_12 AS InventuraMJ,

   Currency_12 AS InventuraNakupKcCelkem,

   Cena02*Double_12 AS InventuraProdejKcCelkemPredPrecenenim,

   Currency_04*Double_12 AS InventuraProdejKcCelkemPoPreceneni

FROM

   XX__Z201

WHERE

   (ABS(Currency_12)>0.0099) or (ABS(Double_12)>0.00099)   

ORDER BY  

   InventuraNakupKcCelkem DESC

 

Inventura podle celkove prodejni ceny pred precenenim

 

SELECT

   Cislo,

   Nazev,

   DPH,

   Double_12 AS InventuraMJ,

   Currency_12 AS InventuraNakupKcCelkem,

   Cena02*Double_12 AS InventuraProdejKcCelkemPredPrecenenim,

   Currency_04*Double_12 AS InventuraProdejKcCelkemPoPreceneni

FROM

   XX__Z201

WHERE

   (ABS(Currency_12)>0.0099) or (ABS(Double_12)>0.00099)   

ORDER BY  

   InventuraProdejKcCelkemPredPrecenenim DESC

 

Inventura podle celkove prodejni ceny po preceneni

 

SELECT

   Cislo,

   Nazev,

   DPH,

   Double_12 AS InventuraMJ,

   Currency_12 AS InventuraNakupKcCelkem,

   Cena02*Double_12 AS InventuraProdejKcCelkemPredPrecenenim,

   Currency_04*Double_12 AS InventuraProdejKcCelkemPoPreceneni

FROM

   XX__Z201

WHERE

   (ABS(Currency_12)>0.0099) or (ABS(Double_12)>0.00099)   

ORDER BY  

   InventuraProdejKcCelkemPoPreceneni DESC

 

Inventura podle DPH

 

SELECT

   SUM(CASE WHEN DPH=0 THEN Currency_12 ELSE 0.0 END) AS CelkemKcNakupDPH0,

   SUM(CASE WHEN DPH=1 THEN Currency_12 ELSE 0.0 END) AS CelkemKcNakupDPH5,

   SUM(CASE WHEN DPH=2 THEN Currency_12 ELSE 0.0 END) AS CelkemKcNakupDPH19,

   SUM(CASE WHEN DPH=0 THEN Cena02*Double_12 ELSE 0.0 END) AS CelkemKcProdejDPH0PredPrecenenim,

   SUM(CASE WHEN DPH=1 THEN Cena02*Double_12 ELSE 0.0 END) AS CelkemKcProdejDPH5PredPrecenenim,

   SUM(CASE WHEN DPH=2 THEN Cena02*Double_12 ELSE 0.0 END) AS CelkemKcProdejDPH19PredPrecenenim,

   SUM(CASE WHEN DPH=0 THEN Currency_04*Double_12 ELSE 0.0 END) AS CelkemKcProdejDPH0PoPreceneni,

   SUM(CASE WHEN DPH=1 THEN Currency_04*Double_12 ELSE 0.0 END) AS CelkemKcProdejDPH5PoPreceneni,

   SUM(CASE WHEN DPH=2 THEN Currency_04*Double_12 ELSE 0.0 END) AS CelkemKcProdejDPH19PoPreceneni

// Currency_04 obsahuje puvodni Cena02 pred precenenim na 9% DPH

FROM

   XX__Z201

 

Zbozi s prodejni cenou (Cena02*Double_12) nizzsi nez nakupni (Currency_12)

 

SELECT

   Cislo,

   Nazev,

   DPH,

   Double_12 AS InventuraMJ,

   Currency_12 AS InventuraNakupKcCelkem,

   Cena02*Double_12 AS InventuraProdejKcCelkemPredPrecenenim,

   Currency_04*Double_12 AS InventuraProdejKcCelkemPoPreceneni

FROM

   XX__Z201

WHERE

   ((ABS(Currency_12)>0.0099) or (ABS(Double_12)>0.00099)) 

   and ((Cena02*Double_12)<Currency_12)  

ORDER BY  

   Cislo

 

Inventura - prodejni marze procent pred precenenim

 

SELECT

   *,

   ((ProdejniCenaPredPrecenenim*InventuraMJ)/InventuraNakupKcCelkemVcetneDPH-1.0)*100.0 AS MarzePredPrecenenimProcent,

   ((ProdejniCenaPoPreceneni*InventuraMJ)/InventuraNakupKcCelkemVcetneDPH-1.0)*100.0 AS MarzePoPreceneniProcent

FROM

(

SELECT

   Cislo,

   Nazev,

   DPH,

   Double_12 AS InventuraMJ,

   Currency_12 AS InventuraNakupKcCelkem,

   (

   CASE 

      WHEN DPH=0 THEN Currency_12

      WHEN DPH=1 THEN Currency_12*1.05

      WHEN DPH=2 THEN Currency_12*1.19 

      ELSE 0.0

   END

   ) AS InventuraNakupKcCelkemVcetneDPH,

   Currency_12/Double_12 AS NakupniCenaBezDPH,

   (

   CASE 

      WHEN DPH=0 THEN Currency_12/Double_12

      WHEN DPH=1 THEN Currency_12/Double_12*1.05

      WHEN DPH=2 THEN Currency_12/Double_12*1.19 

      ELSE 0.0

   END

   ) AS NakupniCenaSDPH,

   Cena02 AS ProdejniCenaPredPrecenenim,

   Currency_04 AS ProdejniCenaPoPreceneni

FROM

   XX__Z201

WHERE

   (ABS(Currency_12)>0.0099)

) AS x1

ORDER BY

   MarzePredPrecenenimProcent DESC

 

Comments (...)