Looping between 2 case parameters (Date Range and Row Count) | SQL Server 2012

Question

I'm trying to understand on how to properly execute this query into this table. The output is supposed to only loop based on count table, but will display all the dates within the parameter of the table row.

I separated the database for APPLE, ORANGE, and MANGO because I need to mimic my database structure. The data should be called from different database, and then process the looping of Date Range and Row Count.

I got the idea from this question, and used some of the codes to try to replicate it :

SQL how to convert row with date range to many rows with each date

This is the expected output.

This is the query that I'm trying to fix

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

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

DECLARE @dbOrange TABLE
                  (
                      FromDate VARCHAR(30) NOT NULL,
                      ToDate VARCHAR(30) NOT NULL,
                      Name VARCHAR(30) NOT NULL,
                      Count VARCHAR(30) NOT NULL
                  )

INSERT INTO @dbOrange (FromDate, ToDate, Name, Count) 
VALUES ('2019-10-10', '2019-10-14', 'ORANGE', '2'); 

DECLARE @dbMango TABLE
                 (
                      FromDate VARCHAR(30) NOT NULL,
                      ToDate VARCHAR(30) NOT NULL,
                      Name VARCHAR(30) NOT NULL,
                      Count VARCHAR(30) NOT NULL
                  )

INSERT INTO @dbMango (FromDate, ToDate, Name, Count) 
VALUES ('2019-10-15', '2019-10-19', 'MANGO', '4');  

(SELECT
     CONVERT(DATE, CONVERT(DATE, DATEADD(D, v.number, FromDate))) AS Date,
     DB.Name,
     CASE 
        WHEN ROW_NUMBER() OVER(PARTITION BY Count, FromDate, ToDate ORDER BY Count) = Count
           THEN Count
           ELSE NULL 
     END AS Count
 FROM 
     @dbApple DB
 JOIN 
     MASTER..spt_values v ON v.TYPE = 'P'
                          AND v.number BETWEEN 0 AND DATEDIFF(D, FromDate, ToDate))

UNION 

(SELECT
     CONVERT(DATE, DATEADD(D, v.number, FromDate)) AS Date,
     DB.Name,
     CASE 
        WHEN ROW_NUMBER() OVER(PARTITION BY Count, FromDate, ToDate ORDER BY Count) = Count
           THEN Count
           ELSE NULL 
     END AS Count
 FROM 
     @dbOrange DB
 JOIN 
     MASTER..spt_values v ON v.TYPE = 'P'
                          AND v.number BETWEEN 0 AND DATEDIFF(D, FromDate, ToDate))

UNION 

(SELECT
     CONVERT(DATE, DATEADD(D, v.number, FromDate)) AS Date,
     DB.Name,
     CASE 
        WHEN ROW_NUMBER() OVER(PARTITION BY Count, FromDate, ToDate ORDER BY Count) = Count 
           THEN Count
           ELSE NULL 
     END AS Count
 FROM 
     @dbMango DB
 JOIN 
     MASTER..spt_values v ON v.TYPE = 'P'
                          AND v.number BETWEEN 0 AND DATEDIFF(D, FromDate, ToDate))

This is the output:

1. Tried using CASE WITHIN CASE but no luck.

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

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

(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
    ELSE '1' END
    ELSE NULL 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))

Output using CASE WITHIN CASE.

Answer 1

How about solving this recursively?

First put the fruit in a basket, then peel them.

WITH BASKET AS 
(
   SELECT FromDate, ToDate, Name, Count
   FROM @dbApple
   UNION ALL
   SELECT FromDate, ToDate, Name, Count
   FROM @dbOrange
   UNION ALL
   SELECT FromDate, ToDate, Name, Count
   FROM @dbMango
),
PEELED AS
(
    SELECT 
     FromDate as [Date], 1 as Lvl,
     FromDate, ToDate, Name, Count
    FROM BASKET

    UNION ALL

    SELECT 
     DATEADD(day,1,[Date]), Lvl +1, 
     FromDate, ToDate, Name, Count
    FROM PEELED p
    WHERE [Date] < ToDate
)
SELECT [Date], [Name], 
CASE WHEN Lvl <= Count THEN 1 END AS [Count]
FROM PEELED
ORDER BY [Date];

A test on rextester here

Answer 2

Using a tally, you can achieve this very easily:

CREATE TABLE dbo.YourTable (Fruit varchar(20),
                            Quantity int);

INSERT INTO dbo.YourTable
VALUES ('Apple',3),
       ('Orange',2),
       ('Mango',4);
GO

DECLARE @StartDate date = '20190101',
        @EndDate date = '20190110';

--Going to us a tally, incase there can be large date ranges
WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP(DATEDIFF(DAY, @StartDate, @EndDate)+1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))  AS I
    FROM N N1, N N2, N N3) --1000 rows
SELECT DATEADD(DAY, T.I-1, @StartDate) aS [Date],
       YT.Fruit,
       CASE WHEN T.I <= YT.Quantity THEN 1 END AS [Count]
FROM dbo.YourTable YT
     CROSS JOIN Tally T    
ORDER BY Fruit,
         [Date];
GO
DROP TABLE dbo.YourTable