Solved How to achieve Pivot (1 Viewer)

[atzdgreat]

OBJECTIVE:
Period Taken is Feb 2024
CY MTD Column is a total amount for Feb 2024 Period
CY YTD Column is a total amount from Jan 2024 to Feb 2024 Period
PY MTD Column is a total amount for Feb 2023 Period
PY YTD Column is a total amount from Jan 2023 to Feb 2023 Period
COHN314 and COHN315 are a pivot result with total amt for Feb 2024 Period

Ledger Account	CY MTD	CY YTD	PY MTD	PY YTD	COHN314	COHN315
1000001​	157,289.00	190,764.00	103,486.00	1,012,420.00	119,242.00	38,047.00

TABLE:

Ledger Acct	Cost Center	Amount	Period
1000001​	COHN314	15,425.00	1-Jan-24​
1000001​	COHN314	3,475.00	1-Jan-24​
1000001​	COHN315	6,000.00	1-Jan-24​
1000001​	COHN315	8,575.00	1-Jan-24​
1000001​	COHN314	23,457.00	1-Feb-24​
1000001​	COHN314	95,785.00	1-Feb-24​
1000001​	COHN315	36,589.00	1-Feb-24​
1000001​	COHN315	1,458.00	1-Feb-24​
1000001​	COHN314	123,456.00	1-Jan-23​
1000001​	COHN314	2,547.00	1-Jan-23​
1000001​	COHN315	687,456.00	1-Jan-23​
1000001​	COHN315	95,475.00	1-Jan-23​
1000001​	COHN314	21,567.00	1-Feb-23​
1000001​	COHN314	1,585.00	1-Feb-23​
1000001​	COHN315	15,756.00	1-Feb-23​
1000001​	COHN315	64,578.00	1-Feb-23​

CODE:

strSQL = "TRANSFORM IIF(SUM(t1.Amount) IS NULL, 0, SUM(t1.Amount)) AS TotalCost " & _
             "SELECT t1.LedgerAcct,
             "IIF(SUM(t2.CYMAmt) IS NULL, 0, SUM(t2.CYMAmt)) AS CYMAmt, IIF(SUM(t2.CYYTDAmt) IS NULL, 0, SUM(t2.CYYTDAmt)) AS CYYTDAmt, " & _
             "IIF(SUM(t2.PYMAmt) IS NULL, 0, SUM(t2.PYMAmt)) AS PYMAmt, IIF(SUM(t2.PYYTDAmt) IS NULL, 0, SUM(t2.PYYTDAmt)) AS PYYTDAmt FROM (([Main Table] t1 " & _
             "FROM ((("
            
    strSQL = strSQL & _
             "SELECT LedgerAcct, CostCenter, SUM(TotCost) AS CYMAmt FROM [Main Table] " & _
             "WHERE Period BETWEEN #02/01/24# AND #02/29/24# AND CostCenter IN ('COHN314','COHN315') " & _
             "GROUP BY LedgerAcct, CostCenter) tCYM "


    strSQL = strSQL & _
             "LEFT JOIN (SELECT LedgerAcct, CostCenter, SUM(TotCost) AS CYYTDAmt FROM [Main Table] " & _
             "WHERE Period BETWEEN #01/01/24# AND #02/29/24# AND CostCenter IN ('COHN314','COHN315') " & _
             "GROUP BY LedgerAcct, CostCenter) tCYYTD ON tCYM.LedgerAcct = tCYYTD.LedgerAcct AND tCYM.CostCenter = tCYYTD.CostCenter) "


    strSQL = strSQL & _
             "LEFT JOIN (SELECT LedgerAcct, CostCenter, SUM(TotCost) AS PYMAmt FROM [Main Table] " & _
             "WHERE Period BETWEEN #02/01/23# AND #02/28/23# AND CostCenter IN ('COHN314','COHN315') " & _
             "GROUP BY LedgerAcct, CostCenter) tPYM ON tCYM.LedgerAcct = tPYM.LedgerAcct AND tCYM.CostCenter = tPYM.CostCenter) "
    
    strSQL = strSQL & _
             "LEFT JOIN (SELECT LedgerAcct, CostCenter, SUM(TotCost) AS PYYTDAmt FROM [Main Table] " & _
             "WHERE Period BETWEEN #01/01/23# AND #02/28/23# AND CostCenter IN ('COHN314','COHN315') " & _
             "GROUP BY LedgerAcct, CostCenter) tPYYTD ON tCYM.LedgerAcct = tPYYTD.LedgerAcct AND tCYM.CostCenter = tPYYTD.CostCenter) t2 " & _
             "ON t1.LedgerAcct = t2.LedgerAcct AND t1.CostCenter = t2.CostCenter "
    
    strSQL = strSQL & _
             "WHERE t1.Period BETWEEN #02/01/24# AND #02/29/24# AND t1.CostCenter IN ('COHN314','COHN315') " & _
             "GROUP BY t1.LedgerAcct " & _
             "PIVOT t1.CostCenter"

 

[ebs17]

PARAMETERS
   parYear Long,
   parMonth Long
;
TRANSFORM
   SUM(Q.Amount) AS A
SELECT
   Q.LedgerAcct
FROM
   (
      SELECT
         LedgerAcct,
         Amount,
         "CYMTD" AS ColumnName
      FROM
         TableX
      WHERE
         Period >= DateSerial(parYear, parMonth, 1)
            AND
         Period < DateSerial(parYear, parMonth + 1, 1)
      UNION ALL
         SELECT
         LedgerAcct,
         Amount,
         "CYYTD" AS ColumnName
      FROM
         TableX
      WHERE
         Period >= DateSerial(parYear, 1, 1)
            AND
         Period < DateSerial(parYear, parMonth + 1, 1)
      UNION ALL
         SELECT
         LedgerAcct,
         Amount,
         "PYMTD" AS ColumnName
      FROM
         TableX
      WHERE
         Period >= DateSerial(parYear - 1, parMonth, 1)
            AND
         Period < DateSerial(parYear - 1, parMonth + 1, 1)
      UNION ALL
         SELECT
         LedgerAcct,
         Amount,
         "PYYTD" AS ColumnName
      FROM
         TableX
      WHERE
         Period >= DateSerial(parYear - 1, 1, 1)
            AND
         Period < DateSerial(parYear - 1, parMonth + 1, 1)
      UNION ALL
         SELECT
         LedgerAcct,
         Amount,
         CostCenter AS ColumnName
      FROM
         TableX
      WHERE
         Period >= DateSerial(parYear, parMonth, 1)
            AND
         Period < DateSerial(parYear, parMonth + 1, 1)
            AND
         CostCenter IN("COHN314", "COHN315")
   ) AS Q
GROUP BY
   Q.LedgerAcct
PIVOT
   Q.ColumnName IN("CYMTD", "CYYTD", "PYMTD", "PYYTD", "COHN314", "COHN315")

PIVOT just does some reshaping of rows into columns with some grouping and aggregation.
The necessary information such as the column name in conjunction with the value and row name must be created beforehand.

I have also added parameterization so that you can be flexible by entering the year and the month. The query can be used as a saved query, which is what I would recommend here.

 

[arnelgp]

you can also test Form1.