ganapathy.arvindan (5/30/2016)
Yes your right, it is a pivot table, please let me know whether it is possible to calculate working days in pivot table itself, please help me in solving this
I can only assume that this is homework and not a real business scenario....is that correct?
I am not sure why you are insisting in creating 4 pivot tables before you want to further analyse your data...but hey ho
by the numbers.....(http://dictionary.cambridge.org/dictionary/english/by-numbers)
USE [tempdb]
GO
IF OBJECT_ID('tempdb..dayshift') IS NOT NULL DROP TABLE dayshift;
IF OBJECT_ID('tempdb..nightshift') IS NOT NULL DROP TABLE nightshift;
IF OBJECT_ID('tempdb..nightshiftIH') IS NOT NULL DROP TABLE nightshiftIH;
IF OBJECT_ID('tempdb..employee') IS NOT NULL DROP TABLE employee;
CREATE TABLE [dbo].[dayshift](
[D1] [varchar](3) NULL,[D2] [varchar](3) NULL,[D3] [varchar](3) NULL,[D4] [varchar](3) NULL,[D5] [varchar](3) NULL,
[D6] [varchar](3) NULL,[D7] [varchar](3) NULL,[D8] [varchar](3) NULL,[D9] [varchar](3) NULL,[D10] [varchar](3) NULL,
[D11] [varchar](3) NULL,[D12] [varchar](3) NULL,[D13] [varchar](3) NULL,[D14] [varchar](3) NULL,[D15] [varchar](3) NULL,
[D16] [varchar](3) NULL,[D17] [varchar](3) NULL,[D18] [varchar](3) NULL,[D19] [varchar](3) NULL,[D20] [varchar](3) NULL,
[D21] [varchar](3) NULL,[D22] [varchar](3) NULL,[D23] [varchar](3) NULL,[D24] [varchar](3) NULL,[D25] [varchar](3) NULL,
[D26] [varchar](3) NULL,[D27] [varchar](3) NULL,[D28] [varchar](3) NULL,[D29] [varchar](3) NULL,[D30] [varchar](3) NULL,
[D31] [varchar](3) NULL
)
CREATE TABLE [dbo].[NightShift](
[D1] [varchar](3) NULL,[D2] [varchar](3) NULL,[D3] [varchar](3) NULL,[D4] [varchar](3) NULL,[D5] [varchar](3) NULL,
[D6] [varchar](3) NULL,[D7] [varchar](3) NULL,[D8] [varchar](3) NULL,[D9] [varchar](3) NULL,[D10] [varchar](3) NULL,
[D11] [varchar](3) NULL,[D12] [varchar](3) NULL,[D13] [varchar](3) NULL,[D14] [varchar](3) NULL,[D15] [varchar](3) NULL,
[D16] [varchar](3) NULL,[D17] [varchar](3) NULL,[D18] [varchar](3) NULL,[D19] [varchar](3) NULL,[D20] [varchar](3) NULL,
[D21] [varchar](3) NULL,[D22] [varchar](3) NULL,[D23] [varchar](3) NULL,[D24] [varchar](3) NULL,[D25] [varchar](3) NULL,
[D26] [varchar](3) NULL,[D27] [varchar](3) NULL,[D28] [varchar](3) NULL,[D29] [varchar](3) NULL,[D30] [varchar](3) NULL,
[D31] [varchar](3) NULL
)
CREATE TABLE [dbo].[NightShiftIH](
[D1] [varchar](3) NULL,[D2] [varchar](3) NULL,[D3] [varchar](3) NULL,[D4] [varchar](3) NULL,[D5] [varchar](3) NULL,
[D6] [varchar](3) NULL,[D7] [varchar](3) NULL,[D8] [varchar](3) NULL,[D9] [varchar](3) NULL,[D10] [varchar](3) NULL,
[D11] [varchar](3) NULL,[D12] [varchar](3) NULL,[D13] [varchar](3) NULL,[D14] [varchar](3) NULL,[D15] [varchar](3) NULL,
[D16] [varchar](3) NULL,[D17] [varchar](3) NULL,[D18] [varchar](3) NULL,[D19] [varchar](3) NULL,[D20] [varchar](3) NULL,
[D21] [varchar](3) NULL,[D22] [varchar](3) NULL,[D23] [varchar](3) NULL,[D24] [varchar](3) NULL,[D25] [varchar](3) NULL,
[D26] [varchar](3) NULL,[D27] [varchar](3) NULL,[D28] [varchar](3) NULL,[D29] [varchar](3) NULL,[D30] [varchar](3) NULL,
[D31] [varchar](3) NULL
)
INSERT [dbo].[dayshift] ([D1], [D2], [D3], [D4], [D5], [D6], [D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16], [D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26], [D27], [D28], [D29], [D30], [D31])
VALUES (N'HL', N'W', N'W', N'W', N'W', N'W', N'W', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'HL', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'W', N'WO', N'W', N'W')
GO
INSERT [dbo].[NightShift] ([D1], [D2], [D3], [D4], [D5], [D6], [D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16], [D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26], [D27], [D28], [D29], [D30], [D31])
VALUES (N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W')
GO
INSERT [dbo].[NightShiftIH] ([D1], [D2], [D3], [D4], [D5], [D6], [D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16], [D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26], [D27], [D28], [D29], [D30], [D31])
VALUES (N'HL', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'HL', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W')
GO
CREATE TABLE EMPLOYEE
(empid1 INT,
d1 VARCHAR(25), d2 VARCHAR(25), d3 VARCHAR(25), d4 VARCHAR(25),
d5 VARCHAR(25), d6 VARCHAR(25), d7 VARCHAR(25), d8 VARCHAR(25),
d9 VARCHAR(25), d10 VARCHAR(25), d11 VARCHAR(25), d12 VARCHAR(25),
d13 VARCHAR(25), d14 VARCHAR(25), d15 VARCHAR(25), d16 VARCHAR(25),
d17 VARCHAR(25), d18 VARCHAR(25), d19 VARCHAR(25), d20 VARCHAR(25),
d21 VARCHAR(25), d22 VARCHAR(25), d23 VARCHAR(25), d24 VARCHAR(25),
d25 VARCHAR(25), d26 VARCHAR(25), d27 VARCHAR(25), d28 VARCHAR(25),
d29 VARCHAR(25), d30 VARCHAR(25), d31 VARCHAR(25)
);
INSERT INTO EMPLOYEE
VALUES
(290, 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT',
'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH',
'NIGHTSHIFT-IH', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'NIGHTSHIFT-IH',
'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH',
'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH'
);
--SELECT * FROM dayshift
--SELECT * FROM nightshift
--SELECT * FROM NightShiftIH
--SELECT * FROM employee
WITH cte_shifts as (
SELECT shift_type,dayno, daytype
FROM
(
SELECT 'dayshift' as shift_type, [D1], [D2], [D3], [D4], [D5], [D6],
[D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16],
[D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26],
[D27], [D28], [D29], [D30], [D31]
FROM dayshift
) AS cp
UNPIVOT
(
daytype FOR dayno IN ([D1], [D2], [D3], [D4], [D5], [D6],
[D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16],
[D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26],
[D27], [D28], [D29], [D30], [D31])
) AS up
UNION ALL
SELECT shift_type,dayno, daytype
FROM
(
SELECT 'nightshift' as shift_type, [D1], [D2], [D3], [D4], [D5], [D6],
[D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16],
[D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26],
[D27], [D28], [D29], [D30], [D31]
FROM nightshift
) AS cp
UNPIVOT
(
daytype FOR dayno IN ([D1], [D2], [D3], [D4], [D5], [D6],
[D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16],
[D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26],
[D27], [D28], [D29], [D30], [D31])
) AS up
UNION ALL
SELECT shift_type,dayno, daytype
FROM
(
SELECT 'nightshift-IH' as shift_type, [D1], [D2], [D3], [D4], [D5], [D6],
[D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16],
[D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26],
[D27], [D28], [D29], [D30], [D31]
FROM nightshiftIH
) AS cp
UNPIVOT
(
daytype FOR dayno IN ([D1], [D2], [D3], [D4], [D5], [D6],
[D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16],
[D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26],
[D27], [D28], [D29], [D30], [D31])
) AS up)
,
cte_empdets as (
SELECT empid1,dayno, shift_type
FROM
(
SELECT empid1, [D1], [D2], [D3], [D4], [D5], [D6],
[D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16],
[D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26],
[D27], [D28], [D29], [D30], [D31]
FROM employee
) AS cp
UNPIVOT
(
shift_type FOR dayno IN ([D1], [D2], [D3], [D4], [D5], [D6],
[D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16],
[D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26],
[D27], [D28], [D29], [D30], [D31])
) AS up
)
SELECT e.empid1,
COUNT(s.daytype) AS Expr1
FROM cte_empdets AS e
INNER JOIN cte_shifts AS s ON e.dayno = s.dayno
AND e.shift_type = s.shift_type
WHERE(s.daytype = 'W')
GROUP BY e.empid1;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day