I have now rewritten it directly in the query. The reason for a UDF is it is general functionality and a UDF is the easiest way to achive code...

update...

I have rewritten the UDF to be an in-line table function that always returns one column and one row.

In SQL2000 you cannot pass a row dependent value to a table...

out of interest, does anyone know what the fewest number of completed squares is you need to be able to complete a sudoku with only one solution?

Thanks for the offer Eugene,

I will try to do that on Monday (almost day end here now...)

I will need to strip the current query down to the offending tables and...

Sorry, wasn't clear enough in the original post/

The aggregate is actually on another field (Table2.contact_ID) but the grouping is by Staff_seq_num

so I would still be getting duplicate records.

It should do as long as most of your queries only hit a limited number or partitions, say this year and last year. I've not used them a lot...

unfortunately, the people who wrote the NHS datasets are morons....

The data gets extracted like this from the source systems in flat file format and loaded to the DW like this.

My...

Just so we are clear about what is required.

You want to be able to pass in a date and have the query tell you all items/cities that had stock at...

that is fine as long as it is only tables and data. It gets more complicated if you need to migrate SPs, check-constraints, user access rights etc..

An alternative would...

you could go one stage further and have one table with only one value column.

Create table myTable

(

SourceRecordID,

ValueType,

Value

)

you would then store (up to) 25 entries in the table for each source...

your backup and restore will take less time 🙂

You might want to look at partitioning your large tables, especially if you are only looking at a portion of the data...

starting to get slightly OT now...

but you would have to prepare the XML by hand in the calling application which is probably even more work than calling separate SPs. ...

To clear all of the data from a table use TRUNCATE.

Alternatively you could use Delete from table tablename.

The differences are that TRUNCATE will reset and identity fields whereas Delete will...

My thoughts exactly, which is why I thought the question may be asking for something more complicated.

You will need a single insert statement for each record that you want to...

That sounds like good coding to me.

All of the business rules about how data gets validated and entered into the table is held in the SPs.

Any user interface gets and...