Formulae for SQL Server table cells

Hi,

I know that we can have User Defined Functions (UDFs) for columns but is it possible to have UDFs for each cell (like we can define functions for each cell in excel). Heres the situation:

I have a database table that will have multiple fields. Some of the columns will be functions of other columns. One of the columns has variable functions.

Heres an example:

TABLE(A, B, C, D)

Total Records = 9;

A

BCDA1B1C1 = A1+ B1D1 = A1A2B2C2 = A2+ B2D2 = A2A3B3C3 = A3+ B3D3 = A3A4B4C4 = A4+ B4D4 = A4A5B5C5 = A5+ B5D5 = A5 + B5A6B6C6 = A6+ B6D6 = A6 + B6A7B7C7 = A7+ B7D7 = A7 + B7A8B8C8 = A8+ B8D8 = A8 + B8A9B9C9 = A9+ B9D9 = A9 + B9

If you notice, for Column D, some of the cells have a different formula than others. Is this doable?

Thanks...

One possible way would be there was an Id column with value 1-9, once the table was populated to make an update stored procedure calculate column unconditionally and column D according to a formula.

|||

Please let me know if I am going off-track here but this is what I understood from what you wrote.

Have an ID column (say autoincrement integers), then when all the values are being populated, I run an UPDATE stored procedure which whould check if ID is from 1-4, then assign the same value otherwise if ID = 5 to 9, perform calculation.

|||

Instead of an Identity column you could just populate it manually.

Incidentally as an alternative you could drop columns C and D from the table, sum A + B to get column C and use a function reading Id, A, B to get column D in the select.

|||

TATWORTH:

drop columns C and D from the table, sum A + B to get column C and use a function reading Id, A, B to get column D in the select

Does that mean I won't have columns C and D in the schema but will be generated dynamically when SELECTed (they won't ever be in the schema).

|||

>Does that mean I won't have columns C and D in the schema but will begenerated dynamically when SELECTed (they won't ever be in the schema).
Yes!

Format of DateTime Column in SELECT query

I am using SQL2005 and ASP.NET 2.0

I have one column in database called DateTime and it is defined like type datetime.It is formated like: day.month.year hour:minutes:seconds

My question is: I want to get date from Column DateTime in format day.month.year in SELECT query, not in stored procedure.

thanks

SELECT

CONVERT(NVARCHAR(10),getdate(), 104)as yourDate

replace the getdate() function with your datetime field in your select statement.

|||I just want to remind you that Column Name DateTime is the keyword of sql. You have to put it in [] or ""|||thanx, i managed it somehow.I found instructions in book about CONVERT so i saw how it works

Format money MS SQL field data as number with commas and no decimals

If I pull a value from a MSSQL field with is defined as money, how can I get it to display in a textbox with commas and NO decimals?

87000.0000 = 87,000

I can currently remove the decimals like below but is there a way to add the commas as well?

decRevenue = drMyData("Revenue")

txtRevenue.Text = decRevenue.ToString("f0")

It current shows "87000".

http://msdn2.microsoft.com/en-us/library/dwhawy9k(VS.80).aspx