CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Haminteu
Haminteu

Reputation: 1334

Calculate Range Number from N to 1 SQL

I have the following code:

DECLARE @StartDate DATE = '2016-01-01'
        ,@EndDate  DATE = '2016-12-31'
        ,@MaxValue  float  = 96.0
        ,@MinValue float = 95.0
        ,@pValue float =  0.000972222222222222
        ,@pValue2 float =  0.002

;WITH ctetest AS 
    (
        SELECT      @StartDate AS CDate, @MaxValue AS Case1, @MaxValue as Case2, 
                    0 as OLNo
        UNION ALL
        SELECT      dateadd(day,1,CDate) ,(1-@pValue) * Case1, 
                    CASE WHEN Case2 < @MinValue THEN 96 ELSE (1-@pValue2) * Case2 END,
                    OLNo + (CASE WHEN Case2 < @MinValue THEN 1 ELSE 0 END)
        FROM        ctetest 
        WHERE       dateadd(day,1,CDate)<=@EndDate
        )
        SELECT      * 
        INTO        #t1 
        FROM        ctetest OPTION (MAXRECURSION 0)


SELECT  @MinValue Val, MAX(OLNo) * 24 * @MinValue tRes, SUM((Case2 * 24)) Res1, 
        (SUM((Case2 * 24))) - (MAX(OLNo) * 24 * @MinValue) Res2
FROM    #t1

The result should be:

----------------------------------------------
Val     tRes         Res1                Res2   
----------------------------------------------
95.0    118560       838244.195613986    719684.195613986

My question is, how can I get a result (above) with range number starting from @MinValue to 1. Like the table below:

----------------------------------------------
Val     tRes         Res1                Res2   
----------------------------------------------
95.0    118560       838244.195613986    719684.195613986
94.0    67680        834131.776802389    766451.776802389
93.0    ??????       ????????????????    ????????????????
ff.
1.0     ??????       ????????????????    ????????????????

I want to put this code on my Store Procedures. Does anyone know how to achieve this? Really appreciated.
Thank you.

Upvotes: 1

Views: 61

Answers (1)

Squirrel
Squirrel

Reputation: 24763

changes :

  • added min_value CTE
  • added MinValue to ctetest
  • change @MinValue to MinValue in your original query
  • Final query - GROUP BY

Query

; WITH 
min_value AS   -- Added this
(
    SELECT      MinValue =  @MinValue
    UNION ALL
    SELECT      MinValue = MinValue - 1
    FROM        min_value
    WHERE       MinValue > 0
),
ctetest AS 
(
        SELECT      @StartDate AS CDate, @MaxValue AS Case1, @MaxValue as Case2,
                    0 as OLNo, MinValue  
        FROM        min_value            -- added this
        UNION ALL
        SELECT      dateadd(day,1,CDate) ,(1-@pValue) * Case1, 
                    CASE WHEN Case2 < MinValue THEN 96 ELSE (1-@pValue2) * Case2 END,
                    OLNo + (CASE WHEN Case2 < MinValue THEN 1 ELSE 0 END), MinValue
        FROM        ctetest 
        WHERE       dateadd(day,1,CDate)<=@EndDate
)
SELECT  MinValue Val, 
        MAX(OLNo) * 24 * @MinValue tRes, SUM((Case2 * 24)) Res1, 
        (SUM((Case2 * 24))) - (MAX(OLNo) * 24 * @MinValue) Res2
FROM    ctetest 
GROUP BY MinValue           -- added this
ORDER BY MinValue DESC
OPTION  (MAXRECURSION 0)

Upvotes: 2

Related Questions