Zeigt her eure SQL Abfragen

[Dp.]

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

 

[NSN]

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