Select statements included within a function cannot return data to a client

Question

Select statements included within a function cannot return data to a client

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Wayne-950947 Ten Centuries Points: 1122 More actions · Answer 1

Hi folks

I've tried some some of the suggestions and although they compile, I do not obtain the desired results.

To better illustrate what I'm trying to do, here's a sample (using daily data). TIME_KEY, COMM_SYMBand CLOSE are the inputs and SMA_5 is the output.

Note:

1)The days are not contiguous - not every calendar day is a trading day.

2)I'm only showing four of 40-something commodities (BO, C, CL, US).

3)The SMA value only "kicks in" once the period (in this case 5) matches the number of days.

4)The SMA values are derived up to the current day - I'm only showing up to 2006-12-18

5)To continue deriving the SMA for new data it's only necessary to go "period" records deep, and therefore not begin calculating from the start of the data set.

(pasting into this editor threw out the column positioning, but the data should be intact.)

TIME_KEYCOMM_SYMBTRD_DAYCLOSESMA_5

703BO2006-12-0429.340.00

703C2006-12-04375.500.00

703CL2006-12-0462.440.00

703US2006-12-04114.630.00

704BO2006-12-0529.340.00

704C2006-12-05378.000.00

704CL2006-12-0562.430.00

704US2006-12-05114.500.00

705BO2006-12-0628.780.00

705C2006-12-06366.000.00

705CL2006-12-0662.190.00

705US2006-12-06114.090.00

706BO2006-12-0728.730.00

706C2006-12-07372.500.00

706CL2006-12-0762.490.00

706US2006-12-07114.060.00

707BO2006-12-0828.6328.96

707C2006-12-08368.50372.10

707CL2006-12-0862.0362.32

707US2006-12-08113.31114.12

710BO2006-12-1129.0328.90

710C2006-12-11370.75371.15

710CL2006-12-1161.2262.07

710US2006-12-11113.72113.94

711BO2006-12-1229.1428.86

711C2006-12-12372.75370.10

711CL2006-12-1261.0261.79

711US2006-12-12113.97113.83

712BO2006-12-1329.0228.91

712C2006-12-13368.25370.55

712CL2006-12-1362.1761.79

712US2006-12-13112.97113.61

713BO2006-12-1428.7928.92

713C2006-12-14371.75370.40

713CL2006-12-1463.3361.95

713US2006-12-14112.72113.34

714BO2006-12-1528.5928.91

714C2006-12-15369.00370.50

714CL2006-12-1564.0962.37

714US2006-12-15112.69113.21

717BO2006-12-1828.1528.74

717C2006-12-18365.75369.50

717CL2006-12-1862.7962.68

717US2006-12-18112.72113.01

Thx,

Wayne

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

And, seriously... if you want better answers faster and maybe even tested answers, post the data for future requests using the techniques found in the link in my signature below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Wayne:

We cannot help you any further unless you do two things:

1) Please provide table definitions and sample data in the form of INSERT statments as explained in the link already provided to you. Here it is again:http://www.sqlservercentral.com/articles/Best+Practices/61537/

2) It is not sufficient to tell us that something "does not work". You must tell us what it did, why that is not what you want, and then delineate explictly what you actually wanted in that situation. The best way to do that is to provide the sample data (see (1), above) and then tell us what the desired solution should return from that data.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Jeff Moden (1/3/2009)
And, seriously... if you want better answers faster and maybe even tested answers, post the data for future requests using the techniques found in the link in my signature below.

In other words, correct and complete the following statement to bring in your entire test data set of 44 rows as posted above.

DROP TABLE dbo.fct_WKLY

CREATE TABLE dbo.fct_WKLY (TIME_KEY INT, COMM_SYMB VARCHAR(2), TRD_DAY DATETIME, [CLOSE] MONEY, SMA_5 MONEY)

INSERT INTO dbo.fct_WKLY (TIME_KEY, COMM_SYMB, TRD_DAY, [CLOSE], SMA_5)

SELECT 703, 'BO', '2006-12-04', 29.34, 0.00 UNION ALL

A couple of minor tweaks to Barry's proc & mine produces exactly the results you require and now we need some data to demonstrate against. Post the test data, I'll post the proc 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Wayne-950947 Ten Centuries Points: 1122 More actions · Answer 5

Hi folks

I guess I should have read those links first, it makes sense...

I'm deliberately using daily (fct.EOD) data as there are always "gaps" in the days, which is not the case of weekly or monthly data, so it's more representative.

-- Sample data

DROP TABLE dbo.fct_EOD_01

CREATE TABLE dbo.fct_EOD_01

(

TIME_KEY int NOT NULL,

COMM_SYMB nvarchar(3) NOT NULL,

TRD_DAY datetime NOT NULL,

CLS float NOT NULL,

SMA_5 float NULL

)

INSERT INTO dbo.fct_EOD_01 (TIME_KEY, COMM_SYMB, TRD_DAY, CLS, SMA_5)

SELECT 703, 'BO', '12/4/2006' , 29.34, NULL UNION ALL

SELECT 703, 'C', '12/4/2006' , 375.5, NULL UNION ALL

SELECT 703, 'CL', '12/4/2006' , 62.44, NULL UNION ALL

SELECT 703, 'US', '12/4/2006' , 114.625, NULL UNION ALL

SELECT 704, 'BO', '12/5/2006' , 29.34, NULL UNION ALL

SELECT 704, 'C', '12/5/2006' , 378, NULL UNION ALL

SELECT 704, 'CL', '12/5/2006' , 62.43, NULL UNION ALL

SELECT 704, 'US', '12/5/2006' , 114.5, NULL UNION ALL

SELECT 705, 'BO', '12/6/2006' , 28.78, NULL UNION ALL

SELECT 705, 'C', '12/6/2006' , 366, NULL UNION ALL

SELECT 705, 'CL', '12/6/2006' , 62.19, NULL UNION ALL

SELECT 705, 'US', '12/6/2006' , 114.09375, NULL UNION ALL

SELECT 706, 'BO', '12/7/2006' , 28.73, NULL UNION ALL

SELECT 706, 'C', '12/7/2006' , 372.5, NULL UNION ALL

SELECT 706, 'CL', '12/7/2006' , 62.49, NULL UNION ALL

SELECT 706, 'US', '12/7/2006' , 114.0625, NULL UNION ALL

SELECT 707, 'BO', '12/8/2006' , 28.63, NULL UNION ALL

SELECT 707, 'C', '12/8/2006' , 368.5, NULL UNION ALL

SELECT 707, 'CL', '12/8/2006' , 62.03, NULL UNION ALL

SELECT 707, 'US', '12/8/2006' , 113.3125, NULL UNION ALL

SELECT 710, 'BO', '12/11/2006' , 29.03, NULL UNION ALL

SELECT 710, 'C', '12/11/2006' , 370.75, NULL UNION ALL

SELECT 710, 'CL', '12/11/2006' , 61.22, NULL UNION ALL

SELECT 710, 'US', '12/11/2006' , 113.71875, NULL UNION ALL

SELECT 711, 'BO', '12/12/2006' , 29.14, NULL UNION ALL

SELECT 711, 'C', '12/12/2006' , 372.75, NULL UNION ALL

SELECT 711, 'CL', '12/12/2006' , 61.02, NULL UNION ALL

SELECT 711, 'US', '12/12/2006' , 113.96875, NULL UNION ALL

SELECT 712, 'BO', '12/13/2006' , 29.02, NULL UNION ALL

SELECT 712, 'C', '12/13/2006' , 368.25, NULL UNION ALL

SELECT 712, 'CL', '12/13/2006' , 62.17, NULL UNION ALL

SELECT 712, 'US', '12/13/2006' , 112.96875, NULL UNION ALL

SELECT 713, 'BO', '12/14/2006' , 28.79, NULL UNION ALL

SELECT 713, 'C', '12/14/2006' , 371.75, NULL UNION ALL

SELECT 713, 'CL', '12/14/2006' , 63.33, NULL UNION ALL

SELECT 713, 'US', '12/14/2006' , 112.71875, NULL UNION ALL

SELECT 714, 'BO', '12/15/2006' , 28.59, NULL UNION ALL

SELECT 714, 'C', '12/15/2006' , 369, NULL UNION ALL

SELECT 714, 'CL', '12/15/2006' , 64.09, NULL UNION ALL

SELECT 714, 'US', '12/15/2006' , 112.6875, NULL UNION ALL

SELECT 717, 'BO', '12/18/2006' , 28.15, NULL UNION ALL

SELECT 717, 'C', '12/18/2006' , 365.75, NULL UNION ALL

SELECT 717, 'CL', '12/18/2006' , 62.79, NULL UNION ALL

SELECT 717, 'US', '12/18/2006' , 112.71875, NULL UNION ALL

SELECT 718, 'BO', '12/19/2006' , 28.5, NULL UNION ALL

SELECT 718, 'C', '12/19/2006' , 373, NULL UNION ALL

SELECT 718, 'CL', '12/19/2006' , 63.46, NULL UNION ALL

SELECT 718, 'US', '12/19/2006' , 112.5625, NULL UNION ALL

SELECT 719, 'BO', '12/20/2006' , 28.53, NULL UNION ALL

SELECT 719, 'C', '12/20/2006' , 372.75, NULL UNION ALL

SELECT 719, 'CL', '12/20/2006' , 63.72, NULL UNION ALL

SELECT 719, 'US', '12/20/2006', 112.5625, NULL

-- end of sample data

The objective is to calculate a simple moving average for any number of periods. In this example it's a 5 day moving average. The table on which this operation is performed is not static as the data grows on a daily basis.

I currently (in MS Access) use a VB function as follows:

Select TIME_KEY, COMM_SYMB, FunctionName(TIME_KEY, COMM_SYMB, Period) -- Period is any integer

From fct_EOD_01

The expected results are:

DROP TABLE dbo.fct_EOD_ER

CREATE TABLE dbo.fct_EOD_ER

(

TIME_KEY int NOT NULL,

COMM_SYMB nvarchar(3) NOT NULL,

TRD_DAY datetime NOT NULL,

CLS float NOT NULL,

SMA_5 float NULL

)

INSERT INTO dbo.fct_EOD_ER (TIME_KEY, COMM_SYMB, TRD_DAY, CLS, SMA_5)

SELECT 703, 'BO', '12/4/2006', 29.34, 0.00 UNION ALL

SELECT 703, 'C', '12/4/2006', 375.5, 0.00 UNION ALL

SELECT 703, 'CL', '12/4/2006', 62.44, 0.00 UNION ALL

SELECT 703, 'US', '12/4/2006', 114.625, 0.00 UNION ALL

SELECT 704, 'BO', '12/5/2006', 29.34, 0.00 UNION ALL

SELECT 704, 'C', '12/5/2006', 378, 0.00 UNION ALL

SELECT 704, 'CL', '12/5/2006', 62.43, 0.00 UNION ALL

SELECT 704, 'US', '12/5/2006', 114.5, 0.00 UNION ALL

SELECT 705, 'BO', '12/6/2006', 28.78, 0.00 UNION ALL

SELECT 705, 'C', '12/6/2006', 366, 0.00 UNION ALL

SELECT 705, 'CL', '12/6/2006', 62.19, 0.00 UNION ALL

SELECT 705, 'US', '12/6/2006', 114.09375, 0.00 UNION ALL

SELECT 706, 'BO', '12/7/2006', 28.73, 0.00 UNION ALL

SELECT 706, 'C', '12/7/2006', 372.5, 0.00 UNION ALL

SELECT 706, 'CL', '12/7/2006', 62.49, 0.00 UNION ALL

SELECT 706, 'US', '12/7/2006', 114.0625, 0.00 UNION ALL

SELECT 707, 'BO', '12/8/2006', 28.63, 28.96 UNION ALL

SELECT 707, 'C', '12/8/2006', 368.5, 372.10 UNION ALL

SELECT 707, 'CL', '12/8/2006', 62.03, 62.32 UNION ALL

SELECT 707, 'US', '12/8/2006', 113.3125, 114.12 UNION ALL

SELECT 710, 'BO', '12/11/2006', 29.03, 28.90 UNION ALL

SELECT 710, 'C', '12/11/2006', 370.75, 371.15 UNION ALL

SELECT 710, 'CL', '12/11/2006', 61.22, 62.07 UNION ALL

SELECT 710, 'US', '12/11/2006', 113.71875, 113.94 UNION ALL

SELECT 711, 'BO', '12/12/2006', 29.14, 28.86 UNION ALL

SELECT 711, 'C', '12/12/2006', 372.75, 370.10 UNION ALL

SELECT 711, 'CL', '12/12/2006', 61.02, 61.79 UNION ALL

SELECT 711, 'US', '12/12/2006', 113.96875, 113.83 UNION ALL

SELECT 712, 'BO', '12/13/2006', 29.02, 28.91 UNION ALL

SELECT 712, 'C', '12/13/2006', 368.25, 370.55 UNION ALL

SELECT 712, 'CL', '12/13/2006', 62.17, 61.79 UNION ALL

SELECT 712, 'US', '12/13/2006', 112.96875, 113.61 UNION ALL

SELECT 713, 'BO', '12/14/2006', 28.79, 28.92 UNION ALL

SELECT 713, 'C', '12/14/2006', 371.75, 370.40 UNION ALL

SELECT 713, 'CL', '12/14/2006', 63.33, 61.95 UNION ALL

SELECT 713, 'US', '12/14/2006', 112.71875, 113.34 UNION ALL

SELECT 714, 'BO', '12/15/2006', 28.59, 28.91 UNION ALL

SELECT 714, 'C', '12/15/2006', 369, 370.50 UNION ALL

SELECT 714, 'CL', '12/15/2006', 64.09, 62.37 UNION ALL

SELECT 714, 'US', '12/15/2006', 112.6875, 113.21 UNION ALL

SELECT 717, 'BO', '12/18/2006', 28.15, 28.74 UNION ALL

SELECT 717, 'C', '12/18/2006', 365.75, 369.50 UNION ALL

SELECT 717, 'CL', '12/18/2006', 62.79, 62.68 UNION ALL

SELECT 717, 'US', '12/18/2006', 112.71875, 113.01 UNION ALL

SELECT 718, 'BO', '12/19/2006', 28.5, 28.61 UNION ALL

SELECT 718, 'C', '12/19/2006', 373, 369.55 UNION ALL

SELECT 718, 'CL', '12/19/2006', 63.46, 63.17 UNION ALL

SELECT 718, 'US', '12/19/2006', 112.5625, 112.73 UNION ALL

SELECT 719, 'BO', '12/20/2006', 28.53, 28.51 UNION ALL

SELECT 719, 'C', '12/20/2006', 372.75, 370.45 UNION ALL

SELECT 719, 'CL', '12/20/2006', 63.72, 63.48 UNION ALL

SELECT 719, 'US', '12/20/2006', 112.5625, 112.65

-- end of expected results

Thanks,

Wayne

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

The table names keep changing - never mind, here's some sample data I made earlier...

DROP TABLE dbo.fct_WKLY

CREATE TABLE dbo.fct_WKLY (TIME_KEY INT, COMM_SYMB VARCHAR(2), TRD_DAY DATETIME, [CLOSE] MONEY, SMA_5 MONEY)

INSERT INTO dbo.fct_WKLY (TIME_KEY, COMM_SYMB, TRD_DAY, [CLOSE], SMA_5)

SELECT 703, 'BO', '2006-12-04', 29.34, 0.00 UNION ALL

SELECT 703, 'C', '2006-12-04', 375.50, 0.00 UNION ALL

SELECT 703, 'CL', '2006-12-04', 62.44, 0.00 UNION ALL

SELECT 703, 'US', '2006-12-04', 114.63, 0.00 UNION ALL

SELECT 704, 'BO', '2006-12-05', 29.34, 0.00 UNION ALL

SELECT 704, 'C', '2006-12-05', 378.00, 0.00 UNION ALL

SELECT 704, 'CL', '2006-12-05', 62.43, 0.00 UNION ALL

SELECT 704, 'US', '2006-12-05', 114.50, 0.00 UNION ALL

SELECT 705, 'BO', '2006-12-06', 28.78, 0.00 UNION ALL

SELECT 705, 'C', '2006-12-06', 366.00, 0.00 UNION ALL

SELECT 705, 'CL', '2006-12-06', 62.19, 0.00 UNION ALL

SELECT 705, 'US', '2006-12-06', 114.09, 0.00 UNION ALL

SELECT 706, 'BO', '2006-12-07', 28.73, 0.00 UNION ALL

SELECT 706, 'C', '2006-12-07', 372.50, 0.00 UNION ALL

SELECT 706, 'CL', '2006-12-07', 62.49, 0.00 UNION ALL

SELECT 706, 'US', '2006-12-07', 114.06, 0.00 UNION ALL

SELECT 707, 'BO', '2006-12-08', 28.63, 28.96 UNION ALL

SELECT 707, 'C', '2006-12-08', 368.50, 372.10 UNION ALL

SELECT 707, 'CL', '2006-12-08', 62.03, 62.32 UNION ALL

SELECT 707, 'US', '2006-12-08', 113.31, 114.12 UNION ALL

SELECT 710, 'BO', '2006-12-11', 29.03, 28.90 UNION ALL

SELECT 710, 'C', '2006-12-11', 370.75, 371.15 UNION ALL

SELECT 710, 'CL', '2006-12-11', 61.22, 62.07 UNION ALL

SELECT 710, 'US', '2006-12-11', 113.72, 113.94 UNION ALL

SELECT 711, 'BO', '2006-12-12', 29.14, 28.86 UNION ALL

SELECT 711, 'C', '2006-12-12', 372.75, 370.10 UNION ALL

SELECT 711, 'CL', '2006-12-12', 61.02, 61.79 UNION ALL

SELECT 711, 'US', '2006-12-12', 113.97, 113.83 UNION ALL

SELECT 712, 'BO', '2006-12-13', 29.02, 28.91 UNION ALL

SELECT 712, 'C', '2006-12-13', 368.25, 370.55 UNION ALL

SELECT 712, 'CL', '2006-12-13', 62.17, 61.79 UNION ALL

SELECT 712, 'US', '2006-12-13', 112.97, 113.61 UNION ALL

SELECT 713, 'BO', '2006-12-14', 28.79, 28.92 UNION ALL

SELECT 713, 'C', '2006-12-14', 371.75, 370.40 UNION ALL

SELECT 713, 'CL', '2006-12-14', 63.33, 61.95 UNION ALL

SELECT 713, 'US', '2006-12-14', 112.72, 113.34 UNION ALL

SELECT 714, 'BO', '2006-12-15', 28.59, 28.91 UNION ALL

SELECT 714, 'C', '2006-12-15', 369.00, 370.50 UNION ALL

SELECT 714, 'CL', '2006-12-15', 64.09, 62.37 UNION ALL

SELECT 714, 'US', '2006-12-15', 112.69, 113.21 UNION ALL

SELECT 717, 'BO', '2006-12-18', 28.15, 28.74 UNION ALL

SELECT 717, 'C', '2006-12-18', 365.75, 369.50 UNION ALL

SELECT 717, 'CL', '2006-12-18', 62.79, 62.68 UNION ALL

SELECT 717, 'US', '2006-12-18', 112.72, 113.01

Here's the function:

CREATE FUNCTION [dbo].[WKLY_SMA_CJM](

@TimeKey int,

@Commodity nvarchar(3),

@Period int)

RETURNS MONEY AS --was int?

BEGIN

DECLARE @SMA MONEY -- return variable

SELECT @SMA = CASE WHEN COUNT(*) = @Period

THEN SUM([CLOSE]) / (COUNT(*)*1.00)

ELSE 0 END

FROM (

SELECT TOP (@Period) [CLOSE]

FROM dbo.fct_WKLY

WHERE COMM_SYMB = @Commodity AND TIME_KEY <= @TimeKey

ORDER BY TIME_KEY DESC

) d

-- Return the result of the function

RETURN @SMA

END

And finally here's usage of the function, complete with a means of deriving the same result using a correlated subquery:

DECLARE @Period INT

SET @Period = 5

SELECT TIME_KEY, COMM_SYMB, TRD_DAY, [CLOSE], SMA_5,

dbo.WKLY_SMA_CJM(TIME_KEY, COMM_SYMB, 5),

CAST( (SELECT CASE WHEN COUNT(*) = @Period THEN SUM([CLOSE]) / (COUNT(*)*1.00) ELSE 0 END

FROM (SELECT TOP (@Period) [CLOSE]

FROM dbo.fct_WKLY

WHERE COMM_SYMB = w.COMM_SYMB AND TIME_KEY <= w.TIME_KEY

ORDER BY TIME_KEY DESC

) d) AS MONEY)

FROM dbo.fct_WKLY w

Here's the results:

TIME_KEYCOMM_SYMBTRD_DAY [CLOSE]SMA_5FunctionCSQ

703BO00:00.029.34000

703C00:00.0375.5000

703CL00:00.062.44000

703US00:00.0114.63000

704BO00:00.029.34000

704C00:00.0378000

704CL00:00.062.43000

704US00:00.0114.5000

705BO00:00.028.78000

705C00:00.0366000

705CL00:00.062.19000

705US00:00.0114.09000

706BO00:00.028.73000

706C00:00.0372.5000

706CL00:00.062.49000

706US00:00.0114.06000

707BO00:00.028.6328.9628.96428.964

707C00:00.0368.5372.1372.1372.1

707CL00:00.062.0362.3262.31662.316

707US00:00.0113.31114.12114.118114.118

710BO00:00.029.0328.928.90228.902

710C00:00.0370.75371.15371.15371.15

710CL00:00.061.2262.0762.07262.072

710US00:00.0113.72113.94113.936113.936

711BO00:00.029.1428.8628.86228.862

711C00:00.0372.75370.1370.1370.1

711CL00:00.061.0261.7961.7961.79

711US00:00.0113.97113.83113.83113.83

712BO00:00.029.0228.9128.9128.91

712C00:00.0368.25370.55370.55370.55

712CL00:00.062.1761.7961.78661.786

712US00:00.0112.97113.61113.606113.606

713BO00:00.028.7928.9228.92228.922

713C00:00.0371.75370.4370.4370.4

713CL00:00.063.3361.9561.95461.954

713US00:00.0112.72113.34113.338113.338

714BO00:00.028.5928.9128.91428.914

714C00:00.0369370.5370.5370.5

714CL00:00.064.0962.3762.36662.366

714US00:00.0112.69113.21113.214113.214

717BO00:00.028.1528.7428.73828.738

717C00:00.0365.75369.5369.5369.5

717CL00:00.062.7962.6862.6862.68

717US00:00.0112.72113.01113.014113.014

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Thanks for the data, Wayne. I see that Chris has already posted an updated solution. Please let us know how it works for you.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Wayne-950947 Ten Centuries Points: 1122 More actions · Answer 8

Many thanks guys. I was out for most of the day - will be testing it tomorrow.

Much appreciated,

Wayne

Wayne-950947 Ten Centuries Points: 1122 More actions · Answer 9

That did the trick.

The results are good, and needless to say, it performs way better than a cursor.

Thank you again,

Wayne

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Thanks for the feedback Wayne.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Glad we could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]