Neu exportvorlage für Destatis Intrahandelsstatistik

[Frank Hoffmeister]

Falls es jemand gebrauchen kann, hier eine einfache Abfrage die in Einkauf --> Bestellungen --> Abgeschlossen -- > Eigene Übersichten angelegt werden kann, um einen CSV Export für die Meldung beim statistischen Bundesamt zu machen.

select 'E' as Verkehrsrichtung,
right('00' + convert(varchar, DATEPART(MONTH, tLieferantenBestellungPos.dLieferdatum)), 2) as Monat,
'11'as 'Art des Geschäfts',
'03' as Verkehrszweig,
tlieferant.cISO as Versendungsland,
'' as Bestimmungsland,
'06' as Bestimmungsregion,
'' as Ursprungsregion, 
tlieferant.cISO as Ursprungsland,
LEFT(tartikel.cTaric,8) as Warennummer,
tLieferantenBestellungPos.cName as Warenbezeichnung,
case when (LEFT(cTaric,8) in (39233010,84238180,96034010,40151100)) then NULL else (cast((tLieferantenBestellungPos.fMengeGeliefert*tartikel.fArtGewicht) as decimal(10,0))) end as Eigenmasse,
case when (LEFT(cTaric,8) in (39233010,84238180,96034010,40151100)) then (cast(sum(tLieferantenBestellungPos.fMengeGeliefert) as decimal(10,0))) else NULL end as 'Besondere Maßeinheit',
cast((tLieferantenBestellungPos.feknetto*tLieferantenBestellungPos.fMengeGeliefert) as decimal(10,0)) as Rechnungsbetrag,
'' as 'Statistischer Wert',
'' as 'USt-IdNr.des Warenempfänger'
from tLieferantenBestellungPos
join tArtikel on tLieferantenbestellungPos.kArtikel =tArtikel.kArtikel
join tLieferantenBestellung on tLieferantenBestellungPos.kLieferantenBestellung = tLieferantenBestellung.kLieferantenBestellung
join tlieferant on tLieferantenBestellung.kLieferant = tlieferant.kLieferant
where (tLieferantenbestellungPos.dLieferdatum >= DATEADD(MONTH, -1 , DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP),1))) and (tLieferantenBestellungPos.dLieferdatum <= DATEADD(DAY, 0 , DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP),1))) and cISO != 'DE'
group by LEFT(cTaric,8),cISO,DATEPART(MONTH, tLieferantenBestellungPos.dLieferdatum),tLieferantenBestellungPos.cName,tLieferantenBestellungPos.feknetto*tLieferantenBestellungPos.fMengeGeliefert,tLieferantenBestellungPos.fMengeGeliefert*tartikel.fArtGewicht
order by DATEPART(MONTH, tLieferantenBestellungPos.dLieferdatum)

Genaue Definition der Vorgaben für Art des Geschäfts usw. zu finden unter : https://www-idev.destatis.de/idev/doc/hilfe9_3.html

 

[zero111181]

Danke für diese tolle Liste, ein Frage habe ich dort werden nicht alle Artikel aufgeführt. Die Artikel aus China bzw. Asien werden garnicht gelistet? Liegt es an den Artikel oder an dem Lieferant.

ich sehe auch gerade das sich der Monat nicht ändert, egal in welchen Monat ich rein klicke in der Tabelle wird immer Monat 01 und exakt immer der gleiche Betrag ausgegeben.

 

[Frank Hoffmeister]

Danke für diese tolle Liste, ein Frage habe ich dort werden nicht alle Artikel aufgeführt. Die Artikel aus China bzw. Asien werden garnicht gelistet? Liegt es an den Artikel oder an dem Lieferant.

ich sehe auch gerade das sich der Monat nicht ändert, egal in welchen Monat ich rein klicke in der Tabelle wird immer Monat 01 und exakt immer der gleiche Betrag ausgegeben.

Ich würde jetzt einmal annehmen, daß du bei deinen Lieferanten aus China bzw Asien als Iso Code DE hinterlegt hast. Da die where Bedingung alle Lieferanten ausgibt bei denen als ISO Code nicht DE hinterlegt ist.

Die Datumseingrenzung ist immer der Vormonat. also im Januar bekommst du den Dezember angezeigt, im Februar den Januar, im März den Februar, im April den März usw.

Dadurch ergibt sich am 1. jeden Monats die fertige Liste die gemeldet werden muß.

Art des Geschäfts, Verkehrszweig und Bestimmungsregion müssen für dich auch angepasst werden. Die passenden Werte dafür findest du unter https://www-idev.destatis.de/idev/doc/hilfe9_3.html . Weiterhin kann es auch sein das du die case Ausnahme aufgrund deiner Waren auch noch anpassen mußt.

Mfg
Frank Hoffmeister

 

[zero111181]

Ich würde jetzt einmal annehmen, daß du bei deinen Lieferanten aus China bzw Asien als Iso Code DE hinterlegt hast. Da die where Bedingung alle Lieferanten ausgibt bei denen als ISO Code nicht DE hinterlegt ist.

Die Datumseingrenzung ist immer der Vormonat. also im Januar bekommst du den Dezember angezeigt, im Februar den Januar, im März den Februar, im April den März usw.

Dadurch ergibt sich am 1. jeden Monats die fertige Liste die gemeldet werden muß.

Art des Geschäfts, Verkehrszweig und Bestimmungsregion müssen für dich auch angepasst werden. Die passenden Werte dafür findest du unter https://www-idev.destatis.de/idev/doc/hilfe9_3.html . Weiterhin kann es auch sein das du die case Ausnahme aufgrund deiner Waren auch noch anpassen mußt.

Mfg
Frank Hoffmeister

Hallo Frank

da passt aber etwas noch nicht weil ich z.b. so wie du das sagst ich ein Liste aus dem Januar angezeigt werden bekommen soll wenn ich im Februar bin. In der Liste sind aber Artikel die ich erst im Juni 2020 gelistet habe. Die dürften in der Liste von Januar 2020 wenn ich mich im Februar befinde nicht da sein.

Die ersten 3 Artikel aus der Liste sind z.B. aus Juni 2020

 

[Frank Hoffmeister]

Sorry aber kann mit den Screenshots nichts anfangen, meine Vermutung ist, daß dein Lieferdatum in der Bestellung wohl nicht paßt, da die Bedingung
SELECT (DATEADD(MONTH, -1 , DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP),1))) AS start_of_last_month,(DATEADD(DAY, 0 , DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP),1))) AS start_of_current_month
den ersten des Vormonat und den ersten des aktuellen Monat ausgibt. Lieferdatum muß also dazwischen liegen. Kannst dir ja den Datumscode mal kopieren und im Managementstudio ausprobieren.

Meine zweite Vermutung, die mir gerade kommt ist ein evtl. Denkfehler.

Da wir jetzt Februar 2021 haben kommt als Ausgabe das von Januar 2021 egal was du oben für einen Monat auswählst. Im März 2021 kommt die Ausgabe von Februar 2021 auch egal was oben für ein Datum ausgewählt ist usw....

 

[zero111181]

Also das Lieferdatum sollte passen, wenn ich die Bestellung ja aufgeben kann das Lieferdatum ja nicht in der Vergangenheit liegen. Ich wollte damit nur zeigen das die ersten 3 Position der warenbezeichnung erst im Juni 2020 aufgenommen und bestellt wurden, da können bzw. dürfen die nicht im Januar 2020 gelistet sein. Das mit dem Denkfehler würde ich eher vermuten.

 

[LiquidSports]

Hallo Zusammen,

bei mir zeigt der Befehl irgendwie gar nichts an. Muss ich mich in einen bestimmten Monat einwählen?

Danke und Grüße

 

[LiquidSports]

Hallo Zusammen,

bei mir zeigt der Befehl irgendwie gar nichts an. Muss ich mich in einen bestimmten Monat einwählen?

Danke und Grüße

 

[fibergirl]

Eine tolle SQL-Abfrage, vielen Dank dafür!

Hast Du das evtl. auch für die VK-Seite fertig? Ich wäre sehr interessiert!

Dank & Gruß,
fibergirl

 

[Frank Hoffmeister]

Hallo fibergirl,
ich habe es bis jetzt noch nicht für VK benötigt. Würde sich aber anpassen lassen, wobei es vermutlich schon bißchen mehr Aufwand sein wird, da es von eurem Arbeitsablauf abhängt.
Kannst dich gerne melden.
Gruß Frank

 

[fibergirl]

So,
jetzt gibt es auch eine "übersichtliche" Version für den VK, allerdings erst ab Wawi 1.6. Danke für die Anregung, @Frank Hoffmeister !

Einfach unter "Verkauf" in die Eigenen Übersichten einfügen.

select 'V' as Verkehrsrichtung,
right('00' + convert(varchar, DATEPART(MONTH, Rechnung.tRechnung.dErstellt)), 2) as Monat,
'11'as 'Art des Geschäfts',
'5' as Verkehrszweig,
'' as Versendungsland,
Rechnung.vRechnungLieferadresse.cISO as Bestimmungsland,
'' as Bestimmungsregion,
'99' as Ursprungsregion,
'' as Ursprungsland,
LEFT(tartikel.cTaric,8) as Warennummer,
'' as Warenbezeichnung,
cast(sum(Rechnung.tRechnungPosition.fAnzahl*dbo.tartikel.fArtGewicht) as decimal(10,0)) as Eigenmasse,
cast(sum(Rechnung.tRechnungPosition.fAnzahl) as decimal(10,0)) as 'Besondere Maßeinheit',
cast(sum(Rechnung.tRechnungPosition.fAnzahl*Rechnung.tRechnungPosition.fVkNetto) as decimal(10,0)) as Rechnungsbetrag,
'' as 'Statistischer Wert',
'' as 'USt-IdNr.des Warenempfänger'
from Rechnung.tRechnungPosition
join tArtikel on Rechnung.tRechnungPosition.kArtikel = tArtikel.kArtikel
join Rechnung.tRechnung on Rechnung.tRechnungPosition.kRechnung = Rechnung.tRechnung.kRechnung
join Rechnung.vRechnungLieferadresse on Rechnung.tRechnungPosition.kRechnung = Rechnung.vRechnungLieferadresse.kRechnung
join tland on Rechnung.tRechnung.cVersandlandISO = dbo.tland.cISO
where Rechnung.tRechnungPosition.nType < 2
and Rechnung.tRechnungPosition.kArtikel is not null
and (Rechnung.tRechnung.dErstellt > EOMONTH(DATEADD(MONTH, -2, CURRENT_TIMESTAMP)))
and (Rechnung.tRechnung.dErstellt <= EOMONTH(DATEADD(MONTH, -1, CURRENT_TIMESTAMP)))
and dbo.tland.nEU = 1
and Rechnung.vRechnungLieferadresse.cISO != 'DE'
group by Rechnung.vRechnungLieferadresse.cISO,
LEFT(cTaric,8),
DATEPART(MONTH,Rechnung.tRechnung.dErstellt)
order by Rechnung.vRechnungLieferadresse.cISO

Zu beachten:
- Die Version ist erstellt mit Wawi 1.6.27.2 und SQL-Server 2019. Nicht geignet für die Wawi 1.5.
- Nur für Versandland DE.
- Es werden jeweils die Daten für den Vormonat ausgegeben, derzeit (28. Juli) also für den Juni. Ab 1. August gibt es dann die Juli-Daten.
- Ausgegeben werden nur die kumulierten absoluten Pflichtangaben, nach Bestimmungsland und TARIC-Nummer zusammengefasst. Die einzelnen Aufträge sind nicht mehr erkennbar.
- Nur "richtige" Artikel aus dem Artikelstamm zählen, denn nur diese können ein Gewicht und eine TARIC haben.

Leider habe ich noch keine aussagekräftigen Daten aus der Vergangenheit, wir sind ja erst auf JTL umgestiegen.
Vielleicht mag jemand mit vorhandenen Daten die korrekte Funktion testen.

Dank & Gruß,
fibergirl

 

[Frank Hoffmeister]

Ich habe noch keine 1.6 zum testen, aber mit 1.5 wird es ziemlich sicher nicht gehen, da es meines Wissens nach keine tRechnungsPosition gibt.
Gruß Frank

 

[fibergirl]

Stimmt, vielen Dank.
Diese Tabelle gibt es erst mit der Wawi 1.6.
Naja, kommt ja bald (hoffentlich).

Vermutlich gibt es auch einen anderen Weg für die 1.5.
Müsste dann aber jemand anders machen.

Hier kann man sehen, welche Tabellen von welcher Wawi-Version verwendet werden:
https://wawi-db.jtl-software.de/tables/1.5.48.0 (am Beispiel JTL-Wawi1.5.48.0)

Dank & Gruß,
fibergirl

 

[Krisscoffee]

Hallo,

Hat es jemand auch mit 1.7.13.1 getestet?

Gruß, Kriss

 

[JuergenSaenger]

Hallo,

Hat es jemand auch mit 1.7.13.1 getestet?

Gruß, Kriss

Hast du es ggf. umgesetzt bekommen?

In der 1.7.12.1 funktioniert der Code nicht.

 

[Krisscoffee]

Hallo Jurgen,

Leider nicht. Für Intrastat verwende ich die kostenpflichtige Lösung von JTL2Datev.


MfG

Kriss

 

[Jamara]

Hallo,

etwas verspätet, aber vielleicht hilft es jemand.
Hier die angepasste Version mit der 1.9.6.5 getestet.
Diesmal aufgeteilt nach einzelnen Positionen.
Der Rechnungsbetrag berücksichtigt noch nicht die Versandkosten, diese müssten wahrscheinlich nach einem Schlüssel auf die einzelnen Positionen aufgeteilt werden.
Alle Felder nach Ust-ID sind nur für Debugging Zwecke gedacht.
Außerdem ist das Lieferdatum wichtig, in dieser Abfrage wird aber noch das Rechnungserstellungsdatum genommen.
Datum ist für Januar, 2025 mit

and (Rechnung.tRechnung.dErstellt > EOMONTH(DATEADD(MONTH, -2, CURRENT_TIMESTAMP)))
and (Rechnung.tRechnung.dErstellt <= EOMONTH(DATEADD(MONTH, -1, CURRENT_TIMESTAMP)))

, hat es den 31 nicht voll genommen.

Für Endkunden Geschäfte und Händlergeschäfte gleichzeitig ausgelegt.
Freue mich auf Hinweise und Verbesserungsvorschläge.
Hoffe hilft hier jemand weiter.

SELECT
    CASE
        WHEN r.cKundengruppe = 'Endkunden' THEN '12'
        ELSE '11'
    END AS [Art des Geschäfts],
    rla.cISO AS Bestimmungsmitgliedstaat,
    a.cHerkunftsland AS Ursprungsland,
    LEFT(a.cTaric, 8) AS Warennummer,
    CAST(rp.fAnzahl * a.fArtGewicht AS DECIMAL(10, 0)) AS Eigenmasse,
    CAST(rp.fAnzahl * rp.fVkNetto AS DECIMAL(10, 0)) AS Rechnungsbetrag,
    CAST(rp.fAnzahl * rp.fVkNetto AS DECIMAL(10, 0)) AS [Statistischer Wert],
    CASE
        WHEN (r.cKundeUstId IS NULL OR r.cKundeUstId = '') AND r.cKundengruppe = 'Endkunden'
            THEN 'QN999999999999'
        WHEN (r.cKundeUstId IS NULL OR r.cKundeUstId = '')
            THEN 'QV999999999999'
        ELSE r.cKundeUstId
    END AS [USt-IdNr.des Warenempfänger],
    a.cArtNr,
    r.cRechnungsnr,
    r.cKundengruppe,
    r.dErstellt,
    rp.kRechnungPosition AS PositionsID,
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(MONTH, r.dErstellt)), 2) AS Monat
FROM
    Rechnung.tRechnungPosition rp
JOIN
    tArtikel a ON rp.kArtikel = a.kArtikel
JOIN
    Rechnung.tRechnung r ON rp.kRechnung = r.kRechnung
OUTER APPLY (
    SELECT TOP 1 *
    FROM Rechnung.vRechnungLieferadresse
    WHERE kRechnung = r.kRechnung
) rla
WHERE
    rp.nType < 2
    AND rp.kArtikel IS NOT NULL
    AND r.cVersandlandISO IN (
        SELECT cISO FROM tland WHERE nEU = 1
    )
    AND rla.cISO != 'DE'
    AND CONVERT(DATE, r.dErstellt) >= '2025-01-01'
    AND CONVERT(DATE, r.dErstellt) <= '2025-01-31'
    AND rp.fVkNetto != 0
    AND a.cTaric != ''
    AND r.nStorno = 0
    AND r.nistProforma = 0
  AND r.nistEntwurf = 0
ORDER BY
    rla.cISO;