sql - add extra field - 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 2000 Forums

Transact-SQL (2000)

sql - add extra field

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-06-08 : 04:22:38

Hello,
The following query is a simplified query which returns four fields as shown below

select
t1.[Name],
t2.[CodeName],
t2.[FieldName],
t3.FixDate,
t3.FixValue
from
table1 t1
inner join table2 t2 on t1.id = t2.id
inner join table3 t3 on t2.id=t3.id
where
t1.IndexID in (1,2,3)

Result is:

Name CodeName FieldName FixDate FixValue

BR DATED PCAAS00 Low 2007-06-04 00:00:00 71.14000
BR DATED PCAAS00 Low 2007-06-05 00:00:00 71.49000
BR DATED PCAAS00 Low 2007-06-06 00:00:00 71.74000
BR DATED PCAAS00 High 2007-06-04 00:00:00 71.16000
BR DATED PCAAS00 High 2007-06-05 00:00:00 71.50000
BR DATED PCAAS00 High 2007-06-06 00:00:00 71.75000

Now I would like to come up with a sql query to have the above results but also have an extra field at the end which shows the FixValue fields only for the Maximum ixDate field. See the result below.
What is the sql query to produce the following result please? Thanks

Name CodeName FieldName FixDate FixValue MaxFixDateFixValue

BR DATED PCAAS00 Low 2007-06-04 00:00:00 71.14000 NULL
BR DATED PCAAS00 Low 2007-06-05 00:00:00 71.49000 NULL
BR DATED PCAAS00 Low 2007-06-06 00:00:00 71.74000 71.74000
BR DATED PCAAS00 High 2007-06-04 00:00:00 71.16000 NULL
BR DATED PCAAS00 High 2007-06-05 00:00:00 71.50000 NULL
BR DATED PCAAS00 High 2007-06-06 00:00:00 71.75000 71.75000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-08 : 04:29:28

[code]select
t1.[Name],
t2.[CodeName],
t2.[FieldName],
t3.FixDate,
t3.FixValue,
m.FixtDate
from
table1 t1
inner join table2 t2 on t1.id = t2.id
inner join table3 t3 on t2.id=t3.id
left join
(
select
t1.[Name],
t2.[CodeName],
t2.[FieldName],
FixtDate = max(t3.FixDate)
from
table1 t1
inner join table2 t2 on t1.id = t2.id
inner join table3 t3 on t2.id=t3.id
group by t1.[Name], t2.[CodeName], t2.[FieldName]
) m on t1.[Name] = m.[Name] and t2.[CodeName] = m.[CodeName]
and t2.[FieldName] = m.[FieldName] add t3.FixDate = m.FixDate
where
t1.IndexID in (1,2,3)
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-08 : 04:51:14

missed out the GROUP BY. Amended the post. Please try again

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-06-08 : 05:15:57

worked it out now.
there was a problem with the left join.
Many thanks for guidance.

melody_ph
Starting Member

11 Posts

Posted - 2007-06-08 : 05:36:29

hi..
i got the same result with this but i dont think its advisable..

select
t1.[Name],
t2.[CodeName],
t2.[FieldName],
t3.FixDate,
t3.FixValue,
t3.FixValue AS MaxFixDateFixValue
from table1 t1
inner join table2 t2 on t1.id = t2.id
inner join table3 t3 on t2.id=t3.id
where t1.IndexID in (1,2,3)
and t3.FixDate = (SELECT MAX(FixDate) From table3)

UNION

select
t1.[Name],
t2.[CodeName],
t2.[FieldName],
t3.FixDate,
t3.FixValue,
Null AS MaxFixDateFixValue
from table1 t1
inner join table2 t2 on t1.id = t2.id
inner join table3 t3 on t2.id=t3.id
where t1.IndexID in (1,2,3)
and t3.FixDate <> (SELECT MAX(FixDate) From table3)

khtan, i think its m.fixvalue you should select

-=la lang=-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-08 : 06:28:29

[code]
select
t1.[Name],
t2.[CodeName],
t2.[FieldName],
t3.FixDate,
t3.FixValue,
case when t3.FixtDate = m.FixtDate then t3.FixtValue else NULL end
from
table1 t1
inner join table2 t2 on t1.id = t2.id
inner join table3 t3 on t2.id=t3.id
left join
(
select
t1.[Name],
t2.[CodeName],
t2.[FieldName],
FixtDate = max(t3.FixDate)
from
table1 t1
inner join table2 t2 on t1.id = t2.id
inner join table3 t3 on t2.id=t3.id
group by t1.[Name], t2.[CodeName], t2.[FieldName]
) m on t1.[Name] = m.[Name] and t2.[CodeName] = m.[CodeName]
and t2.[FieldName] = m.[FieldName] add t3.FixDate = m.FixDate
where
t1.IndexID in (1,2,3)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources