September 24, 2014 at 3:52 pm
I have a Table with Financial Data in it and for Certain Accounts (the Key Field is actindx Column)there is no data for Certain Months.
I need a query to fill in the data for each Calendar_Month. This should look at the actindx column and Calendar_Month column if there is no data for a specific Month for that actindx I want it to copy all columns for that actindx and insert into a new row, but just puts Zero Dollars for ActivityDebit, ActivityCredit, and NetAmount.
I am still new enough to SQL that I have no idea how to start writing this Query. I have created a CalendarMonth_Lookup Table. I assume there is a way to outer Join the two and insert rows or use an "IF" ,"THEN" type of Statement and just manually add the Calendar Month data.
I Appreciate any and all Help.
I am including all the code to make the tables and Insert Data into them.
CODE TO CREATE GPBalances Table
CREATE TABLE [dbo].[GPBalances](
[GP_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
[FISCALYEAR] [smallint] NULL,
[ACCTPERIOD] [varchar](255) NULL,
[YEAR_MONTH_TEXT] [varchar](255) NULL,
[Calendar_Month] [date] NULL,
[actindx] [int] NULL,
[ENTITY] [varchar](6) NULL,
[NATURALACCT] [varchar](7) NULL,
[ACCOUNTDESCRIPTION] [varchar](55) NULL,
[CATEGORY] [varchar](55) NULL,
[POSTINGTYPE] [varchar](14) NULL,
[ACTIVITYDEBIT] [money] NULL,
[ACTIVITYCREDIT] [money] NULL,
[NETAMOUNT] [money] NULL
) ON [PRIMARY]
GO
CODE TO INSERT DATA INTO GPBalances Table
INSERT INTO [dbo].[GPBalances]
( FISCALYEAR ,
ACCTPERIOD ,
YEAR_MONTH_TEXT ,
Calendar_Month ,
actindx ,
ENTITY ,
NATURALACCT ,
ACCOUNTDESCRIPTION ,
CATEGORY ,
POSTINGTYPE ,
ACTIVITYDEBIT ,
ACTIVITYCREDIT ,
NETAMOUNT
)
(SELECT 2014, '201402','2014/02','02-01-2014',2, 1001,11030,'Tax Payments Bank Account','Cash','Balance Sheet',22229.83 , 23739.32 , -1509.49 Union ALL
SELECT 2014, '201403','2014/03','03-01-2014',2, 1001,11030,'Tax Payments Bank Account','Cash','Balance Sheet',155321.37 , 155163.96 , 157.41 Union ALL
SELECT 2014, '201404','2014/04','04-01-2014',2, 1001,11030,'Tax Payments Bank Account','Cash','Balance Sheet',164767.42 , 224682.22 , -59914.8 Union ALL
SELECT 2014, '201406','2014/06','06-01-2014',2, 1001,11030,'Tax Payments Bank Account','Cash','Balance Sheet',71331.38 , 71331.38 , 0 Union ALL
SELECT 2014, '201407','2014/07','07-01-2014',2, 1001,11030,'Tax Payments Bank Account','Cash','Balance Sheet',51546.79 , 51746.8 , -200.01 Union ALL
SELECT 2014, '201401','2014/01','01-01-2014',4, 1001,11061,'Chase Positive Payroll','Cash','Balance Sheet',26331216.98 , 26173846.32 , 157370.66 Union ALL
SELECT 2014, '201402','2014/02','02-01-2014',4, 1001,11061,'Chase Positive Payroll','Cash','Balance Sheet',9112344.1 , 9075366.43 , 36977.67 Union ALL
SELECT 2014, '201403','2014/03','03-01-2014',4, 1001,11061,'Chase Positive Payroll','Cash','Balance Sheet',8701450.4 , 8553677.47 , 147772.93 Union ALL
SELECT 2014, '201404','2014/04','04-01-2014',4, 1001,11061,'Chase Positive Payroll','Cash','Balance Sheet',8813026.26 , 8644568.15 , 168458.11 Union ALL
SELECT 2014, '201405','2014/05','05-01-2014',4, 1001,11061,'Chase Positive Payroll','Cash','Balance Sheet',8475209.25 , 8416390.29 , 58818.96 Union ALL
SELECT 2014, '201401','2014/01','01-01-2014',6, 1001,11105,'A/P Disbursements Chase Bank','Cash','Balance Sheet',64394710.97 , 62061198.98 , 2333511.99 Union ALL
SELECT 2014, '201402','2014/02','02-01-2014',6, 1001,11105,'A/P Disbursements Chase Bank','Cash','Balance Sheet',20508291.97 , 21483616.73 , -975324.76 Union ALL
SELECT 2014, '201403','2014/03','03-01-2014',6, 1001,11105,'A/P Disbursements Chase Bank','Cash','Balance Sheet',17723639.15 , 14253848.29 , 3469790.86 Union ALL
SELECT 2014, '201404','2014/04','04-01-2014',6, 1001,11105,'A/P Disbursements Chase Bank','Cash','Balance Sheet',17085819.94 , 22577943.99 , -5492124.052 Union ALL
SELECT 2014, '201405','2014/05','05-01-2014',6, 1001,11105,'A/P Disbursements Chase Bank','Cash','Balance Sheet',15437400.23 , 13093318.57 , 2344081.66 Union ALL
SELECT 2014, '201406','2014/06','06-01-2014',6, 1001,11105,'A/P Disbursements Chase Bank','Cash','Balance Sheet',18055092.8 , 14016561.38 , 4038531.42 Union ALL
SELECT 2014, '201407','2014/07','07-01-2014',6, 1001,11105,'A/P Disbursements Chase Bank','Cash','Balance Sheet',15906168.54 , 20007024.14 , -4100855.6 Union ALL
SELECT 2014, '201408','2014/08','08-01-2014',6, 1001,11105,'A/P Disbursements Chase Bank','Cash','Balance Sheet',33440.58 , 4685186.49 , -4651745.91 Union ALL
SELECT 2014, '201401','2014/01','01-01-2014',7, 1001,11106,'A/P Refunds','Cash','Balance Sheet',1435967.79 , 1313116.91 , 122850.88 Union ALL
SELECT 2014, '201403','2014/03','03-01-2014',7, 1001,11106,'A/P Refunds','Cash','Balance Sheet',836659.35 , 937280.81 , -100621.46 Union ALL
SELECT 2014, '201404','2014/04','04-01-2014',7, 1001,11106,'A/P Refunds','Cash','Balance Sheet',706500.31 , 404450.42 , 302049.89 Union ALL
SELECT 2014, '201406','2014/06','06-01-2014',7, 1001,11106,'A/P Refunds','Cash','Balance Sheet',787230.6 , 708499.31 , 78731.29 Union ALL
SELECT 2014, '201407','2014/07','07-01-2014',7, 1001,11106,'A/P Refunds','Cash','Balance Sheet',781300.78 , 523497.02 , 257803.76 Union ALL
SELECT 2014, '201408','2014/08','08-01-2014',7, 1001,11106,'A/P Refunds','Cash','Balance Sheet',23672.51 , 419265.75 , -395593.24
)
CODE TO CREATE Lookup Table
CREATE TABLE [dbo].[CalendarMonth_Lookup](
[Calendar_Month] [date] NULL
) ON [PRIMARY]
GO
CODE TO INSERT DATA INTO Lookup Table
INSERT INTO dbo.CalendarMonth_Lookup
( Calendar_Month )
( SELECT '12-01-2013' Union All
SELECT '01-01-2014' Union All
SELECT '02-01-2014' Union All
SELECT '03-01-2014' Union All
SELECT '04-01-2014' Union All
SELECT '05-01-2014' Union All
SELECT '06-01-2014' Union All
SELECT '07-01-2014' Union All
SELECT '08-01-2014'
)
September 24, 2014 at 4:39 pm
I need a query to fill in the data for each Calendar_Month. This should look at the actindx column and Calendar_Month column if there is no data for a specific Month for that actindx I want it to copy all columns for that actindx and insert into a new row, but just puts Zero Dollars for ActivityDebit, ActivityCredit, and NetAmount.
Why not just outer join CalendarMonth to GP_Balances? Then you could show all the dates between @StartDate and @EndDate and the matching records from GP_Balances. Then if you use COALESCE([SomeValue],0), you could add those because the nulls would become zero.
September 24, 2014 at 11:45 pm
Here is a simple solution using CTE to build the full month-account set first and then join the balance table to it.
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
;WITH ACCOUNT_LIST AS
(
SELECT DISTINCT
GP.NATURALACCT
FROM dbo.GPBalances GP
)
,ACCOUNT_CALENDAR AS
(
SELECT
AL.NATURALACCT
,CL.Calendar_Month
FROM ACCOUNT_LIST AL
CROSS APPLY dbo.CalendarMonth_Lookup CL
)
,ACCOUNT_FILL_IN AS
(
SELECT DISTINCT
GB.actindx
,GB.ENTITY
,GB.NATURALACCT
,GB.ACCOUNTDESCRIPTION
,GB.CATEGORY
,GB.POSTINGTYPE
,CONVERT(MONEY,0,0) AS ACTIVITYDEBIT
,CONVERT(MONEY,0,0) AS ACTIVITYCREDIT
,CONVERT(MONEY,0,0) AS NETAMOUNT
FROM dbo.GPBalances GB
)
/* Piecing it all together */
SELECT
ISNULL(GB.GP_Balance_ID, -1) AS GP_Balance_ID
,ISNULL(GB.FISCALYEAR,CONVERT(SMALLINT,YEAR(AC.Calendar_Month),0)) AS FISCALYEAR
,ISNULL(GB.ACCTPERIOD,(YEAR(AC.Calendar_Month) * 100 ) + MONTH(AC.Calendar_Month)) AS ACCTPERIOD
,ISNULL(GB.YEAR_MONTH_TEXT,REPLACE(CONVERT(VARCHAR(7),AC.Calendar_Month,121),'-','/')) AS YEAR_MONTH_TEXT
,AC.Calendar_Month
,ISNULL(GB.actindx , AFI.actindx ) AS actindx
,ISNULL(GB.ENTITY , AFI.ENTITY ) AS ENTITY
,AC.NATURALACCT
,ISNULL(GB.ACCOUNTDESCRIPTION , AFI.ACCOUNTDESCRIPTION ) AS ACCOUNTDESCRIPTION
,ISNULL(GB.CATEGORY , AFI.CATEGORY ) AS CATEGORY
,ISNULL(GB.POSTINGTYPE , AFI.POSTINGTYPE ) AS POSTINGTYPE
,ISNULL(GB.ACTIVITYDEBIT , AFI.ACTIVITYDEBIT ) AS ACTIVITYDEBIT
,ISNULL(GB.ACTIVITYCREDIT , AFI.ACTIVITYCREDIT ) AS ACTIVITYCREDIT
,ISNULL(GB.NETAMOUNT , AFI.NETAMOUNT ) AS NETAMOUNT
FROM ACCOUNT_CALENDAR AC
LEFT OUTER JOIN dbo.GPBalances GB
ON AC.Calendar_Month = GB.Calendar_Month
AND AC.NATURALACCT = GB.NATURALACCT
OUTER APPLY ACCOUNT_FILL_IN AFI
WHERE AC.NATURALACCT = AFI.NATURALACCT;
Results
GP_Balance_ID FISCALYEAR ACCTPERIOD YEAR_MONTH_TEXT Calendar_Month actindx ENTITY NATURALACCT ACCOUNTDESCRIPTION CATEGORY POSTINGTYPE ACTIVITYDEBIT ACTIVITYCREDIT NETAMOUNT
------------- ---------- ----------- ---------------- -------------- ----------- ------ ----------- ------------------------------ ---------- -------------- --------------------- --------------------- ---------------------
-1 2013 201312 2013/12 2013-12-01 2 1001 11030 Tax Payments Bank Account Cash Balance Sheet 0.00 0.00 0.00
-1 2014 201401 2014/01 2014-01-01 2 1001 11030 Tax Payments Bank Account Cash Balance Sheet 0.00 0.00 0.00
1 2014 201402 2014/02 2014-02-01 2 1001 11030 Tax Payments Bank Account Cash Balance Sheet 22229.83 23739.32 -1509.49
2 2014 201403 2014/03 2014-03-01 2 1001 11030 Tax Payments Bank Account Cash Balance Sheet 155321.37 155163.96 157.41
3 2014 201404 2014/04 2014-04-01 2 1001 11030 Tax Payments Bank Account Cash Balance Sheet 164767.42 224682.22 -59914.80
-1 2014 201405 2014/05 2014-05-01 2 1001 11030 Tax Payments Bank Account Cash Balance Sheet 0.00 0.00 0.00
4 2014 201406 2014/06 2014-06-01 2 1001 11030 Tax Payments Bank Account Cash Balance Sheet 71331.38 71331.38 0.00
5 2014 201407 2014/07 2014-07-01 2 1001 11030 Tax Payments Bank Account Cash Balance Sheet 51546.79 51746.80 -200.01
-1 2014 201408 2014/08 2014-08-01 2 1001 11030 Tax Payments Bank Account Cash Balance Sheet 0.00 0.00 0.00
-1 2013 201312 2013/12 2013-12-01 4 1001 11061 Chase Positive Payroll Cash Balance Sheet 0.00 0.00 0.00
6 2014 201401 2014/01 2014-01-01 4 1001 11061 Chase Positive Payroll Cash Balance Sheet 26331216.98 26173846.32 157370.66
7 2014 201402 2014/02 2014-02-01 4 1001 11061 Chase Positive Payroll Cash Balance Sheet 9112344.10 9075366.43 36977.67
8 2014 201403 2014/03 2014-03-01 4 1001 11061 Chase Positive Payroll Cash Balance Sheet 8701450.40 8553677.47 147772.93
9 2014 201404 2014/04 2014-04-01 4 1001 11061 Chase Positive Payroll Cash Balance Sheet 8813026.26 8644568.15 168458.11
10 2014 201405 2014/05 2014-05-01 4 1001 11061 Chase Positive Payroll Cash Balance Sheet 8475209.25 8416390.29 58818.96
-1 2014 201406 2014/06 2014-06-01 4 1001 11061 Chase Positive Payroll Cash Balance Sheet 0.00 0.00 0.00
-1 2014 201407 2014/07 2014-07-01 4 1001 11061 Chase Positive Payroll Cash Balance Sheet 0.00 0.00 0.00
-1 2014 201408 2014/08 2014-08-01 4 1001 11061 Chase Positive Payroll Cash Balance Sheet 0.00 0.00 0.00
-1 2013 201312 2013/12 2013-12-01 6 1001 11105 A/P Disbursements Chase Bank Cash Balance Sheet 0.00 0.00 0.00
11 2014 201401 2014/01 2014-01-01 6 1001 11105 A/P Disbursements Chase Bank Cash Balance Sheet 64394710.97 62061198.98 2333511.99
12 2014 201402 2014/02 2014-02-01 6 1001 11105 A/P Disbursements Chase Bank Cash Balance Sheet 20508291.97 21483616.73 -975324.76
13 2014 201403 2014/03 2014-03-01 6 1001 11105 A/P Disbursements Chase Bank Cash Balance Sheet 17723639.15 14253848.29 3469790.86
14 2014 201404 2014/04 2014-04-01 6 1001 11105 A/P Disbursements Chase Bank Cash Balance Sheet 17085819.94 22577943.99 -5492124.052
15 2014 201405 2014/05 2014-05-01 6 1001 11105 A/P Disbursements Chase Bank Cash Balance Sheet 15437400.23 13093318.57 2344081.66
16 2014 201406 2014/06 2014-06-01 6 1001 11105 A/P Disbursements Chase Bank Cash Balance Sheet 18055092.80 14016561.38 4038531.42
17 2014 201407 2014/07 2014-07-01 6 1001 11105 A/P Disbursements Chase Bank Cash Balance Sheet 15906168.54 20007024.14 -4100855.60
18 2014 201408 2014/08 2014-08-01 6 1001 11105 A/P Disbursements Chase Bank Cash Balance Sheet 33440.58 4685186.49 -4651745.91
-1 2013 201312 2013/12 2013-12-01 7 1001 11106 A/P Refunds Cash Balance Sheet 0.00 0.00 0.00
19 2014 201401 2014/01 2014-01-01 7 1001 11106 A/P Refunds Cash Balance Sheet 1435967.79 1313116.91 122850.88
-1 2014 201402 2014/02 2014-02-01 7 1001 11106 A/P Refunds Cash Balance Sheet 0.00 0.00 0.00
20 2014 201403 2014/03 2014-03-01 7 1001 11106 A/P Refunds Cash Balance Sheet 836659.35 937280.81 -100621.46
21 2014 201404 2014/04 2014-04-01 7 1001 11106 A/P Refunds Cash Balance Sheet 706500.31 404450.42 302049.89
-1 2014 201405 2014/05 2014-05-01 7 1001 11106 A/P Refunds Cash Balance Sheet 0.00 0.00 0.00
22 2014 201406 2014/06 2014-06-01 7 1001 11106 A/P Refunds Cash Balance Sheet 787230.60 708499.31 78731.29
23 2014 201407 2014/07 2014-07-01 7 1001 11106 A/P Refunds Cash Balance Sheet 781300.78 523497.02 257803.76
24 2014 201408 2014/08 2014-08-01 7 1001 11106 A/P Refunds Cash Balance Sheet 23672.51 419265.75 -395593.24
September 25, 2014 at 9:49 am
Thanks I will try this today and get back to you. I will then mark this as solution.
This did work SO I marked it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply