Needing some help with a crosstab/pivotquery for HSQL2.5 backend. In my research I only found a single example in the Northwind.odb of Access2Base.
Now I need to create a form/report listing Sales by Customer by Month in generic Pivot format.
What I have so far:
qryInvoiceStatus
Code: Select all
SELECT "tblCustomer"."customer_name" "Client", "tblJob"."jobid" "InvNumber", "tblJob"."Date" "InvDate", "qryBaseCalcsForInvoiceAccounts"."Payable", "tblJob"."terms" "Terms", "tblPmtmethod"."method" "Payment Method", "tblJob"."paidamount" "Payment", "tblJob"."paydate" "Paydate", "tblJob"."paid" "Paid", TO_CHAR( DATEADD( 'day', "terms", "Date" ), 'DD/MM/YYYY' ) "Due", DATEDIFF( 'dd', "paydate", DATEADD( 'day', "terms", "Date" ) ) "DPV", DATEDIFF( 'dd', DATEADD( 'day', "terms", "Date" ), TODAY( ) ) * - 1 "DCV", NVL2( "paydate", DATEDIFF( 'dd', "paydate", DATEADD( 'day', "terms", "Date" ) ), DATEDIFF( 'dd', DATEADD( 'day', "terms", "Date" ), TODAY( ) ) * - 1 ) "DCV_DPV", "tblSalesRep"."sales_rep" "SalesPerson", "qryBaseCalcsForInvoiceAccounts"."acttax", "paidamount" - "Payable" "Variance", "qryBaseCalcsForInvoiceAccounts"."TotalTaxInclusive", ( "TotalTaxInclusive" - "actdiscount" ) * "commission" "Commission", "TotalTaxInclusive" - "actdiscount" "CommBase", "tblJob"."cancelled", "tblTaxRates"."rate", "tblJob"."Job Number" "PurchaseOrder" FROM "tblJob", "tblPmtmethod", "tblCustomer", "qryBaseCalcsForInvoiceAccounts", "tblSalesRep", "tblTaxRates" WHERE "tblJob"."paymentmethod" = "tblPmtmethod"."pmid" AND "tblJob"."customer_id" = "tblCustomer"."customer_id" AND "tblJob"."jobid" = "qryBaseCalcsForInvoiceAccounts"."jobid" AND "tblSalesRep"."sales_rep" = "tblSalesRep"."sales_rep" AND "tblJob"."salesrep" = "tblSalesRep"."srid" AND "tblJob"."tax" = "tblTaxRates"."sid" AND "tblJob"."paid" = FALSE AND "tblJob"."cancelled" = FALSE ORDER BY "InvNumber" DESC, "InvDate" DESC
qryPivotSource
SELECT "Client", "InvDate", "Payable", "SalesPerson", "acttax" FROM "qryInvoiceStatus"
third attempt to create the crosstab as per Northwind example to be modified to display month rather then quarters
Code: Select all
SELECT
"Client",
SUM(
CASE "Quarter" WHEN '2020Q1' THEN "Data" ELSE 0 END
) As "2020Q1",
SUM(
CASE "Quarter" WHEN '2020Q2' THEN "Data" ELSE 0 END
) As "2020Q2",
SUM(
CASE "Quarter" WHEN '2020Q3' THEN "Data" ELSE 0 END
) As "2020Q3",
SUM(
CASE "Quarter" WHEN '2020Q4' THEN "Data" ELSE 0 END
) As "2020Q4",
SUM("Data") As "Total"
FROM
(
SELECT
"qryPivotSource"."Client" As "Client",
YEAR("InvDate") || 'Q' || QUARTER("InvDate") As "Quarter",
SUM(
"qryPivotSource"."Payable" AS "Data"
FROM
"qryPivotSource”,
WHERE
YEAR("qryPivotSource"."InvDate") = 2020
GROUP BY
"Client",
"Quarter")
GROUP BY
"Client",
ORDER BY
"Total"
Any ideas where I am wrong or whats missing ?
Thanks for your thoughts