Adding missing data and reusing data

Transact-SQL
1

I have a store proc that is creating a Monthly Income Statement

Some of the GL accounts do not have data for each month so this is causing a problem with my closing cost not showing the missing periods.

The table COMPANIES_PERIODS contains all my existing Accounting Periods

The table GLTS_ACCOUNTS contains my transactions

The below store proc is almost working buy my problem is that I need the Account_ID to match up with the missing Period for that account.

How can I get this to work.

;WITH MissingMonths
AS
(
SELECT Period_Nbr
FROM GL_COMPANIES_PERIODS
GROUP BY Period_Nbr
),

Glt_Transactions
AS
(
select g.Account_ID, g.Period_Nbr, g.Debit_Amt, g.Credit_Amt
from GLTS_ACCOUNTS as g
where g.Account_ID = '01-05-000-11125-00-00'
)

SELECT MM.Period_Nbr, Q.Account_ID, Q.Debit_Amt, Q.Credit_Amt
FROM MissingMonths MM
LEFT JOIN Glt_Transactions Q ON MM.Period_Nbr = Q.Period_Nbr

Data Results

Period Account Amount
15-09 01-05-000-11125-00-00 0.11 NULL

I need for the Account_ID 01-05-000-11125-00-00 to repeat for all transaction missing periods

The periods with NULL had no transactions for that period.

15-10 NULL NULL NULL

15-11 01-05-000-11125-00-00 0.11 NULL

15-12 01-05-000-11125-00-00 0.11 NULL

15-12 01-05-000-11125-00-00 0.64 NULL

15-13 NULL NULL NULL

15-14 NULL NULL NULL

2 
DECLARE @tv_GL_COMPANIES_PERIODS AS TABLE
( 
    Period_Nbr CHAR(5) NOT NULL PRIMARY KEY CLUSTERED
);
DECLARE @tv_GLTS_ACCOUNTS AS TABLE
(
    Account_ID  VARCHAR(50) NOT NULL
   ,Period_Nbr CHAR(5) NOT NULL
   ,Debit_Amt  DECIMAL(18,2) NULL
   ,Credit_Amt DECIMAL(18,2) NULL
);

INSERT INTO @tv_GL_COMPANIES_PERIODS(Period_Nbr)
VALUES ('15-09'),('15-10'),('15-11'),('15-12'),('15-13'),('15-14');

INSERT INTO @tv_GLTS_ACCOUNTS(Account_ID,Period_Nbr,Debit_Amt,Credit_Amt)
VALUES
     ('01-05-000-11125-00-00','15-09',0.11,NULL)
    ,('01-05-000-11125-00-00','15-11',0.11,NULL)
    ,('01-05-000-11125-00-00','15-12',0.11,NULL)
    ,('01-05-000-11125-00-00','15-12',0.64,NULL);

;WITH MissingMonths
AS
(
SELECT Period_Nbr
FROM @tv_GL_COMPANIES_PERIODS
GROUP BY Period_Nbr
),

Glt_Transactions
AS
(
select g.Account_ID, g.Period_Nbr
    , sum(g.Debit_Amt) AS Debit_Amt, sum(g.Credit_Amt) AS Credit_Amt
from @tv_GLTS_ACCOUNTS as g
where g.Account_ID = '01-05-000-11125-00-00'
group by g.Account_ID, g.Period_Nbr
)
--ACCOUNTS AND PERIODS
,ACC_AND_PER AS
(
    SELECT 
        MM.Period_Nbr
        ,ACC.Account_ID
    FROM    
        MissingMonths AS MM
        CROSS JOIN 
        (SELECT Account_ID FROM Glt_Transactions GROUP BY Account_ID) AS ACC
)
/* TO SEE THE OUTPUT OF ACC_AND_PER, RUN THE NEXT LINE
SELECT * FROM ACC_AND_PER
*/

SELECT 
    AP.Period_Nbr
    , ISNULL(Q.Account_ID,AP.Account_ID) AS Account_ID
    , Q.Debit_Amt
    , Q.Credit_Amt
FROM 
    ACC_AND_PER AS AP
    LEFT JOIN Glt_Transactions Q 
        ON AP.Period_Nbr = Q.Period_Nbr
        AND AP.Account_ID = Q.Account_ID

The output for this:

Period_Nbr Account_ID                Debit_Amt                               Credit_Amt
15-09      01-05-000-11125-00-00     0.11                                    NULL
15-10      01-05-000-11125-00-00     NULL                                    NULL
15-11      01-05-000-11125-00-00     0.11                                    NULL
15-12      01-05-000-11125-00-00     0.75                                    NULL
15-13      01-05-000-11125-00-00     NULL                                    NULL
15-14      01-05-000-11125-00-00     NULL                                    NULL
1 Like
3

Wow!

Thanks Stepson.

This works GREAT!!!!

4

My pleasure!