August 25, 2016 at 10:09 am
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......
August 25, 2016 at 10:12 am
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/
August 25, 2016 at 10:14 am
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.aspxAnd 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......
August 25, 2016 at 10:18 am
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.
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]
August 25, 2016 at 10:26 am
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......
August 25, 2016 at 10:31 am
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.
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]
August 25, 2016 at 10:31 am
When you are entering SQL code in a post, please surround it with code="sql" IFCodes to improve its display.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 25, 2016 at 10:33 am
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......
August 25, 2016 at 10:37 am
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.
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]
August 25, 2016 at 10:54 am
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/
August 25, 2016 at 10:56 am
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?
August 25, 2016 at 11:16 am
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
August 25, 2016 at 11:34 am
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......
August 25, 2016 at 12:51 pm
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply