August 24, 2016 at 1:45 pm
hello,
i need help with this query.
i will work you through the process maybe ii can be assisted
/* The purpose of this script is to calculate the Delay seconds from the differences in days
and hours allconverted to seconds to be used to update a column in the Dontractdetail table*/
/* selecting days that have true conditions and returning those days as Contractdetailfirstday*
same process is done to select the programfirstdays*/
BEGIN
IF OBJECT_ID(N'tempdb..#updatecontractdetailtable') IS NOT NULL
DROP table #updatecontractdetailtable;
SELECT programID, Stationid, CASE WHEN Monday = 1 THEN 'Monday'
WHEN Tuesday = 1 THEN 'Tuesday'
WHEN Wednesday = 1 THEN 'Wednesday'
WHEN Thursday = 1 THEN 'Thursday'
WHEN friday = 1 THEN 'Friday'
WHEN saturday = 1 THEN 'Saturday'
WHEN sunday = 1 THEN 'Sunday'
END AS ContractdetailFirstDay,
StartTime AS Contractdetailstarttime,contractheaderid,DelaySeconds
into #updatecontractdetailtable
from ContractDetail
WHERE ProgramID IS NOT null
ORDER BY programid,Stationid;
END
BEGIN
IF OBJECT_ID(N'tempdb..#updateprogramtables') IS NOT NULL
DROP table #updateprogramtables;
SELECT ID ,Name,StartTime AS Programstarttime,CASE WHEN Monday = 1 THEN 'Monday'
WHEN Tuesday = 1 THEN 'Tuesday'
WHEN Wednesday = 1 THEN 'Wednesday'
WHEN Thursday = 1 THEN 'Thursday'
WHEN friday = 1 THEN 'Friday'
WHEN saturday = 1 THEN 'Saturday'
WHEN sunday = 1 THEN 'Sunday'
END AS ProgramFirstDay
INTO #updateprogramtables
FROM Program
END
--SELECT * FROM dbo.ContractDetail
--SELECT * FROM PROGRAM
SELECT * FROM #updateprogramtables
SELECT * FROM #updatecontractdetailtable
/* this is where the cursor is used to select the days thats require conversion from days to seconds and also convert
the hrs difference into seconds to be added tup to get the delayseconds total for the each ID/programid , what am i doing wrong ?*/
BEGIN
IF EXISTS ( SELECT * FROM tempdb..sysobjects
WHERE id = OBJECT_ID(N'tempdb..#DELAYSECONDSTABLE'))
DROP table #DELAYSECONDSTABLE
CREATE TABLE #DELAYSECONDSTABLE (daydelayseconds INT,hrsdelayseconds INT)
DECLARE @programID INT, @stationID INT ,@contractdetailfirstday float , @contractdetailStarttime date ,@contractheaderID Int,@Delayseconds Int,
@ID INT,@Name Varchar (100), @ProgramStarttime date , @ProgramFirstday BIT , @daydelayseconds int, @hrsdelayseconds Int
Declare Contractdetailcursor cursor for
select programID, Stationid,ContractdetailFirstDay,Contractdetailstarttime,contractheaderid,DelaySeconds
from #updatecontractdetailtable
SET @ContractdetailFirstDay = CASEWHEN @ContractdetailFirstDay ='Monday'THEN REPLACE('monday', 'monday', '7')
WHEN @contractdetailfirstday ='tuesday'THEN REPLACE('tuesday','tuesday','6')
WHEN @contractdetailfirstday ='wednesday' THEN REPLACE('wednesday','wednesday','5')
WHEN @contractdetailfirstday ='Thursday'THEN REPLACE('thursday','thursday','4')
WHEN @contractdetailfirstday ='friday'THEN REPLACE('friday','friday','3')
WHEN @contractdetailfirstday ='saturday'THEN REPLACE('saturday','saturday','2')
WHEN @contractdetailfirstday ='sunday'THEN REPLACE('sunda','sunday','1')
END
SET @ProgramFirstday = CASEWHEN @ProgramFirstday ='Monday'THEN REPLACE('monday', 'monday', '7')
WHEN @ProgramFirstday ='tuesday'THEN REPLACE('tuesday','tuesday','6')
WHEN @ProgramFirstday ='wednesday' THEN REPLACE('wednesday','wednesday','5')
WHEN @ProgramFirstday ='Thursday'THEN REPLACE('thursday','thursday','4')
WHEN @ProgramFirstday ='friday'THEN REPLACE('friday','friday','3')
WHEN @ProgramFirstday ='saturday'THEN REPLACE('saturday','saturday','2')
WHEN @ProgramFirstday ='sunday'THEN REPLACE('sunday','sunday','1')
END
END
OPEN Contractdetailcursor;
FETCH NEXT FROM Contractdetailcursor into @programID, @Stationid,@ContractdetailFirstDay,@Contractdetailstarttime,@contractheaderid,@DelaySeconds
WHILE @@FETCH_STATUS=0
BEGIN
SET @DelaySeconds=0
DECLARE programdetailcursor CURSOR FOR
SELECT ID,Name,Programstarttime,ProgramFirstDay
FROM #updateprogramtables
OPEN programdetailcursor;
WHILE @@FETCH_STATUS =0
FETCH NEXT FROM programdetailcursor INTO @ID,@Name,@ProgramStarttime ,@ProgramFirstday
BEGIN
IF @contractdetailfirstday-@programfirstday>0 AND @ID =@programID
BEGIN
SET @daydelayseconds = (@contractdetailfirstday-@programfirstday)*24*60*60
SET @hrsdelayseconds= CASE
when DATEDIFF(minute,@Contractdetailstarttime,@ProgramStarttime) < 60 then DATEDIFF(minute,@Contractdetailstarttime,@ProgramStarttime)
when DATEDIFF(minute,@Contractdetailstarttime,@ProgramStarttime) >= 60
then '60,'+ cast( (cast(DATEDIFF(minute,@Contractdetailstarttime,@ProgramStarttime) as int )-60) as nvarchar(50) )
END
INSERT INTO #DELAYSECONDSTABLE ( daydelayseconds , hrsdelayseconds)
SELECT @daydelayseconds,@hrsdelayseconds
END
ELSE
BEGIN
IF @contractdetailfirstday-@contractdetailfirstday=0
BEGIN
SET @DELAYSECONDS=0
FETCH NEXT FROM programdetailcursor INTO @ID,@Name,@ProgramStarttime ,@ProgramFirstday;
END;
CLOSE programdetailcursor;
DEALLOCATE programdetailcursor;
FETCH NEXT FROM Contractdetailcursor into @programID, @Stationid,@ContractdetailFirstDay,@Contractdetailstarttime,@contractheaderid,@DelaySeconds;
END;
CLOSE Contractdetailcursor;
DEALLOCATE Contractdetailcursor;
END
END
I learn from the footprints of giants......
August 24, 2016 at 2:00 pm
1) we have no sample tables and data and expected output to help you with the code.
2) you have multiple cursors (not even declared fast_forward). Row-based processing in SQL Server is to be avoided in almost every scenario.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 24, 2016 at 2:11 pm
Those CASE statements with the REPLACE??? They make me question if you understand what you're trying to do.
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 24, 2016 at 2:14 pm
While agreeing with TheSQLGuru that cursors are to be avoided, the way to calculate the number of seconds is fairly simple.
Declare @Start datetime = '8/23/2016 8:30am'
,@Stop datetime = '8/24/2016 12:30pm'
select datediff(second,@start,@stop) as seconds
If you want a solution that involves how you are joining your tables together and filtering out unwanted rows, please supply the following:
(1) scripts to create all relevant tables
(2) scripts to INSERT sample data into those tables
(3) what you would expect results to look like from your sample data.
Please understand that descriptions are hard to understand because we have no background with your data. We don't need you to work us through your process. Just show us the objective and you will get a more efficient solution. But you have to draw us a picture of what you want, then supply sample data (in tables) so we can code and TEST solutions.
Thanks for helping us help you. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2016 at 2:39 pm
Thanks Guys for your imputs, see attached sample data in excel below, this data is is populated in the Temp tables
#updateprogramtables
#updatecontractdetailtable
My purpose is to get the difference in the Contractdetailfirstday-Programdetail first day i.e based on the id column =programid column
see description below:
contract detailFirstday(Minus)Program detail firstday = A
A*24*60*60 = Delayseconds in day (B)
Contractdetailstarttime (minus) Programdetailstartime =C
B+C= D 'delayseconds'
IF (D>0) THEN RETURN D
ELSE 0
does this make sense?
I learn from the footprints of giants......
August 24, 2016 at 3:18 pm
Given that this is a free forum, I don't know that you will find anyone that will take the time to set up imports from a 5+MB excel spreadsheet into multiple tables (for which no table definition script is given) just to refactor your script. Perhaps someone can solve the logic without needing the data.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 24, 2016 at 3:28 pm
can I please get some clarification.....for example
in #updateprogramtables......"ProgramFirstDay" = Monday and "Programstarttime" = 05:00:00
and
in #updatecontractdetailtable .... "ContractdetailFirstDay" = Sunday and "Contractdetailstarttime" = 23:00:00
what results are you expecting?
eg...is this a long delay or an earlier than scheduled start?
how are you defining you days of the week precedence?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 24, 2016 at 3:32 pm
TheSQLGuru (8/24/2016)
Given that this is a free forum, I don't know that you will find anyone that will take the time to set up imports from a 5+MB excel spreadsheet into multiple tables (for which no table definition script is given) just to refactor your script. Perhaps someone can solve the logic without needing the data.
+1
can you please define datatypes for columns Contractdetailstarttime and Programstarttime
suggest you cut down on the volume of data and post sufficient and suitable data that demonstrates all possibilities and expected results.
here is a good place to start
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 24, 2016 at 3:41 pm
+1 as well.
Good luck, guys.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2016 at 3:50 pm
just a thought...but looking at this post and a previous post of yours........ we might be missing a few tricks to get you to your results, maybe...maybe not.
Are you able to post representative data (CREATE/INSERT) for the two underlying tables...."ContractDetail" and "Program". ???
I fully appreciate that you have provided data for your own designed temp tables....but just possibly, given the base data, we maybe able to suggest an alternative.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 24, 2016 at 3:51 pm
I haven't looked at the calculations, but replace your nested cursors with this. And why go to the trouble of specifying a name for the day of the week when what you want is a number. Just calculate the number in the first place.
SELECT *,
CASE
WHEN cd.Monday = 1 THEN 7
WHEN cd.Tuesday = 1 THEN 6
WHEN cd.Wednesday = 1 THEN 5
WHEN cd.Thursday = 1 THEN 4
WHEN cd.Friday = 1 THEN 3
WHEN cd.Saturday = 1 THEN 2
WHEN cd.Sunday = 1 THEN 1
END AS ContractDetailFirstDay,
CASE
WHEN p.Monday = 1 THEN 7
WHEN p.Tuesday = 1 THEN 6
WHEN p.Wednesday = 1 THEN 5
WHEN p.Thursday = 1 THEN 4
WHEN p.Friday = 1 THEN 3
WHEN p.Saturday = 1 THEN 2
WHEN p.Sunday = 1 THEN 1
END AS ProgramFirstDay
FROM ContractDetail cd
INNER JOIN Program p
ON cd.programID = p.ID
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 25, 2016 at 10:08 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 11:43 am
JALLYKAMOZE (8/25/2016)
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?
No, it doesn't make sense. Using DATEDIFF() as was already mentioned makes sense. The built-in functions are generally going to be much more efficient and accurate than anything that you code (especially if you're going to use cursors).
pls assist as this is due today.
thanks
You do realize that we are all volunteering our time and effort. If you need something done in a specific timeline, then you should look into hiring a contractor.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 25, 2016 at 11:58 am
drew.allen (8/25/2016)
JALLYKAMOZE (8/25/2016)
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?
No, it doesn't make sense. Using DATEDIFF() as was already mentioned makes sense. The built-in functions are generally going to be much more efficient and accurate than anything that you code (especially if you're going to use cursors).
pls assist as this is due today.
thanks
You do realize that we are all volunteering our time and effort. If you need something done in a specific timeline, then you should look into hiring a contractor.
Drew
thanks you for your response.
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