# Spit value into predefined parts

• ### 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

• ```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
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 Thursday, August 11, 2016 5:21 PM
• Marked as answer by 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)

,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.00002/01/2016 | (1/12)*1.00003/01/2016 | (1/12)*1.00004/01/2016 | (1/12)*1.00005/01/2016 | (1/12)*1.00006/01/2016 | (1/12)*1.00007/01/2016 | (1/12)*1.00008/01/2016 | (1/12)*1.00009/01/2016 | (1/12)*1.00010/01/2016 | (1/12)*1.00011/01/2016 | (1/12)*1.00012/01/2016 | (1/12)*1.00001/01/2017 | (1/24)*1.000`

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
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 Thursday, August 11, 2016 5:21 PM
• Marked as answer by 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)