Advertisement

09.16.2008 at 01:10AM PDT, ID: 23734343
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
8.2

Cursor on Inner Query

Asked by martgriff in SQL Query Syntax, MS SQL Server

I have the below query which is meant to get all free timeslots. The issue im having is with the cursor at the bottom where its working out what slots to show if the time is PM. The issue is it bring back several tables rather than just rows, and also the PM timeslot is only showing for the first row in the tables, the other rows are still showing AM times as well.

Any suggestions as to where ive gone wrong with the cursor or is a cursor not the best way to go about this?

Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:

ALTER PROCEDURE [dbo].[sp_EngineerTimeslots]
 
	
	@STARTDATE DATETIME,
	@ORIGIN VARCHAR(20),
	@JOBLENGTH INT = 0,
	@PRIORITY INT,
	@CONTRACT VARCHAR(3),
	@TIME VARCHAR(10)
	
			
AS
 
SET NOCOUNT ON
 
	DECLARE @SQLTIMESLOTS VARCHAR(3000)
	DECLARE @TOTALDAYS INT
	DECLARE @SQLSELECT VARCHAR(1000)
	DECLARE @SQLWHERE VARCHAR(1000)
	DECLARE @COUNTER INT
	DECLARE @STARTTIME DATETIME
	DECLARE @MIDDAYTIME DATETIME
	DECLARE @ENDTIME DATETIME
	DECLARE @CURSOR_PM	VARCHAR(3000)
	DECLARE @CURSOR_AM	VARCHAR(3000)
	DECLARE @CURSOR_AT	VARCHAR(3000)
	DECLARE @CURSOR_FC	VARCHAR(3000)
	DECLARE @CURSOR_ENGINEER VARCHAR(30)
	DECLARE @CURSOR_BOOKEDDATE DATETIME
	DECLARE @CURSOR_BOOKEDFROMTIME DATETIME
	DECLARE @CURSOR_BOOKEDTOTIME DATETIME
	DECLARE @CURSOR_TOTALDAYS INT
	DECLARE @CURSOR_PRIMARYSKILL VARCHAR(20)
	DECLARE @CURSOR_SECONDARYSKILL VARCHAR(20)
	DECLARE @CURSOR_JOBMINS INT
	DECLARE @CURSOR_MINPMTIME DATETIME
	DECLARE @CURSOR_BTIME DATETIME
 
Select @STARTTIME = CONVERT(VARCHAR(10), @STARTDATE, 101) + ' ' + CONVERT(VARCHAR(5), ContractStartTime, 114) From tbl_contract
Select @MIDDAYTIME = CONVERT(VARCHAR(10), @STARTDATE, 101) + ' ' + CONVERT(VARCHAR(5), ContractMiddayTime, 114) From tbl_contract 	
Select @ENDTIME = CONVERT(VARCHAR(10), @STARTDATE, 101) + ' ' + CONVERT(VARCHAR(5), ContractEndTime, 114) From tbl_contract 
		
Select @TOTALDAYS = @PRIORITY/24
 
If @TOTALDAYS = 0
	Select @TOTALDAYS = 1
 
Select @COUNTER = 1
 
If @JOBLENGTH = 0
Select @JOBLENGTH = RepairJobLength From tbl_contract Where contract = @CONTRACT
 
BEGIN
 
--Create Temp Table
 
Create table #TempEngineer (TempEngineer varchar(30), BookedDate DateTime, BookedFromTime DateTime, BookedToTime DateTime, TotalDays INT, PrimarySkill VARCHAR(20), SecondarySkill VARCHAR(20), JobMins INT, MinPMTime DateTime, BTime DATETIME)
 
While (@COUNTER <= @TOTALDAYS)
	Begin
 
		--Get details of active engineers from engineer table
 
	INSERT #TempEngineer 
			SELECT     TOP 100 PERCENT 
					dbo.tbl_engineer.ENGINEER AS Engineer, 
					@STARTDATE AS BDate, 
					CAST((CONVERT(VARCHAR(10), @STARTDATE, 101) + ' ' + CONVERT(VARCHAR(5), dbo.tbl_engineer.STARTTIME, 114)) AS DATETIME) AS FTime, 
					CAST((CONVERT(VARCHAR(10), @STARTDATE, 101) + ' ' + CONVERT(VARCHAR(5), dbo.tbl_engineer.ENDTIME, 114)) AS DATETIME) AS TTime, 
					@TOTALDAYS AS TotalDays, 
					dbo.tbl_engineer.Primaryskill AS PrimarySkill, 
					dbo.tbl_engineer.Secondaryskill AS SecondarySkill,
					NULL AS JobMins,
					NULL AS MinPMTime,
					NULL AS BTime
						
			FROM    dbo.tbl_engineer 
			WHERE     (dbo.tbl_engineer.ACTIVE = 1) AND (dbo.tbl_engineer.PrimarySkill = @ORIGIN OR dbo.tbl_engineer.SecondarySkill = @ORIGIN) 
			AND		Engineer NOT IN (SELECT Engineer	
					FROM  schedule  
					WHERE      bookedDate <= DATEADD(DAY, @TOTALDAYS, CONVERT(DATETIME, @STARTDATE, 103))
					AND		   bookedDate >= @STARTDATE)
 
				
		INSERT #TempEngineer 
 
				--get details of schedules booked form schedule table
 
				SELECT     TOP 100 PERCENT Schedule.Engineer AS Engineer, 
							Schedule.BookedDate AS BDate,
							Schedule.FromTime AS FTime, 
							ISNULL(Schedule.ToTime, CAST((CONVERT(VARCHAR(10), @STARTDATE, 101) + ' ' + CONVERT(VARCHAR(5), ENDTIME, 114)) AS DATETIME)) AS ToTime, 
							@TOTALDAYS AS TotalDays, 
							NULL AS PrimarySkill, 
							NULL AS SecondarySkill,
							NULL AS JobMins,
							NULL AS MinPMTime,
							NULL AS BTime
							
				FROM       
						 (SELECT     
								Engineer, 
								BookedDate, 
								ToTime AS FromTime,
								(SELECT     MIN(fromTime) FROM schedule x WHERE x.engineer = s1.engineer AND x.bookedDate = s1.bookedDate AND x.fromTime >= s1.ToTime) AS ToTime 
							FROM       schedule s1 
							WHERE      bookedDate <= DATEADD(DAY, @TOTALDAYS, CONVERT(DATETIME, @STARTDATE, 103))
							AND		   bookedDate >= @STARTDATE	
							
								UNION ALL 
 
							SELECT     e2.Engineer, s2.BookedDate,  CONVERT(VARCHAR(10), @STARTDATE, 101) + ' ' + CONVERT(VARCHAR(5), e2.StartTime, 114) AS FromTime, MIN(s2.FromTime) AS ToTime 
							FROM       tbl_engineer e2 INNER JOIN schedule s2 ON s2.engineer = e2.engineer						
							WHERE      s2.bookedDate <= DATEADD(DAY, @TOTALDAYS, CONVERT(DATETIME, @STARTDATE, 103))
							AND		   s2.bookedDate >= @STARTDATE
							
							GROUP BY   e2.Engineer, s2.BookedDate, e2.StartTime 
							HAVING     MIN(s2.FromTime) > e2.StartTime) 
 
				Schedule INNER JOIN dbo.tbl_engineer engineer ON engineer.ENGINEER = Schedule.Engineer 
	
		Select @COUNTER = @COUNTER + 1
		Select @STARTDATE = DATEADD(Day, 1, @STARTDATE)
 
 
	End
 
END
--Data from above select statements which is in the temp table			
 
IF @TIME = 'AM'
			SELECT  TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, PrimarySkill, SecondarySkill, DATEDIFF(mi,BookedFromTime, BookedToTime) AS JobMins 
			FROM #TempEngineer 
			WHERE (DATEDIFF(mi,BookedFromTime, BookedToTime) >= @JOBLENGTH) 
				AND (DATEDIFF(mi,BookedFromTime, BookedToTime) < 600) 
				--AND BookedFromTime < @MIDDAYTIME
			ORDER BY TempEngineer, BookedFromTime
ELSE
IF @TIME = 'AT'
			SELECT  TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, PrimarySkill, SecondarySkill, DATEDIFF(mi,BookedFromTime, BookedToTime) AS JobMins 
			FROM #TempEngineer 
			WHERE (DATEDIFF(mi,BookedFromTime, BookedToTime) >= @JOBLENGTH) 
				AND (DATEDIFF(mi,BookedFromTime, BookedToTime) < 600) 
			ORDER BY TempEngineer, BookedFromTime
ELSE
IF @TIME = 'FC'
			SELECT  TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, PrimarySkill, SecondarySkill, DATEDIFF(mi,BookedFromTime, BookedToTime) AS JobMins 
			FROM #TempEngineer 
			WHERE (DATEDIFF(mi,BookedFromTime, BookedToTime) >= @JOBLENGTH) 
				AND (DATEDIFF(mi,BookedFromTime, BookedToTime) < 600)
				AND BookedFromTime BETWEEN @STARTTIME AND DATEADD(hh, 1, @STARTTIME)
			ORDER BY TempEngineer, BookedFromTime
ELSE
IF @TIME = 'PM'
SELECT @CURSOR_PM = ''
DECLARE C_PM CURSOR FOR
 
			SELECT TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, PrimarySkill, SecondarySkill, DATEDIFF(minute, BookedFromTime, BookedToTime) AS JobMins, MinPMTime, BookedFromTime AS BTime
			FROM #TempEngineer 
 
OPEN C_PM
FETCH NEXT FROM C_PM INTO @CURSOR_ENGINEER, @CURSOR_BOOKEDDATE, @CURSOR_BOOKEDFROMTIME, @CURSOR_BOOKEDTOTIME, @CURSOR_TOTALDAYS, @CURSOR_PRIMARYSKILL, @CURSOR_SECONDARYSKILL, @CURSOR_JOBMINS, @CURSOR_MINPMTIME, @CURSOR_BTIME
WHILE @@FETCH_STATUS = 0
BEGIN
 
			SELECT TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, PrimarySkill, SecondarySkill, DATEDIFF(mi,BookedFromTime, BookedToTime) AS JobMins, MinPMTime, BookedFromTime AS BTime
			FROM (
					SELECT TempEngineer, BookedDate, BookedFromTime AS BTime, BookedToTime, TotalDays, PrimarySkill, SecondarySkill, JobMins, MinPMTime,
							CASE
								WHEN BookedFromTime >= @MIDDAYTIME Then BookedFromTime
								WHEN BookedFromTime < @MIDDAYTIME Then MinPMTime
							END AS BookedFromTime
					FROM (
							SELECT  TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, PrimarySkill, SecondarySkill, DATEDIFF(mi,BookedFromTime, BookedToTime) AS JobMins,
									max(CASE WHEN BookedFromTime < @MIDDAYTIME AND IsNull(PMAppointments, 0) >= 0 THEN @MIDDAYTIME
											 WHEN BookedFromTime > @MIDDAYTIME AND IsNull(PMAppointments, 0) >= 0 THEN BookedFromTime END) AS MinPMTime
							FROM (	
									SELECT TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, PrimarySkill, SecondarySkill, DATEDIFF(mi,BookedFromTime, BookedToTime) AS JobMins,
											CASE  
												WHEN BookedFromTime >= @MIDDAYTIME THEN DATEDIFF(mi,BookedFromTime, @ENDTIME)/@JOBLENGTH
												WHEN BookedToTime >= @MIDDAYTIME THEN DATEDIFF(mi,@MIDDAYTIME, BookedToTime)/@JOBLENGTH
											END As PMAppointments				 
									FROM #TempEngineer 	
							
								)INSIDEQUERY_A
								GROUP BY TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, PrimarySkill, SecondarySkill, JobMins
						)INSIDEQUERY_B				
						GROUP BY TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, PrimarySkill, SecondarySkill, JobMins, MinPMTime
				)OUTSIDEQUERY		
				WHERE (DATEDIFF(mi,BookedFromTime, BookedToTime) >= @JOBLENGTH)	
						AND (DATEDIFF(mi,BookedFromTime, BookedToTime) < 600)
				GROUP BY TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, PrimarySkill, SecondarySkill, JobMins, MinPMTime
 
FETCH NEXT FROM C_PM INTO @CURSOR_ENGINEER, @CURSOR_BOOKEDDATE, @CURSOR_BOOKEDFROMTIME, @CURSOR_BOOKEDTOTIME, @CURSOR_TOTALDAYS, @CURSOR_PRIMARYSKILL, @CURSOR_SECONDARYSKILL, @CURSOR_JOBMINS, @CURSOR_MINPMTIME, @CURSOR_BTIME
END	
 
CLOSE C_PM
DEALLOCATE C_PM
RETURN @CURSOR_PM
[+][-]09.16.2008 at 01:58AM PDT, ID: 22486228

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]09.16.2008 at 02:47AM PDT, ID: 22486497

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]09.16.2008 at 03:56AM PDT, ID: 22486896

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: SQL Query Syntax, MS SQL Server
Sign Up Now!
Solution Provided By: martgriff
Participating Experts: 1
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628