Dp. Posted January 21, 2021 Share Posted January 21, 2021 Da in meine SQL-Abfragen teilweise recht viele Gedanken geflossen sind, würde ich sie gerne zum Nutzen aller teilen. Take it or leave it 😉 Über weitere nützliche Abfragen von eurer Seite inkl. kurzer Erklärung würde ich mich freuen. Ich bin kein SQL Profi, daher sind die Abfragen hinsichtlich Struktur oder Effizienz bestimmt nicht optimal. Sie tun aber, was sie sollen 😁 1. Abfrage aller Bestellungen inklusive Details - nutze ich für mein Reporting SELECT a.`id_order`, `reference`, REPLACE(ROUND(`total_paid_tax_incl`, 2), '.', ',') AS `total_paid_tax_incl`, REPLACE(ROUND(`total_products`, 2), '.', ',') AS `total_products`, REPLACE(ROUND(a.`total_shipping_tax_incl`, 2), '.', ',') AS `total_shipping_tax_incl`, REPLACE(ROUND(`total_wrapping_tax_incl`, 2), '.', ',') AS `total_wrapping_tax_incl`, REPLACE(ROUND((-1)*(`total_discounts_tax_incl`), 2), '.', ',') AS `total_discounts_tax_incl`, REPLACE(ROUND((-1)*(sl.`total_products_tax_incl`), 2), '.', ',') AS `ret_total_products_tax_incl`, REPLACE(ROUND((-1)*(sl.`total_shipping_tax_incl`), 2), '.', ',') AS `ret_total_shipping_tax_incl`, `payment`, a.`date_add` AS `date_add`, `shipping_number`, CONCAT(c.`firstname`,' ', c.`lastname`) AS `customer`, osl.`name` AS `osname`, IF((SELECT so.id_order FROM `ps_orders` so WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order LIMIT 1) > 0, 0, 1) as new FROM `ps_orders` a LEFT JOIN `ps_order_slip` sl ON (sl.`id_order` = a.`id_order`) LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`) LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = a.`current_state`) LEFT JOIN `ps_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 1) LEFT JOIN `ps_shop` shop ON a.`id_shop` = shop.`id_shop` WHERE 1 AND a.id_shop IN (1) ORDER BY a.id_order ASC 2. Übersicht aller Rechnungen und Erstattungen aus dem Vormonat - nutze ich zum Export in meine Buchhaltung (SELECT oi.`date_add` AS `Datum`, CONCAT('RE',IF(LENGTH(oi.`number`)=3,'000',IF(LENGTH(oi.`number`)=4,'00','0')),oi.`number`,'/2020') AS `Beleg-ID`, CONCAT('Bestellung ',IF(LENGTH(oi.`id_order`)=3,'000',IF(LENGTH(oi.`id_order`)=4,'00','0')),oi.`id_order`,' ',c.`firstname`,' ', c.`lastname`) AS `Beschreibung`, REPLACE(ROUND(oi.`total_paid_tax_incl`, 2), '.', ',') AS `Betrag`, REPLACE(ROUND(oi.`total_paid_tax_excl`, 2), '.', ',') AS `total_paid_tax_excl`, REPLACE(ROUND(oi.`total_products`, 2), '.', ',') AS `total_products`, REPLACE(ROUND(oi.`total_shipping_tax_excl`, 2), '.', ',') AS `total_shipping_tax_excl`, REPLACE(ROUND(oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`, 2), '.', ',') AS `total_vat`, oi.`id_order`, round(((oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`)/oi.`total_paid_tax_excl`)*100, 2) as tax_rate FROM ps_order_invoice oi LEFT OUTER JOIN ps_orders o on oi.`id_order` = o.`id_order` LEFT OUTER JOIN ps_customer c on o.`id_customer` = c.`id_customer` WHERE MONTH(oi.date_add) = IF(MONTH(now())=1,12,MONTH(now())) AND YEAR(oi.date_add) = IF(MONTH(now())=1,YEAR(now())-1,YEAR(now()))) UNION (SELECT s.`date_add` AS `Datum`, CONCAT(IF(LENGTH(s.`id_order_slip`)=1,'00000',IF(LENGTH(s.`id_order_slip`)=2,'0000','00')),s.`id_order_slip`) AS `Beleg-ID`, CONCAT('Rückerstattung zu Bestellung 000',s.`id_order`) AS `Beschreibung`, REPLACE(ROUND(s.`amount` + s.`shipping_cost_amount`, 2), '.', ',') AS `Betrag`, REPLACE(ROUND(s.`total_products_tax_excl` + s.`total_shipping_tax_excl`, 2), '.', ',') AS `total_paid_tax_excl`, REPLACE(ROUND(s.`total_products_tax_excl`, 2), '.', ',') AS `total_products`, REPLACE(ROUND(s.`total_shipping_tax_excl`, 2), '.', ',') AS `total_shipping_tax_excl`, REPLACE(ROUND((s.`amount` + s.`shipping_cost_amount`) - (s.`total_products_tax_excl` + s.`total_shipping_tax_excl`), 2), '.', ',') AS `total_vat`, s.`id_order`, round((((s.`amount` + s.`shipping_cost_amount`) - (s.`total_products_tax_excl` + s.`total_shipping_tax_excl`))/(s.`total_products_tax_excl` + s.`total_shipping_tax_excl`))*100, 2) as tax_rate FROM `ps_order_slip` s LEFT JOIN ps_orders o ON (o.`id_order` = s.`id_order`) WHERE 1 AND o.id_shop IN (1) AND MONTH(s.date_add) = IF(MONTH(now())=1,12,MONTH(now())) AND YEAR(s.date_add) = IF(MONTH(now())=1,YEAR(now())-1,YEAR(now()))) ORDER BY `Datum` ASC 3. Übersicht aller momentan ausstehenden Geschenkgutscheine inkl. Restwert - die Differenzierung zu anderen Gutscheinen läuft bei mir über das Präfix "LP-", müsstet ihr für eure Gegebenheiten anpassen; die letzte Spalte, weil beim Anlegen neuer Gutscheine "zzgl. MwSt." vorausgewählt ist und ich das manchmal vergesse umzustellen... SELECT a.code AS 'Code', a.description AS 'Beschreibung', a.reduction_amount AS 'Restguthaben', a.date_to AS 'Gültig bis', a.id_cart_rule AS 'Rabatt Regel Nr.', a.reduction_tax AS 'Steuer korrekt?' FROM ps_cart_rule a WHERE a.quantity >= 1 AND LEFT(a.code, 3)= 'LP-' ORDER BY a.date_to DESC 4. Waren-Umsatz pro Tag - wenn ich nur kurz schauen will, wo wir gerade stehen SELECT CAST(`date_add` AS DATE) AS `Datum`, REPLACE(ROUND(SUM(`total_paid_tax_incl`-`total_shipping_tax_incl`), 2), '.', ',') AS `Umsatz ohne Versand` FROM `ps_orders` GROUP BY `Datum` ORDER BY `Datum`DESC 3 Link to comment Share on other sites More sharing options...
NSN Posted January 21, 2021 Share Posted January 21, 2021 Gute Idee. SQL ist immer noch ein rotes Tuch für mich und die meisten meiner Abfragen wären ohne dieses Forum nicht zustande gekommen. 1. Bestellhistorie bestimmter Artikel SELECT DISTINCT date(o.date_add) AS Datum, o.id_order AS Bestellung, od.product_quantity AS Anzahl, od.product_reference AS BestellNr, od.product_name AS Artikel, od.total_price_tax_incl AS Summe, c.`id_customer` AS Kundennummer, c.`firstname` AS Vorname, c.`lastname` AS Name FROM `ps_customer` c LEFT JOIN `ps_orders` o ON (c.`id_customer` = o.`id_customer`) LEFT JOIN `ps_order_detail` od ON o.`id_order` = od.`id_order` WHERE od.`product_id` =XXX Die drei XXX am Ende einfach durch die ID des gewünschten Produkts ersetzen. 2. Zum checken bei welchen Kunden die Standardkundengruppe vom Soll abweicht SELECT c.id_customer FROM ps_customer c LEFT JOIN ps_customer_group cg ON (c.id_customer = cg.id_customer) WHERE cg.id_group=3 AND c.id_default_group!=3 Mir ging es dabei um die Kundengruppe 3 "Customer" da die bei Kunden die zuvor "Guest" waren nicht immer automatisch auf "Customer" geändert wurde. 3. Simple Abfrage mit Kundenname, E-Mail und Land des Kunden SELECT a.`firstname`, a.`lastname`, a.`email`, (SELECT c.`date_add` FROM `ps_guest` g LEFT JOIN `ps_connections` c ON c.`id_guest` = g.`id_guest` WHERE g.`id_customer` = a.`id_customer` ORDER BY c.`date_add` DESC LIMIT 1) AS `last_activity`, (SELECT cl.`name` FROM `ps_address` ad LEFT JOIN `ps_country_lang` cl ON cl.`id_country` = ad.`id_country` WHERE ad.`id_customer` = a.`id_customer` ORDER BY ad.`id_address` DESC LIMIT 1) AS `country_name` FROM `ps_customer` a 2 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now