July 23, 2014 at 1:03 pm
Hello, I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last Calendar_Month. I am providing Temp Table code as well as fake data.
=====
IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL
DROP TABLE #MyTrialBalance
CREATE TABLE #MyTrialBalance (
[Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[FISCALYEAR] [smallint] NULL,
[Calendar_Month] [date] NULL,
[actindx] [int] NULL,
[ACCOUNTDESCRIPTION] [varchar](55) NULL,
[CATEGORY] [varchar](55) NULL,
[POSTINGTYPE] [varchar](14) NULL,
[ACTIVITYDEBIT] [decimal](19,5) NULL,
[ACTIVITYCREDIT] [decimal](19,5) NULL,
[NETAMOUNT] [decimal](19,5) NULL,
[Trail_Balance_Debit] [decimal](19,5) NULL,
[Trail_Balance_Credit] [decimal](19,5) NULL,
[Trail_Balance_Net] [decimal](19,5) NULL,
[Beginning_Balance_Debit] [decimal](19,5) NULL,
[Beginning_Balance_Credit] [decimal](19,5) NULL,
[Beginning_Balance_Net] [decimal](19,5) NULL
)
====
INSERT INTO #MyTrialBalance
( FISCALYEAR ,
Calendar_Month ,
actindx ,
ACCOUNTDESCRIPTION ,
CATEGORY ,
POSTINGTYPE ,
ACTIVITYDEBIT ,
ACTIVITYCREDIT ,
NETAMOUNT ,
Trail_Balance_Debit ,
Trail_Balance_Credit ,
Trail_Balance_Net ,
Beginning_Balance_Debit ,
Beginning_Balance_Credit ,
Beginning_Balance_Net
)
( SELECT 2013,'12/1/2013',11112,'Gross AR - Receipts' ,'Accounts Receivable' ,'Balance Sheet',2741546.87,1369544.99,1372001.88,2741546.87,1369544.99,1372001.88,0,0,0 Union ALL
SELECT 2014,'1/1/2014',11117,'Gross AR - Receipts' ,'Accounts Receivable' ,'Balance Sheet',742031.3,303873.57,438157.73,0,0,0,0,0,0 Union ALL
SELECT 2014,'2/1/2014',11117,'Gross AR - Receipts' ,'Accounts Receivable' ,'Balance Sheet',645181.1,418997.83,226183.27,0,0,0,0,0,0 Union ALL
SELECT 2014,'3/1/2014',11117,'Gross AR - Receipts' ,'Accounts Receivable' ,'Balance Sheet',741697.83,399998.89,341698.94,0,0,0,0,0,0 Union ALL
SELECT 2014,'4/1/2014',11117,'Gross AR - Receipts' ,'Accounts Receivable' ,'Balance Sheet',520439.78,141881.54,378558.24,0,0,0,0,0,0 Union ALL
SELECT 2014,'5/1/2014',11117,'Gross AR - Receipts' ,'Accounts Receivable' ,'Balance Sheet',680894.23,331147.62,349764.61,0,0,0,0,0,0 Union ALL
SELECT 2014,'6/1/2014',11117,'Gross AR - Receipts' ,'Accounts Receivable' ,'Balance Sheet',38348.73,17764.73,20584,0,0,0,0,0,0
)
===
Here is my Query I am trying but not working. I cant figure out how to doo the dateadd for correct column.
SELECT A.Trial_Balance_ID,A.ACTIVITYDEBIT --SUM(A.ACTIVITYDEBIT + B.Last_Trail_Balance_Debit) AS New_TB
FROM
(SELECT [Trial_Balance_ID], [Calendar_Month],[ACTIVITYDEBIT]
FROM Mytrialbalance
WHERE actindx='48397' AND ACTIVITYDEBIT='820439.78000'
)A INNER JOIN
(SELECT [Trial_Balance_ID],DATEADD(MM, -1,Calendar_Month)AS Last_Month
FROM Mytrialbalance) B ON B.Trial_Balance_ID=A.Trial_Balance_ID
July 23, 2014 at 3:49 pm
I'm not sure if I'm understanding you, but does this do what you want?
IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL
DROP TABLE #MyTrialBalance
CREATE TABLE #MyTrialBalance (
[Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[FISCALYEAR] [smallint] NULL,
[Calendar_Month] [datetime] NULL,
[actindx] [int] NULL,
[ACCOUNTDESCRIPTION] [varchar](55) NULL,
[CATEGORY] [varchar](55) NULL,
[POSTINGTYPE] [varchar](14) NULL,
[ACTIVITYDEBIT] [decimal](19,5) NULL,
[ACTIVITYCREDIT] [decimal](19,5) NULL,
[NETAMOUNT] [decimal](19,5) NULL,
[Trail_Balance_Debit] [decimal](19,5) NULL,
[Trail_Balance_Credit] [decimal](19,5) NULL,
[Trail_Balance_Net] [decimal](19,5) NULL,
[Beginning_Balance_Debit] [decimal](19,5) NULL,
[Beginning_Balance_Credit] [decimal](19,5) NULL,
[Beginning_Balance_Net] [decimal](19,5) NULL
)
INSERT INTO #MyTrialBalance
(
FISCALYEAR ,
Calendar_Month ,
actindx ,
ACCOUNTDESCRIPTION ,
CATEGORY ,
POSTINGTYPE ,
ACTIVITYDEBIT ,
ACTIVITYCREDIT ,
NETAMOUNT ,
Trail_Balance_Debit ,
Trail_Balance_Credit ,
Trail_Balance_Net ,
Beginning_Balance_Debit ,
Beginning_Balance_Credit ,
Beginning_Balance_Net
)
SELECT 2013,'12/1/2013',11112,'Gross AR - Receipts' ,'Accounts Receivable' ,'Balance Sheet',2741546.87,1369544.99,1372001.88,2741546.87,1369544.99,1372001.88,0,0,0 Union ALL
SELECT 2014,'1/1/2014',11117,'Gross AR - Receipts' ,'Accounts Receivable' ,'Balance Sheet',742031.3,303873.57,438157.73,0,0,0,0,0,0 Union ALL
SELECT 2014,'2/1/2014',11117,'Gross AR - Receipts' ,'Accounts Receivable' ,'Balance Sheet',645181.1,418997.83,226183.27,0,0,0,0,0,0 Union ALL
SELECT 2014,'3/1/2014',11117,'Gross AR - Receipts' ,'Accounts Receivable' ,'Balance Sheet',741697.83,399998.89,341698.94,0,0,0,0,0,0 Union ALL
SELECT 2014,'4/1/2014',11117,'Gross AR - Receipts' ,'Accounts Receivable' ,'Balance Sheet',520439.78,141881.54,378558.24,0,0,0,0,0,0 Union ALL
SELECT 2014,'5/1/2014',11117,'Gross AR - Receipts' ,'Accounts Receivable' ,'Balance Sheet',680894.23,331147.62,349764.61,0,0,0,0,0,0 Union ALL
SELECT 2014,'6/1/2014',11117,'Gross AR - Receipts' ,'Accounts Receivable' ,'Balance Sheet',38348.73,17764.73,20584,0,0,0,0,0,0
;WITH CTE AS
(
SELECT *
FROM #MyTrialBalance
)
SELECT
MTB.*,
MTB.ACTIVITYDEBIT + ISNULL(CTE.Trail_Balance_Debit, 0) AS New_TB
FROM #MyTrialBalance AS MTB
LEFT OUTER JOIN CTE ON
DATEADD(month, -1, MTB.Calendar_Month) = CTE.Calendar_Month
July 23, 2014 at 4:19 pm
No Not quite. the best way to explain is as if it is an Excel sheet say ActivityDebit is Column H and it's Value in H3 is 742031.30 (which is Calendar_Month of 1/1/2014). Now Trial_Balance_Debit is in Column K and it's Value in K2 is 2741546.87 (This is in Calendar_Month of 12/12/2013 "the previous month before H3) I want my statement to Add H3 and K2 to make New_TB.
I have attached both excel file and picture with my logic.
Thanks in advance for your help.
July 23, 2014 at 4:22 pm
I'm pretty sure that's what I thought you meant. Are you sure my code isn't working right? I am adding the current month's ACTIVITYDEBIT to the previous month's Last_Trail_Balance_Debit.
July 23, 2014 at 4:35 pm
I'm sorry Your right I ran it on my full real DB and it didn't look right, but when I used fake data it works. THANKS!
July 23, 2014 at 4:44 pm
Glad I could help. You can mark that post as the solution if you're all set. 🙂
July 23, 2014 at 4:45 pm
As I said this worked. My next question is how do Insert this New_TB into Trial_Balance_Debit?
Again Following my logic in the Excel sheet this would insert into K3 and then the Formula would "Loop again" to provide the next New_TB?
July 23, 2014 at 4:56 pm
When someone solves your original question, there is a button you can click on that post to "mark as solution", that's all I meant.
As for your next question, I should probably let someone else tackle that. I think it might involve a recursive CTE. Not my forte, don't want to steer you wrong.
July 23, 2014 at 5:44 pm
There's no need for a recursive CTE. If that query gives the correct results you can use it to update your table.
WITH CTE AS(
SELECT MTB.*,
Trail_Balance_Debit_New = MTB.ACTIVITYDEBIT + ISNULL(CTE.Trail_Balance_Debit, 0)
FROM #MyTrialBalance AS MTB
LEFT OUTER JOIN #MyTrialBalance CTE ON
MTB.Calendar_Month = DATEADD(month, 1, CTE.Calendar_Month)
)
UPDATE b SET
Trail_Balance_Debit = c.Trail_Balance_Debit_New
FROM #MyTrialBalance b
JOIN CTE c ON b.Trial_Balance_ID = c.Trial_Balance_ID
However, you could always try a fast method called Quirky Update (QU) which will help you to generate running totals. You can read more about it and other methods on the following article: http://www.sqlservercentral.com/articles/T-SQL/68467/
If you use the QU, be sure to follow all the rules.
July 24, 2014 at 8:53 am
Thanks I will mark this as solution completed.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply