API tools faq
paste
Advertisement
horozov86

Task 13 - SQL, added Sender and Receiver country

horozov86
Oct 8th, 2024 (edited)
185
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 5.19 KB | None | 0 0
raw download clone embed print report
  1. SELECT --+all_rows
  2. skunden.kundennr
  3. ,skunden.name1
  4. ,(SELECT bezeichnung FROM sbranche WHERE skunden.branche=sbranche.id)
  5. ,(SELECT listagg(t.t, ',') WITHIN GROUP (ORDER BY t.t) FROM (SELECT DISTINCT SKUNDENGRP.bezeichnung t FROM SKUNDENGRP LEFT JOIN SKUNDENGRPZUORD ON SKUNDENGRP.id = SKUNDENGRPZUORD.GRUPPENID LEFT JOIN ABPGRPCUSTOMFIELDV ON ABPGRPCUSTOMFIELDV.id = SKUNDENGRP.id WHERE CFBOOLEAN2 = 1 AND SKUNDENGRPZUORD.KUNDENNR = skunden.nr )t)
  6. ,akopf.auftragsnummer
  7. ,shierarchie.bezeichnung
  8. ,(SELECT  SVerkaufsart.matchcode FROM SVerkaufsart WHERE asendung.VERKAUFSARTID=SVerkaufsart.id)
  9. ,(SELECT SCUSTOMTYPEVALUES.BEZEICHNUNG FROM SCUSTOMTYPEVALUES WHERE asendung.SDGTYP11=SCUSTOMTYPEVALUES.id)
  10. ,asendung.check5
  11. ,(SELECT SUM(STATISTIKBETRAGRG) FROM akopf ak LEFT JOIN asendung ON ak.nr = asendung.akopfnr LEFT JOIN atasend ON asendung.sendungid = atasend.asendungid WHERE ak.nr = akopf.nr GROUP BY akopf.auftragsnummer)
  12. ,(SELECT SUM(STATISTIKBBETRAG/asd.ABRGRUPPEITEMSCOUNT ) costs FROM atasend LEFT JOIN asdgabrechnung asd ON atasend.nr = asd.ataid LEFT JOIN asendung ON asendung.sendungid = atasend.asendungid   LEFT JOIN akopf  ak ON asendung.akopfnr = ak.nr LEFT JOIN akondition ON asd.AKONDITION = akondition.nr LEFT JOIN skondi0 ON akondition.KONDITIONSNR = skondi0.nr LEFT JOIN ASDGABRECHNUNGPOS  asdp ON asd.AABRGRUPPE = asdp.ABRGRUPPE LEFT JOIN SLEISTUNGSARTEN s ON asdp.IDLEISTUNGSART = s.ID WHERE  skondi0.nr NOT IN (910368,3996738) AND UPPER(s.MATCHCODE) <> UPPER('sconto') AND UPPER(s.MATCHCODE) <> UPPER('ADJUSTMENT') AND UPPER(s.MATCHCODE) <> UPPER('CURRATE') AND  ak.nr = akopf.nr GROUP BY akopf.auftragsnummer)
  13. ,(CASE WHEN (SELECT SUM(STATISTIKBETRAGRG) FROM akopf ak LEFT JOIN asdgabrechnung asd ON ak.nr = asd.akopfnr LEFT JOIN skunden ON ak.FRACHTAGNR = skunden.nr LEFT JOIN skufaktu ON skunden.nr = skufaktu.nr LEFT JOIN asendung ON ak.nr = asendung.akopfnr LEFT JOIN atasend ON asendung.sendungid = atasend.asendungid LEFT JOIN atour ON atasend.tournr = atour.nr LEFT JOIN UDX_VIEW_CUSTOMREALLOCCNTOUR ON atour. nr = UDX_VIEW_CUSTOMREALLOCCNTOUR.tournr WHERE ak.nr = akopf.nr GROUP BY akopf.auftragsnummer) IS NULL THEN 0 ELSE (SELECT SUM(STATISTIKBETRAGRG) FROM akopf ak LEFT JOIN asdgabrechnung asd ON ak.nr = asd.akopfnr LEFT JOIN skunden ON ak.FRACHTAGNR = skunden.nr LEFT JOIN skufaktu ON skunden.nr = skufaktu.nr LEFT JOIN asendung ON ak.nr = asendung.akopfnr LEFT JOIN atasend ON asendung.sendungid = atasend.asendungid LEFT JOIN atour ON atasend.tournr = atour.nr LEFT JOIN UDX_VIEW_CUSTOMREALLOCCNTOUR ON atour. nr = UDX_VIEW_CUSTOMREALLOCCNTOUR.tournr WHERE ak.nr = akopf.nr GROUP BY akopf.auftragsnummer) END - CASE WHEN (SELECT  SUM(STATISTIKBBETRAG/asd.ABRGRUPPEITEMSCOUNT ) costs FROM atasend LEFT JOIN asdgabrechnung asd ON atasend.nr = asd.ataid LEFT JOIN asendung ON asendung.sendungid = atasend.asendungid   LEFT JOIN akopf  ak ON asendung.akopfnr = ak.nr LEFT JOIN akondition ON asd.AKONDITION = akondition.nr LEFT JOIN skondi0 ON akondition.KONDITIONSNR = skondi0.nr LEFT JOIN ASDGABRECHNUNGPOS  asdp ON asd.AABRGRUPPE = asdp.ABRGRUPPE LEFT JOIN SLEISTUNGSARTEN s ON asdp.IDLEISTUNGSART = s.ID WHERE  skondi0.nr NOT IN (910368,3996738) AND UPPER(s.MATCHCODE) <> UPPER('sconto') AND UPPER(s.MATCHCODE) <> UPPER('ADJUSTMENT') AND UPPER(s.MATCHCODE) <> UPPER('CURRATE') AND  ak.nr = akopf.nr GROUP BY akopf.auftragsnummer)IS NULL THEN 0 ELSE (SELECT  SUM(STATISTIKBBETRAG/asd.ABRGRUPPEITEMSCOUNT ) costs FROM atasend LEFT JOIN asdgabrechnung asd ON atasend.nr = asd.ataid LEFT JOIN asendung ON asendung.sendungid = atasend.asendungid   LEFT JOIN akopf  ak ON asendung.akopfnr = ak.nr LEFT JOIN akondition ON asd.AKONDITION = akondition.nr LEFT JOIN skondi0 ON akondition.KONDITIONSNR = skondi0.nr LEFT JOIN ASDGABRECHNUNGPOS  asdp ON asd.AABRGRUPPE = asdp.ABRGRUPPE LEFT JOIN SLEISTUNGSARTEN s ON asdp.IDLEISTUNGSART = s.ID WHERE  skondi0.nr NOT IN (910368,3996738) AND UPPER(s.MATCHCODE) <> UPPER('sconto')AND UPPER(s.MATCHCODE) <> UPPER('ADJUSTMENT') AND UPPER(s.MATCHCODE) <> UPPER('CURRATE')  AND  ak.nr = akopf.nr GROUP BY akopf.auftragsnummer) END)
  14. ,(SELECT MIN(atasend.VONLADEDATUM) FROM atasend LEFT JOIN asendung ase ON ase.sendungid=atasend.asendungid WHERE ase.sendungid=asendung.sendungid)
  15. ,(SELECT MAX(atasend.BISLADEDATUM) FROM atasend LEFT JOIN asendung ase ON ase.sendungid=atasend.asendungid WHERE ase.sendungid=asendung.sendungid)
  16. ,asendung.ABS_NAME1
  17. ,asendung.ABS_ORT1
  18. ,asendung.ABS_PLZ
  19. ,asendung.EMP_NAME1
  20. ,asendung.EMP_ORT1
  21. ,asendung.EMP_PLZ
  22. ,SLAND.LANDNAME
  23. ,SL.LANDNAME
  24.  
  25.  
  26.  
  27. FROM akopf
  28. LEFT JOIN skunden ON akopf.frachtagnr=skunden.nr
  29. LEFT JOIN asendung ON asendung.akopfnr=akopf.nr
  30. LEFT JOIN shierarchie ON akopf.hierarchie=shierarchie.id
  31. LEFT JOIN SLAND ON asendung.ABS_LAND = SLAND.LAND
  32. LEFT JOIN SLAND SL ON asendung.EMP_LAND = SL.LAND
  33. WHERE
  34. akopf.auftragsnummer > 0
  35. AND akopf.INRECYCLEBIN = 0 AND akopf.auftragsnummer = 998247
  36.  
  37.  
  38. AND (SELECT MIN(atasend.VONLADEDATUM) FROM atasend LEFT JOIN asendung ase ON ase.sendungid=atasend.asendungid WHERE ase.sendungid=asendung.sendungid) >= TO_DATE('2024-09-01', 'yyyy-mm-dd')
  39. AND (SELECT MAX(atasend.BISLADEDATUM) FROM atasend LEFT JOIN asendung ase ON ase.sendungid=atasend.asendungid WHERE ase.sendungid=asendung.sendungid) < TO_DATE('2024-10-09', 'yyyy-mm-dd')
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!