Sql-server – How to calculate end_date from start_date and duration in SQL Server Query

alter-tablecomputed-columnfunctionssql servert-sql

I have a table with 4 fields .Start-date, Duration, End_date, Actual_date.

I am providing the start_date and duration as the input to calculate end-date.

While calculating the end_date the Saturday and Sundays are not considered.

Also if the actual_date is having value that value is saved as end_date.

Example : Start_date = 18/05/2020 , Duration : 10. So the End_date should be calculated by excluding 23/05/2020 and 24/05/2020.

I had tried different ways to archive this.

The below is the alter query for the column end_date.

This is meeting half of my requirement but dont know how to exclude the weekends (Saturday and Sunday) from the start_date and duration.

ALTER TABLE [DBName].[dbo].[TestDate] ADD End_Date as CAST(
CASE 
WHEN (Actual_Date='' or Actual_Date IS NULL) then  DATEADD(day, Duration, Start_Date)
ELSE Actual_Date
END as DATE
)
GO

Please help me on this.

Best Answer

Iterative solution:

WITH cte AS ( SELECT id, 
                     DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, start_date) > 5 
                                       THEN 9 - DATEPART(WEEKDAY, start_date) 
                                       ELSE 1 
                                       END, start_date) cur_date, duration-1 reminder
              FROM test
              UNION ALL
              SELECT id, 
                     DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, cur_date) > 5 
                                       THEN 3 
                                       ELSE 1 
                                       END, cur_date), 
                     reminder - 1
              FROM cte
              WHERE reminder > 0 )
UPDATE test
SET test.end_date = cte.cur_date
FROM cte
WHERE test.id = cte.id
  AND cte.reminder = 0;

fiddle

PD. It is optimal hardly... for large duration values you may int. divide it by 7 and add according weeks (5 days per each) then iterate by int. reminder only.

Related Solutions

Sql-server – Query performance and join hints, plan cost, and duration

Another option to optimizer hints if you cannot even touch the query is to use Plan Guides. However, since your query statement is dynamic with many unparameterized tokens, I doubt it's possible.

Since you know your data, I think you're better off just staying the path of query hints. Statistics have been known to be lies, lies and damn lies. It can only provide an overall shape of the data, but for specific conditions (e.g. fund_src_name_id values, state_id values, combination of the two etc) it can just produce the wrong plan.

Sql-server – T-SQL Calculate average value for Duration Field

Store duration in seconds as an integer; then average is quite easy. Right now you're trying to take an average of a string converted to a float converted to a datetime converted to a time. If that doesn't sound wrong to you, read it again. Then consider that time represents a point time. What is the average of 3:12 AM and 4:57 PM? Meet in the middle?

Don't be tempted to change this from nvarchar to time. Again, time is a point in time, not an interval. You should be storing start and end values as datetime; you can always calculate duration from that (typically I see duration as an additional nonsense column that doesn't need to be stored in the first place).

The formatting as hh:mm:ss should not happen at storage time, only at presentation time. And no idea why you would ever use nvarchar for this in the first place - what Unicode characters do you expect to support in hh:mm:ss?

In the meantime:

SELECT CONVERT(TIME(0), DATEADD(SECOND, AVG(DATEDIFF(SECOND, 0, CONVERT(DATETIME, 
  CASE WHEN ISDATE(Duration) = 1 THEN Duration END))),0)) -- in case of garbage data 
FROM dbo.your_table_name;

See how messy that is? Wouldn't it be much easier as:

SELECT AVG(Duration) FROM dbo.your_table_name;

Or:

SELECT AVG(DATEDIFF(SECOND, [start], [end])) FROM dbo.your_table_name;

And then format as hh:mm:ss in the client language? Yes, I think so.

Best Answer

Related Solutions

Sql-server – Query performance and join hints, plan cost, and duration

Sql-server – T-SQL Calculate average value for Duration Field

Related Question