Haminteu Haminteu - 7 months ago 11
SQL Question

Simultaneous result from single parameter SQL Server stored procedure

I have the following t-sql:

declare @startdate datetime, @finishdate datetime;
declare @declinerate float, @setpoint float;
declare @recoveredmw;

set @startdate = '2016-01-01';
set @finishdate = '2016-12-31'
set @declinerate = 0.0972 / 100;
set @setpoint = 98;
set @recoveredmw = @setpoint - 1;

;WITH ctetest AS
(
SELECT
@StartDate AS CDate,
@setpoint as Case2,
0 as OLNo

UNION ALL

SELECT
dateadd(day,1,CDate),
CASE
WHEN Case2 < @recoveredMW THEN @setpoint
ELSE (1 - @declinerate1) * Case2
END,
OLNo + (CASE WHEN Case2 < @recoveredMW THEN 1 ELSE 0 END)
FROM
ctetest
WHERE
dateadd(day, 1, CDate) <= @finishdate
)
SELECT *
FROM ctetest OPTION (MAXRECURSION 0)


Then the result should be:

---------------------------------
CDate Case2 OLNo
---------------------------------
2016-01-01 98 0
2016-01-02 97.9 0
2016-01-03 97.81 0
ff.
2016-12-30 97.62 30
2016-12-31 97.52 30


It creates a data from 1st of January until the end of december base on
@startdate
and
@finishdate
parameter.

What I want is create a simultaneous
@recoveredMW
. Let say the
@setpoint
is 98 then it will create the result above which is
@recoveredMW
is minus 1 from the
@setpoint
. After
2016-12-31
, it will create another raw data start from the beginning but the
@recoveredMW
is minus 2 and so on.

Result sample:

---------------------------------
CDate Case2 OLNo
---------------------------------
2016-01-01 98 0
2016-01-02 97.9 0
2016-01-03 97.81 0
ff.
2016-12-30 97.62 30
2016-12-31 97.52 30
2016-01-01 97 0 <-- New RawData after 2016-12-31, @recoveredMW-2
2016-01-02 96.91 0
2016-01-03 96.81 0
ff.
2016-12-31 96.53 30


Is there a way to do this?

Thank you.

Answer

add another anchor member to your ctetest after the first anchor member

        SELECT      @startdate AS CDate, 
                    @setpoint - 1 as Case2, 
                    0 as OLNo

if you want the result to order separately, add another column to differentiate the original run with @setpoint from @setpoint - 1

here is the modified query

;WITH ctetest AS 
        (
            SELECT      1 AS Data,   -- Added
            @startdate AS CDate, 
                        @setpoint as Case2, 
                        0 as OLNo

            UNION ALL

            SELECT      2 as Data,
            @startdate AS CDate, 
                        @setpoint - 1 as Case2, 
                        0 as OLNo

            UNION ALL

            SELECT      Data,
            dateadd(day,1,CDate),
                        CASE WHEN Case2 < @recoveredmw THEN @setpoint ELSE (1 - @declinerate) * Case2 END,
                        OLNo + (CASE WHEN Case2 < @recoveredmw THEN 1 ELSE 0 END)
            FROM        ctetest 
            WHERE       dateadd(day, 1, CDate) <= @finishdate
        )
        SELECT      * 
        FROM        ctetest 
    order by    Data, CDate
    OPTION (MAXRECURSION 0)

EDIT 1 :

;WITH 
numbers AS          -- added
(
    SELECT  n = 1
    UNION ALL
    SELECT  n = n + 1
    FROM    numbers
    WHERE   n < 999
),
ctetest AS 
        (
            SELECT      n AS Data,                   -- change
            @startdate AS CDate, 
                        CAST (n as FLOAT) as Case2,   -- change
                        0 as OLNo
        FROM        numbers                           -- change
        WHERE   n   <= @setpoint                 -- change

            UNION ALL

            SELECT      Data,
            dateadd(day,1,CDate),
                        CASE WHEN Case2 < @recoveredmw THEN @setpoint ELSE (1 - @declinerate) * Case2 END,
                        OLNo + (CASE WHEN Case2 < @recoveredmw THEN 1 ELSE 0 END)
            FROM        ctetest 
            WHERE       dateadd(day, 1, CDate) <= @finishdate
        )
        SELECT      * 
        FROM        ctetest 
    order by    Data DESC, CDate     -- change
    OPTION (MAXRECURSION 0)