I would probably use PATINDEX(). Something like

DECLARE @search CHAR(2)

SET @search='sm';

WITH tab AS

(

SELECT 'Smith' AS lastname, 'John' AS firstname UNION ALL

SELECT 'GoldSmith', 'Vincent' UNION ALL

SELECT 'Smith', 'Patrick' UNION ALL

SELECT 'Blacksmith',...

Would something along the following lines help you? (the "trick" is to build subgroups by contract_type as shown with the subgrp aliased column)

;

WITH cte AS

(

SELECT

*,

ROW_NUMBER() OVER(ORDER BY...

Please provide table def, sample data and expected result together with what you've tried so far. For details on how to post sample data please see the first link in...

But I don't think it's going to change the concept in general 😉

It just leaves a little more work to do for the OP to modify the solution...

Chris Morris-439714 (8/6/2010)

---

...

Chris, please take the time to set up the sample data properly. The whole batch should run without errors, and the column names and types must match those...

Matthew VanDerlofske-459596 (8/5/2010)

---

Thank you for the explaination, that change has really made the difference, the query now runs on average less then 1 second which is well within acceptable limits.

Thank...

Continuing my prev. post: You have an index lApprovalId and lItemId. Using the phone book example your index is sorted by last name (=lApprovalId) and first name (=lItemId).

But your WHERE...

Matthew VanDerlofske-459596 (8/5/2010)

---

Yeah sorry the sample data did not include any slots that would be picked up the the union, To get a data set that would get data from...

Ok, here's what I came up with so far:

SELECT

RP.PERIODNAME,

[1.1] = COUNT(CASE WHEN C.CASETYPE = 'F' AND C.PROPERTYTYPE = 'P' AND RIGHT(C.IRN, 2) = 'PA' AND CE.EVENTNO = -16 AND MONTH(CE.EVENTDATE)...

Matthew, do you have an example that will actually trigger both parts of the UNION statement? With the data you provided I would suggest to delete the secon UNION part...

GilaMonster (8/4/2010)

---

My supervisor at the university has a very irritating clock. No numbers...

Hmm.... it seems like the execution plan got lost somewhere in between (2 bytes doesnt sound right...).

Anyway. Step 1 would be to replace UNION with UNION ALL to eliminate a...

Matthew VanDerlofske-459596 (8/4/2010)

---

LutzM, I implemented your suggestion, however running the query the old way and the your way resulted in the same duration (12 seconds with my test case).

it seems...

Please provide table DDL and sample data.

Based on what you've provided so far, I guess there a re some missing rows in your B cte.

Hard to tell without sample data...

Changing the function into an inline-table valued function would also help, I guess...

CREATE FUNCTION [DSUSER].[BDP_WPS_Approvals] (@lWPSID AS INT)

RETURNS TABLE

AS

RETURN

(SELECT

(SELECT

a.lUserID ID,

a.lApprovalState...