June 12, 2016 at 4:13 am
Create Stored procedure with input year and month which should calculate working days of dayshift,NightShift,NightShiftIH based on if its a holiday in Holidaymaster table it should mark in Holiday in all dayshift,NightShift,NightShiftIH
also for dayshift 2nd and 3rd week of saturday working day..For Nightshift,NightshiftIH all saturday and sunday are WeekOff
For example
Input Year : 2016 Month:01
Output Expected
Dayshift table
d1 -- Holiday d2 -- Week Off(WO) d3 -- Week Off(WO) d4--WorkingDay(W) d5--WorkingDay(W) d6--WorkingDay(W) d7--WorkingDay(W) d8--WorkingDay(W) d9--WorkingDay(W) d10--Week Off(WO) d11--WorkingDay(W) d12--WorkingDay(W) d13--WorkingDay(W) d14--WorkingDay(W) d15--WorkingDay(W) d16--Holiday d17--Week Off(WO) d18--WorkingDay(W) d19--WorkingDay(W) d20--WorkingDay(W) d21--WorkingDay(W) d22--WorkingDay(W) d23--Week Off(WO) d24--Week Off(WO) d25--WorkingDay(W) d26----WorkingDay(W) d27--WorkingDay(W) d28--WorkingDay(W) d29--WorkingDay(W) d30--WorkingDay(W) d31--WeekOff(wo)
Nightshift table
d1 -- Holiday d2 -- Week Off(WO) d3 -- Week Off(WO) d4--WorkingDay(W) d5--WorkingDay(W) d6--WorkingDay(W) d7--WorkingDay(W) d8--WorkingDay(W) d9--Week Off(WO) d10--Week Off(WO) d11--WorkingDay(W) d12--WorkingDay(W) d13--WorkingDay(W) d14--WorkingDay(W) d15--WorkingDay(W) d16--Holiday d17--Week Off(WO) d18--WorkingDay(W) d19--WorkingDay(W) d20--WorkingDay(W) d21--WorkingDay(W) d22--WorkingDay(W) d23--Week Off(WO) d24--Week Off(WO) d25--WorkingDay(W) d26----WorkingDay(W) d27--WorkingDay(W) d28--WorkingDay(W) d29--WorkingDay(W) d30--Week Off(WO) d31--WeekOff(wo)
NightshiftIH table
d1 -- Holiday d2 -- Week Off(WO) d3 -- Week Off(WO) d4--WorkingDay(W) d5--WorkingDay(W) d6--WorkingDay(W) d7--WorkingDay(W) d8--WorkingDay(W) d9--Week Off(WO) d10--Week Off(WO) d11--WorkingDay(W) d12--WorkingDay(W) d13--WorkingDay(W) d14--WorkingDay(W) d15--WorkingDay(W) d16--Holiday d17--Week Off(WO) d18--WorkingDay(W) d19--WorkingDay(W) d20--WorkingDay(W) d21--WorkingDay(W) d22--WorkingDay(W) d23--Week Off(WO) d24--Week Off(WO) d25--WorkingDay(W) d26----WorkingDay(W) d27--WorkingDay(W) d28--WorkingDay(W) d29--WorkingDay(W) d30--Week Off(WO) d31--WeekOff(wo)
create table Holidaymaster(Holiday_ID int,Holiday VARCHAR(50),Date1 datetime)
INSERT INTO Holidaymaster values(1,'New Year','01-01-2016')
INSERT INTO Holidaymaster values(2,'Pongal','16-01-2016')
INSERT INTO Holidaymaster values(3,'Labour day','01-05-2016')
INSERT INTO Holidaymaster values(4,'Christmas','26-12-2016')
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
)
June 12, 2016 at 5:39 am
this appears to be another post that is similar to others you have posted in recent weeks.......what have you tried so far, based on previous solutions you have been given?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 12, 2016 at 5:59 am
In previous i am working with shift calculations based on working days of the individual month..here i am trying to calculate every month working days..since the company declares holiday list earlier in the year so calculating every month how many holidays,weekoff's and working days..
June 12, 2016 at 6:09 am
ganapathy.arvindan (6/12/2016)
In previous i am working with shift calculations based on working days of the individual month..here i am trying to calculate every month working days..since the company declares holiday list earlier in the year so calculating every month how many holidays,weekoff's and working days..
so what code are you using to generate the pivots?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 12, 2016 at 6:16 am
Getting no idea about this...please help me
June 12, 2016 at 6:35 am
DECLARE @businessDay date;
--- Recursive common table expression:
WITH rcte ([date], nextBusinessDay)
AS (
--- The anchor is the input date (if it's a bank holiday)
SELECT [date], DATEADD(dd, 1, [date]) AS nextBusinessDay
FROM dbo.HolidayMaster
WHERE [date]=@date
UNION ALL
SELECT h1.[date],
DATEADD(dd, 1, h1.nextBusinessDay) AS nextBusinessDay
FROM rcte AS h1
INNER JOIN dbo.HolidayMaster AS h2 ON
h1.nextBusinessDay=h2.[date]
)
SELECT @businessDay=MAX(nextBusinessDay)
FROM rcte
WHERE [date]=@date
IF (@businessDay IS NULL)
SET @businessDay=@date;
RETURN @businessDay;
END;
June 12, 2016 at 8:52 am
as an initial thought for "dayshift" only ...........does the following give the desired results?.....obviously you will need to pivot the results !
DECLARE @sd DATEtime = '20160101'
DECLARE @ed DATEtime = dateadd(mm, datediff(mm, 0, @sd) + 1, -1);
WITH yourcalendar as (
SELECT DATEADD(dd, rn - 1, @sd) thedate, 'D' + CAST ((rn ) as varchar(2)) AS DN
FROM
( SELECT rn = ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM sys.objects a CROSS JOIN sys.objects b
) x
WHERE x.rn <= DATEDIFF(dd, @sd, DATEADD(day, 1, @ed))
)
SELECT
--c.thedate,
c.DN,
-- h.date1,
-- DATEPART(dw, c.thedate) dw,
-- DATENAME(dw, c.thedate),
--ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) dwrn,
CASE
WHEN h.date1 > 0 THEN 'H'
WHEN DATEPART(dw, c.thedate) IN (2,3,4,5,6) THEN 'W'
WHEN DATEPART(dw, c.thedate) = 7 and ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) IN (2,3) THEN 'W'
ELSE 'WO'
END hdL
FROM yourcalendar AS c
LEFT OUTER JOIN Holidaymaster AS h ON c.thedate = h.Date1
ORDER BY c.thedate
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 12, 2016 at 9:16 am
Thank you so much..working fine for dayshift...let me know the changes for Nightshift
June 12, 2016 at 9:20 am
ganapathy.arvindan (6/12/2016)
Thank you so much..working fine for dayshift...let me know the changes for Nightshift
feel free to PM me for my rates <grin>
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 12, 2016 at 11:19 am
Any updates reg to Nightshift tables
June 12, 2016 at 11:34 am
ganapathy.arvindan (6/12/2016)
Any updates reg to Nightshift tables
based on the code I gave you for "dayshift"...what have you tried that doesnt work for the other tables?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 12, 2016 at 12:09 pm
ganapathy.arvindan (6/12/2016)
Any updates reg to Nightshift tables
You have an example for the day shift that works. Put your shoulder to the wheel and give it a try for the night shift. Remember, it's your job, not ours. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2016 at 8:09 pm
Thanks, i am trying for it, just i asked for it to make the changes, Sorry and thanks
June 12, 2016 at 11:55 pm
Done with the Nightshift
DECLARE @sd DATEtime = '20161201'
DECLARE @ed DATEtime = dateadd(mm, datediff(mm, 0, @sd) + 1, -1);
WITH yourcalendar as (
SELECT DATEADD(dd, rn - 1, @sd) thedate, 'D' + CAST ((rn ) as varchar(2)) AS DN
FROM
( SELECT rn = ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM sys.objects a CROSS JOIN sys.objects b
) x
WHERE x.rn <= DATEDIFF(dd, @sd, DATEADD(day, 1, @ed))
)
SELECT
--c.thedate,
c.DN,
-- h.date1,
-- DATEPART(dw, c.thedate) dw,
-- DATENAME(dw, c.thedate),
--ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) dwrn,
CASE
WHEN h.date1 > 0 THEN 'H'
WHEN DATEPART(dw, c.thedate) IN (2,3,4,5,6) THEN 'W'
--WHEN DATEPART(dw, c.thedate) = 7 and ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) IN (2,3) THEN 'W'
WHEN DATEPART(dw, c.thedate) = 5 and ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) IN (1,2,3,4,5) THEN 'W'
ELSE 'WO'
END hdL
FROM yourcalendar AS c
LEFT OUTER JOIN Holidaymaster AS h ON c.thedate = h.Date1
ORDER BY c.thedate
June 13, 2016 at 1:42 am
ganapathy.arvindan (6/12/2016)
Done with the NightshiftDECLARE @sd DATEtime = '20161201'
DECLARE @ed DATEtime = dateadd(mm, datediff(mm, 0, @sd) + 1, -1);
WITH yourcalendar as (
SELECT DATEADD(dd, rn - 1, @sd) thedate, 'D' + CAST ((rn ) as varchar(2)) AS DN
FROM
( SELECT rn = ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM sys.objects a CROSS JOIN sys.objects b
) x
WHERE x.rn <= DATEDIFF(dd, @sd, DATEADD(day, 1, @ed))
)
SELECT
--c.thedate,
c.DN,
-- h.date1,
-- DATEPART(dw, c.thedate) dw,
-- DATENAME(dw, c.thedate),
--ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) dwrn,
CASE
WHEN h.date1 > 0 THEN 'H'
WHEN DATEPART(dw, c.thedate) IN (2,3,4,5,6) THEN 'W'
--WHEN DATEPART(dw, c.thedate) = 7 and ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) IN (2,3) THEN 'W'
WHEN DATEPART(dw, c.thedate) = 5 and ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) IN (1,2,3,4,5) THEN 'W'
ELSE 'WO'
END hdL
FROM yourcalendar AS c
LEFT OUTER JOIN Holidaymaster AS h ON c.thedate = h.Date1
ORDER BY c.thedate
or you could do this
DECLARE @sd DATEtime = '20161201'
DECLARE @ed DATEtime = dateadd(mm, datediff(mm, 0, @sd) + 1, -1);
WITH yourcalendar as (
SELECT DATEADD(dd, rn - 1, @sd) thedate, 'D' + CAST ((rn ) as varchar(2)) AS DN
FROM
( SELECT rn = ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM sys.objects a CROSS JOIN sys.objects b
) x
WHERE x.rn <= DATEDIFF(dd, @sd, DATEADD(day, 1, @ed))
)
SELECT
c.DN,
CASE
WHEN h.date1 > 0 THEN 'H'
WHEN DATEPART(dw, c.thedate) IN (1,7) THEN 'WO' ELSE 'W'
END hdL
FROM yourcalendar AS c
LEFT OUTER JOIN Holidaymaster AS h ON c.thedate = h.Date1
ORDER BY c.thedate
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply