按周期天数订购日期/小组任务

debugcn 发表于 Dev

阿拉恩

我有一组任务，每个周期执行一次，并且可以重复执行。现在，我首先必须计算这些任务的发生并对它们进行排序，然后对结果进行透视以在临时表中获得以下结果：

此数据是一个周期集合，发生于2016年12月5日至2016年12月16日（7天周期），每个周期中有5个任务，我希望数据像这样聚集在一起：

我想不出如何从第一个数据集中获得最终结果。正如您在第一个数据集中看到的那样，列中的日期顺序可以是无序的，就像2016年12月9日在2016年12月7日之前在2016年8月12日之前一样，但是任务始终会在一个特定的日期中发生模式，即“ Ecs和打印标签”将始终在“库存预览”和“订购并接收药品”之后出现。

我总是可以使用循环和东西，但是如果有人可以帮助我找到对此ID的正确查询，我们将不胜感激。

这是一些创建上面显示的数据的示例代码：

CREATE TABLE tasks (
    CurrentOccurrenceDate DATETIME,
    TaskID INT,
    EmpID INT,
    FacName VARCHAR(50),
    Census DATETIME,
    [Cycle Drop] DATETIME,
    [ECS and Print Label] DATETIME,
    [Inventory Preview] DATETIME,
    [Order and Receive Meds] DATETIME
)

INSERT INTO tasks (CurrentOccurrenceDate, TaskID, EmpID, FacName, Census, [Cycle Drop], [ECS and Print Label], [Inventory Preview], [Order and Receive Meds]) VALUES
('2016-12-05', 1, 1, '75TH TERRACE', '2016-12-05', NULL, NULL, NULL, NULL),
('2016-12-06', 2, 1, '75TH TERRACE', NULL, '2016-12-06', NULL, NULL, NULL),
('2016-12-07', 3, 1, '75TH TERRACE', NULL, NULL, NULL, '2016-12-07', NULL),
('2016-12-08', 4, 1, '75TH TERRACE', NULL, NULL, NULL, NULL, '2016-12-08'),
('2016-12-09', 5, 1, '75TH TERRACE', NULL, NULL, '2016-12-09', NULL, NULL),
('2016-12-12', 1, 1, '75TH TERRACE', '2016-12-12', NULL, NULL, NULL, NULL),
('2016-12-13', 2, 1, '75TH TERRACE', NULL, '2016-12-13', NULL, NULL, NULL),
('2016-12-14', 3, 1, '75TH TERRACE', NULL, NULL, NULL, '2016-12-14', NULL),
('2016-12-15', 4, 1, '75TH TERRACE', NULL, NULL, NULL, NULL, '2016-12-15'),
('2016-12-16', 5, 1, '75TH TERRACE', NULL, NULL, '2016-12-16', NULL, NULL)

3N1GM4

我将使用CTE标记周期，然后使用子查询查询每个周期的相关日期值：

WITH tasksEnhanced (CurrentOccurrenceDate, TaskID, EmpID, FacName, Census, [Cycle Drop], [ECS and Print Label], [Inventory Preview], [Order and Receive Meds], cycleNum) AS
(
    SELECT  t1.*,
            (
                SELECT COUNT(*) 
                FROM tasks t2 
                WHERE t1.FacName = t2.FacName 
                AND t1.TaskID = t2.TaskID 
                AND t2.CurrentOccurrenceDate < t1.CurrentOccurrenceDate
            ) + 1 AS cycleNum
    FROM tasks t1
)
SELECT DISTINCT
        te1.FacName,
        te1.cycleNum,
        (SELECT Census                   FROM tasksEnhanced census    WHERE census.FacName    = te1.FacName AND census.cycleNum    = te1.cycleNum AND census.Census                      IS NOT NULL) AS Census,
        (SELECT [Cycle Drop]             FROM tasksEnhanced cycleDrop WHERE cycleDrop.FacName = te1.FacName AND cycleDrop.cycleNum = te1.cycleNum AND cycleDrop.[Cycle Drop]             IS NOT NULL) AS [Cycle Drop],
        (SELECT [Inventory Preview]      FROM tasksEnhanced inv       WHERE inv.FacName       = te1.FacName AND inv.cycleNum       = te1.cycleNum AND inv.[Inventory Preview]            IS NOT NULL) AS [Inventory Preview],
        (SELECT [Order and Receive Meds] FROM tasksEnhanced orderMeds WHERE orderMeds.FacName = te1.FacName AND orderMeds.cycleNum = te1.cycleNum AND orderMeds.[Order and Receive Meds] IS NOT NULL) AS [Order and Receive Meds],
        (SELECT [ECS and Print Label]    FROM tasksEnhanced ECS       WHERE ECS.FacName       = te1.FacName AND ECS.cycleNum       = te1.cycleNum AND ECS.[ECS and Print Label]          IS NOT NULL) AS [ECS and Print Label]
FROM tasksEnhanced te1
ORDER BY te1.FacName, te1.cycleNum

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。