Untitled

DECLARE @TargetDate date = CAST(DATEADD(day, -1, GETDATE()) AS date);

WITH PayAgg AS (
    SELECT 
		p.transaction_id,
        SUM(CASE WHEN p.payment_id = 2 THEN p.amount ELSE 0 END) AS BancomatAmt,
        SUM(CASE WHEN p.payment_id = 1 THEN p.amount ELSE 0 END) AS ContantiAmt
    FROM dbo.transactions t
    JOIN dbo.trans_payments p 
		ON p.transaction_id = t.id
    WHERE t.fiscal_bill IS NOT NULL
		AND CONVERT(date, t.trans_date) = @TargetDate
    GROUP BY p.transaction_id
),
VATAgg AS (
    SELECT
		v.transaction_id,
        SUM(CASE WHEN v.vat_percent = 10 THEN v.gross_amount ELSE 0 END) AS IVA3Amt
    FROM dbo.transactions t
    JOIN dbo.trans_vats v 
		ON v.transaction_id = t.id
    WHERE t.fiscal_bill IS NOT NULL
		AND CONVERT(date, t.trans_date) = @TargetDate
    GROUP BY v.transaction_id
)

SELECT
    '@NewCode'                                          AS [Code],
    '@NewCode'                                          AS [Name],
    566594                                              AS [U_Filiale],
    CONVERT(char(8), @TargetDate, 112)					AS [U_Data],
    CAST(SUM(ISNULL(v.IVA3Amt,     0.0)) AS decimal(18,2)) AS [U_IVA3],
    CAST(SUM(ISNULL(p.BancomatAmt, 0.0)) AS decimal(18,2)) AS [U_Bancomat],
    CAST(SUM(ISNULL(p.ContantiAmt, 0.0)) AS decimal(18,2)) AS [U_Contanti]
FROM dbo.transactions t
LEFT JOIN PayAgg p ON p.transaction_id = t.id
LEFT JOIN VATAgg v ON v.transaction_id = t.id
WHERE t.fiscal_bill IS NOT NULL
  AND CONVERT(date, t.trans_date) = @TargetDate;