Need help on functions - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help on functions

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2009-05-12 : 23:19:23

Hi friends

I have two tables like the following

table A

OID Name
1 A
2 B
3 C
4 D
5 E
6 F

table B

OID tableAOID code value modifieddttm date

1 1 Height 152 01/05/2009 01/05/2009
2 1 weight 52 01/05/2009 01/05/2009
3 1 height 165 02/05/2009 02/05/2009
4 1 weight 65 02/05/2009 02/05/2009
5 2 height 172 01/05/2009 01/05/2009
6 2 weight 72 01/05/2009 01/05/2009
7 2 height 165 02/05/2009 02/05/2009
8 2 weight 65 02/05/2009 02/05/2009
9 2 height 185 03/05/2009 03/05/2009
10 2 weight 75 03/05/2009 03/05/2009

Now i need to write a function and the input parameter will be "OID of table A".

Using same function i need to get columns height ,weight & date individually based on latest modifieddttm .

like

Oid Height weight date
1 165 65 02/05/2009
2 185 75 03/05/2009

i need to write only function . and suggest me is there any other way also.

I will use this function in a sp.

Kindly help me . How can i do this.

Thanks

Zakeer Sk

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-12 : 23:29:33

[code]
create function dbo.udf_sample(
@oid int)
RETURNS @temp2 TABLE ( oid INT, height int ,weight int, date DATETIME)
AS
BEGIN

insert into @temp2
select b.tableAOID,max(case when code ='height' then b.value end) as height,
max(case when code ='weight' then b.value end) as weight ,max(modifieddttm)
from @t t
inner join @b b on b.tableaoid = t.oid
where t.oid = @oid
group by b.tableAOID
RETURN

END
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 10:46:08

quote:

Originally posted by bklr


create function dbo.udf_sample(
@oid int)
RETURNS	@temp2 TABLE (	oid INT, height int ,weight int, date DATETIME)
AS
BEGIN 

insert into @temp2
select b.tableAOID,max(case when code ='height' then b.value end) as height,
max(case when code ='weight' then b.value end) as weight ,max(modifieddttm)
from @t t
inner join @b b on b.tableaoid = t.oid 
where t.oid = @oid
group by  b.tableAOID
RETURN

END

how will this ensure you get height,weight values for latest date? i think it should be like below


create function dbo.udf_sample(
@oid int)
RETURNS	@temp2 TABLE (	oid INT, height int ,weight int, date DATETIME)
AS
BEGIN 

insert into @temp2
select b.tableAOID,max(case when b.code ='height' then b.value end) as height,
max(case when b.code ='weight' then b.value end) as weight ,max(b.modifieddttm)
from @t t
inner join @b b on b.tableaoid = t.oid 
inner join (select tableAOID,max(modifieddttm)	as latest
            from @b 
            group by tableAOID) b1
on b1.tableaoid=b.tableaoid
and b1.latest=b. modifieddttm
where t.oid = @oid
group by  b.tableAOID
RETURN

END

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2009-05-13 : 23:04:57

actually i m calling this function in a SP along with some other columns which is from some other tables.

Like

select
a.name,
b.name,
----
---
---
dbo.udf_sample(@oid) as height,
dbo.udf_sample(@oid) as weight,
dbo.udf_sample(@oid) as date
from
table a
join
table b
join
----------------

the output should be like

FName LName ADDRESS CITY ZIP HEIGHT WEIGHT DATE
A B NO:2/5 NY NY4353 172 75 03/05/2009

Is this possible to get like this or i need to pass one more parameter like

dbo.udf_sample(@oid,'Height') as Height
dbo.udf_sample(@oid,'Weight') as Weight
-----

my scenario is i should hot code like this.

Kindly help me to overcome this.

thanks in advance.

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 10:49:47

will the code values be always height,weight? if not then you need to deterime this dynamically and need to do it in a procedure.
However, if they are static, you can use udf given and pass the value for @oid from main table using cross apply and it will give you crosstabbed results

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2009-05-14 : 23:04:13

yes the code values always will be static only...

using the given function i am getting three values at a time.

i think i need to pass one more parameter like what i have mentioned in the above reply.

dbo.udf_sample(@oid,'Height') as Height
dbo.udf_sample(@oid,'Weight') as Weight
dbo.udf_sample(@oid,'modifiedat') as modifiedat

will it hamper the performance?

Kindly suggest me what is the best way to do this.

Thanks

Zakeer Sk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources