Computed columns and constant

Hi, I have a small problem with my database. I've got following situation: I have a computed column, which value is base on currency rate: rent * rate. Users have to have possibility to change currency rate easily (maybe another table or constant). Is there any way to create formula, which would compute value properly, via constant or something like this? Or the easiest workaround would be load data into dataset (I'm building asp.net application - database will be very small - couple of hundreds of records) and make calculations programmatically?

Przemek

hi,

computed columns can only be based on constants, variables, functions and columns of the same table, and the last one exclude your

Users have to have possibility to change currency rate easily (maybe another table or constant)

the client side calculation can be an option, as the same can be true at SELECT time as well.. you can do lot of sophisticated things in your SELECT statement, defining a "virtual" columns as well based on calculations..

ie:

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.t1 (

Id int NOT NULL PRIMARY KEY,

Rate varchar(10) NOT NULL,

Val decimal (8,4) NOT NULL

);

CREATE TABLE dbo.t2 (

Id int NOT NULL,

RateId int NOT NULL

CONSTRAINT fk_t2$has$rates_on_t1 FOREIGN KEY

REFERENCES dbo.t1 (Id),

Rent decimal(8,4) NOT NULL

);

INSERT INTO dbo.t1 VALUES ( 1 , 'Dollar', 1 );

INSERT INTO dbo.t1 VALUES ( 2 , 'Euro', 1.25 );

INSERT INTO dbo.t2 VALUES ( 1 , 1 , 5 );

INSERT INTO dbo.t2 VALUES ( 2 , 2 , 5 );

SELECT t2.Id, t1.Rate, t2.Rent, t1.Val * t2.Rent AS [calculated value in base rate units]

FROM dbo.t2 t2

RIGHT JOIN dbo.t1 t1 ON t1.Id = t2.RateId;

DROP TABLE dbo.t2, dbo.t1;

--<

Id Rate Rent calculated value in base rate units

-- - --

1 Dollar 5.0000 5.00000000

2 Euro 5.0000 6.25000000

regards

Complex Query - Need help

Hi,

I have a sql table with corpcode, EmpName, rate, reportdate and Amount fields. I need to write a query that can return corpcode, EmpName, rate and sum of Amount for January, Sum of Amount for Feburary, Sum of Amount for March, Sum of Amount for April, ........., Sum of Amount for December, Total for All months, Average for all months.

I tried few option, it didn't work for me, Is it possible to do? Have some tried like this earlier?

Thanks!Are you just looking for something like this?

select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate|||No, I need total for each month for each employee, here is a sample of what I am looking for:

CorpCode--EmpName--Rate--Jan--Feb--Mar ......... Dec
A1112222--Ted Zeb--$45--$123--$456--$0.00 ...... $678.0
A1112222--Ray Bob--$89--$780--$234--$458.0 ...... $341

Thanks a lot for your help!|||Is an employee's rate going to change? If so what rate should be displayed?|||It will use group by "corpcode, EmpName, rate". So if rate changes for an employee, there should be a new line in the query output.

Thanks again!|||select corpcode
, EmpName
, rate
, sum(Amount) as test
, Sum (CASE DATEPART(yy,reportdate)
WHEN 1 THEN Amount Else 0) As January
End,
, Sum (CASE DATEPART(yy,reportdate)
WHEN 2 THEN Amount Else 0) As February
End,
...List rest oh the months here
...

FROM <table>
Group By corpcode, EmpName, rate|||I am getting this error message:

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.

also instead of DATEPART(yy,reportdate), don't we need DATEPART(mm,reportdate)?

Thanks again!|||I believe jaraba's response will still have all the months in seperate rows. You may have to do something like this. I am not sure if there is a better way it is just the first thing that came to mind.

CREATE TABLE #tmp (mymonth datetime, myyear datetime, corpcode int, EmpName varchar(50), rate int, Amount int)

insert into #tmp
select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate

select corpcode, EmpName, rate,
(SELECT top 1 Amount From #tmp a where mymonth = 1
and a.corpcode = #tmp.corpcode
and a.EmpName = #tmp.EmpName
and a.rate = #tmp.rate order by myyear) as January,
(SELECT top 1 Amount From #tmp a where mymonth = 2
and a.corpcode = #tmp.corpcode
and a.EmpName = #tmp.EmpName
and a.rate = #tmp.rate order by myyear) as February
...
from #tmp

drop table #tmp

This will only display the most recent months, if you want years too you could have a whole mess of columns.

Hope this helps|||If you don't mind, send me some data in an excel spreadsheet. i will work a solution for you.|||Thanks a lot, you guys are big help.

I got jaraba's query to work, but I want to ask one more question,

Whenever CorpCode changes, I need a line for sub totals and grand total as follows:

CorpCode--EmpName--Rate--Jan--Feb--Mar ......... Dec
A1112222--Ted Zeb--$45--$123--$456--$0.00 ...... $678.0
A1112222--Ray Bob--$89--$780--$234--$458.0 ......$341
----------------------
Sub-Total--2 employees--$134--$903--$690--$458.........$1019
----------------------
B1114444--ABC Zeb--$15--$13--$46--$0.00 ...... $68.0
B1114444--TTT Bob--$11--$0--$23--$48.0 .......$31
B1114444--GTH Bob--$19--$70--$3--$8.0 .........$15
----------------------
Sub-Total--3 employees--$45--$83--$73--$56..........$114
----------------------
----------------------
Grand Total--5 employees--$189--$986--$763--$514.........$1133
----------------------|||Actually, my response was no better. Sorry, I think something like this should get you what you are looking for.

CREATE TABLE #tmp (mymonth datetime, myyear datetime, corpcode int, EmpName varchar(50), rate int, Amount int)

CREATE TABLE #emp (corpcode int, EmpName varchar(50), rate int)

insert into #tmp
select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate

insert into #emp
SELECT DISTINCT corpcode, EmpName, rate
FROM #tmp

select #emp.corpcode, #emp.EmpName, #emp.rate, j.Amount as January, f.Amount as February ...
from #emp
LEFT OUTER join #tmp j on #emp.corpcode = j.corpcode
and #emp.EmpName = j.EmpName
and #emp.rate = j.rate and j.mymonth = 1
LEFT OUTER join #tmp f on #emp.corpcode = f.corpcode
and #emp.EmpName = f.EmpName
and #emp.rate = f.rate and f.mymonth = 2
...

drop table #tmp
drop table #emp

Sorry for the confusion.|||Sorry again, you can ignore my last post if jaraba's query got you what you are looking for. If you want subtotals for each corpcode you will have to write a seperate query or use the functionality of your report writer.|||Look up COMPUTE BY in BOL