Are you sure your dateField is datetime???

Implicit conversion of datetime to smalldatetime used to be my "favourite exercise" some time ago.

And converting...

Is there any secret reason why SystemUser, Buyer and Supplier must be in different tables?

You return it in the same column, that must give you a hint that they are...

Then you need to read this:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=310214

Just not to repeat all those words.

What is the datatype of dateField?

Why you need all those IF's?

INSERT INTO myTable (

keyCOL1, keyCOL2, valueCOL1, valueCOL1_LastUpdated,

ValueCOL2, ValueCOL2_LastUpdated, keyCOL3)

SELECT @keyCOL1, @keyCOL2,

@valueCOL1, @TimeStamp,

@ValueCOL2,@TimeStamp,@keyCOL3

WHERE NOT EXISTS( SELECT * FROM myTable WITH(ROWLOCK,UPDLOCK,HOLDLOCK)

WHERE keyCOL1 = @keyCOL1 AND...

If you're never gonna exceed capasity of Excel spreadsheet you can use the approaches above. (Why you need SQL Server then?)

But for real databases:

1) you better don't separate date and...

I believe it was a typo in Ram's script.

You must read:

Insert Into #InstanceTable(FinancialYear,Code,ServiceID)

Nothing is tricky here.

(datepart(dw, @Date)+ @@datefirst)%7 is constant and does not depend on datefirst.

By adding a parameter:

(datepart(dw, @Date)+ @@datefirst+6-@N)%7+1

you may set up any datefirst you want without changing...

Oh, what is this about...

There are no recursive calls in that function.

Just simple WHILE loop addressing table variable.

So, no one can stop me if I make a mistake!

That means an application using this function is not really portable...

Why?

Once I forgot to change IDs to newly retrieved and it was retrieving same set again and again.

When I stopped it there were couple of millions recursive calls completed.

And tempdb...

Move your rights to look-up table and do the search on it.

It will be scan anyway, but it will be scan on small table.

Than join this lookup to FTE_TABLE_SIT on...

It must be table UDF accepting @CustomerId as a parameter and collecting ID's for all children using recursive search on table Customer.

After all you use

SELECT * FROM dbo.CustomerChildren(@SomeID)

You may join...

David, you definitely never were involved in any big project. Otherwise you would not say such a b... sh...

Sorry.

Table structure must support data entities and its relationships, not data flow.

Data...

Yes, that function is OK, but only for English speaking servers.

Change SQL Server collation - and it will fail.