Reading 0.5 value in ROW_NUMBER() and PARTITION BY | SQL Server 2012

Question

I have this question before Looping between 2 case parameters (Date Range and Row Count) | SQL Server 2012 , now I'm thinking about a scenario, what if the value has a 0.5? or .5? Will this work using ROW_NUMBER()?

I'm trying to make this work using only CASE method.

This is my old script:

DECLARE @dbApple TABLE
                 (
                     FromDate varchar(30) NOT NULL,
                     ToDate varchar(30) NOT NULL,
                     Name varchar(30) NOT NULL,
                     Count float(30) NOT NULL
                 )

INSERT INTO @dbApple (FromDate, ToDate, Name, Count) 
VALUES ('2019-10-05', '2019-10-09', 'APPLE', '2.5');

(SELECT
    CONVERT(date, CONVERT(date, DATEADD(D, VAL.NUMBER, FromDate))) AS Date,
    DB.Name,
    CASE 
       WHEN CONVERT(date, CONVERT(date, DATEADD(D, VAL.NUMBER, FromDate))) BETWEEN CONVERT(date, CONVERT(date, DATEADD(D, VAL.NUMBER, FromDate))) AND CONVERT(date, CONVERT(date, DATEADD(D, VAL.NUMBER, ToDate)))
          THEN 
             CASE 
                WHEN ROW_NUMBER() OVER (PARTITION BY Count, FromDate, ToDate ORDER BY Count) <= Count
                   THEN (COUNT / COUNT) 
             END 
    END AS Count
FROM 
    @dbApple DB
JOIN 
    MASTER..SPT_VALUES VAL ON VAL.TYPE = 'P'
                           AND VAL.NUMBER BETWEEN 0 AND DATEDIFF(D, FromDate, ToDate))

This is the output:

This is my expected output:

Is there a way for this to work? Thank you.

Nick · Accepted Answer · 2020-02-14T01:39:17.837

1

You can greatly simplify your query by noting that VAL.NUMBER is already your row number (just starting at 0 instead of 1). You can then compare your Count value to VAL.NUMBER and if Count - VAL.NUMBER is greater than 1, output 1; if it's greater than 0 output the difference, otherwise output NULL. For this demo query I've simulated your numbers table with a table value constructor:

declare @dbApple TABLE(
    FromDate varchar(30) NOT NULL,
    ToDate varchar(30) NOT NULL,
    Name varchar(30) NOT NULL,
    Count float(30) NOT NULL
)

INSERT INTO @dbApple
(FromDate,ToDate,Name,Count) VALUES ('2019-10-05','2019-10-09','APPLE',2.5);

SELECT
    CONVERT(date,CONVERT(date,DATEADD(D,VAL.NUMBER,FromDate))) AS Date,
    Name,
    CASE WHEN Count - VAL.NUMBER > 1 THEN 1
         WHEN Count - VAL.NUMBER > 0 THEN Count - VAL.NUMBER
    END AS Count
FROM 
    @dbApple D
JOIN (VALUES (0), (1), (2), (3), (4), (5)) VAL(NUMBER)
  ON VAL.NUMBER BETWEEN 0 AND DATEDIFF(D, FromDate, ToDate)

Output:

Date        Name    Count
2019-10-05  APPLE   1
2019-10-06  APPLE   1
2019-10-07  APPLE   0.5
2019-10-08  APPLE   (null)
2019-10-09  APPLE   (null)

Demo on dbfiddle

edited Feb 14 '20 at 01:39

answered Feb 14 '20 at 01:22

Nick

138,499
22
57
95

Thank you for this, I have a question though, how will we make ````JOIN (VALUES (0), (1), (2), (3), (4), (5)) VAL(NUMBER)```` dynamic? What if the count is more than 30? and the date range is 2 months? – pjustindaryll Feb 14 '20 at 01:32
1

@pjustindaryll I just put that in so I could make a working query because I don't have access to your numbers table. You would just replace the part after `JOIN` with what you have in your existing query i.e. `MASTER..SPT_VALUES VAL on VAL.TYPE='P' AND VAL.NUMBER BETWEEN 0 AND DATEDIFF(D, FromDate, ToDate))` – Nick Feb 14 '20 at 01:33
Okay, go it. Thank you so much, I'll study your structure and implement this on my project, you've been a great help! – pjustindaryll Feb 14 '20 at 01:35
One question sir, is there a way to do this without using SPT_VALUES?? – pjustindaryll Feb 14 '20 at 01:54
1

@pjustindaryll the answer to your previous question shows how to do that using a recursive CTE to generate the list of dates. If you were to take that approach you would need to use the `Lvl` column where I have used `VAL.NUMBER` in this one (although you would need to start `Lvl` at 0 instead of 1). – Nick Feb 14 '20 at 02:17

Reading 0.5 value in ROW_NUMBER() and PARTITION BY | SQL Server 2012

1 Answers1