1-96 variable - loop - 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

1-96 variable - loop

Author

Topic

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-09-14 : 09:21:38

I need to run the following update but I need to apply it to 96 individual columns. As opposed to writing it out that many times i.e. Set AHT1
from - where
Set AHT2
from where etc.

Can I use some sort of variable that changes the AHT value but references the same FROM and WHERE clause?

Set AHT1 = Case When FTS.CALLVOLUME1 <> 0 Then Case When AHT1 = 0 Then Case When (((FTS.AHT1*FTS.CALLVOLUME1)))End Else FTS.AHT1 End Else FTS.AHT1 END
FROM
dbo.QUEUE
INNER JOIN dbo.FORECASTTIMESERIES FTS
INNER JOIN dbo.SPQUEUE
ON FTS.SPQUEUEID = dbo.SPQUEUE.ID
ON dbo.QUEUE.ID = dbo.SPQUEUE.QUEUEID
WHERE
(dbo.QUEUE.PARENTQUEUEID IS NULL)
AND (FTS.DATETIME BETWEEN '2011-11-13' AND '2011-11-19')
and dbo.queue.name = 'OC Fax-GEN'

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-14 : 09:37:56

Excel and Notepad++ is in fact great for this. Type it like this where A, B, C etc are columns in Excel ->


|A       |B      |C                             |D    |E   
-----------------------------------------------------------  
|Set AHT |1      | = Case When FTS.CALLVOLUME   |1    | ...

The you drag the corner of cell B1 down so until you get to row 96 so that the number in the cell also increases to 96. Then you do this with all the columns. Then you copy all the text to Notepad and then do a search/replace and remove all tab's. With some tweaking you will be able to create perfectly good sql code

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-09-14 : 09:44:07

Yeah I know about that option, was trying to do it in the script though.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-09-14 : 09:45:02

DECLARE @sql varchar(max)
SET @SQL = ''
SELECT @sql = @SQL + 'AHT'+convert(varchar(2),spt.number) +
'= Case When FTS.CALLVOLUME1 <> 0 Then Case When AHT'+convert(varchar(2),spt.number)+ ' = 0
Then Case When (((FTS.AHT' +convert(varchar(2),spt.number)+'*FTS.CALLVOLUME1)))
End Else FTS.AHT' +convert(varchar(2),spt.number)+'
End Else FTS.AHT'+convert(varchar(2),spt.number)+' END, '

FROM master..spt_values spt where spt.number between 1 and 96 and spt.[type] = 'P'

SET @SQL = 'SET ' + @SQL

PRINT @SQl

Jim

Everyday I learn something that somebody else already knew

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-14 : 11:01:18

What is your objective here? Are you looking for a way to generate a loop that will run this query 96 times with different columns every time? In that case you should really reconsider...running them all in one go will perform like a bazillion times better.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-09-14 : 11:43:47

Working off Jimf's suggestion I came up with this (not working) probably have it setup wrong.

Basically the 96 columes are 15 min intervals of a day. I need o modify each column based on criteria, I was hoping to use one update query but that didnt seem to work. It appears as if I have to add a from and where clause for each Set statement. Thats where I got hung up. Can you give me an example of the GO statement you referred to?

DECLARE @sql varchar(max)
SET @SQL = ''
SELECT @sql = @SQL + 'AHT'+convert(varchar(2),spt.number) +
'= Case When FTS.CALLVOLUME1 <> 0 Then Case When AHT'+convert(varchar(2),spt.number)+ ' = 0
Then Case When (((FTS.AHT' +convert(varchar(2),spt.number)+'*FTS.CALLVOLUME1)))
End Else FTS.AHT' +convert(varchar(2),spt.number)+'
End Else FTS.AHT'+convert(varchar(2),spt.number)+' END, '

FROM
spt_values spt
dbo.QUEUE
INNER JOIN dbo.FORECASTTIMESERIES FTS
INNER JOIN dbo.SPQUEUE
ON FTS.SPQUEUEID = dbo.SPQUEUE.ID
ON dbo.QUEUE.ID = dbo.SPQUEUE.QUEUEID
WHERE
(dbo.QUEUE.PARENTQUEUEID IS NULL)
AND (FTS.DATETIME BETWEEN '2011-11-13' AND '2011-11-19')
and dbo.queue.name = 'OC Fax-GEN'
and spt.number between 1 and 96 and spt.[type] = 'P'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-09-14 : 12:13:02

There's a problem with mine in that the last column has an ',' after it
SET AT1 = ...,
.
.
.AT96,

You shouldn't need a different FROM for each column. Do mine in two parts so you can get all the results in the window and then ccopy/paste them together, removing the last ',' and see if that helps.

Jim

Everyday I learn something that somebody else already knew

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-09-14 : 12:32:31

.Net SqlClient Data Provider: Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "spt.number" could not be bound.

Error I got when I changed it to this:

DECLARE @sql varchar(max)
SET @SQL = ''
SELECT @sql = @SQL + 'AHT'+convert(varchar(2),spt.number) +
'= Case When FTS.CALLVOLUME1 <> 0 Then Case When AHT'+convert(varchar(2),spt.number)+ ' = 0
Then Case When (((FTS.AHT' +convert(varchar(2),spt.number)+'*FTS.CALLVOLUME1)))
End Else FTS.AHT' +convert(varchar(2),spt.number)+'
End Else FTS.AHT'+convert(varchar(2),spt.number)+' END '

FROM

dbo.QUEUE
INNER JOIN dbo.FORECASTTIMESERIES FTS
INNER JOIN dbo.SPQUEUE
ON FTS.SPQUEUEID = dbo.SPQUEUE.ID
ON dbo.QUEUE.ID = dbo.SPQUEUE.QUEUEID

WHERE
(dbo.QUEUE.PARENTQUEUEID IS NULL)
AND (FTS.DATETIME BETWEEN '2011-11-13' AND '2011-11-19')
and dbo.queue.name = 'OC Fax-GEN'
and spt.number between 1 and 96 and spt.[type] = 'P'

SET @SQL = 'SET ' + @SQL

PRINT @SQl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources