Hi,
I have a following data as below.
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);
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
My requirement is to get data as below.
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
Here PA-IND,IND-CLD,IND-WFI,WFI-CLD is the average days taken for same type.
Is it achievable. Please help.
What I have tried:
Plotting data - [result i require] is it can be done by pivot.
I tried sample of pivot as below.
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