I am trying to create a projection query this is the sql query which works. I tried something like this
[PXProjection(typeof(
Select4<
PMBudget,
Where2<
Where<PMBudget.type, Equal<AccountType.income>>,
Or<
Where<PMBudget.type, Equal<AccountType.expense>,
And<ParentChildContractExt.usrIsChildProject, Equal<True>>>>>,
And<PMBudget.accountGroupID, In< Select<ProjectTransferRequestSetup,Where<ProjectTransferRequestSetup.transferAccountGroup, IsNotNull>>>>>>>>,
Aggregate<
GroupBy<PMBudget.projectID,
Sum<PMBudget.curyRevisedAmount,
Sum<PMBudget.curyAmount,
Sum<PMBudget.curyInvoicedAmount,
Sum<PMBudget.curyActualAmount,
Sum<PMBudget.curyInclTaxAmount,
Sum<PMBudget.curyTotalRetainedAmount,
Sum<PMBudget.curyAmountToInvoice,
Sum<PMBudget.curyChangeOrderAmount>>>>>>>>>>>))]
it doesnot work
SELECT
b.ProjectID,
c.ContractCD,
c.usrIsChildProject,
-- Aggregated Fields
SUM(b.CuryRevisedAmount) AS Total_CuryRevisedAmount,
SUM(b.CuryAmount) AS Total_CuryAmount,
SUM(b.CuryInvoicedAmount) AS Total_CuryInvoicedAmount,
SUM(b.CuryActualAmount) AS Total_CuryActualAmount,
SUM(b.CuryInclTaxAmount) AS Total_CuryInclTaxAmount,
SUM(b.CuryTotalRetainedAmount) AS Total_CuryTotalRetainedAmount,
SUM(b.CuryAmountToInvoice) AS Total_CuryAmountToInvoice,
SUM(b.CuryChangeOrderAmount) AS Total_CuryChangeOrderAmount
FROM PMBudget b
LEFT JOIN Contract c ON b.ProjectID = c.ContractID
WHERE c.ContractCD = 'SL-P-25-0017' -- Filter for specific contract
AND (
b.Type = 'I' -- Include all income budgets
OR (
b.Type = 'E'
AND c.usrIsChildProject = 1 -- Only include expenses for child projects
AND b.AccountGroupID IN (SELECT TransferAccountGroup FROM ProjectTransferRequestSetup) -- Filter by ProjectTransferRequestSetup.transferAccountGroup
)
)
GROUP BY
b.ProjectID,
c.ContractCD,
c.usrIsChildProject -- Grouping similar to projection
ORDER BY b.ProjectID;
