Advertisement
Guest User

Untitled

a guest
Dec 15th, 2016
186
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.31 KB | None | 0 0
  1. DECLARE @dt_from    DATETIME        =   '2016-01-01';
  2. DECLARE @dt_to      DATETIME        =   '2016-02-01';
  3. DECLARE @acc        VARCHAR(256)    =   '1-AAA';
  4.  
  5.  
  6. SELECT
  7.      [Month_Name]   =   DATENAME(MONTH, [agg_data].[dt]) + SPACE(1) + CAST(DATEPART(YEAR, [agg_data].[dt]) AS VARCHAR(4))
  8. --  ,[Month]        =   [agg_data].[dt]
  9.     ,[Invoice_Amt]  =   [agg_data].[amount_inv]
  10.     ,[Payment_Amt]  =   [agg_data].[amount_pmt]
  11. FROM
  12.     (
  13.         SELECT
  14.               [dt]          =   ISNULL([i].[dt], [p].[dt])    
  15.              ,[amount_inv]  =   ISNULL([i].[amount], 0)
  16.              ,[amount_pmt]  =   ISNULL([p].[amount], 0)
  17.              ,[acc]         =   ISNULL([i].[acc], [p].[acc])
  18.         FROM
  19.             (
  20.                 SELECT
  21.                      [acc]      =   [account]      
  22.                     ,[dt]       =   DATEFROMPARTS(YEAR([trans_date]), MONTH([trans_date]), 1)
  23.                     ,[amount]   =   SUM([amount])
  24.                 FROM
  25.                     [invoice]
  26.                 GROUP BY
  27.                      DATEFROMPARTS(YEAR([trans_date]), MONTH([trans_date]), 1)
  28.                     ,[account]
  29.             )   AS  [i]
  30.         FULL JOIN
  31.             (
  32.                 SELECT
  33.                      [acc]      =   [account]      
  34.                     ,[dt]       =   DATEFROMPARTS(YEAR([payment_date]), MONTH([payment_date]), 1)
  35.                     ,[amount]   =   SUM([amount])
  36.                 FROM
  37.                     [payment]
  38.                 GROUP BY
  39.                      DATEFROMPARTS(YEAR([payment_date]), MONTH([payment_date]), 1)
  40.                     ,[account]
  41.             )   AS  [p]
  42.         ON
  43.                 [i].[acc]   =   [p].[acc]
  44.             AND [i].[dt]    =   [p].[dt]
  45.     )   AS  [agg_data]
  46. WHERE
  47.         [dt]    BETWEEN @dt_from AND @dt_to
  48.     AND [acc]   =       @acc
  49. ORDER BY
  50.      [dt] ASC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement