Sage Timberline - Job Cost Totals by transaction type

SageTimberline-JobCostTotalsByTransactionType.sql

WITH JobTransactionsSumTypes AS
(
SELECT 
      [Job],
      [Cost_Code],
      SUM(CASE WHEN [Transaction_Type] = 'AP cost' 		THEN [Amount] ELSE NULL END) AS APcostAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Approved est changes' THEN [Amount] ELSE NULL END) AS ApprovedEstChangesAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Aprvd cmmtt cst chng' THEN [Amount] ELSE NULL END) AS AprvdCmmttCstChngAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Aprvd schdl val chn' 	THEN [Amount] ELSE NULL END) AS AprvdSchdlValChnAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Cash receipt' 	THEN [Amount] ELSE NULL END) AS CashReceiptAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Committed cost' 	THEN [Amount] ELSE NULL END) AS CommittedCostAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Dollars paid' 	THEN [Amount] ELSE NULL END) AS DollarsPaidAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'General Ledger only' 	THEN [Amount] ELSE NULL END) AS GeneralLedgerOnlyAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'JC cost' 		THEN [Amount] ELSE NULL END) AS JCcostAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Misc worksheet 1' 	THEN [Amount] ELSE NULL END) AS MiscWorksheet1AmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Misc worksheet 2' 	THEN [Amount] ELSE NULL END) AS MiscWorksheet2AmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Misc worksheet 3' 	THEN [Amount] ELSE NULL END) AS MiscWorksheet3AmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Misc worksheet 4' 	THEN [Amount] ELSE NULL END) AS MiscWorksheet4AmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Original estimate' 	THEN [Amount] ELSE NULL END) AS OriginalEstimateAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Pending est changes' 	THEN [Amount] ELSE NULL END) AS PendingEstChangesAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Percent complete' 	THEN [Amount] ELSE NULL END) AS PercentCompleteAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Pndng cmmtt cst chng' THEN [Amount] ELSE NULL END) AS PndngCmmttCstChngAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Pndng schd val chg 2' THEN [Amount] ELSE NULL END) AS PndngSchdValChg2AmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Pndng schd val chg 3' THEN [Amount] ELSE NULL END) AS PndngSchdValChg3AmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Pndng schdl val chng' THEN [Amount] ELSE NULL END) AS PndngSchdlValChngAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Receivable Adjstment' THEN [Amount] ELSE NULL END) AS ReceivableAdjstmentAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Retention billed' 	THEN [Amount] ELSE NULL END) AS RetentionBilledAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Scheduled value' 	THEN [Amount] ELSE NULL END) AS ScheduledValueAmountSum,
      SUM(CASE WHEN [Transaction_Type] = 'Work Billed' 		THEN [Amount] ELSE NULL END) AS WorkBilledAmountSum
  FROM [ADCData_Doric].[dbo].[JCT_CURRENT__TRANSACTION]
GROUP BY
      [Job],
      [Cost_Code]
)
SELECT
	Job,
        Cost_Code,
	APcostAmountSum,
	ApprovedEstChangesAmountSum,
	AprvdCmmttCstChngAmountSum,
	AprvdSchdlValChnAmountSum,
	CashReceiptAmountSum,
	CommittedCostAmountSum,
	DollarsPaidAmountSum,
	GeneralLedgerOnlyAmountSum,
	JCcostAmountSum,
	MiscWorksheet1AmountSum,
	MiscWorksheet2AmountSum,
	MiscWorksheet3AmountSum,
	MiscWorksheet4AmountSum,
	OriginalEstimateAmountSum,
	PendingEstChangesAmountSum,
	PercentCompleteAmountSum,
	PndngCmmttCstChngAmountSum,
	PndngSchdValChg2AmountSum,
	PndngSchdValChg3AmountSum,
	PndngSchdlValChngAmountSum,
	ReceivableAdjstmentAmountSum,
	RetentionBilledAmountSum,
	ScheduledValueAmountSum,
	WorkBilledAmountSum,
	(ScheduledValueAmountSum) AS StartContractAmount,
	(AprvdSchdlValChnAmountSum) AS ApprovedVariations,
	(ScheduledValueAmountSum + AprvdSchdlValChnAmountSum) AS JTDApprovedContractAmount
	-- You Could Add additional Calculations here.
FROM
	[JobTransactionsSumTypes]