SQL query to get Desired Output which Excel formula Does with Ease

Transact-SQL
, ,
1

hey fellas i need help to convert excel formula into sql query
this is excel formula =IF((X3-Y3)*24<=24,(X3-Y3)*24,IF(AND(WEEKDAY(Y3,2)<6,WEEKDAY(X3,2)<6),(NETWORKDAYS(Y3,X3)-1+MOD(X3,1)-MOD(Y3,1))*24,IF(OR(WEEKDAY(Y3,2)>5,WEEKDAY(X3,2)>5),(NETWORKDAYS(Y3,X3)*24))))

i want to convert this formula into sql query

here cell X3 = (time_Responded) (DATETIME) eg 6/15/2016 3:03:12 AM
Y3=(Change_language) (DATETIME) eg = nothing

output : at cell (AH) Column Header = Tat Final = 729192.00 miliseconds

query.JPG822×133 34.3 KB

this is my excel screenshot..

i want query to retrieve value in Tat_final column of sql table

2

Please look at DATEDIFF for the differences and DATEPART for the day of week number.
I do not remember what NETWORKDAYS does. The IF(OR is also something I would have to look up.

3

If I recall correctly, NETWORKDAYS returns days between two dates minus weekend and minus holidays.
There is no such function in sql, but it can be handled if you have a workday table or holiday table - do you have either of these?

Also could you please put the formular in preformated text - the "button" that looks like this: < / >

4

this is what i have got from sources...

IF OBJECT_ID(N'DATEDIFFBIG', N'FN') IS NOT NULL
DROP FUNCTION dbo.DATEDIFFBIG;
GO

CREATE FUNCTION DATEDIFFBIG(@d1 datetime, @d2 datetime)
RETURNS bigint
AS
BEGIN
RETURN CONVERT(bigint, DATEDIFF(day, @d1, @d2)) * 86400000 -
DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, @d1), 0), @d1) * 1000 +
DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, @d2), 0), @d2) * 1000;
END
GO

--Network days shim
IF OBJECT_ID(N'NETWORKDAYS', N'FN') IS NOT NULL
DROP FUNCTION dbo.NETWORKDAYS;
GO
CREATE FUNCTION dbo.NETWORKDAYS(@d1 datetime, @d2 datetime)
RETURNS int
AS
BEGIN
DECLARE @w1 int = DATEPART(weekday, @d1);
DECLARE @w2 int = DATEPART(weekday, @d1);
DECLARE @dd float = FLOOR(dbo.DATEDIFFBIG(@d1, @d2) / 86400000.0);

-- network days is based on a holidays table; I just added this date arbitrarily so that
-- the results match what Excel says
DECLARE @holidays TABLE(holiday datetime);
INSERT INTO @holidays VALUES
    ('2016-06-15');

RETURN (@dd + @w2 - @w1) / 7 * 5 +
        @w2 - @w1 + 1 +
        IIF(@w2 = 7, -1, 0) +
        IIF(@w1 = 1, -1, 0) +
        (SELECT COUNT(*) FROM @holidays WHERE @d1 <= holiday AND holiday < @d2);

END
GO

-- Turn around time shim
IF OBJECT_ID(N'TURNAROUND', N'FN') IS NOT NULL
DROP FUNCTION dbo.TURNAROUND;
GO
CREATE FUNCTION dbo.TURNAROUND(@d1 datetime, @d2 datetime)
RETURNS float
AS
BEGIN
DECLARE @w1 int = DATEPART(weekday, @d1);
DECLARE @w2 int = DATEPART(weekday, @d1);
DECLARE @nd int = dbo.NETWORKDAYS(@d1, @d2);

DECLARE @hd float = dbo.DATEDIFFBIG(@d1, @d2) / 3600000.0;
DECLARE @td float = dbo.DATEDIFFBIG(CAST(@d1 AS TIME), CAST(@d2 AS TIME)) / 86400000.0;

RETURN (

IIF(@hd <= 24.0,
    @hd,
    IIF(@w1 < 6 AND @w2 < 6,
        24 * (@nd - 1 + @td),
        IIF(@w2 > 5 OR @w1 > 5,
            24 * @nd, 0))));

END
GO

-- the data
DECLARE @items TABLE
(
time_created datetime,
time_responded datetime
);

INSERT INTO @items VALUES
('2016-06-10 15:42:00.000', '2016-06-15 03:03:00.000'),
('2016-06-15 01:28:00.000', '2016-06-15 03:03:00.000'),
('2016-06-14 07:46:00.000', '2016-06-15 03:03:00.000'),
('2016-07-04 05:35:25.000', '2016-07-04 19:05:48.000'),
('2016-07-04 04:56:09.000', '2016-07-04 18:29:28.000'),
('2016-07-04 09:15:33.000', '2016-07-04 22:08:43.000'),
('2016-07-04 08:44:24.000', '2016-07-04 21:40:57.000'),
('2016-07-04 07:14:51.000', '2016-07-04 21:39:24.000'),
('2015-07-04 07:14:51.000', '2016-07-04 21:39:24.000');

-- the results
SELECT time_created, time_responded, dbo.TURNAROUND(time_created, time_responded) AS [TAT Orig] FROM @items;

5

but i need to calculate WEWE values on the basis of Y3(Change Language) (DateTIME) column ...which is NULL....and formula for which is =IF((X3-Y3)24<=24,(X3-Y3)24,IF(AND(WEEKDAY(Y3,2)<6,WEEKDAY(X3,2)<6),(NETWORKDAYS(Y3,X3)-1+MOD(X3,1)-MOD(Y3,1))24,IF(OR(WEEKDAY(Y3,2)>5,WEEKDAY(X3,2)>5),(NETWORKDAYS(Y3,X3)24))))