August 1, 2014 at 1:42 pm
I have a table with Million plus records. Thanks to the help of the Running Totals article and some help on the forums I have been able to calculate the Trial_Balance for all months.
Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I thought I could just do a self Join but this is not working. Please Help.
UPDATE dbo.TrialBalance
SET Beginning_Balance_Debit = B.Trial_Balance_Debit
FROM (SELECT DATEADD(month, -1, Calendar_Month) AS PrevCalMonth,Trial_Balance_Debit
FROM dbo.TrialBalance) AS B INNER JOIN dbo.TrialBalance A
ON b.PrevCalMonth=A.Calendar_Month
I am providing all the code to create table and insert data below.
/******Code to Create Table***************************************/
CREATE TABLE #TrialBalance(
[Trial_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
[FISCALYEAR] [smallint] NULL,
[Calendar_Month] [date] NULL,
[actindx] [int] NULL,
[CATEGORY] [varchar](55) NULL,
[POSTINGTYPE] [varchar](14) NULL,
[ACTIVITYDEBIT] [money] NULL,
[ACTIVITYCREDIT] [money] NULL,
[NETAMOUNT] [money] NULL,
[Trial_Balance_Debit] [money] NULL,
[Trial_Balance_Credit] [money] NULL,
[Trial_Balance_Net] [money] NULL,
[Beginning_Balance_Debit] [money] NULL,
[Beginning_Balance_Credit] [money] NULL,
[Beginning_Balance_Net] [money] NULL,
CONSTRAINT [PK_B_Balance] PRIMARY KEY NONCLUSTERED
(
[Trial_Balance_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/************Code to Insert Data***********************************/
INSERT INTO #TrialBalance
( FISCALYEAR ,
Calendar_Month ,
actindx ,
CATEGORY ,
POSTINGTYPE ,
ACTIVITYDEBIT ,
ACTIVITYCREDIT ,
NETAMOUNT ,
Trial_Balance_Debit ,
Trial_Balance_Credit ,
Trial_Balance_Net
)
(
SELECT 2013,'12/1/2013',6,'cash' ,'Balance Sheet ',1,9365247.77,-9365247.77,1,9365247.77,-9365247.77 Union ALL
SELECT 2014,'1/1/2014',6,'cash' ,'Balance Sheet ',64394710.97,62061198.98,2333511.99,64394710.97,71426446.75,-7031735.78 Union ALL
SELECT 2014,'2/1/2014',6,'cash' ,'Balance Sheet ',20508291.97,21483616.73,-975324.76,84903002.94,92910063.48,-8007060.54 Union ALL
SELECT 2014,'3/1/2014',6,'cash' ,'Balance Sheet ',17723639.15,14253848.29,3469790.86,102626642.1,107163911.8,-4537269.68 Union ALL
SELECT 2014,'4/1/2014',6,'cash' ,'Balance Sheet ',17085819.94,22577943.99,-5492124.052,119712462,129741855.8,-10029393.73 Union ALL
SELECT 2014,'5/1/2014',6,'cash' ,'Balance Sheet ',15437400.23,13093318.57,2344081.66,135149862.3,142835174.3,-7685312.072 Union ALL
SELECT 2014,'6/1/2014',6,'cash' ,'Balance Sheet ',149700.58,14015646.38,-13865945.8,135299562.8,156850820.7,-21551257.87 Union ALL
SELECT 2014,'7/1/2014',6,'cash' ,'Balance Sheet ',6367.92,3407346.12,-3400978.2,135305930.8,160258166.8,-24952236.07 Union ALL
SELECT 2013,'12/1/2013',7,'cash' ,'Balance Sheet ',1,949796.56,-949796.56,1,949796.56,-949796.56 Union ALL
SELECT 2014,'1/1/2014',7,'cash' ,'Balance Sheet ',1435967.79,1313116.91,122850.88,1435967.79,2262913.47,-826945.68 Union ALL
SELECT 2014,'2/1/2014',7,'cash' ,'Balance Sheet ',466811,690122.53,-223311.53,1902778.79,2953036,-1050257.21 Union ALL
SELECT 2014,'3/1/2014',7,'cash' ,'Balance Sheet ',836659.35,937280.81,-100621.46,2739438.14,3890316.81,-1150878.67 Union ALL
SELECT 2014,'4/1/2014',7,'cash' ,'Balance Sheet ',706500.31,404450.42,302049.89,3445938.45,4294767.23,-848828.78 Union ALL
SELECT 2014,'5/1/2014',7,'cash' ,'Balance Sheet ',773404.99,1090985.83,-317580.84,4219343.44,5385753.06,-1166409.62 Union ALL
SELECT 2014,'6/1/2014',7,'cash' ,'Balance Sheet ',51050.32,708499.31,-657448.99,4270393.76,6094252.37,-1823858.61 Union ALL
SELECT 2014,'7/1/2014',7,'cash' ,'Balance Sheet ',3280.26,150984.86,-147704.6,4273674.02,6245237.23,-1971563.21 Union ALL
SELECT 2013,'12/1/2013',5,'cash' ,'Balance Sheet ',2423163.64,1,2423163.64,2423163.64,1,2423163.64 Union ALL
SELECT 2013,'12/1/2013',435473,'Accounts Payable ' ,'Balance Sheet ',1357956.78,1,1357956.78,1357956.78,1,1357956.78 Union ALL
SELECT 2013,'12/1/2013',435474,'Accounts Payable ' ,'Balance Sheet ',1237681.83,1,1237681.83,2595638.61,1,2595638.61 Union ALL
SELECT 2013,'12/1/2013',435475,'Accounts Payable ' ,'Balance Sheet ',291629.5,1,291629.5,2887268.11,1,2887268.11 Union ALL
SELECT 2013,'12/1/2013',435476,'Accounts Payable ' ,'Balance Sheet ',338045.88,1,338045.88,3225313.99,1,3225313.99 Union ALL
SELECT 2013,'12/1/2013',435478,'Accounts Payable ' ,'Balance Sheet ',416254.06,1,416254.06,3641568.05,1,3641568.05 Union ALL
SELECT 2013,'12/1/2013',435479,'Accounts Payable ' ,'Balance Sheet ',214027.43,1,214027.43,3855595.48,1,3855595.48 Union ALL
SELECT 2013,'12/1/2013',435480,'Accounts Payable ' ,'Balance Sheet ',117815.51,1,117815.51,3973410.99,1,3973410.99 Union ALL
SELECT 2013,'12/1/2013',435481,'Accounts Payable ' ,'Balance Sheet ',3671.28,1,3671.28,3977082.27,1,3977082.27 Union ALL
SELECT 2013,'12/1/2013',435482,'Accounts Payable ' ,'Balance Sheet ',84777.46,1,84777.46,4061859.73,1,4061859.73 Union ALL
SELECT 2013,'12/1/2013',435483,'Accounts Payable ' ,'Balance Sheet ',52089,1,52089,4113948.73,1,4113948.73 Union ALL
SELECT 2013,'12/1/2013',435484,'Accounts Payable ' ,'Balance Sheet ',2691.89,1,2691.89,4116640.62,1,4116640.62 Union ALL
SELECT 2013,'12/1/2013',435485,'Accounts Payable ' ,'Balance Sheet ',34701.89,1,34701.89,4151342.51,1,4151342.51 Union ALL
SELECT 2013,'12/1/2013',435486,'Accounts Payable ' ,'Balance Sheet ',83483.01,1,83483.01,4234825.52,1,4234825.52 Union ALL
SELECT 2013,'12/1/2013',435487,'Accounts Payable ' ,'Balance Sheet ',146814.65,1,146814.65,4381640.17,1,4381640.17 Union ALL
SELECT 2013,'12/1/2013',435488,'Accounts Payable ' ,'Balance Sheet ',4419878.45,1,4419878.45,8801518.62,1,8801518.62 Union ALL
SELECT 2013,'12/1/2013',435490,'Accounts Payable ' ,'Balance Sheet ',140274.59,1,140274.59,8941793.21,1,8941793.21 Union ALL
SELECT 2013,'12/1/2013',435491,'Accounts Payable ' ,'Balance Sheet ',398.79,1,398.79,8942192,1,8942192 Union ALL
SELECT 2013,'12/1/2013',435492,'Accounts Payable ' ,'Balance Sheet ',565937.73,1,565937.73,9508129.73,1,9508129.73 Union ALL
SELECT 2013,'12/1/2013',435493,'Accounts Payable ' ,'Balance Sheet ',75775.29,1,75775.29,9583905.02,1,9583905.02 Union ALL
SELECT 2013,'12/1/2013',435494,'Accounts Payable ' ,'Balance Sheet ',550289.71,1,550289.71,10134194.73,1,10134194.73 Union ALL
SELECT 2013,'12/1/2013',435495,'Accounts Payable ' ,'Balance Sheet ',55409.82,1,55409.82,10189604.55,1,10189604.55 Union ALL
SELECT 2013,'12/1/2013',435496,'Accounts Payable ' ,'Balance Sheet ',702280.71,1,702280.71,10891885.26,1,10891885.26 Union ALL
SELECT 2013,'12/1/2013',435497,'Accounts Payable ' ,'Balance Sheet ',110839.54,1,110839.54,11002724.8,1,11002724.8 Union ALL
SELECT 2013,'12/1/2013',435498,'Accounts Payable ' ,'Balance Sheet ',411883.27,1,411883.27,11414608.07,1,11414608.07 Union ALL
SELECT 2013,'12/1/2013',435499,'Accounts Payable ' ,'Balance Sheet ',14853.5,1,14853.5,11429461.57,1,11429461.57 Union ALL
SELECT 2013,'12/1/2013',435500,'Accounts Payable ' ,'Balance Sheet ',267555.29,1,267555.29,11697016.86,1,11697016.86 Union ALL
SELECT 2013,'12/1/2013',435501,'Accounts Payable ' ,'Balance Sheet ',2714882.45,1,2714882.45,14411899.31,1,14411899.31 Union ALL
SELECT 2013,'12/1/2013',435502,'Accounts Payable ' ,'Balance Sheet ',427031.21,1,427031.21,14838930.52,1,14838930.52 Union ALL
SELECT 2013,'12/1/2013',435503,'Accounts Payable ' ,'Balance Sheet ',455515.21,1,455515.21,15294445.73,1,15294445.73 Union ALL
SELECT 2013,'12/1/2013',435504,'Accounts Payable ' ,'Balance Sheet ',158.45,1,158.45,15294604.18,1,15294604.18 Union ALL
SELECT 2013,'12/1/2013',435506,'Accounts Payable ' ,'Balance Sheet ',314109.27,1,314109.27,15608713.45,1,15608713.45 Union ALL
SELECT 2013,'12/1/2013',435507,'Accounts Payable ' ,'Balance Sheet ',53718.89,1,53718.89,15662432.34,1,15662432.34 Union ALL
SELECT 2013,'12/1/2013',435508,'Accounts Payable ' ,'Balance Sheet ',713517.16,1,713517.16,16375949.5,1,16375949.5
)
August 1, 2014 at 1:53 pm
You should make sure your DDL works before you post it here.
You have columns in your table called TRIAL... and in your insert statement called TRAIL...
You have a column in your insert called FISCALYEAR that doesn't exist in your table.
Your dates need to be enclosed in single quotes.
Etc.
August 1, 2014 at 2:03 pm
...also...please provide expected results based on your sample data...thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 1, 2014 at 3:38 pm
Fixed my CODE and attaching Excel sheet to show desired output. see column N, O, P for results.
August 1, 2014 at 4:05 pm
I'm still not able to understand what you want to do here.
If you had one record per Calendar_Month, this would make more sense to me. As it is, you have a lot of them, with no apparent way to sort them. So I'm guessing you want to somehow SUM your columns and group by Calendar_Month or something? If so, you would want to use that as your starting point and then do something like this:
-- This would have to have one record per Calendar_Month to work:
WITH A AS
(
SELECT *, RN = ROW_NUMBER() OVER(ORDER BY Calendar_Month) FROM #TrialBalance
)
SELECT
A.Trial_Balance_ID,
A.FISCALYEAR,
A.Calendar_Month,
A.actindx,
A.CATEGORY,
A.POSTINGTYPE,
A.ACTIVITYDEBIT,
A.ACTIVITYCREDIT,
A.NETAMOUNT,
A.Trial_Balance_Debit,
A.Trial_Balance_Credit,
A.Trial_Balance_Net,
ISNULL(B.Trial_Balance_Debit, 0) AS Beginning_Balance_Debit,
A.Beginning_Balance_Credit,
A.Beginning_Balance_Net
FROM A
LEFT OUTER JOIN A AS B ON
A.RN - 1 = B.RN
ORDER BY A.Calendar_Month
August 2, 2014 at 2:54 am
jameslauf (8/1/2014)
Fixed my CODE and attaching Excel sheet to show desired output. see column N, O, P for results.
Looking at the expected results prompts few questions:
😎
1. Is this dataset for a single account?
2. Why would an earlier record be assigned the beginning balance from a later record, ie. row 10, 18?
3. Can you explain why a Accounts Payable account receives the beginning balance of an cash account from the same month? (row 19)
To obtain the results in the Expected results spreadsheet, simply do a self join on Trial_Balance_ID = Trial_Balance_ID - 1. Must say it seems illogical to me though.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply