Jump to content

SQL Querys - paid work


frezzo

Recommended Posts

Hi all

I need in some cases someone who would help me on create some SQL Queries. Your work will be payed. Please message me with a offer for thoose two cases:

DB and Prestashop Version: 1.7.7.1

e.g. this query spits out nothing and should give me an overview of when I should call again:


Select a.id_product As 'ID', a.reference As 'ART.NO', sa.active As 'AKTIV', b.name As 'PRODUKT', sav.quantity As 'AUF LAGER', Sum(d.product_quantity) As 'VERKAUFT 12M', (Sum(d.product_quantity) / 365) As 'VERKAUF PRO TAG', round((sav.quantity / (Sum(d.product_quantity) / 365)), 1) As 'REICHWEITE TAGE', round(((sav.quantity / (Sum(d.product_quantity) / 365)) / 30), 1) As 'REICHWEITE MONAT', Max(x.date_add) As 'LETZER VERKAUF', 'BESTELLEN' As 'BESTELLEN FÜR ', round((Sum(d.product_quantity) * 1.10) - sav.quantity) As '12 MONATE', ' CHF ' As '. ', round(a.wholesale_price, 2) As 'STK -EK ', ' CHF ' As '   ', round(((((Sum(d.product_quantity) * 1.10) - sav.quantity)) * a.wholesale_price), 2) As 'BESTELLWERT' From ps_order_detail d Join ps_orders x On d.id_order = x.id_order And x.date_add Between Date_Sub(Now(), Interval 365 Day) And Now() Join ps_product a On d.product_id = a.id_product Join ps_product_lang b On b.id_product = a.id_product And (b.id_lang = 1 And b.id_shop = 1) Join ps_product_shop sa On a.id_product = sa.id_product And sa.id_shop = a.id_shop_default Join ps_category_lang cl On sa.id_category_default = cl.id_category And b.id_lang = cl.id_lang And cl.id_shop = a.id_shop_default And cl.id_category = 3 Join ps_stock_available sav On sav.id_product = a.id_product Group By a.id_product Order By sav.quantity

But I currently can't manage to add the name of the product to my query for the inventory either:

Select ps_product.reference As ART, ps_product.supplier_reference As ARTHersteller, ps_product.ean13 As EAN, ps_product.quantity As Anzahl, ps_product.price As Preis, ps_product.wholesale_price As WholesalePrice, ps_product.location As Standort, ps_product.active As Aktiv, ps_supplier.name As Lieferant From ps_product Inner Join ps_category On ps_category.id_category = ps_product.id_category_default Inner Join ps_supplier On ps_supplier.id_supplier = ps_product.id_supplier, ps_store_lang

Who would like to support me in this?

Thank you in forward.

 

Link to comment
Share on other sites

try this query:

Select ps_product.reference As ART, ps_product.supplier_reference As ARTHersteller, ps_product.ean13 As EAN, 
ps_product.quantity As Anzahl, ps_product.price As Preis, ps_product.wholesale_price As WholesalePrice, 
ps_product.location As Standort, ps_product.active As Aktiv, ps_supplier.name As Lieferant,ps_product_lang.name
From ps_product 
left Join ps_category On ps_category.id_category = ps_product.id_category_default 
left Join ps_supplier On ps_supplier.id_supplier = ps_product.id_supplier
LEFT JOIN ps_product_lang  ON ps_product.id_product=ps_product_lang.id_product

  • Like 2
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...