Advertisement
horozov86

Task 9 - SQL

Aug 30th, 2024 (edited)
268
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2.     SUM(ASD.BETRAG) + SUM(ASD.BETRAGSTP) + SUM(ASD.CALCSTATSTEUERBETRAG) AS "Total Income",
  3.     SWA.ISOWAEHRUNGSCODE AS "Currency order",
  4.     SUM(ASDG.BETRAG) + SUM(ASDG.BETRAGSTP) + SUM(ASDG.CALCSTATSTEUERBETRAG) AS "Total Expenses",
  5.     SW.ISOWAEHRUNGSCODE AS "Currency Tour",
  6.     AKOPF.AUFTRAGSNUMMER AS "Order number",
  7.     SH.BEZEICHNUNG AS "Order organisation",
  8.     SK.NAME1 AS "Customer Name",
  9.     SUM(ASD.BETRAG) AS "Amount order",
  10.     SUM(ASD.BETRAGSTP) AS "Amount without TAX order",
  11.     SUM(ASD.CALCSTATSTEUERBETRAG) AS "TAX Amount order",
  12. --    SHI.BEZEICHNUNG AS "Tour Organisation",
  13. --    ATOUR.TOURNUMMER AS "Tour numbers",
  14.     LISTAGG(ATOUR.TOURNUMMER, ', ') WITHIN GROUP (ORDER BY ATOUR.TOURNUMMER) AS "Tour numbers",
  15.     LISTAGG(SKUN.NAME1, ', ') WITHIN GROUP (ORDER BY SKUN.NAME1) AS "Carrier Name",
  16.     LISTAGG(SHI.BEZEICHNUNG, ', ') WITHIN GROUP (ORDER BY SHI.BEZEICHNUNG) AS "Tour Organisation",
  17. --    SKUN.NAME1 AS "Carrier Name",
  18.     SUM(ASDG.BETRAG) AS "Amount tour",
  19.     SUM(ASDG.BETRAGSTP) AS "Amount without TAX tour",
  20.     SUM(ASDG.CALCSTATSTEUERBETRAG) AS "TAX Amount tour"
  21.  
  22. FROM
  23.     AKOPF
  24. LEFT JOIN
  25.     SHIERARCHIE SH ON SH.ID = AKOPF.HIERARCHIE
  26. LEFT JOIN
  27.     ASENDUNG ON ASENDUNG.AKOPFNR = AKOPF.NR
  28. LEFT JOIN
  29.     SKUNDEN SK ON SK.NR = AKOPF.FRACHTAGNR
  30. LEFT JOIN
  31.     ASDGABRECHNUNG ASD ON ASD.ASENDUNGID = ASENDUNG.SENDUNGID
  32. LEFT JOIN
  33.     SWAEHRUNG SWA ON SWA.ID = ASD.WAEHRUNGID
  34. LEFT JOIN
  35.     ATASEND ON ASENDUNG.SENDUNGID = ATASEND.ASENDUNGID
  36. LEFT JOIN
  37.     ATOUR ON ATASEND.TOURNR = ATOUR.NR
  38. LEFT JOIN
  39.     SHIERARCHIE SHI ON SHI.ID = ATOUR.HIERARCHIE
  40. LEFT JOIN
  41.     SKUNDEN SKUN ON SKUN.NR = ATOUR.UNTERNEHMER
  42. LEFT JOIN
  43.     ASDGABRECHNUNG ASDG ON ASDG.ATAID = ATASEND.NR
  44. LEFT JOIN
  45.     SWAEHRUNG SW ON SW.ID = ASDG.WAEHRUNGID
  46. WHERE
  47.     AKOPF.AUFTRAGSNUMMER IN (633168, 633104, 634503)
  48.    
  49. GROUP BY
  50.     AKOPF.AUFTRAGSNUMMER,
  51.     SH.BEZEICHNUNG,
  52.     SK.NAME1,
  53.     SWA.ISOWAEHRUNGSCODE,
  54. --    ATOUR.TOURNUMMER,
  55. --    SHI.BEZEICHNUNG,
  56.     SW.ISOWAEHRUNGSCODE;
  57. --    SKUN.NAME1;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement