1 2 Previous Next 15 Replies Latest reply on Jan 15, 2018 7:09 AM by Mohammad Naveed

# Calculating Exact Business day using Time based

Hi All,

i am stuck in calculating Business day based on time , below is the SQL query which i am using currently for my project and its working perfectly in SQL Server however when i using same in Tableau its showing an error as "Datediff(string,integer,datetime) error"

can anyone please guide me on this.

Sql Query

[Code]

declare @StartDate datetime, @EndDate datetime

select @StartDate = '1/11/2017  3:08:25 PM',@EndDate='1/9/2018  10:40:21 PM'

Select convert(decimal(10,2),(cast(Datediff(second,@StartDate, @EndDate) as decimal(10,2)))/86400 )

- (DATEDIFF(wk,@StartDate, @EndDate)*2) --Subtact 2 days for each full weekend

- (1-SIGN(DATEDIFF(dd,6,@StartDate)%7)) --If StartDate is a Sunday, Subtract 1

- (1-SIGN(DATEDIFF(dd,5, @EndDate)%7)) --If EndDate is a Saturday, Subtract 1

[/code]

• ###### 1. Re: Calculating Exact Business day using Time based

Hi,

Can anyone please guide me how can i change the function in tableau to get same result like in sql server

• ###### 2. Re: Calculating Exact Business day using Time based

Can you please share packaged workbook?

your date column might be a string in the tableau.

• ###### 3. Re: Calculating Exact Business day using Time based

i can not share packaged workbook as my file is connected to live database server....

date field is showing under dimension and it is date time field and data type is Date time

• ###### 4. Re: Calculating Exact Business day using Time based

what is the formula, you are using in tableau.

Also, you can still create a packaged workbook. Please see below

The packaged workbook will have in solving the problem little faster.

• ###### 5. Re: Calculating Exact Business day using Time based

same formula i want to use in tableau..

let me create a sample tableau package workbook

• ###### 6. Re: Calculating Exact Business day using Time based

Here is my tableau file, i have added SQL Query calculation field (Field Name: Correct Time to resolve) and i have created a Calculated field to get Time to resolve however i am not getting correct Time to resolve.

Note: we are excluding Weekends

SQL Query Function: Which will give result as [306.08]

declare @StartDate datetime, @EndDate datetime

select @StartDate = '11/2/2016  6:09:34 PM',@EndDate='1/4/2018  8:03:36 PM'

Select convert(decimal(10,2),(cast(Datediff(second,@StartDate, @EndDate) as decimal(10,2)))/86400 )

- (DATEDIFF(wk,@StartDate, @EndDate)*2) --Subtact 2 days for each full weekend

- (1-SIGN(DATEDIFF(dd,6,@StartDate)%7)) --If StartDate is a Sunday, Subtract 1

- (1-SIGN(DATEDIFF(dd,5, @EndDate)%7)) --If EndDate is a Saturday, Subtract 1

Function in Tableau: Which is giving result as [302.08]

(DATEDIFF('second',DATEtime([Date Opened]),DATEtime([Date Closed]))/86400)

-

(DATEDIFF('week',DATE([Date Opened]),DATE([Date Closed]))*2)

-

(1-SIGN(DATEDIFF('day',date(str(YEAR([Date Opened])) + '-' + str(MONTH([Date Opened])) + '-06'),DATE([Date Opened]))%7))

-

(1-SIGN(DATEDIFF('day',date(str(YEAR([Date Closed])) + '-' + str(MONTH([Date Closed])) + '-05'),DATE([Date Closed]))%7))

• ###### 7. Re: Calculating Exact Business day using Time based

Please try below formula. hope this works

DATEDIFF('second',DATEtime([Date Opened]),DATEtime([Date Closed])) / 86400

-

2*(DATEDIFF('week',DATEtime([Date Opened]),DATEtime([Date Closed])))

• ###### 8. Re: Calculating Exact Business day using Time based

ok, let me check in my live database

• ###### 9. Re: Calculating Exact Business day using Time based

Thank you so much Arvind for your help, however for almost 218 records 1 day difference is coming

• ###### 10. Re: Calculating Exact Business day using Time based

Note: We need to exclude Saturday's and Sundays if comes between date opened and date closed

• ###### 11. Re: Calculating Exact Business day using Time based

what if date open or close falls on sat or sun. how to deal with them?

• ###### 12. Re: Calculating Exact Business day using Time based

what if date open or close falls on sat or sun. how to deal with them?

I think we should exclude them.

So what I did

i created two more data items

NDate Opened

if datepart('weekday',[Date Opened]) = 1 then DATEADD('day',1,[Date Opened])

elseif datepart('weekday',[Date Opened]) = 7 then  DATEADD('day',2,[Date Opened])

else [Date Opened]

end

NDate Closed

if datepart('weekday',[Date Closed]) = 1 then DATEADD('day',-2,[Date Closed])

elseif datepart('weekday',[Date Closed]) = 7 then  DATEADD('day',-1,[Date Closed])

else [Date Closed]

end

using these new data items, I created actual date diff calc like below

Now everything is matching with database calc. Please check.

DATEDIFF('second',DATEtime([NDate Opened]),DATEtime([NDate Closed])) / 86400

-

2*(DATEDIFF('week',DATEtime([NDate Opened]),DATEtime([NDate Closed])))

1 of 1 people found this helpful
• ###### 13. Re: Calculating Exact Business day using Time based

HI Arvind,

Superb awesome

its matching perfectly now....Thank you so much for your kind help and patients

Now my issue is resolved...and i will post another query later after 2days

thank you so much again....

• ###### 14. Re: Calculating Exact Business day using Time based

I am glad, it worked out for you.

1 2 Previous Next