Member 8478234 Ответов: 1

Как найти разницу между двумя датами в одном столбце В SQL

Привет,

У меня есть следующие данные, как показано ниже.

Создать таблицу #TaskTable
(Идентификатор_задачи ИНТ, ИНТ Кодтипа, [тип] ИНТ MailBoxId ИНТ [дата] DateTime и StatusName тип varchar(50), StatusId ИНТ);
ГО

Вставить в #TaskTable значения(3, 1, 5, 1, '2017-06-05', 'ПА', 189);
Вставить в #TaskTable значения(3, 1, 5, 1, '2017-06-07', 'IND', 199);

Вставить в #TaskTable значения(8, 1, 8, 1, '2017-06-18', 'ПА', 189);
Вставить в #TaskTable значения(8, 1, 8, 1, '2017-06-19', 'IND', 199);
Вставить в #TaskTable значения(8, 1, 8, 1, '2017-06-19', 'WFI', 190);
Вставить в #TaskTable значения(8, 1, 8, 1, '2017-06-19', 'CP', 191);

Вставить в #TaskTable значения(3, 1, 5, 1, '2017-06-19', 'WFI', 190);
Вставить в #TaskTable значения(3, 1, 5, 1, '2017-06-20', 'CP', 191);

Вставить в #TaskTable значения(10, 1, 8, 1, '2017-06-19', 'ПА', 189);
Вставить в #TaskTable значения(10, 1, 8, 1, '2017-06-20', 'IND', 199);
Вставить в #TaskTable значения(10, 1, 8, 1, '2017-06-22', 'WFI', 190);

Вставить в #TaskTable значения(3, 1, 5, 1, '2017-06-21', 'CLD', 197);

Вставить в #TaskTable значения(8, 1, 8, 1, '2017-06-21', 'CLD', 197);

Вставить в #TaskTable значения(10, 1, 8, 1, '2017-06-25', 'CLD', 197);

Вставить в #TaskTable значения(11, 1, 5, 1, '2017-06-21', 'ПА', 189);
Вставить в #TaskTable значения(11, 1, 5, 1, '2017-06-22', 'IND', 199);
Вставить в #TaskTable значения(11, 1, 5, 1, '2017-06-23', 'CLD', 197);


TaskId	CategoryId	TypeId	MailBoxId	StartTime	StatusName	StatusTypeId
3	1	5	1	2017-06-05	PA	189
3	1	5	1	2017-06-07	IND	199
8	1	8	1	2017-06-18	PA	189
8	1	8	1	2017-06-19	IND	199
8	1	8	1	2017-06-19	WFI	190
8	1	8	1	2017-06-19	CP	191
3	1	8	1	2017-06-19	WFI	190
3	1	8	1	2017-06-20	CP	191
10	1	8	1	2017-06-19	PA	189
10	1	8	1	2017-06-20	IND	199
10	1	8	1	2017-06-22	WFI	190
3	1	5	1	2017-06-21	CLD	197
8	1	8	1	2017-06-21	CLD	197
10	1	8	1	2017-06-25	CLD	197
11	1	5	1	2017-06-21	PA	189
11	1	5	1	2017-06-22	IND	199
11	1	5	1	2017-06-23	CLD	197


Мое требование-получить данные, как показано ниже.

CategoryId	TypeId	MailBoxId	PA-IND	IND-CLD	IND-WFI	WFI-CLD
1	5	1	1	1.5	0	0
1	8	1	1	1.00	1	2



Здесь ПА-Син,Син-Хи,Син-воды для инъекций,воды для инъекций-ЦРМ-это среднее число дней, взятых для одного типа.
Достижимо ли это? Пожалуйста помочь.

Что я уже пробовал:

Построение графиков данных - [результат, который я требую], это может быть сделано с помощью pivot.
Я попробовал образец pivot, как показано ниже. 

CREATE TABLE yt 
(
  [Store] int, 
  [Week] int, 
  [xCount] int
);

INSERT INTO yt
(
  [Store], 
  [Week], [xCount]
)
VALUES
    (102, 1, 96),
    (101, 1, 138),
    (105, 1, 37),
    (109, 1, 59)

select *
from 
(
  select store, week, xCount
  from yt
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;

Below is the insert query for data into table.

CREATE TABLE #TaskTable
(TaskId INT, CategoryID INT, [Type] INT, MailBoxId INT, [Date] DateTime, StatusName VarChar(50), StatusId INT);
GO

INSERT INTO #TaskTable VALUES(3,    1,  5,  1,  '2017-06-05',   'PA',   189);
INSERT INTO #TaskTable VALUES(3,	1,	5,	1,	'2017-06-07',	'IND',	199);

INSERT INTO #TaskTable VALUES(8,	1,	8,	1,	'2017-06-18',	'PA',	189);
INSERT INTO #TaskTable VALUES(8,	1,	8,	1,	'2017-06-19',	'IND',	199);
INSERT INTO #TaskTable VALUES(8,	1,	8,	1,	'2017-06-19',	'WFI',	190);
INSERT INTO #TaskTable VALUES(8,	1,	8,	1,	'2017-06-19',	'CP',	191);

INSERT INTO #TaskTable VALUES(3,	1,	5,	1,	'2017-06-19',	'WFI',	190);
INSERT INTO #TaskTable VALUES(3,	1,	5,	1,	'2017-06-20',	'CP',	191);

INSERT INTO #TaskTable VALUES(10,	1,	8,	1,	'2017-06-19',	'PA',	189);
INSERT INTO #TaskTable VALUES(10,	1,	8,	1,	'2017-06-20',	'IND',	199);
INSERT INTO #TaskTable VALUES(10,	1,	8,	1,	'2017-06-22',	'WFI',	190);

INSERT INTO #TaskTable VALUES(3,	1,	5,	1,	'2017-06-21',	'CLD',	197);

INSERT INTO #TaskTable VALUES(8,	1,	8,	1,	'2017-06-21',	'CLD',	197);

INSERT INTO #TaskTable VALUES(10,	1,	8,	1,	'2017-06-25',	'CLD',	197);

INSERT INTO #TaskTable VALUES(11,	1,	5,	1,	'2017-06-21',	'PA',	189);
INSERT INTO #TaskTable VALUES(11,	1,	5,	1,	'2017-06-22',	'IND',	199);
INSERT INTO #TaskTable VALUES(11,	1,	5,	1,	'2017-06-23',	'CLD',	197);

Thanks in advance

1 Ответов

Рейтинг:
0

Richard Deeming

Предполагая, что я правильно понял ваш вопрос; что вы используете относительно недавнюю версию Microsoft SQL Server; и что набор переходов состояния исправлен; что-то вроде этого должно работать:

WITH cteTasks As
(
    SELECT
        CategoryID,
        Type,
        MailBoxId,
        StatusName As FromStatus,
        LEAD(StatusName) OVER (PARTITION BY CategoryID, Type, MailBoxId ORDER BY [Date]) As ToStatus,
        [Date] As StartDate,
        LEAD([Date]) OVER (PARTITION BY CategoryID, Type, MailBoxId ORDER BY [Date]) As EndDate
    FROM
        #TaskTable
),
cteTime As
(
    SELECT
        CategoryID,
        Type,
        MailBoxId,
        FromStatus + '-' + ToStatus As Status,
        DateDiff(minute, StartDate, EndDate) / 1440. As DaysTaken
    FROM
        cteTasks
    WHERE
        ToStatus Is Not Null
)
SELECT
    CategoryID,
    Type,
    MailBoxId,
    [PA-IND], 
    [IND-CLD], 
    [IND-WFI], 
    [WFI-CLD], 
    [CLD-PA], 
    [CLD-WFI], 
    [WFI-CP], 
    [CP-PA], 
    [CP-CLD]
FROM
    cteTime As T
    PIVOT
    (
        SUM(DaysTaken)
        FOR Status In ([PA-IND], [IND-CLD], [IND-WFI], [WFI-CLD], [CLD-PA], [CLD-WFI], [WFI-CP], [CP-PA], [CP-CLD])
    ) As P
;

Использование PIVOT и UNPIVOT | Microsoft Docs[^]
LEAD (Transact-SQL) | Microsoft Docs[^]

Используя ваши примерные данные, выход будет следующим:
CategoryID    Type    MailBoxId    PA-IND    IND-CLD    IND-WFI    WFI-CLD    CLD-PA    CLD-WFI    WFI-CP    CP-PA    CP-CLD
-----------------------------------------------------------------------------------------------------------------------------
1             5    	  1            3         1          12         NULL       0         NULL       1         NULL     1
1             8    	  1            2         1           0         3          NULL      1          0         0        NULL

Демонстрация[^]