Post reply

use previous month data in column as following months data different column.

  • August 20, 2014 at 2:19 pm

    #310837

    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 need to be able to do this for multiple account types. So the two datasets that need to be included in logic is actindx and Calendar_Month.

    For actindx of 2 and Calendar_Month of 2014-01-01The Trial_Balance_Debit is 19585.46 This would make the Beginning_Balance of actindx 2 and Calendar_Month of 2014-02-01 19585.46

    I am trying to do some type of self join, but not sure how to include each actindx number differently. Please Help.

    Table creation and data insert is below.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[TrialBalance](

    [Trial_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,

    [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_TrialBalance] 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

    SET ANSI_PADDING ON

    GO

    /********* Insert Data****************/

    INSERT INTO TrialBalance (

    [FISCALYEAR],

    [ACCTPERIOD] ,

    [YEAR_MONTH_TEXT] ,

    [Calendar_Month],

    [actindx],

    [ENTITY] ,

    [NATURALACCT] ,

    [ACCOUNTDESCRIPTION],

    [CATEGORY] ,

    [POSTINGTYPE] ,

    [ACTIVITYDEBIT] ,

    [ACTIVITYCREDIT] ,

    [NETAMOUNT] ,

    [Trial_Balance_Debit] ,

    [Trial_Balance_Credit] ,

    [Trial_Balance_Net]

    )

    SELECT 2014,'201401','2014/01','1/1/2014',2,'1001','12102','Taxes' ,'Cash','Balance Sheet',19585.46,10128.21,9457.25,19585.46,21996.85,-2411.39 UNION ALL

    SELECT 2014,'201402','2014/02','2/1/2014',2,'1001','12102','Taxes' ,'Cash','Balance Sheet',22229.83,23739.32,-1509.49,41815.29,45736.17,-3920.88 UNION ALL

    SELECT 2014,'201403','2014/03','3/1/2014',2,'1001','12102','Taxes' ,'Cash','Balance Sheet',155321.37,155163.96,157.41,197136.66,200900.13,-3763.47 UNION ALL

    SELECT 2014,'201404','2014/04','4/1/2014',2,'1001','12102','Taxes' ,'Cash','Balance Sheet',164767.42,224682.22,-59914.8,361904.08,425582.35,-63678.27 UNION ALL

    SELECT 2014,'201405','2014/05','5/1/2014',2,'1001','12102','Taxes' ,'Cash','Balance Sheet',154346.32,85048.99,69297.33,516250.4,510631.34,5619.06 UNION ALL

    SELECT 2014,'201406','2014/06','6/1/2014',2,'1001','12102','Taxes' ,'Cash','Balance Sheet',71331.38,71331.38,0,587581.78,581962.72,5619.06 UNION ALL

    SELECT 2014,'201407','2014/07','7/1/2014',2,'1001','12102','Taxes' ,'Cash','Balance Sheet',51546.79,51746.8,-200.01,639128.57,633709.52,5419.05 UNION ALL

    SELECT 2014,'201401','2014/01','1/1/2014',4,'1001','14104','Chase Account' ,'Cash','Balance Sheet',26331216.98,26173846.32,157370.66,26331216.98,26953177.38,-621960.4 UNION ALL

    SELECT 2014,'201402','2014/02','2/1/2014',4,'1001','14104','Chase Account' ,'Cash','Balance Sheet',9112344.1,9075366.43,36977.67,35443561.08,36028543.81,-584982.73 UNION ALL

    SELECT 2014,'201403','2014/03','3/1/2014',4,'1001','14104','Chase Account' ,'Cash','Balance Sheet',8701450.4,8553677.47,147772.93,44145011.48,44582221.28,-437209.8 UNION ALL

    SELECT 2014,'201404','2014/04','4/1/2014',4,'1001','14104','Chase Account' ,'Cash','Balance Sheet',8813026.26,8644568.15,168458.11,52958037.74,53226789.43,-268751.69 UNION ALL

    SELECT 2014,'201405','2014/05','5/1/2014',4,'1001','14104','Chase Account' ,'Cash','Balance Sheet',8475209.25,8416390.29,58818.96,61433246.99,61643179.72,-209932.73 UNION ALL

    SELECT 2014,'201406','2014/06','6/1/2014',4,'1001','14104','Chase Account' ,'Cash','Balance Sheet',8733407.61,8678338.42,55069.19,70166654.6,70321518.14,-154863.54 UNION ALL

    SELECT 2014,'201407','2014/07','7/1/2014',4,'1001','14104','Chase Account' ,'Cash','Balance Sheet',11834591.04,11804975.74,29615.3,82001245.64,82126493.88,-125248.24 UNION ALL

    SELECT 2014,'201408','2014/08','8/1/2014',4,'1001','14104','Chase Account','Cash','Balance Sheet',3344888.3,4064208.33,-719320.03,85346133.94,86190702.21,-844568.27 UNION ALL

    SELECT 2014,'201401','2014/01','1/1/2014',6,'1001','16106','A/P Disbursements ' ,'Cash','Balance Sheet',64394710.97,62061198.98,2333511.99,64394710.97,71426446.75,-7031735.78 UNION ALL

    SELECT 2014,'201402','2014/02','2/1/2014',6,'1001','16106','A/P Disbursements ','Cash','Balance Sheet',20508291.97,21483616.73,-975324.76,84903002.94,92910063.48,-8007060.54 UNION ALL

    SELECT 2014,'201403','2014/03','3/1/2014',6,'1001','16106','A/P Disbursements ','Cash','Balance Sheet',17723639.15,14253848.29,3469790.86,102626642.1,107163911.8,-4537269.68 UNION ALL

    SELECT 2014,'201404','2014/04','4/1/2014',6,'1001','16106','A/P Disbursements ','Cash','Balance Sheet',17085819.94,22577943.99,-5492124.052,119712462,129741855.8,-10029393.73 UNION ALL

    SELECT 2014,'201405','2014/05','5/1/2014',6,'1001','16106','A/P Disbursements ','Cash','Balance Sheet',15437400.23,13093318.57,2344081.66,135149862.3,142835174.3,-7685312.072 UNION ALL

    SELECT 2014,'201406','2014/06','6/1/2014',6,'1001','16106','A/P Disbursements ','Cash','Balance Sheet',18055092.8,14016561.38,4038531.42,153204955.1,156851735.7,-3646780.652 UNION ALL

    SELECT 2014,'201407','2014/07','7/1/2014',6,'1001','16106','A/P Disbursements ','Cash','Balance Sheet',15906168.54,20007024.14,-4100855.6,169111123.6,176858759.9,-7747636.252 UNION ALL

    SELECT 2014,'201408','2014/08','8/1/2014',6,'1001','16106','A/P Disbursements ','Cash','Balance Sheet',33440.58,4685186.49,-4651745.91,169144564.2,181543946.3,-12399382.16 UNION ALL

    SELECT 2014,'201401','2014/01','1/1/2014',7,'1001','17107','A/P Refunds','Cash','Balance Sheet',1435967.79,1313116.91,122850.88,1435967.79,2262913.47,-826945.68 UNION ALL

    SELECT 2014,'201402','2014/02','2/1/2014',7,'1001','17107','A/P Refunds','Cash','Balance Sheet',466811,690122.53,-223311.53,1902778.79,2953036,-1050257.21 UNION ALL

    SELECT 2014,'201403','2014/03','3/1/2014',7,'1001','17107','A/P Refunds','Cash','Balance Sheet',836659.35,937280.81,-100621.46,2739438.14,3890316.81,-1150878.67 UNION ALL

    SELECT 2014,'201404','2014/04','4/1/2014',7,'1001','17107','A/P Refunds','Cash','Balance Sheet',706500.31,404450.42,302049.89,3445938.45,4294767.23,-848828.78 UNION ALL

    SELECT 2014,'201405','2014/05','5/1/2014',7,'1001','17107','A/P Refunds','Cash' ,'Balance Sheet',773404.99,1090985.83,-317580.84,4219343.44,5385753.06,-1166409.62 UNION ALL

    SELECT 2014,'201406','2014/06','6/1/2014',7,'1001','17107','A/P Refunds','Cash','Balance Sheet',787230.6,708499.31,78731.29,5006574.04,6094252.37,-1087678.33 UNION ALL

    SELECT 2014,'201407','2014/07','7/1/2014',7,'1001','17107','A/P Refunds','Cash','Balance Sheet',781300.78,523497.02,257803.76,5787874.82,6617749.39,-829874.57 UNION ALL

    SELECT 2014,'201408','2014/08','8/1/2014',7,'1001','17107','A/P Refunds','Cash','Balance Sheet',23672.51,419265.75,-395593.24,5811547.33,7037015.14,-1225467.81 UNION ALL

    SELECT 2014,'201401','2014/01','1/1/2014',8,'1001','18108','Turnover Expense','Cash','Balance Sheet',253194.78,204633.81,48560.97,253194.78,267010.2,-13815.42 UNION ALL

    SELECT 2014,'201402','2014/02','2/1/2014',8,'1001','18108','Turnover Expense','Cash','Balance Sheet',77110.69,101448.13,-24337.44,330305.47,368458.33,-38152.86 UNION ALL

    SELECT 2014,'201403','2014/03','3/1/2014',8,'1001','18108','Turnover Expense','Cash','Balance Sheet',151304.49,138727.69,12576.8,481609.96,507186.02,-25576.06 UNION ALL

    SELECT 2014,'201404','2014/04','4/1/2014',8,'1001','18108','Turnover Expense','Cash','Balance Sheet',152126.86,209009.47,-56882.61,633736.82,716195.49,-82458.67 UNION ALL

    SELECT 2014,'201405','2014/05','5/1/2014',8,'1001','18108','Turnover Expense','Cash','Balance Sheet',145038.72,76161.78,68876.94,778775.54,792357.27,-13581.73 UNION ALL

    SELECT 2014,'201406','2014/06','6/1/2014',8,'1001','18108','Turnover Expense','Cash','Balance Sheet',121233.83,107069.73,14164.1,900009.37,899427,582.37 UNION ALL

    SELECT 2014,'201407','2014/07','7/1/2014',8,'1001','18108','Turnover Expense','Cash','Balance Sheet',161196.24,200399.96,-39203.72,1061205.61,1099826.96,-38621.35 UNION ALL

    SELECT 2014,'201401','2014/01','1/1/2014',9,'1001','19109','Check B of A Manual Posting','Cash','Balance Sheet',3036517073,3035377382,1139691.68,3045566683,3035377382,10189301.39 UNION ALL

    SELECT 2014,'201402','2014/02','2/1/2014',9,'1001','19109','Check B of A Manual Posting','Cash','Balance Sheet',931065570.7,937224766.5,-6159195.73,3976632254,3972602148,4030105.66 UNION ALL

    SELECT 2014,'201403','2014/03','3/1/2014',9,'1001','19109','Check B of A Manual Posting','Cash','Balance Sheet',968981931.1,959434081.8,9547849.31,4945614185,4932036230,13577954.97 UNION ALL

    SELECT 2014,'201404','2014/04','4/1/2014',9,'1001','19109','Check B of A Manual Posting','Cash','Balance Sheet',929271857.1,923945667.6,5326189.44,5874886042,5855981897,18904144.41 UNION ALL

    SELECT 2014,'201405','2014/05','5/1/2014',9,'1001','19109','Check B of A Manual Posting','Cash','Balance Sheet',818907429.2,821788487.1,-2881057.94,6693793471,6677770385,16023086.47 UNION ALL

    SELECT 2014,'201406','2014/06','6/1/2014',9,'1001','19109','Check B of A Manual Posting','Cash','Balance Sheet',1514121030,1511964988,2156042.12,8207914501,8189735372,18179128.59 UNION ALL

    SELECT 2014,'201407','2014/07','7/1/2014',9,'1001','19109','Check B of A Manual Posting','Cash','Balance Sheet',277255793.1,251467153.3,25788639.84,8485170294,8441202526,43967768.43 UNION ALL

    SELECT 2014,'201408','2014/08','8/1/2014',9,'1001','19109','Check B of A Manual Posting','Cash','Balance Sheet',0,6023964.94,-6023964.94,8485170294,8447226491,37943803.49 UNION ALL

    SELECT 2014,'201401','2014/01','1/1/2014',12,'1001','11212','Payroll','Cash','Balance Sheet',222565.76,220943.11,1622.65,222565.76,233191.96,-10626.2 UNION ALL

    SELECT 2014,'201402','2014/02','2/1/2014',12,'1001','11212','Payroll','Cash','Balance Sheet',169775.03,171456.41,-1681.38,392340.79,404648.37,-12307.58 UNION ALL

    SELECT 2014,'201403','2014/03','3/1/2014',12,'1001','11212','Payroll','Cash','Balance Sheet',116228.32,103362.45,12865.87,508569.11,508010.82,558.29 UNION ALL

    SELECT 2014,'201404','2014/04','4/1/2014',12,'1001','11212','Payroll','Cash','Balance Sheet',139971.93,141907.66,-1935.73,648541.04,649918.48,-1377.44

  • August 20, 2014 at 2:57 pm

    #1739729

    Create a cte that includes ROW_NUMBER. I don't know what column(s) make your order. Then you select from the cte, join to the cte on rownumber = rownumber + 1 (or -1 depending on how you build it). Then cte1.EndingBalance as BeginningBalance. That make sense? If I get a little time I will try to mock this up nice and tidy since you went the extra mile and provided us ddl and sample data (THANKS for that!!!!).

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • August 20, 2014 at 3:08 pm

    #1739732

    I know this is a rookie question, but how do I find out which column makes my Row number? I assume there is an internal SP or something that can tell me this?

  • August 20, 2014 at 3:14 pm

    #1739733

    jameslauf (8/20/2014)

    I know this is a rookie question, but how do I find out which column makes my Row number? I assume there is an internal SP or something that can tell me this?

    There is no such thing as a rookie question!!! ROW_NUMBER is a built in sql function you can use to add row numbers to a query. Since you haven't used it before my explanation is not going to get you where you need to be. Give me a little bit and I will see if I can knock something together.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • August 20, 2014 at 3:22 pm

    #1739734

    Something along these lines.

    with NumberedResults as

    (

    select ROW_NUMBER() over (Partition By NATURALACCT order by Calendar_Month) as RowNum

    , *

    from TrialBalance

    )

    select n2.ACTIVITYDEBIT as StartingBalance, n1.*

    from NumberedResults n1

    left join NumberedResults n2 on n2.RowNum = n1.RowNum - 1 and n1.NATURALACCT = n2.NATURALACCT

    where n1.NATURALACCT = '12102'

    This currently only shows you NATURALACCT 12102 so you can view the results. It will work for all accounts if you remove the where clause.

    Here is a link to ROW_NUMBER which will explain it far better than I can. http://msdn.microsoft.com/en-us/library/ms186734.aspx

    Hopefully this will at least get you pointed in the right direction.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • August 20, 2014 at 3:38 pm

    #1739737

    Thanks again. this is what I changed it too and it seems to work.

    with NumberedResults as

    (

    select ROW_NUMBER() over (Partition By actindx order by Calendar_Month) as RowNum

    , *

    from TrialBalance

    )

    select n2.Trial_Balance_Debit as StartingBalance, n1.*

    from NumberedResults n1

    left join NumberedResults n2 on n2.RowNum = n1.RowNum - 1 and n1.actindx = n2.actindx

    where n1.actindx = '6'

    My next Question i that I currently have 20592 distinct actindx numbers in my true database can I use the same code without the where clause to make this work for entire table?

  • August 20, 2014 at 3:42 pm

    #1739739

    jameslauf (8/20/2014)

    Thanks again. this is what I changed it too and it seems to work.

    with NumberedResults as

    (

    select ROW_NUMBER() over (Partition By actindx order by Calendar_Month) as RowNum

    , *

    from TrialBalance

    )

    select n2.Trial_Balance_Debit as StartingBalance, n1.*

    from NumberedResults n1

    left join NumberedResults n2 on n2.RowNum = n1.RowNum - 1 and n1.actindx = n2.actindx

    where n1.actindx = '6'

    My next Question i that I currently have 20592 distinct actindx numbers in my true database can I use the same code without the where clause to make this work for entire table?

    Ok cool. I wasn't sure what column(s) to use for separation so I guessed. 😉

    Yes you should be able to use this code as is for the whole table. I am a little concerned about performance though because your table is pretty big. Make sure to test it on your dev server first.

    Glad this helped. The big question is do you understand it?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • August 20, 2014 at 3:59 pm

    #1739742

    Yes I did understand once I saw it work Thanks again I marked this as Solved.

    As far as performance this will be part of an SSIS ETL job once a month that runs at 2am so performance is not very important.

    Thank You Very Much! I really appreciate this Forum and all the help (and increased knowledge ) I receive from it.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply