在两个案例参数(日期范围和行数)之间循环SQL Server 2012 [英] Looping between 2 case parameters (Date Range and Row Count) | SQL Server 2012

问题描述

我试图了解如何正确执行此查询到该表中.输出应该仅基于计数表循环，但将显示表行的参数内的所有日期.

我为 APPLE ， ORANGE 和 MANGO 分离了数据库，因为我需要模仿我的数据库结构.应该从不同的数据库调用数据，然后处理日期范围"和行计数"的循环.

我从这个问题中得到了这个主意，并使用了一些代码来尝试复制它:

这是我要解决的查询

  DECLARE @dbApple TABLE(FromDate VARCHAR(30)NOT NULL，ToDate VARCHAR(30)NOT NULL，名称VARCHAR(30)NOT NULL，计数VARCHAR(30)非空)插入@dbApple(FromDate，ToDate，名称，计数)值('2019-10-05'，'2019-10-09'，'APPLE'，'3');声明@dbOrange表(FromDate VARCHAR(30)NOT NULL，ToDate VARCHAR(30)NOT NULL，名称VARCHAR(30)NOT NULL，计数VARCHAR(30)非空)插入@dbOrange(FromDate，ToDate，名称，计数)值('2019-10-10'，'2019-10-14'，'ORANGE'，'2');声明@dbMango表(FromDate VARCHAR(30)NOT NULL，ToDate VARCHAR(30)NOT NULL，名称VARCHAR(30)NOT NULL，计数VARCHAR(30)非空)插入@dbMango(FromDate，ToDate，名称，计数)值('2019-10-15'，'2019-10-19'，'MANGO'，'4');(选择CONVERT(DATE，CONVERT(DATE，DATEADD(D，v.number，FromDate)))AS日期，DB.Name，案件当ROW_NUMBER()超过(按计数划分，FromDate，ToDate按计数进行排序)=计数时然后计数否则为空完结计数从@dbApple DB加入MASTER..spt_values v ON v.TYPE ='P'AND v.number之间的0和DATEDIFF(D，FromDate，ToDate))联盟(选择CONVERT(DATE，DATEADD(D，v.number，FromDate))AS日期，DB.Name，案件当ROW_NUMBER()超过(按计数划分，FromDate，ToDate按计数进行排序)=计数时然后计数否则为空完结计数从@db橙色DB加入MASTER..spt_values v ON v.TYPE ='P'AND v.number之间的0和DATEDIFF(D，FromDate，ToDate))联盟(选择CONVERT(DATE，DATEADD(D，v.number，FromDate))AS日期，DB.Name，案件当ROW_NUMBER()超过(按计数划分，FromDate，ToDate按计数进行排序)=计数时然后计数否则为空完结计数从@dbMango数据库加入MASTER..spt_values v ON v.TYPE ='P'AND v.number之间的0和DATEDIFF(D，FromDate，ToDate)) 

这是输出:

1. 尝试使用CASE WITHIN CASE，但没有运气.

 声明@dbApple TABLE(FromDate varchar(30)NOT NULL，ToDate varchar(30)NOT NULL，名称varchar(30)NOT NULL，计数varchar(30)非空)插入@dbApple(FromDate，ToDate，Name，Count)值('2019-10-05'，'2019-10-09'，'APPLE'，'3');(选择作为日期的CONVERT(date，CONVERT(date，DATEADD(D，VAL.NUMBER，FromDate)))DB.Name，转换之间的情况CONVERT(date，CONVERT(date，DATEADD(D，VAL.NUMBER，FromDate)))和CONVERT(日期，CONVERT(日期，DATEADD(D，VAL.NUMBER，ToDate)))然后在ROW_NUMBER()的情况下超过(按计数，从日期，到日期的部分)ORDER BY Count)=计数然后计数ELSE'1'结束ELSE NULL END AS计数从@dbApple DB在VAL.TYPE ='P'上加入MASTER..SPT_VALUES VAL0和DATEDIFF之间的AND VAL.NUMBER(D，FromDate，ToDate)) 

使用CASE WITHIN CASE输出.

解决方案

如何递归解决呢?

首先将水果放在篮子里，然后将它们去皮.

 带篮子(选择FromDate，ToDate，名称，计数来自@dbApple全联盟选择FromDate，ToDate，名称，计数来自@dbOrange全联盟选择FromDate，ToDate，名称，计数从@dbMango)，剥皮的(选择FromDate作为[Date]，1作为Lvl，FromDate，ToDate，名称，计数从篮子里全联盟选择DATEADD(day，1，[Date])，Lvl +1，FromDate，ToDate，名称，计数从剥皮的p[日期]<迄今为止)选择[日期]，[名称]，Lvl< =计数然后以1结束为例[计数]时的情况从剥皮ORDER BY [Date]; 

关于妊娠期的测试此处

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.

解决方案

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

这篇关于在两个案例参数(日期范围和行数)之间循环SQL Server 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！