Question about Loops - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question about Loops

Author

Topic

bluvoodoo7
Starting Member

7 Posts

Posted - 2010-07-14 : 15:58:42

OK, so I'm new to this. Very new and I'm trying to make my way through this project.

Here's the breakdown:

I have a table in my DB that contains 10 "rounds" of fields. Open1, Open2, Open3, etc.
I have a stored procedure along with some functions that I want to use to evaluate each "round" of data. i.e. Open1, Close1, OpenTime1, CloseTime1, etc. get evaluated with results produced and then it does the same thing for "round" 2. Below is my code that works for a single round. How can I change this code so that it will do the calculations for each "round"?

Thanks for any help/guidance!

[code]
U

-T.

bluvoodoo7
Starting Member

7 Posts

Posted - 2010-07-14 : 16:04:53

So why didn't my code come over? I used the code block as required. Am I just dense?

-T.

bluvoodoo7
Starting Member

7 Posts

Posted - 2010-07-14 : 16:21:03

[code]USE [Corporate_Express_2009]
GO
/****** Object: StoredProcedure [dbo].[up_dataLaserOrdersTurnTime] Script Date: 07/12/2010 15:25:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[up_EbusinessOrdersTurnTime]
@BEGIN_DATE datetime,
@END_DATE datetime
AS
SET @BEGIN_DATE = Convert(datetime, Convert(varchar(64), @BEGIN_DATE, 101))
SET @END_DATE = Convert(datetime, Convert(varchar(64), @END_DATE, 101))

DECLARE @LTE24hrs int
DECLARE @GT24LTE48hrs int
DECLARE @GT48LTE72hrs int
DECLARE @GT72hrs int

SELECT
[dbo].fn_calculateTurnAroundTimeHours([dbo].fn_convertDateAndTime([view E_Business].[Open1], [view E_Business].[OpenTime1]),
[dbo].fn_convertDateAndTime([view E_Business].[On1HoldDate1], [view E_Business].[On1HoldTime1]),
[dbo].fn_convertDateAndTime([view E_Business].[Off1HoldDate1], [view E_Business].[Off1HoldTime1]),
[dbo].fn_convertDateAndTime([view E_Business].[On2HoldDate1], [view E_Business].[On2HoldTime1]),
[dbo].fn_convertDateAndTime([view E_Business].[Off2HoldDate1], [view E_Business].[Off2HoldTime1]),
[dbo].fn_convertDateAndTime([view E_Business].[Close1], [view E_Business].[CloseTime1])) AS [Turn Time Hours],
[dbo].fn_calculateTurnAroundTimeDays([dbo].fn_convertDateAndTime([view E_Business].[Open1], [view E_Business].[OpenTime1]),
[dbo].fn_convertDateAndTime([view E_Business].[On1HoldDate1], [view E_Business].[On1HoldTime1]),
[dbo].fn_convertDateAndTime([view E_Business].[Off1HoldDate1], [view E_Business].[Off1HoldTime1]),
[dbo].fn_convertDateAndTime([view E_Business].[On2HoldDate1], [view E_Business].[On2HoldTime1]),
[dbo].fn_convertDateAndTime([view E_Business].[Off2HoldDate1], [view E_Business].[Off2HoldTime1]),
[dbo].fn_convertDateAndTime([view E_Business].[Close1], [view E_Business].[CloseTime1])) AS [Turn Time Days]
INTO #turnTimes
FROM
[view E_Business]
WHERE
[view E_Business].[Close1] BETWEEN @BEGIN_DATE AND @END_DATE

SET @LTE24hrs = (SELECT COUNT(*) FROM #turnTimes WHERE [Turn Time Hours] <= 24)
SET @GT24LTE48hrs = (SELECT COUNT(*) FROM #turnTimes WHERE [Turn Time Hours] > 24 AND [Turn Time Hours] <= 48)
SET @GT48LTE72hrs = (SELECT COUNT(*) FROM #turnTimes WHERE [Turn Time Hours] > 48 AND [Turn Time Hours] <= 72)
SET @GT72hrs = (SELECT COUNT(*) FROM #turnTimes WHERE [Turn Time Hours] > 72)

SELECT
COUNT(*) AS [Num Jobs],
AVG([Turn Time Hours]) AS [Average Turn Time Hours],
AVG(Convert(decimal(10,2), [Turn Time Days])) AS [Average Turn Time Days],
@LTE24hrs AS [LTE24hrs],
@GT24LTE48hrs AS [GT24LTE48hrs],
@GT48LTE72hrs AS [GT48LTE72hrs],
@GT72hrs AS [GT72hrs]
FROM
#turnTimes

DROP TABLE #turnTimes[/code]

-T.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-14 : 17:21:10

I'm a little confused since I don't know what the base data is. Perhaps if you posted some DDL, DML and expected results we could help you better. Here is a link that might help post your information:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

bluvoodoo7
Starting Member

7 Posts

Posted - 2010-07-15 : 09:16:19

OK, as you can see in the code above, we have the following fields in the DB:
Open1 (date)
OpenTime1 (time)
On1HoldDate1 (date)
On1HoldTime1 (time)
Off1HoldDate1 (date)
Off1HoldTime1 (time)
On2HoldDate1 (date)
On2HoldTime1 (time)
Off2HoldDate1 (date)
Off2HoldTime1 (time)
Close1 (date)
CloseTime1 (time)

We have 10 "sets" of these fields with corresponding numbers at the end. Set 2 becomes Open2, OpenTime2, etc.

In the code you'll see that there are 2 outside functions being called. One converts the separated date and time fields into an appropriate datetime format and then it passes those into another function that Calculates the Turn around time in hours. Turn around time is the amount of time between open and close, with the "On Hold" spans subtracted out and it accounts for weekends as well.

Then there is another set of functions that do the same thing but calculate the turn time in days instead of hours.

It populates all of this data into a temp table called #turnTimes which would look something like:

ID | Turn Time Hours | Turn Time Days
1 | 3.21 | 1
2 | 1.12 | 1
3 | 9.67 | 2

Then further down in the code it does some counting functions and some averaging which gives me a final result of something like this:

Num Jobs | Average Turn Time Hours | Average Turn Time Days | LTE24hrs | GT24LTE48hrs | GT48LTE72hrs | GT72hrs
72 | 5.2 | 1 | 32 | 30 | 5 | 5

So as I stated before, this stored procedure works right now, but it only works for the items in "Round 1". I would like to accomplish this same task for all 10 "Rounds" without building a separate procedure for all 10 and without duplicating the code for all 10. I was thinking some kind of loop would work, but I tried a WHILE loop and couldn't get it to work because you can't use variables in your column names during a SELECT statement.

Hope this helps, cuz I would like to get this figured out. Thanks!!

-T.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-15 : 13:00:44

The best suggestion I could make (other than redesigning your table) is to try the UNPIVOT operator, it's documented in Books Online. You could simulate UNPIVOT with multiple queries UNIONed together too. I'm not even sure UNPIVOT will work since you have groups of columns.

bluvoodoo7
Starting Member

7 Posts

Posted - 2010-07-15 : 13:16:21

Yeah, I don't think UNPIVOT is going to work for my circumstances. Unfortunately I can't redesign the DB, it's part of our shop floor data collection tool and the DB kind of does what it wants based on what's created on the forms. I really want to avoid multiple queries, but if that's what it has to be then that's what it will be. Anybody have any other suggestions?

-T.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources