March 18, 2008 at 6:00 am
Hi,
I have a problem of calculating an index. My Table is something like this:
SID as int 'PrimaryKey
SDate as DATETIME
FID as int
PID as int
PNO as int
TypeCode as int
....
Some example data:
SDATE FID PID PNO TypeCode
01/03/08 1 1 1 4
01/03/08 1 1 2 4
01/03/08 1 1 3 5
01/03/08 1 1 4 6
01/03/08 1 2 1 5
01/03/08 1 2 2 5
01/03/08 1 2 3 6
01/03/08 1 2 4 6
01/03/08 1 2 5 6
Problem is for each SDATE, FID and PID
calculate the expression: [(#of 4s in TypeCode)*1+(# of 5s in TypeCode)*2+(#of 6's in TypeCode)*3] divided by (# of 4s + # of 5s + # of 6s)
For the example data the expression is calculated as
SID FID PID Expr
01/03/08 1 1 1.75 [=(2*1+1*2+1*3)/4 ]
01/03/08 1 2 2.6 [=(2*2+3*3)/5]
How to do this? Any help/hint would be highly appreciated
Regards
Dhandapani
March 18, 2008 at 7:19 am
You need to use the count aggregation with a case statement.
declare @t table(
SID int identity(1,1),
SDate DATETIME,
FID int,
PID int,
PNO int,
TypeCode int
)
insert into @t (SDate,FID,PID,PNO,TypeCode)
select '01/03/08', 1, 1, 1, 4 union all
select '01/03/08', 1, 1, 2, 4 union all
select '01/03/08', 1, 1, 3, 5 union all
select '01/03/08', 1, 1, 4, 6 union all
select '01/03/08', 1, 2, 1, 5 union all
select '01/03/08', 1, 2, 2, 5 union all
select '01/03/08', 1, 2, 3, 6 union all
select '01/03/08', 1, 2, 4, 6 union all
select '01/03/08', 1, 2, 5, 6
select SDate, FID, PID,
(((COUNT(CASE WHEN TypeCode = 4 THEN TypeCode END)*1.0) +
(COUNT(CASE WHEN TypeCode = 5 THEN TypeCode END)*2.0) +
(COUNT(CASE WHEN TypeCode = 6 THEN TypeCode END)*3.0)) /
((COUNT(CASE WHEN TypeCode = 4 THEN TypeCode END)) +
(COUNT(CASE WHEN TypeCode = 5 THEN TypeCode END)) +
(COUNT(CASE WHEN TypeCode = 6 THEN TypeCode END))))
from @t
group by SDate, FID, PID
March 18, 2008 at 7:20 am
I think the best way is to normalize data and put number of 4's 5's and 6's in a record referenced by foreign key (FID, PID). There should be only one record for given pair in that table.
create table texpressions
( fid int,
pid int,
fours int default(0),
fives int default(0),
sixs int default(0),
expression as case when (fours + fives + sixs) > 0
then (fours * 1 + fives * 2 + sixs * 3) / (fours + fives + sixs)
else 0 end
)
Then you add new record to this table only when (fid, pid) pair doesn't exist there, otherwise you increment appropriate values, for example in trigger.
Didn't test this code but it's likely to work 🙂
Moreover it looks like this table should be the master table (only single occurence of (fid, pid) and the one with dates should be child table.
Piotr
...and your only reply is slàinte mhath
March 18, 2008 at 8:48 am
Thanks, the first solution would work for me. Since I have not completely given the strucutre, it looks like further normalization is required. Actually, TypeCode is important only for certain PIDs (say >1000); for other PID values, expression is calculated differently (for eg. Average of another field not mentioned in my post).
Thanks again, you saved lot of my time. SQL is not my cup of tea, :), seems that I need to learn a lot to think like you in SQL.
Regards,
Dhandapani
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply