sql help

I am trying to update the table, but it seems like I need to have a recursive call. So at first I need to find all the booked classes , then for each class I need to find all classes with 59 minutes from start till end of the class , and so on and update the location


<cfquery name="qGetSessionDetail" datasource="#datasource#">
					SELECT SU.sessionStart,SU.sessionEnd,SU.instructorKey,S.carKey
					FROM SessionUnit SU
					inner join SESSION S on s.sessionKey = SU.sessionKey
					WHERE SU.sessionKey = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.selected#">
				</cfquery>

                <!---Check if the location is the hub and if it has remote locations. --->
                
                <cfquery name="qryCheckLocation" datasource="#datasource#">
                	SELECT locCatkey,locHubID from Location L 
                	WHERE locationKEy = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.lockey#">
                </cfquery> 
                
                <cfset isHub = 0>
                <cfif qryCheckLocation.locCatKey eq 4>
                    <cfset isHub = 1>
                </cfif>

				<cfquery name="qGetContiguousSessions" datasource="#datasource#">
					SELECT S.sessionKey,SU.sessionStart,SU.sessionEnd
					FROM  Session S
					INNER JOIN Sessionunit SU on SU.sessionKey = S.sessionKey
					inner join SessionMap SM on SM.sessionKey = S.sessionKey
					WHERE
					0=0
					and SM.userKey <> 0 
					
					AND (SU.instructorKey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetSessionDetail.instructorKey#"> OR S.carKey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetSessionDetail.carKey#">)
					<!-----and s.sessionKey <> <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.selected#">--->
					and Convert(DATE,SU.sessionStart) = <cfqueryparam cfsqltype="cf_sql_date" value="#qGetSessionDetail.sessionStart#">
				</cfquery>
<Cfdump var="#qGetContiguousSessions#" label="qGetContiguousSessions">
				<cfif qGetContiguousSessions.recordcount gt 0>

					<cfloop query="qGetContiguousSessions">
								<cfquery name="qGetContiguousSessionsForEachSession" datasource="#datasource#">
									SELECT S.sessionKey,SU.instructorKey,S.locationKey
									FROM  Session S
									INNER JOIN Sessionunit SU on SU.sessionKey = S.sessionKey
									WHERE
									 0=0
									 and (SU.instructorKey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetSessionDetail.instructorKey#"> OR S.carKey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetSessionDetail.carKey#">)
									 AND s.sessionKey <> <cfqueryparam cfsqltype="cf_sql_integer" value="#url.selected#">
									 AND (
											((<cfqueryparam value="#qGetContiguousSessions.sessionStart#" cfsqltype="cf_sql_timestamp"> between DateAdd(minute, -59, SU.sessionStart) and DateAdd(minute, 59, SU.sessionEnd)
												OR <cfqueryparam value="#qGetContiguousSessions.sessionEnd#" cfsqltype="cf_sql_timestamp"> between DateAdd(minute, -59, SU.sessionStart) and DateAdd(minute, 59, SU.sessionEnd))
											)
										    )
								</cfquery>
					<cfdump var="#qGetContiguousSessionsForEachSession#" label="qGetContiguousSessionsForEachSession">		
								<cfloop query="qGetContiguousSessionsForEachSession">
									<cfif qGetContiguousSessionsForEachSession.instructorKey eq qGetSessionDetail.instructorKey>
    									<cfquery datasource="#datasource#">
    										UPDATE session
    										SET locationkey = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.lockey#">
    										WHERE sessionkey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetContiguousSessionsForEachSession.sessionkey#">
    									</cfquery>
    								<cfelse>
    								    <!---if it is a different instructor the session must be at the hub. --->
    								    <cfif isHub eq 0>
        								    <cfquery datasource="#datasource#">
                                                UPDATE session
                                                SET locationkey = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.lockey#">
                                                WHERE sessionkey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetContiguousSessionsForEachSession.sessionkey#">
                                            </cfquery>
                                        <cfelse>
                                            <cfquery datasource="#datasource#">
                                                UPDATE session
                                                SET locationkey = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.lockey#">
                                                WHERE sessionkey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetContiguousSessionsForEachSession.sessionkey#">
                                            </cfquery>
                                        </cfif>
									</cfif>
								</cfloop>

					</cfloop>

				</cfif>

Open in new window

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, you can.
I just wanted you to put the question in a SQL way instead of code so I can understand it better.
Or post the tables structure and some data sample for what you have now and the expected results.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not a ColdFusion guy but since this question didn't have any answer until now I have a question for you:
- There's a chance to write a stored procedure to perform what you want? Then in your code you'll only need to call the SP and pass the correct parameters.
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
I am not really good at stored procedures. If you would not mind helping me
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sure.
Which SQL statements you already have and running and what's missing to achieve what you want?
Then we'll put them together in a stored procedure that will execute the code following a logical order.
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
The query that I am running is in my question post. Can I do query inside of the SP
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
I still need advice on this
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.