The following query produces records when I run it iin the Sqlview. When I run the list page it however produces "No data yet" and shows no records. Any advice would be greatly appreciated.
Leon.
-- Subquery to calculate total VAT for all lines except "Vat Control"
WITH TotalVAT AS (
SELECT
SUM(AllRecords.VAT) AS TotalVAT
FROM AllRecords
LEFT OUTER JOIN AccountNos ON AllRecords.Account = AccountNos.`No`
WHERE AccountNos.`Desc` <> 'Vat Control'
AND ((AllRecords.`year` < :session_year) OR (AllRecords.`year` = :session_year AND AllRecords.period <= :session_period))
)
-- Main query to calculate balance, vat, and final balance including adjustment for "Vat Control"
SELECT
`Desc`,
`Type`,
balance,
CASE
WHEN `Desc` = 'Vat Control' THEN balance + COALESCE(tv.TotalVAT, 0)
ELSE balance
END AS `Final Balance`,
vat,
CASE
WHEN (CASE WHEN `Desc` = 'Vat Control' THEN balance + COALESCE(tv.TotalVAT, 0) ELSE balance END) >= 0
THEN (CASE WHEN `Desc` = 'Vat Control' THEN balance + COALESCE(tv.TotalVAT, 0) ELSE balance END)
ELSE 0
END AS Debit,
CASE
WHEN (CASE WHEN `Desc` = 'Vat Control' THEN balance + COALESCE(tv.TotalVAT, 0) ELSE balance END) < 0
THEN - (CASE WHEN `Desc` = 'Vat Control' THEN balance + COALESCE(tv.TotalVAT, 0) ELSE balance END)
ELSE 0
END AS Credit
FROM (
SELECT
CASE
WHEN AccountNos.BalanceSheet = 0 AND AccountNos.`Type` = 'Creditors' THEN 'Creditors'
WHEN AccountNos.BalanceSheet = 0 THEN 'Retained Earnings'
ELSE AccountNos.`Desc`
END AS `Desc`,
CASE
WHEN AccountNos.BalanceSheet = 0 AND AccountNos.`Type` = 'Creditors' THEN 'Creditors'
WHEN AccountNos.BalanceSheet = 0 THEN 'Retained Earnings'
ELSE AccountNos.`Type`
END AS `Type`,
SUM(
CASE
WHEN AccountNos.`Desc` = 'Vat Control' THEN AllRecords.Amount
ELSE AllRecords.Amount
END
) AS balance,
SUM(
CASE
WHEN AccountNos.`Desc` = 'Vat Control' THEN 0 -- Set to 0 for "Vat Control" line
ELSE AllRecords.VAT
END
) AS vat
FROM AllRecords
LEFT OUTER JOIN AccountNos ON AllRecords.Account = AccountNos.`No`
WHERE ((AllRecords.`year` < :session_year) OR (AllRecords.`year` = :session_year AND AllRecords.period <= :session_period))
GROUP BY
CASE
WHEN AccountNos.BalanceSheet = 0 AND AccountNos.`Type` = 'Creditors' THEN 'Creditors'
WHEN AccountNos.BalanceSheet = 0 THEN 'Retained Earnings'
ELSE AccountNos.`Desc`
END,
CASE
WHEN AccountNos.BalanceSheet = 0 AND AccountNos.`Type` = 'Creditors' THEN 'Creditors'
WHEN AccountNos.BalanceSheet = 0 THEN 'Retained Earnings'
ELSE AccountNos.`Type`
END
) AS subquery
CROSS JOIN TotalVAT tv -- Join with TotalVAT subquery to get the total VAT sum
ORDER BY
`Type`,
`Desc`;