May 30, 2016 at 10:58 am
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
May 30, 2016 at 11:02 am
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
have you tried unpivoting your pivot tables?
what have you got so far?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 30, 2016 at 11:57 am
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
May 30, 2016 at 4:41 pm
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
Working from pivot tables, which are a result in themselves, is the wrong way to approach any of this.
I recommend solving the problem using the data in the original tables and then pivoting the results if that's what you need for a final result. Think of it like any other code... keep the presentation layer separate from the other layers. Figure out the results first, the figure out how to display them. Life will be much easier across the board for this and similar problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2016 at 11:09 pm
it is a realtime scenario..it is payroll stuff to calculate SP to calculate working days based on the shift.. please help me in this
May 30, 2016 at 11:27 pm
IT IS WORKING FINE BUT IN THE OUTPUT I ALSO NEED THE DETAILS OF D1..D31 SHIFTS AND FINALLY THE WORKING DAYS
LIKE THIS
empid D1 D2.............D31 working days
290 dayshift dayshift........Nightshift 22
May 31, 2016 at 3:15 am
ganapathy.arvindan (5/30/2016)
IT IS WORKING FINE BUT IN THE OUTPUT I ALSO NEED THE DETAILS OF D1..D31 SHIFTS AND FINALLY THE WORKING DAYSLIKE THIS
empid D1 D2.............D31 working days
290 dayshift dayshift........Nightshift 22
join your employee table to the results from the code I gave you.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 31, 2016 at 4:13 am
yeeep
http://bci-consulting.pl/
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply