March 24, 2014 at 2:38 am
Hi Team,
I have a requirement to perform the following calculation, could you some one assist me with the logic to achieve the below result as per the requirement.
The below are the DB columns with some sample data
Starttime - 2013-01-12 09:45:00:0000
Duration ( mins ) - 10
ESO ( mins) - 15
LSO (mins) -15
I want to calculate these two columns " Early start = Diff( StartTime" - " ESO') & Late Finish = (StartTime + Duration + LSO)
The o/p should be reflect as below
Earliest Start should returns as = 2013-01-12 09:30:00:0000
Latest Finish should return as = 2013-01-12 09: 10:10:00:0000
Please note Duration, ESO and LSO are all in MINUTES
Thanks for your time and assistance in advance.
Krishna.
March 24, 2014 at 2:54 am
You can use the datediff function. Here is an example:
declare @StartTime datetime
declare @Duration int
declare @ESO int
declare @LSO int
SELECT @StartTime = '20130112 09:45:00', @Duration = 10,
@ESO = 15, @LSO = 15
select EarlyStart = dateadd(mi,0-@ESO,@StartTime), LateFinise = dateadd(mi,@Duration + @LSO,@StartTime)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 24, 2014 at 2:59 am
Thanks for the reply.
Since I already have the data in the database.. do I need to declare those variables OR I can go directly to the date funtcions as you suggested in the select statement to the data...
I believe the first function is DateDiff and the second one is DateAdd...am I correct?
I have tried your solution and the o/p am getting is in the format like - 59462490
Is there any other function, am I using here along with the DateDiff and DateAdd?
Regards,
March 24, 2014 at 3:06 am
If you have the data as columns in table, you can use the column's names instead of variables. If you want to get an accurate example for your case, it would help if you'll post a small script that creates your table, inserts some data into it and explain the results that you expect to get from the select statement.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 24, 2014 at 3:20 am
Hi Adi,
Thanks for the prompt reply.
is this of any help?
create table sampletable
apptid int not null,
starttime datetime,
duration int,
ESO int,
LSO int,
insert into sampletable values (1,2013-01-12 09:45:00:0000, 10,15,15)
regards,
krishna.
March 24, 2014 at 3:34 am
Yes, that helps. Here is the code that is based on your code:
create table sampletable (
apptid int not null,
starttime datetime,
duration int,
ESO int,
LSO int
)
insert into sampletable values (1,'20130112 09:45:00.000', 10,15,15)
select EarlyStart = dateadd(mi,0-ESO,starttime), LateFinise = dateadd(mi,duration + LSO,starttime)
from sampletable
go
--cleanup
drop table sampletable
I hope that this is what you meant.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply