none
Deriving a periodic value from a year-to-date value

    Question

  • My problem is that I want to derive a periodic value from a year-to-date (YTD) value.  All of the data in my source table for my Income Statement is stored as YTD, but I need it to be represented as periodic, which can be accomplished by subtracting the current period from the prior period.  The code below accomplished this and aggregates the information into 12 columns (one for each month), but my destination application  has a problem accepting that man fields, so I slightly modified the code the bring the 12 columns into one, but it doesn't seem to work.

    This source table also contains Balance Sheet data that is also stored YTD and that is appropriate, so in the code below you will also see that it is selecting a rage of accounts for this purpose also.  The columns I am using are structured as follows:

    [Time] [Account] [Entity] [BusinessLine] [Department] [value]
    201201 430000 Entity_Name Business_line Department 5555.55
    201202 551122 Entity_Name Business_line Department 1111.11
    201203 112233 Entity_Name Business_line Department 3333.33

    set nocount on; 
    declare @fact table (
    					 time varchar(50) not null, 
    					 Account varchar(50) not null, 
    					 Entity varchar(80) not null, 
    					 BusinessLine varchar(50) not null, 
    					 Department varchar(50) not null, 
    					 value decimal(20,2) not null,
    					 process varchar (50) not null,
    					 loadingID varchar (50) not null
    					 ); 
    
    
    insert into @fact (
    					Time, 
    					account,
    					Entity, 
    					BusinessLine, 
    					Department, 
    					value,
    					Process,
    					loadingid
    					)
    
    Select Time, Account, Entity, BusinessLine, Department, Value, process, loadingid 
    from dbo.vw_Fact_Join_Writeback 
    where time like '2012%' and Currency='USD' and Value != 0 ;
    
    
    
    -- ======================================================
    ----------Periodic Aggregation of P&L Data---------------
    -- ======================================================
    
    Declare @TempTable Table (
    						 Time varchar (20) not null,	
    						 Account varchar(50) not null, 
    						 Entity varchar(80) not null, 
    						 BusinessLine varchar(50) not null, 
    						 Department varchar(50) not null,
    						 Jan decimal(20,2) not null,
    						 Feb decimal(20,2) not null,
    						 Mar decimal(20,2) not null,
    						 Apr decimal(20,2) not null,
    						 May decimal(20,2) not null,
    						 Jun decimal(20,2) not null,
    						 Jul decimal(20,2) not null,
    						 Aug decimal(20,2) not null,
    						 Sep decimal(20,2) not null,
    						 Oct decimal(20,2) not null,
    						 Nov decimal(20,2) not null,
    						 Dec decimal(20,2) not null
    						 )
    
    Insert into @TempTable (
    						Time,
    						account,
    						Entity, 
    						BusinessLine, 
    						Department,
    						Jan,
    						Feb,
    						Mar,
    						Apr,
    						May,
    						Jun,
    						Jul,
    						Aug,
    						Sep,
    						Oct,
    						Nov,
    						Dec 
    						)
    
     Select time, Account, Entity, businessline, department, sum(case when right(time, 2) = '01' then value else 0 end)*-1 as Jan, 
    sum(case when right(time, 2) = '01' then value else 0 end) - sum(case when right(time, 2) = '02' then value else 0 end) as Feb, 
    sum(case when right(time, 2) = '02' then value else 0 end) - sum(case when right(time, 2) = '03' then value else 0 end) as Mar,
    sum(case when right(time, 2) = '03' then value else 0 end) - sum(case when right(time, 2) = '04' then value else 0 end) as Apr,
    sum(case when right(time, 2) = '04' then value else 0 end) - sum(case when right(time, 2) = '05' then value else 0 end) as May,
    sum(case when right(time, 2) = '05' then value else 0 end) - sum(case when right(time, 2) = '06' then value else 0 end) as Jun,
    sum(case when right(time, 2) = '06' then value else 0 end) - sum(case when right(time, 2) = '07' then value else 0 end) as Jul,
    sum(case when right(time, 2) = '07' then value else 0 end) - sum(case when right(time, 2) = '08' then value else 0 end) as Aug,
    sum(case when right(time, 2) = '08' then value else 0 end) - sum(case when right(time, 2) = '09' then value else 0 end) as Sep,
    sum(case when right(time, 2) = '09' then value else 0 end) - sum(case when right(time, 2) = '10' then value else 0 end) as Oct,
    sum(case when right(time, 2) = '10' then value else 0 end) - sum(case when right(time, 2) = '11' then value else 0 end) as Nov, 
    sum(case when right(time, 2) = '11' then value else 0 end) - sum(case when right(time, 2) = '12' then value else 0 end) as Dec
    
    
    from @fact
    where account between '430000' and '899999'
    group by account, Entity, BusinessLine, Department, time
    order by account, Entity, BusinessLine, Department, time
    ;
    
    
    -- ======================================================
    ------------------YTD Balance Sheet----------------------
    -- ======================================================
    
    
    Insert into @TempTable (time,
    						account,
    						Entity, 
    						BusinessLine, 
    						Department,
    						Jan,
    						Feb,
    						Mar,
    						Apr,
    						May,
    						Jun,
    						Jul,
    						Aug,
    						Sep,
    						Oct,
    						Nov,
    						Dec 
    						)
    
    Select time, Account, Entity, businessline, department, sum(case when right(time, 2) = '01' then value else 0 end) as Jan, 
    sum(case when right(time, 2) = '02' then value else 0 end) as Feb, sum(case when right(time, 2) = '03' then value else 0 end) as Mar,
    sum(case when right(time, 2) = '04' then value else 0 end) as Apr,  sum(case when right(time, 2) = '05' then value else 0 end) as May,
    sum(case when right(time, 2) = '06' then value else 0 end) as Jun, sum(case when right(time, 2) = '07' then value else 0 end) as Jul,
    sum(case when right(time, 2) = '08' then value else 0 end) as Aug, sum(case when right(time, 2) = '09' then value else 0 end) as Sep,
    sum(case when right(time, 2) = '10' then value else 0 end) as Oct, sum(case when right(time, 2) = '11' then value else 0 end) as Nov,
    sum(case when right(time, 2) = '12' then value else 0 end) as Dec 
    
    
    from @fact
    where account between '100000' and '377999'
    group by account, Entity, BusinessLine, Department, time
    order by account, Entity, BusinessLine, Department, time
    ; 
    
    -- ======================================================
    -------------- Insert BU Intercompay-----------------
    -- ======================================================
    
    
    Delete from @TempTable
    where (account like '430088' or account like'530088') and entity like 'B%';
    
    
    insert into @TempTable (
    						time,
    						account,
    						Entity, 
    						BusinessLine, 
    						Department,
    						Jan,
    						Feb,
    						Mar,
    						Apr,
    						May,
    						Jun,
    						Jul,
    						Aug,
    						Sep,
    						Oct,
    						Nov,
    						Dec 
    						)
    
    
    Select time, account, entity, businessline, department,
    sum(case when right(time, 2) = '01' then value else 0 end)*-1 as Jan,
    sum(case when right(time, 2) = '02' then value else 0 end)*-1 as Feb,
    sum(case when right(time, 2) = '03' then value else 0 end)*-1 as Mar,
    sum(case when right(time, 2) = '04' then value else 0 end)*-1 as Apr,
    sum(case when right(time, 2) = '04' then value else 0 end)*-1 as May,
    sum(case when right(time, 2) = '04' then value else 0 end)*-1 as Jun,
    sum(case when right(time, 2) = '04' then value else 0 end)*-1 as Jul,
    sum(case when right(time, 2) = '04' then value else 0 end)*-1 as Aug,
    sum(case when right(time, 2) = '04' then value else 0 end)*-1 as Sep,
    sum(case when right(time, 2) = '04' then value else 0 end)*-1 as Oct,
    sum(case when right(time, 2) = '04' then value else 0 end)*-1 as Nov,
    sum(case when right(time, 2) = '04' then value else 0 end)*-1 as Dec
    
    from @fact
    where (account like '430088' or account like'530088') and process like 'etl' and loadingid != 'carryforward converted currency' and entity like 'B%'
    group by account, entity, businessline, department, time
    order by account, entity, businessline, department, time
    ;
    
    -- ======================================================
    --------------Make Total-----------------
    -- ======================================================
    
    
    select Account, Entity, BusinessLine, Department, right(time,2) as Period, case 
    when right(time,2) = '01' then jan 
    when right(time,2) = '02' then feb  
    when right(time,2) = '03' then Mar 
    when right(time,2) = '04' then Apr 
    when right(time,2) = '05' then May 
    when right(time,2) = '06' then Jun 
    when right(time,2) = '07' then Jul 
    when right(time,2) = '08' then Aug 
    when right(time,2) = '09' then Sep 
    when right(time,2) = '10' then Oct 
    when right(time,2) = '11' then Nov 
    when right(time,2) = '12' then Dec 
    else 0 end as Total 
    
    from @TempTable
    group by time,account, entity, businessline, department, jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec
    order by time,account, entity, businessline, department, jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec
    ;


    Thanks in advance for the help!  I know I'm missing something simple here...
    • Edited by The1Ash10 Tuesday, September 24, 2013 7:12 PM
    Tuesday, September 24, 2013 1:51 PM

All replies

  • You can start by providing a more specific definition of what "doesn't seem to work" actually means.  In the meantime, I suggest you review the 2nd-to-last statement where you insert into your table variable - you reuse the condition "right(time, 2) = '04' " multiple times. 
    Tuesday, September 24, 2013 3:14 PM
  • You can start by providing a more specific definition of what "doesn't seem to work" actually means.  In the meantime, I suggest you review the 2nd-to-last statement where you insert into your table variable - you reuse the condition "right(time, 2) = '04' " multiple times. 

    I apologize for the lack of specificity.  For whatever reason, I'm not getting the difference between the values (i.e. the periodic value) when I introduced the "time" column into  the "insert into @TempTable" statement.  I'm still getting a YTD value the "total" column that is generated by the last statement. 

    Thanks for catching the "right(time,2) = '04' " mistake.  I hadn't finished that statement and forgot to come back to it, but it is independent of the issue described above.  

    Thanks again for the help!

    Tuesday, September 24, 2013 3:58 PM
  • You have multiple "insert @TempTable" statements, so it is not clear which one you are referring to.  I suggest that you break your logic into pieces and verify that each one does what is intended.  WIthout knowing your data nor what the final result should look like, it is difficult to say anything specific. 

    Just to point out things that are obvious to you but not to anyone unfamiliar with your situation, it seems like the column "time" in your source table should be a 6 character value in the format of YYYYMM (even though it is defined in your table variable as varchar(50)).  Is that correct or is there an implicit conversion occurring there?  It seems like this should be a relatively straightforward pivot with the exception that you need to calculate monthly numbers.  In addition, it seems odd that your source data has YTD monthly numbers only.  Are you certain that the actual monthly numbers are not also present in some other dimension table?  From an accounting perspective I find that very unusual. 

    Tuesday, September 24, 2013 6:38 PM
  • This sounds like a delta query. 

    CREATE TABLE Foobar
    (foo_key INTEGER NOT NULL PRIMARY KEY,
     floob_amt DECIMAL (10,2) NOT NULL);

    INSERT INTO Foobar
    VALUES 
    (1, 12.50);
    (2, -2.50);
    (3, 10.00);
    (4, -2.50);

    Now you use either LEAD() or LAG() to get the prior values

    SELECT foo_key, floob_amt,
           (LAG(floob_amt) OVER(ORDER BY foo_key)
              + floob_amt) AS flob_delta
       FROM Foobar;

    1 12.50 NULL
    2 -2.50 10.00
    3 10.00 7.50
    4 -2.50 7.50

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, September 24, 2013 6:39 PM
  • Just to add to CELKOs post.

    You can do this:

    create table #ytdvals (
    	[Time] char(6),
    	[Account] varchar(50),
    	[Entity] varchar(80),
    	[BusinessLine] varchar(50),
    	[Department] varchar(50), 
    	[value] decimal(20,2)
    )
    
    INSERT INTO #ytdvals 
    SELECT '201201','430000','Entity_Name','Business_line','Department','5555.55'
    UNION ALL
    SELECT '201202','551122','Entity_Name','Business_line','Department','1111.11'
    UNION ALL
    SELECT '201203','112233','Entity_Name','Business_line','Department','3333.33'
    
    
    SELECT *, monthvalue = ([value] - LAG([value]) OVER (ORDER BY [Time],[Account],[Entity],[BusinessLine],[Department]))  
    FROM #ytdvals
    
    DROP TABLE #ytdvals
    

    Tuesday, September 24, 2013 7:00 PM
    Moderator
  • Here is an example of a pivot that may help you figure out things.  It seems like you are sorta doing the pivot in the old way into your table variable and then unpivoting for the result - which seems odd since you're doing twice the work. 

    declare @tran table (account char(10) not null, [time] char(6) not null, amount decimal(10,2) not null); 
    insert @tran(account, [time], [amount]) values ('430000', '201201', 2000), ('430000', '201202', 0), ('430000', '201203', 2500);
    insert @tran(account, [time], [amount]) values ('440000', '201112', 2000), ('440000', '201201', 500), ('440000', '201202', 2500), ('440000', '201203', 3000);
    select *, cast([time] + '01' as date) as BOM, datename(month, cast([time] + '01' as date)) as mon  from @tran order by account, [time];
    select *
    from (
    	select account, [time], cast([time] + '01' as date) as BOM, datename(month, cast([time] + '01' as date)) as mon, amount from @tran ) as src 
    	pivot (sum(amount) for mon in ([January], [February], [March])) as pvt
    select account, January, February, March 
    from (
    	select account, datename(month, cast([time] + '01' as date)) as mon, amount from @tran where left([time], 4) = '2012') as src 
    	pivot (sum(amount) for mon in ([January], [February], [March])) as pvt
    order by account; 
    select account, January, February - January as Feb2, March - February as Mar2
    from (
    	select account, datename(month, cast([time] + '01' as date)) as mon, amount from @tran where left([time], 4) = '2012') as src 
    	pivot (sum(amount) for mon in ([January], [February], [March])) as pvt
    order by account; 

    Tuesday, September 24, 2013 7:07 PM
  • You have multiple "insert @TempTable" statements, so it is not clear which one you are referring to.  I suggest that you break your logic into pieces and verify that each one does what is intended.  WIthout knowing your data nor what the final result should look like, it is difficult to say anything specific. 

    Just to point out things that are obvious to you but not to anyone unfamiliar with your situation, it seems like the column "time" in your source table should be a 6 character value in the format of YYYYMM (even though it is defined in your table variable as varchar(50)).  Is that correct or is there an implicit conversion occurring there?  It seems like this should be a relatively straightforward pivot with the exception that you need to calculate monthly numbers.  In addition, it seems odd that your source data has YTD monthly numbers only.  Are you certain that the actual monthly numbers are not also present in some other dimension table?  From an accounting perspective I find that very unusual. 

    I found it weird that the only way the data was presented was on a YTD basis, but perhaps that is how it retrieved from the source systems.

    The piece of code that I was referring to was this:

    Insert into @TempTable (
    						Time,
    						account,
    						Entity, 
    						BusinessLine, 
    						Department,
    						Jan,
    						Feb,
    						Mar,
    						Apr,
    						May,
    						Jun,
    						Jul,
    						Aug,
    						Sep,
    						Oct,
    						Nov,
    						Dec 
    						)
    
     Select time, Account, Entity, businessline, department, sum(case when right(time, 2) = '01' then value else 0 end)*-1 as Jan, 
    sum(case when right(time, 2) = '01' then value else 0 end) - sum(case when right(time, 2) = '02' then value else 0 end) as Feb, 
    sum(case when right(time, 2) = '02' then value else 0 end) - sum(case when right(time, 2) = '03' then value else 0 end) as Mar,
    sum(case when right(time, 2) = '03' then value else 0 end) - sum(case when right(time, 2) = '04' then value else 0 end) as Apr,
    sum(case when right(time, 2) = '04' then value else 0 end) - sum(case when right(time, 2) = '05' then value else 0 end) as May,
    sum(case when right(time, 2) = '05' then value else 0 end) - sum(case when right(time, 2) = '06' then value else 0 end) as Jun,
    sum(case when right(time, 2) = '06' then value else 0 end) - sum(case when right(time, 2) = '07' then value else 0 end) as Jul,
    sum(case when right(time, 2) = '07' then value else 0 end) - sum(case when right(time, 2) = '08' then value else 0 end) as Aug,
    sum(case when right(time, 2) = '08' then value else 0 end) - sum(case when right(time, 2) = '09' then value else 0 end) as Sep,
    sum(case when right(time, 2) = '09' then value else 0 end) - sum(case when right(time, 2) = '10' then value else 0 end) as Oct,
    sum(case when right(time, 2) = '10' then value else 0 end) - sum(case when right(time, 2) = '11' then value else 0 end) as Nov, 
    sum(case when right(time, 2) = '11' then value else 0 end) - sum(case when right(time, 2) = '12' then value else 0 end) as Dec
    
    
    from @fact
    where account between '430000' and '899999'
    group by account, Entity, BusinessLine, Department, time
    order by account, Entity, BusinessLine, Department, time
    ;

    If I strip the "time" column from that, I get what I need (periodic values) , but I have no way of getting everything back into a single column (the last section that I have in my OP does this).  Perhaps this is all a little overly complicated and I don't need to go through all of this work, but I'm definitely open to suggestions. 


    • Edited by The1Ash10 Tuesday, September 24, 2013 7:17 PM
    Tuesday, September 24, 2013 7:16 PM
  • Just to add to CELKOs post.

    You can do this:

    create table #ytdvals (
    	[Time] char(6),
    	[Account] varchar(50),
    	[Entity] varchar(80),
    	[BusinessLine] varchar(50),
    	[Department] varchar(50), 
    	[value] decimal(20,2)
    )
    
    INSERT INTO #ytdvals 
    SELECT '201201','430000','Entity_Name','Business_line','Department','5555.55'
    UNION ALL
    SELECT '201202','551122','Entity_Name','Business_line','Department','1111.11'
    UNION ALL
    SELECT '201203','112233','Entity_Name','Business_line','Department','3333.33'
    
    
    SELECT *, monthvalue = ([value] - LAG([value]) OVER (ORDER BY [Time],[Account],[Entity],[BusinessLine],[Department]))  
    FROM #ytdvals
    
    DROP TABLE #ytdvals

    This looks like it would work great, but unfortunately, while I'm using SQL Server MS 2012 to interact with the data, the actual server is on 2008 R2, so I'm getting an error that " 'lag' is not a recognized built-in function name."

    Is there any way to enable it for 2008 R2, or is this just a 2012/denali function?


    • Edited by The1Ash10 Tuesday, September 24, 2013 7:44 PM
    Tuesday, September 24, 2013 7:31 PM
  • When you include time in your group by clause, that effectively negates your attempt to sum a given 2 month period together.  To see how that works, simply select a small subset of rows and remove the sum aggregate (and then alternately run the query with time in the group and with time not in the group).  It isn't quite clear why you need [time] in that particular resultset since it seems you want one row per account/entity/businesline/depart - and that row should cover a single year (with a column for each month).  The presence of time does not logically make sense - at least from the perspective of trying to sum amounts for each month.  I suggest you start with a repro script (either the one I posted or one of the others) that illustrates your source data - limited as it may be- and then an example of what the output should be based on the source data. 

    Tuesday, September 24, 2013 7:54 PM