CODEWITHSUNDEEP
sql-servert-sql
jmstoh
jmstoh

Reputation: 157

Unexpected results with SELECT INTO

I've got a table valued function created in MSSQL that takes in 2 paramateres 1. End Date 2. Number of weeks to go back to generate a table of dates with the week start date.

The PERIOD table simply is a table with 1 column (called pPeriod) with all the dates between '19971229' and '20201231'

CREATE FUNCTION [dbo].[Get_Week_Rank]
(   
    -- Add the parameters for the function here
    @PERIOD_END DATETIME,
    @NUM_WEEKS INT
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT A.PPERIOD TY_PPERIOD, B.PPERIOD TY_PWKSTART, DATEADD(YY, -1, A.PPERIOD) LY_PPERIOD, DATEADD(YY, -1, B.PPERIOD) LY_PWKSTART, B.CRNK WEEK_RANK  FROM (
        SELECT PPERIOD, ROW_NUMBER() OVER (ORDER BY PPERIOD) CRNK FROM PERIODS
            WHERE PPERIOD BETWEEN DATEADD(WW, @NUM_WEEKS - 1, @PERIOD_END)  + 1 AND @PERIOD_END
        ) AS A
        JOIN (
            SELECT PPERIOD, ROW_NUMBER() OVER (ORDER BY CRNK % 7) CRNK FROM (
                SELECT PPERIOD, ROW_NUMBER() OVER (ORDER BY PPERIOD) CRNK FROM PERIODS
                    WHERE PPERIOD BETWEEN DATEADD(WW, @NUM_WEEKS, @PERIOD_END)  +  1 AND @PERIOD_END
                ) AS A
                WHERE CRNK % 7 = 1
            ) AS B ON (A.CRNK - 1)/7 = B.CRNK
)

I noticed then when @NUM_WEEKS is between -1 and -130, the results are correct when running this query:

SELECT * INTO #WEEKS FROM GET_WEEK_RANK('20160401', -104)
SELECT * FROM #WEEKS ORDER BY 1

However, any number below -130 (eg -156, -208), the returned results are all wrong.

Wrong results

You can see that TY_PWKSTART is all jumbled up and not in sync with TY_PPERIOD. If I run the query directly, the results return fine:

SELECT * FROM GET_WEEK_RANK('20160401', -140)

What could be the issue? I am using Microsoft SQL Server 2014

EDIT: Posting images of results As you can see, both queries essentially do the same thing, but the results returned are different. The order of pWkStart in the first query when using SELECT INTO is wrong.

Wrong results:

SELECT * INTO #WEEK_WRONG FROM GET_WEEK_RANK('20160410', -140)
SELECT * FROM #WEEK_WRONG ORDER BY 1

WRONG RESULTS

Correct results:

CREATE TABLE #WEEK_CORRECT (TY_PPERIOD DATETIME, TY_PWKSTART DATETIME, LY_PPERIOD DATETIME, LY_PWKTART DATETIME, WEEK_RANK INT)
INSERT INTO #WEEK_CORRECT
SELECT * FROM GET_WEEK_RANK('20160410', -140)
SELECT * FROM #WEEK_CORRECT ORDER BY 1

CORRECT RESULTS

EDIT2: Turns out that my initial query was producing unexpected results. I've fixed my query and managed to get consistent results from SELECT INTO and INSERT INTO. Just sharing the code here:

CREATE FUNCTION [dbo].[Get_Week_Rank]
(   
    @PERIOD_END DATETIME,
    @NUM_WEEKS INT
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT A.PPERIOD TY_PPERIOD, B.PPERIOD TY_PWKSTART, DATEADD(YY, -1, A.PPERIOD) LY_PPERIOD, DATEADD(YY, -1, B.PPERIOD) LY_PWKSTART, B.CRNK + 1 WEEK_RANK  FROM (
            SELECT PPERIOD, (ROW_NUMBER() OVER (ORDER BY PPERIOD)-1)/7 CRNK FROM PERIODS
                WHERE PPERIOD BETWEEN DATEADD(WW, @NUM_WEEKS, @PERIOD_END)  + 1 AND @PERIOD_END
        ) AS A
        JOIN (
            SELECT PPERIOD, ROW_NUMBER() OVER (ORDER BY PPERIOD)-1 CRNK FROM (
                SELECT PPERIOD , ROW_NUMBER() OVER (PARTITION BY CRNK ORDER BY CRNK) CRNK FROM (
                    SELECT PPERIOD, (ROW_NUMBER() OVER (ORDER BY PPERIOD)-1)/7 CRNK FROM PERIODS
                        WHERE PPERIOD BETWEEN DATEADD(WW, @NUM_WEEKS, @PERIOD_END)  +  1 AND @PERIOD_END
                    ) AS A
                ) AS A
                WHERE CRNK = 1
            ) AS B ON A.CRNK = B.CRNK
)

Upvotes: 1

Views: 161

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

This part of your query is broken:

SELECT PPERIOD, ROW_NUMBER() OVER (ORDER BY CRNK % 7) CRNK FROM (
    ...
) AS A
WHERE CRNK % 7 = 1

Since the where clause establishes that CRNK % 7 is equal to 1, the ROW_NUMBER() expression is free to assign row numbers in any order1. I would guess that you still would want to assign row numbers in the order in which PPERIOD or CRNK values work, and so the expression should instead by:

SELECT PPERIOD, ROW_NUMBER() OVER (ORDER BY CRNK) CRNK FROM (
    ...
) AS A
WHERE CRNK % 7 = 1

1Since you haven't provided enough expressions in the ORDER BY for row numbers to be assigned unambiguously, there's no guarantee on the values assigned to each row.

Upvotes: 1

Related Questions