Converting excel formula to SQL query

Question

This is Excel file where I can Use formula to get TAT ORIG:

=IF((X3-W3)*24<=24,(X3-W3)*24,
IF(AND(WEEKDAY(W3,2)<6,WEEKDAY(X3,2)<6),(NETWORKDAYS(W3,X3)-1+MOD(X3,1)-MOD(W3,1))*24,
IF(OR(WEEKDAY(W3,2)>5,WEEKDAY(X3,2)>5),(NETWORKDAYS(W3,X3)*24))))

this is the formula I used to get value for TAT ORIG in EXCEL

I need same formula to be converted or trick to get the same value in existing SQL table, with column name tat_orig

in this table I need tat_orig to be calculated with SQL query:

We need more context. Few people will spend the time to dissect that excel formula. Use as many words as you need. Also you didn't provide what is TAT ORIG. Just explain what the conditions are to get the end result. — S3S
– S3S, Commented Jul 14, 2016 at 14:15
I strongly suggest to try and split down the excel formula... you dont't want to end up with numerous CASE statements embedded in each other - which is basically what Excel's formula "IF(A,1,IF(B,2,IF..." will lead to. — Tyron78
– Tyron78, Commented Jul 14, 2016 at 14:20
whatever i have posted...tats the only material i have .... plz help asap friends — Raj JAdhav
– Raj JAdhav, Commented Jul 14, 2016 at 15:14
Have you tried writing this in SQL? I don't think most people will just write your queries for you. — Kyle
– Kyle, Commented Jul 14, 2016 at 15:22

hector-j-rivas · Accepted Answer · 2016-07-21 16:52:27Z

6

Wow, I did not expect this to take so long. Here's what I've got:

--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(DATEDIFF(ms, @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 = DATEDIFF(ms, @d1, @d2) / 3600000.0;
    DECLARE @td float = DATEDIFF(ms, 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');

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

The difficult part was figuring out the date arithmetic. You do not have to declare functions--they are there for clarity and calculating middle values, but technically you should be able to use the return values in a SELECT statement.

BTW, you're out of luck if your computed column is taking values from the next row--it's not impossible in SQL, but it is close.

I hope this helps!

EDIT:

I added the date diff big shim. I added test data one year appart.

--Big datediff shim
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;

edited Jul 21, 2016 at 16:52

answered Jul 20, 2016 at 20:47

hector-j-rivas

8159 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Raj JAdhav Over a year ago

wooooh .....hats off to you bro...you are truly genius and born Legend...this is wat i need...and u did it like a charm...this is exactly i want, now do me a small favor how can i implement this code in my existing table with values already exist in my column time_Created, time_responded , this is what i m trying now ....alter table dbo.FinalExtract add tat_orig float update dbo.FinalExtract set tat_orig =

Raj JAdhav Over a year ago

i tried this .... update dbo.FinalExtract set tat_orig = dbo.TURNAROUND(time_created,time_responded) from dbo.FinalExtract but i m getting error : Msg 535, Level 16, State 0, Line 2 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart. The statement has been terminated.

hector-j-rivas Over a year ago

Thanks. You can define computed columns in T-SQL with an equation, e.g. , [Name] = [First] + ' ' + [Last]; of course in your case the computation is way more complex, but it doesn't mean it cannot be inlined. About the overflow, try lowering the date diff specifiers from milliseconds (ms) to seconds--I just happen to default to the highest precision available.

Raj JAdhav Over a year ago

its not happening ...the desire output value gets changed....if i changed to seconds...all i want only 2 places after decimal point eg. 1.58 or 19.28

hector-j-rivas Over a year ago

Hmm, there is a cap in the millisecond date difference between start and end date (24 days, 20 hours, 31 minutes and 23.647 seconds, because the function return type is int), see msdn.microsoft.com/en-us/library/ms189794.aspx. You'll need to use DATEDIFF_BIG (SQL Server 2016) or a custom date diff parser otherwise, check out the accepted answer at social.msdn.microsoft.com/Forums/sqlserver/en-US/…. Seconds should not overflow--the cap is 68 years.

|

Collectives™ on Stack Overflow

Converting excel formula to SQL query

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related