locked
Spit value into predefined parts RRS feed

  • Question

  • I have a numeric value that needs to be split into unequal but predifined parts. The parts are always 13 periodes into the future.

    Input: 1-1-2016  | 1.000

    Output:

    1-1-2016 | (1/24)*1000

    1-2-2016 | (1/12)*1.000

    1-3-2016 | (1/12)*1.000

    1-4-2016 | (1/12)*1.000

    etc

    1-1-2017 | (1/24)*1.000

    The parts always number 13 months into the future and only the first and last part are 1/24, the rest is 1/12.

    Don't really know where to start.

    Thursday, August 11, 2016 4:11 PM

Answers

  • DECLARE  @tbl AS TABLE ([dt] date, [Value] NUMERIC(10,5))
     
    INSERT INTO @tbl 
    VALUES ('2016-01-01',1.000);--Add any other Rows (Date/Values)
    
    ;WITH base AS 
    (
       SELECT [dt] , Value ,  1 AS n FROM @tbl
       UNION ALL
       SELECT  DATEADD(MONTH,1,dt), Value , n+1
       FROM base  WHERE n+1 <= 13
    )
    SELECT 
    *,
    CONVERT(VARCHAR(10), dt, 110)  +' | ' +  CASE WHEN n IN (1,13) THEN  '(1/24)' ELSE '(1/12)' END +'*' + CAST([Value] AS VARCHAR(10)) AS [Calculation_Text]
    ,  CASE WHEN n IN (1,13) THEN  (1.0000/24)*[Value] ELSE (1.0000/12)*[Value] END  AS [Calculated_Value]
     
      FROM base
    
    
       

    • Edited by msbi_Dev Thursday, August 11, 2016 5:21 PM
    • Marked as answer by PeervanEersel Thursday, August 11, 2016 6:53 PM
    Thursday, August 11, 2016 5:16 PM

All replies

  • create table t (dtcol date, val decimal(10,3))
     
    Insert into t values ('2016-01-01',1.000)
    
     Select dateadd(month,n,dtcol) dt
     ,Cast(1./12 *  Case when n%12 =0 then 2 else 1 end * val as decimal(6,3)) as newVal 
     from t
     CROSS APPLY (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) d(n)
    
    drop table t

    Thursday, August 11, 2016 4:48 PM
  • I have a numeric value that needs to be split into unequal but predifined parts. The parts are always 13 periodes into the future.

    Input: 1-1-2016  | 1.000

    Output:

    1-1-2016 | (1/24)*1000

    1-2-2016 | (1/12)*1.000

    1-3-2016 | (1/12)*1.000

    1-4-2016 | (1/12)*1.000

    etc

    1-1-2017 | (1/24)*1.000

    The parts always number 13 months into the future and only the first and last part are 1/24, the rest is 1/12.

    Don't really know where to start.

    Hi Peervan,

    Here's a simple sample that may be able to point you in the right direction. I hope it helps!

    DECLARE @periods table (dt date)
    INSERT INTO @periods (dt) 
    VALUES ('1/1/16'), ('2/1/16'), ('3/1/16'), ('4/1/16'), ('5/1/16'), ('6/1/16'), ('7/1/16'), ('8/1/16'), ('9/1/16'), ('10/1/16'), ('11/1/16'), ('12/1/16'), 
    ('1/1/17'), ('2/1/17'), ('3/1/17'), ('4/1/17'), ('5/1/17'), ('6/1/17'), ('7/1/17'), ('8/1/17'), ('9/1/17'), ('10/1/17'), ('11/1/17'), ('12/1/17')
    
    DECLARE @input varchar(50) = '1-1-2016 | 1.000'
    DECLARE @mydt date = substring(@input, 0, charindex(' | ', @input)),
    	@myVal varchar(20) = substring(@input, charindex(' | ', @input) + 3, len(@input) - 3)
    
    SELECT CASE WHEN ROW_NUMBER() OVER(ORDER BY dt) in (1, 13) 
    		THEN CONCAT(convert(varchar(10), dt, 101), ' | (1/24)*', @myVal) 
    		ELSE CONCAT(convert(varchar(10), dt, 101), ' | (1/12)*', @myVal) END as Output
    FROM @periods
    WHERE dt between @mydt and dateadd(m, 12, @mydt)
    ORDER BY dt

    Here's the output from the sample code:

    01/01/2016 | (1/24)*1.000
    02/01/2016 | (1/12)*1.000
    03/01/2016 | (1/12)*1.000
    04/01/2016 | (1/12)*1.000
    05/01/2016 | (1/12)*1.000
    06/01/2016 | (1/12)*1.000
    07/01/2016 | (1/12)*1.000
    08/01/2016 | (1/12)*1.000
    09/01/2016 | (1/12)*1.000
    10/01/2016 | (1/12)*1.000
    11/01/2016 | (1/12)*1.000
    12/01/2016 | (1/12)*1.000
    01/01/2017 | (1/24)*1.000


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)



    Thursday, August 11, 2016 4:50 PM
  • DECLARE  @tbl AS TABLE ([dt] date, [Value] NUMERIC(10,5))
     
    INSERT INTO @tbl 
    VALUES ('2016-01-01',1.000);--Add any other Rows (Date/Values)
    
    ;WITH base AS 
    (
       SELECT [dt] , Value ,  1 AS n FROM @tbl
       UNION ALL
       SELECT  DATEADD(MONTH,1,dt), Value , n+1
       FROM base  WHERE n+1 <= 13
    )
    SELECT 
    *,
    CONVERT(VARCHAR(10), dt, 110)  +' | ' +  CASE WHEN n IN (1,13) THEN  '(1/24)' ELSE '(1/12)' END +'*' + CAST([Value] AS VARCHAR(10)) AS [Calculation_Text]
    ,  CASE WHEN n IN (1,13) THEN  (1.0000/24)*[Value] ELSE (1.0000/12)*[Value] END  AS [Calculated_Value]
     
      FROM base
    
    
       

    • Edited by msbi_Dev Thursday, August 11, 2016 5:21 PM
    • Marked as answer by PeervanEersel Thursday, August 11, 2016 6:53 PM
    Thursday, August 11, 2016 5:16 PM
  • Wow everybody thanks for the fast and smart answers. In the end the solution provided by Raijiv provided the best results for my scenario.

    Thursday, August 11, 2016 6:55 PM
  • I modify my previous query to get the string type you want:

    create table t (dtcol date, val decimal(4,3))
     
    Insert into t values ('2016-01-01',1.000)
    
     Select dateadd(month,n,dtcol) dt
     ,'(1/'+Cast(12 * Case when n%12 =0 then 2 else 1 end as varchar(2)) +')*'+ Cast(val as varchar(50))   as newVal 
     from t
     CROSS APPLY (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) d(n)
    
    drop table t

    Thursday, August 11, 2016 8:30 PM