Neu SQL für komplett lieferbare Aufträge

[chummer]

Hallo,

ich versuche eine Abfrage zu erstellen, mit der ich alle sofort lieferbaren Aufträge (bezahlt, vollständig auf Lager) angezeigt bekomme.
Im Endeffekt das gleiche Ergebnis wie im Tab Versand -> lieferbar, allerdings nach Plattform oder Versandart gruppiert.

Das Ganze dient dazu um es in eine txt Datei zu exportieren die über einen internen Webserver dargestellt wird. So kann man ein Display damit füttern und man sieht direkt im Lager wie viele Bestellungen von wo noch bearbeitet werden müssen.

Hat vielleicht jemand so etwas schon parat liegen oder kann mir mit der Abfrage helfen? Bin da leider nicht so fit mit SQL.

Danke sehr und schönen Arbeitstag.

Grüße, René

 

[Karobube]

Lieber René,

das sollte so klappen, teste es vielleicht einmal - einfach so ins SQLManagement Studio kopieren,
alternativ kannst Du oben die Felder rauslöschen, die Du nicht brauchst, bzw ganz unten nur nach Versandart/Platform sortieren, indem Du das jeweils andere löschst. Aus dem SQLManagement Studio kannst Du die Ergebnistabelle dann als CSV exportieren, im Editor öffnen, und als TXT speichern.

Wenn's geholfen hat, gerne dem nächsten Bedürftigen zustecken, was es Dir Wert ist,

LG Karobube



SELECT DISTINCT tBestellung.kBestellung
,[tRechnung_kRechnung]
,[tBenutzer_kBenutzer]
,[tAdresse_kAdresse]
,[tText_kText]
,[tKunde_kKunde]
,[cBestellNr]
,[cType]
,[cAnmerkung]
,tBestellung.dErstellt
,[nZahlungsziel]
,[tVersandArt_kVersandArt]
,[fVersandBruttoPreis]
,tBestellung.fRabatt
,[kInetBestellung]
,[cVersandInfo]
,[dVersandt]
,[cIdentCode]
,[cBeschreibung]
,[cInet]
,tBestellung.dLieferdatum
,[kBestellHinweis]
,[cErloeskonto]
,tBestellung.cWaehrung
,tBestellung.fFaktor
,[kShop]
,[kFirma]
,[kLogistik]
,[nPlatform]
,[kSprache]
,tBestellung.fGutschein
,[dGedruckt]
,[dMailVersandt]
,[cInetBestellNr]
,tBestellung.kZahlungsArt
,[kLieferAdresse]
,[kRechnungsAdresse]
,[nIGL]
,[nUStFrei]
,[cStatus]
,[dVersandMail]
,[dZahlungsMail]
,[cUserName]
,[cVerwendungszweck]
,[fSkonto]
,[kColor]
,[nStorno]
,[cModulID]
,[nZahlungsTyp]
,tBestellung.nHatUpload
,[fZusatzGewicht]
,[nKomplettAusgeliefert]
,[dBezahlt]
,[kSplitBestellung]
,[cPUIZahlungsdaten]
,[nPrio]
,[cVersandlandISO]
,[cUstId]
,[nPremium]
,[cVersandlandWaehrung]
,[fVersandlandWaehrungFaktor]
,[kRueckhalteGrund]
,[cOutboundId]
,[kFulfillmentLieferant]
,[cKundenauftragsnummer]
,[nIstReadOnly]
,[cAmazonServiceLevel]
,[nIstExterneRechnung]
,[cKampagne]
,[cKampagneParam]
,[cKampagneName]
,[cUserAgent]
,[cReferrer]
,[nMaxLiefertage]
,tBestellung.bRowversion
,[dErstelltWawi] FROM tBestellung
JOIN dbo.tBestellungEckDaten ON tBestellungEckDaten.kBestellung = tBestellung.kBestellung
JOIN dbo.tZahlungsart ON tBestellung.kZahlungsart = tZahlungsart.kZahlungsart
JOIN dbo.tbestellpos ON tBestellung.kBestellung = tbestellpos.tBestellung_kBestellung
JOIN dbo.tlagerbestand ON tlagerbestand.kArtikel = tbestellpos.tArtikel_kArtikel
WHERE (tBestellungEckDaten.fWert-tBestellungEckDaten.fZahlung <= 0 /* Bezahlt oder Wert 0 */
OR tZahlungsart.nAusliefernVorZahlung = 1) /* oder Auslieferung vor Zahlung */
AND tBestellung.nKomplettAusgeliefert = 0 /* noch nicht komplett geliefert */
AND tBestellung.nStorno = 0 /* nicht storniert */
AND tBestellung.kRueckhaltegrund = 0 /* nicht zurückgehalten */
AND tBestellung.cType = 'B' /* Bestellung, kein Angebot oder Umlagerung */
AND ( tlagerbestand.fLagerbestand >= tbestellpos.nAnzahl /* Genug auf Lager je Position */
OR tbestellpos.nType = 0 ) /* oder ist Freiposition */
ORDER BY tBestellung.tVersandArt_kVersandArt, tBestellung.nPlatform

 

[chummer]

Vielen Dank. Das funktioniert sehr gut. 🙂

 

[garifulin]

Klasse Skript ich vernutte aber da hat sich irgendwo ein Fehler eingeschliechen.
Wenn ich den Sktipt ausführe bekomme ich 125 Ergebnisse unter Lieferbar habe ich jedoch nur 29
Kann das sein dass hier teilversendeten Aufträge mit dabei sind?

 

[Karobube]

Klasse Skript ich vernutte aber da hat sich irgendwo ein Fehler eingeschliechen.
Wenn ich den Sktipt ausführe bekomme ich 125 Ergebnisse unter Lieferbar habe ich jedoch nur 29
Kann das sein dass hier teilversendeten Aufträge mit dabei sind?

Hallo Garifulin,

ich habe das Skript schnell runtergeschrieben, das handelt teillieferbare Aufträge und Artikelreservierungen für Aufträge vermutlich noch nicht korrekt, habe Dir eine PN geschrieben.

VG KB

 

[garifulin]

Für schnell mal runtergeschrieben ist das auf jedenfall schon mal deluxe.
Danke für den Tipp mit den Artikelreservierungen, die habe ich noch gar nicht bedacht gehabt.
Bin gerade eigentlich die Views am durchgehen.

Danke für das Angebot komme ich evt. zurück (sobald die Anforderung vom Chef kommt)

 

[DeinConsultant]

Hi zusammen, hat das eventuell jemand schon für die 1.6 angepasst? @Karobube eventuell?

 

[HMside]

Lieber René,

das sollte so klappen, teste es vielleicht einmal - einfach so ins SQLManagement Studio kopieren,
alternativ kannst Du oben die Felder rauslöschen, die Du nicht brauchst, bzw ganz unten nur nach Versandart/Platform sortieren, indem Du das jeweils andere löschst. Aus dem SQLManagement Studio kannst Du die Ergebnistabelle dann als CSV exportieren, im Editor öffnen, und als TXT speichern.

Wenn's geholfen hat, gerne dem nächsten Bedürftigen zustecken, was es Dir Wert ist,

LG Karobube



SELECT DISTINCT tBestellung.kBestellung
,[tRechnung_kRechnung]
,[tBenutzer_kBenutzer]
,[tAdresse_kAdresse]
,[tText_kText]
,[tKunde_kKunde]
,[cBestellNr]
,[cType]
,[cAnmerkung]
,tBestellung.dErstellt
,[nZahlungsziel]
,[tVersandArt_kVersandArt]
,[fVersandBruttoPreis]
,tBestellung.fRabatt
,[kInetBestellung]
,[cVersandInfo]
,[dVersandt]
,[cIdentCode]
,[cBeschreibung]
,[cInet]
,tBestellung.dLieferdatum
,[kBestellHinweis]
,[cErloeskonto]
,tBestellung.cWaehrung
,tBestellung.fFaktor
,[kShop]
,[kFirma]
,[kLogistik]
,[nPlatform]
,[kSprache]
,tBestellung.fGutschein
,[dGedruckt]
,[dMailVersandt]
,[cInetBestellNr]
,tBestellung.kZahlungsArt
,[kLieferAdresse]
,[kRechnungsAdresse]
,[nIGL]
,[nUStFrei]
,[cStatus]
,[dVersandMail]
,[dZahlungsMail]
,[cUserName]
,[cVerwendungszweck]
,[fSkonto]
,[kColor]
,[nStorno]
,[cModulID]
,[nZahlungsTyp]
,tBestellung.nHatUpload
,[fZusatzGewicht]
,[nKomplettAusgeliefert]
,[dBezahlt]
,[kSplitBestellung]
,[cPUIZahlungsdaten]
,[nPrio]
,[cVersandlandISO]
,[cUstId]
,[nPremium]
,[cVersandlandWaehrung]
,[fVersandlandWaehrungFaktor]
,[kRueckhalteGrund]
,[cOutboundId]
,[kFulfillmentLieferant]
,[cKundenauftragsnummer]
,[nIstReadOnly]
,[cAmazonServiceLevel]
,[nIstExterneRechnung]
,[cKampagne]
,[cKampagneParam]
,[cKampagneName]
,[cUserAgent]
,[cReferrer]
,[nMaxLiefertage]
,tBestellung.bRowversion
,[dErstelltWawi] FROM tBestellung
JOIN dbo.tBestellungEckDaten ON tBestellungEckDaten.kBestellung = tBestellung.kBestellung
JOIN dbo.tZahlungsart ON tBestellung.kZahlungsart = tZahlungsart.kZahlungsart
JOIN dbo.tbestellpos ON tBestellung.kBestellung = tbestellpos.tBestellung_kBestellung
JOIN dbo.tlagerbestand ON tlagerbestand.kArtikel = tbestellpos.tArtikel_kArtikel
WHERE (tBestellungEckDaten.fWert-tBestellungEckDaten.fZahlung <= 0 /* Bezahlt oder Wert 0 */
OR tZahlungsart.nAusliefernVorZahlung = 1) /* oder Auslieferung vor Zahlung */
AND tBestellung.nKomplettAusgeliefert = 0 /* noch nicht komplett geliefert */
AND tBestellung.nStorno = 0 /* nicht storniert */
AND tBestellung.kRueckhaltegrund = 0 /* nicht zurückgehalten */
AND tBestellung.cType = 'B' /* Bestellung, kein Angebot oder Umlagerung */
AND ( tlagerbestand.fLagerbestand >= tbestellpos.nAnzahl /* Genug auf Lager je Position */
OR tbestellpos.nType = 0 ) /* oder ist Freiposition */
ORDER BY tBestellung.tVersandArt_kVersandArt, tBestellung.nPlatform

Sehr hilfreiches Skript, vielen Dank dafür. Ich hätte es gerne noch etwas einfacher aufgelistet und zwar nur die Anzahl aller vollständig lieferbaren Aufträge als Gesamtsumme und zusätzlich die Summe pro Platform. Hättest Du dazu ggf. auch etwas parat?

 

[Mario.]

Hallo,
nach Update auf die 1.7 funktioniert es leider nicht mehr. Die Tabellen haben sich geändert.
Wer könnte ein aktuelles Skript einstellen? Karobude?

 

[kelvin.]

Die Tabellen tBestellung, tBestellungEckdaten und tBestellpos haben sich seit 1.6 geändert.
So müsste es funktionieren:

SELECT *
FROM Verkauf.tAuftrag AS Auftrag
JOIN Verkauf.tAuftragEckdaten AS Eckdaten
    ON Eckdaten.kAuftrag = Auftrag.kAuftrag
JOIN dbo.tZahlungsart AS Zahlungsart
    ON Zahlungsart.kZahlungsart = Auftrag.kZahlungsart
JOIN Verkauf.tAuftragPosition AS Pos
    ON Pos.kAuftrag = Auftrag.kAuftrag
JOIN dbo.tlagerbestand AS Lagerbestand
    ON Lagerbestand.kArtikel = Pos.kArtikel
WHERE
    (
    Eckdaten.fWertBrutto - Eckdaten.fZahlung <= 0 -- Bezahlt oder Wert 0
    OR Zahlungsart.nAusliefernVorZahlung = 1 -- oder Auslieferung vor Zahlung
    )
    AND Auftrag.nKomplettAusgeliefert = 0 -- noch nicht komplett ausgeliefert
    AND Auftrag.nStorno = 0 -- nicht storniert
    AND Auftrag.kRueckhaltegrund = 0 -- nicht zurückgehalten
    AND Auftrag.nType = 1 -- Auftrag, kein Angebot
    AND
        (
        Lagerbestand.fLagerbestand >= Pos.fAnzahl -- Genug auf Lager je Position
        OR Pos.nType = 0 -- oder ist Freiposition
        )
ORDER BY Auftrag.kVersandArt, Auftrag.kPlattform

 

[Mario.]

Leider kommt dieser Fehler:

convert frame from rows error: duplicate column names are not allowed, found identical name "kAuftrag" at column indices 0 and 65

WaWi 1.7.9.0

 

[Karobube]

Leider kommt dieser Fehler:

convert frame from rows error: duplicate column names are not allowed, found identical name "kAuftrag" at column indices 0 and 65

WaWi 1.7.9.0

SELECT TOP (1000) Auftrag.kAuftrag
      ,[kBenutzer]
      ,[kKunde]
      ,[cAuftragsNr]
      ,Auftrag.nType
      ,[dErstellt]
      ,[kShopauftrag]
      ,[nBeschreibung]
      ,[cInet]
      ,[cWaehrung]
      ,Auftrag.fFaktor
      ,[kShop]
      ,[kFirmaHistory]
      ,[kPlattform]
      ,[kSprache]
      ,[cExterneAuftragsnummer]
      ,[nSteuereinstellung]
      ,[cEbayUsername]
      ,[cShopZahlungsmodul]
      ,Auftrag.nHatUpload
      ,[fZusatzGewicht]
      ,[cVersandlandISO]
      ,[cVersandlandBundeslandKuerzel]
      ,[cVersandlandWaehrung]
      ,[fVersandlandWaehrungFaktor]
      ,[kVersandArt]
      ,[nZahlungszielTage]
      ,[dVoraussichtlichesLieferdatum]
      ,[dAuslieferdatum]
      ,[fSkonto]
      ,[nSkontoTage]
      ,[kVorgangsstatus]
      ,[nStorno]
      ,[nKomplettAusgeliefert]
      ,[nLieferPrioritaet]
      ,[nPremiumVersand]
      ,[kRueckhaltegrund]
      ,Auftrag.kZahlungsart
      ,[kWarenlager]
      ,[nIstExterneRechnung]
      ,[nIstReadOnly]
      ,[nMaxLiefertage]
      ,[cOutboundId]
      ,[kFulfillmentLieferant]
      ,[cUstId]
      ,[nArchiv]
      ,Auftrag.nReserviert
      ,[nDebitorennr]
      ,[nAuftragStatus]
      ,[cKundenAuftragsnummer]
      ,[cAmazonServiceLevel]
      ,[dExternesErstelldatum]
      ,[kFarbe]
      ,[fFinanzierungskosten]
      ,[kAuftragQuelle]
      ,[nAuftragQuelleAktion]
      ,[kArtikelKarton]
      ,[kAmazonUser]
      ,Auftrag.bRowversion
      ,[cKundenNr]
      ,[kKundengruppe]
      ,[dErstelltWawi]
      ,[dAuslieferungAb]
      ,[cKundeUstId]
      ,[nIstDropshipping]
      ,[nPending]
FROM Verkauf.tAuftrag AS Auftrag
JOIN Verkauf.tAuftragEckdaten AS Eckdaten
    ON Eckdaten.kAuftrag = Auftrag.kAuftrag
JOIN dbo.tZahlungsart AS Zahlungsart
    ON Zahlungsart.kZahlungsart = Auftrag.kZahlungsart
JOIN Verkauf.tAuftragPosition AS Pos
    ON Pos.kAuftrag = Auftrag.kAuftrag
JOIN dbo.tlagerbestand AS Lagerbestand
    ON Lagerbestand.kArtikel = Pos.kArtikel
WHERE
    (
    Eckdaten.fWertBrutto - Eckdaten.fZahlung <= 0 -- Bezahlt oder Wert 0
    OR Zahlungsart.nAusliefernVorZahlung = 1 -- oder Auslieferung vor Zahlung
    )
    AND Auftrag.nKomplettAusgeliefert = 0 -- noch nicht komplett ausgeliefert
    AND Auftrag.nStorno = 0 -- nicht storniert
    AND Auftrag.kRueckhaltegrund = 0 -- nicht zurückgehalten
    AND Auftrag.nType = 1 -- Auftrag, kein Angebot
    AND
        (
        Lagerbestand.fLagerbestand >= Pos.fAnzahl -- Genug auf Lager je Position
        OR Pos.nType = 0 -- oder ist Freiposition
        )
ORDER BY Auftrag.kVersandArt, Auftrag.kPlattform

 

[TomRogge]

Ich habe mich auch mal eben mit dem Snippet ausprobiert. Leider erhalte ich für diese Abfrage nur eine leere Tabelle (bzw. 0 rows affected) im SQL Management Studio zurück. Es existieren im JTL jedoch Aufträge die lieferbar sind. Wir arbeiten mit der WAWI 1.7.10.0

 

[TomRogge]

Okay, ich habe jetzt noch ein wenig rumprobiert und habe festgestellt, dass der Rückhaltegrund mit = 0 nicht mehr ausgelesen werden kann, sondern lediglich via Abfrage IS NULL.

AND Auftrag.kRueckhaltegrund IS Null

Leider erhalte ich immer noch eine erhebliche Differenz. Es sind noch Aufträge enthalten, welche sich auf einer Pickliste befinden, sowie Aufträge die nur teillieferbar oder nicht lieferbar sind, obwohl der Lagerbestand abgefragt wird.

 

[HMside]

Hallo, ggf. ist das für Euch auch interessant. Anzahl lieferbare Auftrage nach Plattform aufgeschlüsselt (Wawi 1.7.13.1):

SELECT lvAuftrag.cPlattformName AS Plattform, COUNT(*) AS AnzahlAufträge
FROM [Versand].[lvAuftrag] AS lvAuftrag
WHERE (lvAuftrag.[nIstBezahlt] = 1)
  AND (lvAuftrag.[kRueckhalteGrund] = 0)
  AND (lvAuftrag.[nAuftragStatus] = 0)
  AND (lvAuftrag.[nPickstatus] = 0)
  AND (lvAuftrag.[nVersandstatusEigen] = 2)
  AND (lvAuftrag.[kWarenlager] = 0)
GROUP BY lvAuftrag.cPlattformName;
;

 

[chummer]

Hallo, ggf. ist das für Euch auch interessant. Anzahl lieferbare Auftrage nach Plattform aufgeschlüsselt (Wawi 1.7.13.1):

SELECT lvAuftrag.cPlattformName AS Plattform, COUNT(*) AS AnzahlAufträge
FROM [Versand].[lvAuftrag] AS lvAuftrag
WHERE (lvAuftrag.[nIstBezahlt] = 1)
  AND (lvAuftrag.[kRueckhalteGrund] = 0)
  AND (lvAuftrag.[nAuftragStatus] = 0)
  AND (lvAuftrag.[nPickstatus] = 0)
  AND (lvAuftrag.[nVersandstatusEigen] = 2)
  AND (lvAuftrag.[kWarenlager] = 0)
GROUP BY lvAuftrag.cPlattformName;
;

Einfach, kurz und knackig. Vielen Dank fürs Teilen.