CODEWITHSUNDEEP
sqlsql-serversql-server-2012
chaitanya
chaitanya

Reputation: 3

Include Holidays count in working days

CREATE TABLE [dbo].[Timedate](
    [TimeKey] [date] NULL,
    [DayName] [varchar](100) NULL,
    [Holiday] [varchar](10) NULL,
    [BusinessDays] [int] NULL,
    [QuarterNumber] [int] NULL,
    [CalenderWeek] [int] NULL,
    [MonthNo] [int] NULL,
    [CalenderMonth] [varchar](100) NULL,
    [CalenderMonthAbbrevation] [varchar](100) NULL,
    [CalenderMonthWeekNumber] [int] NULL,
    [CalenderDayabbrevation] [varchar](100) NULL
) ON [PRIMARY]

GO


 insert into timedate values ('2017-09-25','Monday','',1,3,'',9,'September','SEP',4,'MON')
 GO
 insert into timedate values ('2017-09-26','Tuesday','',1,3,'',9,'September','SEP',4,'Tue')
 GO
 insert into timedate values ('2017-09-27','Wednesday','',1,3,'',9,'September','SEP',4,'Wed')
 GO
 insert into timedate values ('2017-09-28','Thursday','',1,3,'',9,'September','SEP',4,'Thu')
 GO
 insert into timedate values ('2017-09-29','Friday','',1,3,'',9,'September','SEP',4,'Fri')
 GO
insert into timedate values ('2017-09-30','saturday','',0,4,'',10,'October','OCT',1,'Sat')
 GO
insert into timedate values ('2017-10-1','Sunday','',0,4,'',10,'October','OCT',1,'SUn')
 GO

i want to get a result like when Businessdays=0 then previousworkingday=1 when 2 businessdays in a month=0 then previous woringday=2

Note: 1.Businessdays=0 defines holiday 2.businessdays=1 defines woring day.

Upvotes: 0

Views: 69

Answers (2)

chaitanya
chaitanya

Reputation: 3

SELECT t.TimeKey,t.DayName,t.BusinessDays, case when(select count(1) from  timedate bd where bd.BusinessDays=0 and bd.TimeKey in (dateadd(day,2, t.timekey) ,  dateadd(day,1, t.timekey) )  and  [monthNo]=  datepart(mm,dateadd(day,2, t.timekey))and  [monthNo]=  datepart(mm,dateadd(day,1, t.timekey)))=2 then 3
when(select count(1) from  timedate bd where bd.BusinessDays=0 and (dateadd(day,1, t.timekey) =bd.TimeKey)and
 [monthNo]=  datepart(mm,dateadd(day,1, t.timekey)) and bd.BusinessDays<>t.BusinessDays )=1then 2  when t.BusinessDays=0 then 0else 1 end as valsFROM timedate t;

Upvotes: 0

zarruq
zarruq

Reputation: 2465

Looking in to your sample data and desired output, One way could be to use ANSI standard OLAP functions to check if following BusinessDays = 0 and BusinessDays not in sat/sun then current BusinessDays +1 as below.

SELECT t.TimeKey,
       t.DayName,
       t.BusinessDays,
       CASE
           WHEN min(BusinessDays) over(
                                       ORDER BY TimeKey ROWS BETWEEN 1 following AND 1 following) = 0
                AND BusinessDays <> 0
                AND DayName NOT IN ('saturday',
                                    'sunday') THEN BusinessDays + 1
           ELSE BusinessDays
       END AS CalcValue
FROM timedate t;

OR LEAD().

SELECT t.TimeKey,
       t.DayName,
       t.BusinessDays,
       CASE
           WHEN lead(BusinessDays) over(
                                       ORDER BY TimeKey ) = 0
                AND BusinessDays <> 0
                AND DayName NOT IN ('saturday',
                                    'sunday') THEN BusinessDays + 1
           ELSE BusinessDays
       END AS CalcValue
FROM timedate t;

Result:

TimeKey                DayName       BusinessDays   CalcValue
-------------------------------------------------------------
25.09.2017 00:00:00    Monday        1              2
26.09.2017 00:00:00    Tuesday       0              0
27.09.2017 00:00:00    Wednesday     1              1
28.09.2017 00:00:00    Thursday      1              1
29.09.2017 00:00:00    Friday        1              2
30.09.2017 00:00:00    saturday      0              0
01.10.2017 00:00:00    Sunday        0              0
02.10.2017 00:00:00    Monday        1              1

DEMO

Upvotes: 1

Related Questions