You have two problems with the query.

1 you covering index only covers the columns in the where clause and not the columns in your output clause so you will get...

try this:

CREATE TABLE purchase --table:

(Date DATETIME,

Location VARCHAR(100),

Purchase_id INT)

INSERT INTO purchase

SELECT '2009-12-24', 'Dadar', 123

CREATE TABLE Item --table:

(Item_name VARCHAR(100),

Item_id INT)

INSERT INTO Item

SELECT 'Laptop', 21 UNION ALL

SELECT 'Mouse', 22 UNION ALL

SELECT 'Speaker', 23

CREATE...

WOW.

Lynn that is some awesome code. Very clever and very fast 🙂

Depending on how you system works you might need to make this dynamic as you may get more years creeping in for example 2009/2010/2011 ect.

if you need more help just...

Because you using a case sensitive coalation so the query checks for case sensitivity

try this:

select *

from customer

where surname = LOWER(surname) COLLATE SQL_Latin1_General_CP1_CS_AS

Could you provide us with the actual queries please.

you could also use this for the surname:

SUBSTRING(@nam,LEN(@nam) - charindex(' ',@nam),LEN(@nam))

Have you tried this.

taking the above post as base code.

REVERSE(left(REVERSE(@nam), charindex(' ',REVERSE(@nam))-1))

Ok I'm gonna guess and say that some of those nested correlated queries are going to kill you.

However please could you attach the Actual Graphical Execution plan (*.sqlplan) as a...

Ok here is an example of some code, it doesn't look up from a table but I think the formula is what you will be interested in.

DECLARE @Year INT

DECLARE @Month...

If you using 2000 you can't use include...

As far as know you can't use the INCLUDE in SQL 2000

I must have missed something but I didn't see the op defining the input. The output rule is all that was defined.

If you now the values are going to be...

timscronin (6/26/2009)

---

THanks, interesting now I am being told they can trap and to use a rule. Since the rule should allow for the first 2 char of being either...