August 19, 2014 at 12:24 pm
Good afternoon! I'm writing a query that will be used in Jasper Ireports, but prefer to have the values done ahead of time using SQL rather than relying on the report to do the lifting.
The fields are pretty straight forward, only the display is where I have a question.
Fields Used: PERIOD ('MON-yyyy') and VALUE
The results must start with the CURRENT PERIOD (AUG-2014) in one column and the VALUE for the current period multiplied by 1/12 (VALUE*(1/12)).
The next column should return the VALUE for CURRENT PERIOD - 1 (JUL-2014) and multiply by 2/12 (VALUE*(2/12))
This should continue for the last 11 months and would end with OCT-2013 with the value being multiplied (VALUE*(11/12))
Is the easiest solution to this a CASE statement looking at PERIOD then PERIOD minus one month, minus two months...etc?
August 20, 2014 at 1:04 am
This is straight forward using the ROW_NUMBER function and setting the order by descending order of the PERIOD. Here is an example that should get you passed this hurdle.
😎
USE tempdb;
GO
--PERIOD ('MON-yyyy') and VALUE
/* CREATE TEST TABLE (VARIABLE) */
DECLARE @SOURCE_DATA TABLE
(
SRC_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,PERIOD CHAR(8) NOT NULL
,VALUE DECIMAL(18,5) NOT NULL
);
/* INSERT SOME TEST DATA */
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(12) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1, T T2)
,CURR_PERIOD(PDATE) AS ( SELECT GETDATE() AS PDATE)
INSERT INTO @SOURCE_DATA(PERIOD,VALUE)
SELECT
UPPER(SUBSTRING(DATENAME(MONTH,DATEADD(MONTH, (NM.N - 12),CP.PDATE)),1,3)) + CHAR(45) + CONVERT(CHAR(4),YEAR(DATEADD(MONTH, (NM.N - 12),CP.PDATE))) AS PERIOD
,10000.00 AS VALUE
FROM NUMS NM
OUTER APPLY CURR_PERIOD CP
/* CTE TO PREP THE DATA, INTRODUCING A DESCENDING ROW NUMBER */
;WITH SRC_BASE AS
(
SELECT
SD.SRC_ID
,SD.PERIOD
,SD.VALUE
,CONVERT(DATE,'01-' + PERIOD) AS P_DATE
,ROW_NUMBER() OVER (ORDER BY CONVERT(DATE,'01-' + PERIOD) DESC) * 1.0 AS P_RID
FROM @SOURCE_DATA SD
)
/* DO THE CALCULATION */
SELECT
SB.PERIOD
,(SB.P_RID / 12.0) * (SB.VALUE * (SB.P_RID / 12.0)) AS VALUE
FROM SRC_BASE SB
Results
PERIOD VALUE
-------- ---------------
AUG-2014 69.443889
JUL-2014 277.775556
JUN-2014 625.000000
MAY-2014 1111.108889
APR-2014 1736.105556
MAR-2014 2500.000000
FEB-2014 3402.773889
JAN-2014 4444.435556
DEC-2013 5625.000000
NOV-2013 6944.438889
OCT-2013 8402.765556
SEP-2013 10000.000000
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply