Hi Experts.
I have created a query to act as a pivot table to sum all IGE1 activities by month for the previous 12 months from the last full month backwards as below...
I want to name the columns 1-12 in relation to month names via an SQL arguement. IE ...
"1" = ADD_MONTHS(LAST_DAY(NOW()),-13)
"2" = ADD_MONTHS(LAST_DAY(NOW()),-12)
Etc, etc
I have searched high and low for answer to this, as it seems a common thing for one to do, but I have not found an answer.
Could anyone advise how to replace the 'AS' text based syntax with an SQL arguement naming syntax?
Thanks.
SELECT
"ItemCode",
SUM("1") AS "1",
SUM("2") AS "2",
SUM("3") AS "3",
SUM("4") AS "4",
SUM("5") AS "5",
SUM("6") AS "6",
SUM("7") AS "7",
SUM("8") AS "8",
SUM("9") AS "9",
SUM("10") AS "10",
SUM("11") AS "11",
SUM("12") AS "12"
FROM
(SELECT
T1."ItemCode",
CASE WHEN MONTHS_BETWEEN(LAST_DAY(T0."DocDate"),LAST_DAY(NOW()))=11 THEN CAST(SUM(T1."Quantity") AS INTEGER) ELSE CAST(0 AS INTEGER) END AS "1",
CASE WHEN MONTHS_BETWEEN(LAST_DAY(T0."DocDate"),LAST_DAY(NOW()))=10 THEN CAST(SUM(T1."Quantity") AS INTEGER) ELSE CAST(0 AS INTEGER) END AS "2",
CASE WHEN MONTHS_BETWEEN(LAST_DAY(T0."DocDate"),LAST_DAY(NOW()))=9 THEN CAST(SUM(T1."Quantity") AS INTEGER) ELSE CAST(0 AS INTEGER) END AS "3",
CASE WHEN MONTHS_BETWEEN(LAST_DAY(T0."DocDate"),LAST_DAY(NOW()))=8 THEN CAST(SUM(T1."Quantity") AS INTEGER) ELSE CAST(0 AS INTEGER) END AS "4",
CASE WHEN MONTHS_BETWEEN(LAST_DAY(T0."DocDate"),LAST_DAY(NOW()))=7 THEN CAST(SUM(T1."Quantity") AS INTEGER) ELSE CAST(0 AS INTEGER) END AS "5",
CASE WHEN MONTHS_BETWEEN(LAST_DAY(T0."DocDate"),LAST_DAY(NOW()))=6 THEN CAST(SUM(T1."Quantity") AS INTEGER) ELSE CAST(0 AS INTEGER) END AS "6",
CASE WHEN MONTHS_BETWEEN(LAST_DAY(T0."DocDate"),LAST_DAY(NOW()))=5 THEN CAST(SUM(T1."Quantity") AS INTEGER) ELSE CAST(0 AS INTEGER) END AS "7",
CASE WHEN MONTHS_BETWEEN(LAST_DAY(T0."DocDate"),LAST_DAY(NOW()))=4 THEN CAST(SUM(T1."Quantity") AS INTEGER) ELSE CAST(0 AS INTEGER) END AS "8",
CASE WHEN MONTHS_BETWEEN(LAST_DAY(T0."DocDate"),LAST_DAY(NOW()))=3 THEN CAST(SUM(T1."Quantity") AS INTEGER) ELSE CAST(0 AS INTEGER) END AS "9",
CASE WHEN MONTHS_BETWEEN(LAST_DAY(T0."DocDate"),LAST_DAY(NOW()))=2 THEN CAST(SUM(T1."Quantity") AS INTEGER) ELSE CAST(0 AS INTEGER) END AS "10",
CASE WHEN MONTHS_BETWEEN(LAST_DAY(T0."DocDate"),LAST_DAY(NOW()))=1 THEN CAST(SUM(T1."Quantity") AS INTEGER) ELSE CAST(0 AS INTEGER) END AS "11",
CASE WHEN MONTHS_BETWEEN(LAST_DAY(T0."DocDate"),LAST_DAY(NOW()))=0 THEN CAST(SUM(T1."Quantity") AS INTEGER) ELSE CAST(0 AS INTEGER) END AS "12"
FROM
OIGE T0
LEFT OUTER JOIN IGE1 T1 ON T0."DocEntry" = T1."DocEntry"
LEFT OUTER JOIN OWOR T2 ON T1."BaseRef" = T2."DocNum"
WHERE T2."Type" <> 'D' AND T0."DocDate" <= LAST_DAY(ADD_MONTHS(LAST_DAY(NOW()),-1))
GROUP BY
T1."ItemCode",
T0."DocDate"
)
GROUP BY
"ItemCode"
ORDER BY
"ItemCode"
Bluesky
