Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
michael.appleton
Posting Yak Master
160 Posts |
 Posted - 2008-11-06 : 09:23:46
|
| Hello,I have an issue with organisation of the different models that are being developed about objects in a database. For instance, if I decided that I wanted to score all customers based on their amount of purchases, the value of each purchase, the length of time they've been signed up to company and the amount of times they've cancelled an order, I could write this kind of query quite easily. The issue is, where do I store it and how do I store it so it is available to everyone using the database. Initially I thought that something like this could be stored as a view which will have two columns, the CustomerId and Score. Then anyone who needs this and other data about customers can simply join it onto customer table in a query. An issue with this occurs when the model that is created cannot be stored as a view as it contains temporary tables or something else illegal in a view. Of course, I could just circulate the query that the model is based on, although this does not seem a very good way to organise something so general that could be used in lots of other queries. I really need a centralised way of storing it so any other queries based on it will change accordingly with any change on the centralised query.Any ideas? Thanks! |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-11-06 : 10:01:27
|
| A table-valued function is may suit your needs. It can be used inside queries and can support parameters being passed to it Or used with-out them similar to View but with less restrictions. E.g.CREATE Function [dbo].[fnCustomerScore](@CustomerId varchar(20), @MaxValue int .....etc)RETURNS TABLE ASRETURNselect * from ....... where ......... |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-11-06 : 11:02:15
|
| Store the customer score in a table, and update the table on a regular schedule.CODO ERGO SUM |
|
|
|
|
|
|
Model Organisation