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

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.

Answer

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