CALCULATING DELAY SECONDS

Question

CALCULATING DELAY SECONDS

JALLY

SSCommitted

Points: 1865
More actions
August 25, 2016 at 10:09 am

#316663

CREATE TABLE DELAYSECONDS(
ProgramID INT, ID INT, ContractHeaderID INT, ContractStartTime TIME, ProgramStartTime TIME ,ContractDetailFirstDay INT ,ProgramFirstDay INT, DelaySeconds INT)
INSERT INTO DELAYSECONDS
VALUES (625, 625, 115278, '18:00:00', '18:00:00', 2, 2, 0),
(623, 623, 115279, '8:00:00', '15:05:00', 1,NULL, 0),
(624, 624, 115280, '18:00:00', '19:00:00', 7, 7, 0),
(625, 625, 115281, '18:00:00', '18:00:00', 2, 2, 0),
(625, 625, 115282, '19:00:00', '18:00:00', 2, 2, 3600)
see my data above,
i want the delayseconds field populated
the logic is below:
lets get the time difference between the
contractdetailfirstday - programdetailfirstday = a
a*24(hrs of the day)*60(minutes in the hour)*60(seconds in the minute) =b
lets get the time difference between the time
contractdetailstarttime-programstarttime (inseconds) =c
b+c = d 'delayseconds'
if (d>0) then return d
else o
does this make sense?
pls assist as this is due today.
thanks
I learn from the footprints of giants......

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Please turn off caps lock. It is considered yelling and is might difficult to read. Also, there is no reason to post a separate question. You already have a thread on this topic here. http://www.sqlservercentral.com/Forums/Topic1812194-3077-1.aspx

And keep in mind that we are all volunteers. The fact that your homework is due today represents a lack of planning on your part. And your failure to plan does not constitute and emergency on my part. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

JALLY SSCommitted Points: 1865 More actions · Answer 2

Sean Lange (8/25/2016)
Please turn off caps lock. It is considered yelling and is might difficult to read. Also, there is no reason to post a separate question. You already have a thread on this topic here. http://www.sqlservercentral.com/Forums/Topic1812194-3077-1.aspx
And keep in mind that we are all volunteers. The fact that your homework is due today represents a lack of planning on your part. And your failure to plan does not constitute and emergency on my part. 😉

my apologies, ive been trying to fix this myself, but guess it requires throwing in the towel earlier on my part.

I learn from the footprints of giants......

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

It would also help if you provided us with proper information. The SQL logic you provided does not work!

CREATE TABLE DELAYSECONDS(

ProgramID INT, ID INT, ContractHeaderID INT StationID INT ContractStartTime TIME ProgramStartTime TIME ContractDetailFirstDay INT ProgramFirstDay INT DelaySeconds INT)

INSERT INTO DELAYSECONDS

VALUES (625, 625, 115278, '0:00:00', '18:00:00', '18:00:00', 2, 2, 0),

(623, 623, 115279, '0:00:00', '8:00:00', '15:05:00', 1, NULL, 0),

(624, 624, 115280, '0:00:00', '18:00:00, '19:00:00', 7, 7, 0),

(625, 625, 115281, '0:00:00', '18:00:00', '18:00:00', 2, 2, 0),

(625, 625, 115282, '0:00:00', '19:00:00', '18:00:00', 2, 2, 3600)

The above SQL has a few errors in it.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

JALLY SSCommitted Points: 1865 More actions · Answer 4

Alvin Ramard (8/25/2016)
It would also help if you provided us with proper information. The SQL logic you provided does not work!
CREATE TABLE DELAYSECONDS(
ProgramID INT, ID INT, ContractHeaderID INT StationID INT ContractStartTime TIME ProgramStartTime TIME ContractDetailFirstDay INT ProgramFirstDay INT DelaySeconds INT)
INSERT INTO DELAYSECONDS
VALUES (625, 625, 115278, '0:00:00', '18:00:00', '18:00:00', 2, 2, 0),
(623, 623, 115279, '0:00:00', '8:00:00', '15:05:00', 1, NULL, 0),
(624, 624, 115280, '0:00:00', '18:00:00, '19:00:00', 7, 7, 0),
(625, 625, 115281, '0:00:00', '18:00:00', '18:00:00', 2, 2, 0),
(625, 625, 115282, '0:00:00', '19:00:00', '18:00:00', 2, 2, 3600)
The above SQL has a few errors in it.

i have corrected it , see below

CREATE TABLE DELAYSECONDS(

ProgramID INT, ID INT, ContractHeaderID INT, ContractStartTime TIME, ProgramStartTime TIME ,ContractDetailFirstDay INT ,ProgramFirstDay INT, DelaySeconds INT)

INSERT INTO DELAYSECONDS

VALUES (625, 625, 115278, '18:00:00', '18:00:00', 2, 2, 0),

(623, 623, 115279, '8:00:00', '15:05:00', 1,NULL, 0),

(624, 624, 115280, '18:00:00', '19:00:00', 7, 7, 0),

(625, 625, 115281, '18:00:00', '18:00:00', 2, 2, 0),

(625, 625, 115282, '19:00:00', '18:00:00', 2, 2, 3600)

I learn from the footprints of giants......

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

JALLYKAMOZE (8/25/2016)
Alvin Ramard (8/25/2016)
It would also help if you provided us with proper information. The SQL logic you provided does not work!
CREATE TABLE DELAYSECONDS(
ProgramID INT, ID INT, ContractHeaderID INT StationID INT ContractStartTime TIME ProgramStartTime TIME ContractDetailFirstDay INT ProgramFirstDay INT DelaySeconds INT)
INSERT INTO DELAYSECONDS
VALUES (625, 625, 115278, '0:00:00', '18:00:00', '18:00:00', 2, 2, 0),
(623, 623, 115279, '0:00:00', '8:00:00', '15:05:00', 1, NULL, 0),
(624, 624, 115280, '0:00:00', '18:00:00, '19:00:00', 7, 7, 0),
(625, 625, 115281, '0:00:00', '18:00:00', '18:00:00', 2, 2, 0),
(625, 625, 115282, '0:00:00', '19:00:00', '18:00:00', 2, 2, 3600)
The above SQL has a few errors in it.
i have corrected it , see below
CREATE TABLE DELAYSECONDS(
ProgramID INT, ID INT, ContractHeaderID INT, ContractStartTime TIME, ProgramStartTime TIME ,ContractDetailFirstDay INT ,ProgramFirstDay INT, DelaySeconds INT)
INSERT INTO DELAYSECONDS
VALUES (625, 625, 115278, '18:00:00', '18:00:00', 2, 2, 0),
(623, 623, 115279, '8:00:00', '15:05:00', 1,NULL, 0),
(624, 624, 115280, '18:00:00', '19:00:00', 7, 7, 0),
(625, 625, 115281, '18:00:00', '18:00:00', 2, 2, 0),
(625, 625, 115282, '19:00:00', '18:00:00', 2, 2, 3600)

Did you try executing that code? I still get at least one error.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Phil Parkin SSC Guru Points: 246692 More actions · Answer 6

When you are entering SQL code in a post, please surround it with code="sql" IFCodes to improve its display.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

JALLY SSCommitted Points: 1865 More actions · Answer 7

Alvin Ramard (8/25/2016)
JALLYKAMOZE (8/25/2016)
Alvin Ramard (8/25/2016)
It would also help if you provided us with proper information. The SQL logic you provided does not work!
CREATE TABLE DELAYSECONDS(
ProgramID INT, ID INT, ContractHeaderID INT StationID INT ContractStartTime TIME ProgramStartTime TIME ContractDetailFirstDay INT ProgramFirstDay INT DelaySeconds INT)
INSERT INTO DELAYSECONDS
VALUES (625, 625, 115278, '0:00:00', '18:00:00', '18:00:00', 2, 2, 0),
(623, 623, 115279, '0:00:00', '8:00:00', '15:05:00', 1, NULL, 0),
(624, 624, 115280, '0:00:00', '18:00:00, '19:00:00', 7, 7, 0),
(625, 625, 115281, '0:00:00', '18:00:00', '18:00:00', 2, 2, 0),
(625, 625, 115282, '0:00:00', '19:00:00', '18:00:00', 2, 2, 3600)
The above SQL has a few errors in it.
i have corrected it , see below
CREATE TABLE DELAYSECONDS(
ProgramID INT, ID INT, ContractHeaderID INT, ContractStartTime TIME, ProgramStartTime TIME ,ContractDetailFirstDay INT ,ProgramFirstDay INT, DelaySeconds INT)
INSERT INTO DELAYSECONDS
VALUES (625, 625, 115278, '18:00:00', '18:00:00', 2, 2, 0),
(623, 623, 115279, '8:00:00', '15:05:00', 1,NULL, 0),
(624, 624, 115280, '18:00:00', '19:00:00', 7, 7, 0),
(625, 625, 115281, '18:00:00', '18:00:00', 2, 2, 0),
(625, 625, 115282, '19:00:00', '18:00:00', 2, 2, 3600)
Did you try executing that code? I still get at least one error.

what error did you get, i executed it and it populated the tables perfectly

I learn from the footprints of giants......

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 8

JALLYKAMOZE (8/25/2016)
Alvin Ramard (8/25/2016)
JALLYKAMOZE (8/25/2016)
Alvin Ramard (8/25/2016)
It would also help if you provided us with proper information. The SQL logic you provided does not work!
CREATE TABLE DELAYSECONDS(
ProgramID INT, ID INT, ContractHeaderID INT StationID INT ContractStartTime TIME ProgramStartTime TIME ContractDetailFirstDay INT ProgramFirstDay INT DelaySeconds INT)
INSERT INTO DELAYSECONDS
VALUES (625, 625, 115278, '0:00:00', '18:00:00', '18:00:00', 2, 2, 0),
(623, 623, 115279, '0:00:00', '8:00:00', '15:05:00', 1, NULL, 0),
(624, 624, 115280, '0:00:00', '18:00:00, '19:00:00', 7, 7, 0),
(625, 625, 115281, '0:00:00', '18:00:00', '18:00:00', 2, 2, 0),
(625, 625, 115282, '0:00:00', '19:00:00', '18:00:00', 2, 2, 3600)
The above SQL has a few errors in it.
i have corrected it , see below
CREATE TABLE DELAYSECONDS(
ProgramID INT, ID INT, ContractHeaderID INT, ContractStartTime TIME, ProgramStartTime TIME ,ContractDetailFirstDay INT ,ProgramFirstDay INT, DelaySeconds INT)
INSERT INTO DELAYSECONDS
VALUES (625, 625, 115278, '18:00:00', '18:00:00', 2, 2, 0),
(623, 623, 115279, '8:00:00', '15:05:00', 1,NULL, 0),
(624, 624, 115280, '18:00:00', '19:00:00', 7, 7, 0),
(625, 625, 115281, '18:00:00', '18:00:00', 2, 2, 0),
(625, 625, 115282, '19:00:00', '18:00:00', 2, 2, 3600)
Did you try executing that code? I still get at least one error.
what error did you get, i executed it and it populated the tables perfectly

Oops, my apologies. The error I got was due to not dropping the table I created earlier.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Sean Lange SSC Guru Points: 286573 More actions · Answer 9

So now that we have a working table and sample data what have you tried? I suspect you should be able to do most of this without any help at all. I don't quite understand why there are some variables a,b,c,d since that is just one piece of math. Put that part together. Once you are done you will need to handle the "if (d>0) then return d else o" part. This can easily be done with a case expression. Sure we could write this for you but then you aren't going to learn anything. Give it a shot and post your efforts. We can help dial you in to the correct answer.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 10

Other than using basic operations, you just need to use DATEDIFF and a CASE expression, maybe ISNULL to prevent NULL values. What are you having problems with?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 11

I asked a similar question on one of your earlier posts.....

for example

"ProgramFirstday" = 1

"ContractDetailFirstDay" = 7

how do you determine whether this is 6 day delay OR 1 day early?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

JALLY SSCommitted Points: 1865 More actions · Answer 12

J Livingston SQL (8/25/2016)
I asked a similar question on one of your earlier posts.....
for example
"ProgramFirstday" = 1
"ContractDetailFirstDay" = 7
how do you determine whether this is 6 day delay OR 1 day early?

The initial table has 0 false or 1 true for each day of the week, i used case expressions to return the first day of the week with 1 (true)as first day and same for the program table

to enable me know the difference between the days from contract and program tables , i passed values eg, 7 for monday, 6 for tuesday and so on, and the same for the program table . so if the first day of the contract table is monday 7 and the first day for the program table is tuesday 6? then the difference will be 1 day=7-6

1)i want to be able to convert this difference of 1 day to seconds

2) i also want to convert the diffrence between the contract start time and program start time to seconds, eg, if the difference is 2 hrs, convert it to seconds

add both together to give us the delay seconds

I learn from the footprints of giants......

Phil Parkin SSC Guru Points: 246692 More actions · Answer 13

JALLYKAMOZE (8/25/2016)
J Livingston SQL (8/25/2016)
I asked a similar question on one of your earlier posts.....
for example
"ProgramFirstday" = 1
"ContractDetailFirstDay" = 7
how do you determine whether this is 6 day delay OR 1 day early?
The initial table has 0 false or 1 true for each day of the week, i used case expressions to return the first day of the week with 1 (true)as first day and same for the program table
to enable me know the difference between the days from contract and program tables , i passed values eg, 7 for monday, 6 for tuesday and so on, and the same for the program table . so if the first day of the contract table is monday 7 and the first day for the program table is tuesday 6? then the difference will be 1 day=7-6
1)i want to be able to convert this difference of 1 day to seconds
2) i also want to convert the diffrence between the contract start time and program start time to seconds, eg, if the difference is 2 hrs, convert it to seconds
add both together to give us the delay seconds

1) Multiply by 86400.

2) Multiply by 3600.

Add (1) and (2). Which part is the difficult part?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.