Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @dt_from DATETIME = '2016-01-01';
- DECLARE @dt_to DATETIME = '2016-02-01';
- DECLARE @acc VARCHAR(256) = '1-AAA';
- SELECT
- [Month_Name] = DATENAME(MONTH, [agg_data].[dt]) + SPACE(1) + CAST(DATEPART(YEAR, [agg_data].[dt]) AS VARCHAR(4))
- -- ,[Month] = [agg_data].[dt]
- ,[Invoice_Amt] = [agg_data].[amount_inv]
- ,[Payment_Amt] = [agg_data].[amount_pmt]
- FROM
- (
- SELECT
- [dt] = ISNULL([i].[dt], [p].[dt])
- ,[amount_inv] = ISNULL([i].[amount], 0)
- ,[amount_pmt] = ISNULL([p].[amount], 0)
- ,[acc] = ISNULL([i].[acc], [p].[acc])
- FROM
- (
- SELECT
- [acc] = [account]
- ,[dt] = DATEFROMPARTS(YEAR([trans_date]), MONTH([trans_date]), 1)
- ,[amount] = SUM([amount])
- FROM
- [invoice]
- GROUP BY
- DATEFROMPARTS(YEAR([trans_date]), MONTH([trans_date]), 1)
- ,[account]
- ) AS [i]
- FULL JOIN
- (
- SELECT
- [acc] = [account]
- ,[dt] = DATEFROMPARTS(YEAR([payment_date]), MONTH([payment_date]), 1)
- ,[amount] = SUM([amount])
- FROM
- [payment]
- GROUP BY
- DATEFROMPARTS(YEAR([payment_date]), MONTH([payment_date]), 1)
- ,[account]
- ) AS [p]
- ON
- [i].[acc] = [p].[acc]
- AND [i].[dt] = [p].[dt]
- ) AS [agg_data]
- WHERE
- [dt] BETWEEN @dt_from AND @dt_to
- AND [acc] = @acc
- ORDER BY
- [dt] ASC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement