Possibly use a CTE Query? - 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)

Possibly use a CTE Query?

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-06 : 19:00:56

The following query produces

1,9101,2.00
2,1213,3.00

which is correct, I just was wondering if their was a
Better way to do this by possibly using a CTE or some
other way method.

Please note that what I need to accomplish is
to add each Unique val items from @tmp1 which does
not exist in Tmp. If the item does not exist, I
need to add it to tmp2 filtering the items ( I just
used the date as a example in the live table I have roughly
4 columns that I need to filter on).

THis is why I chose a top 1 clause to illustrate.

Please let me know if there is a better way to get these
reults. Please factor in that I can't use a group by because
I need to return a specific record (In the scenerio above I use the rate


Declare @tmp  Table (ID int not null Identity(1,1),Val varchar(4),Rate money)
Declare @tmp1 Table (ID int not null Identity(1,1),Val varchar(4),dt datetime,Rate money)
Declare @tmp2 Table (ID int not null Identity(1,1),Val varchar(4),Rate money)

begin
Insert Into @Tmp(Val,Rate)
select '1234',5
union all
select '5678',6
end
begin
Insert Into @Tmp1(Val,Rate,Dt)
select '1234',1,'01/01/2005'
union all
select '5678',2,'01/03/2005'
union all
select '5678',3,'01/01/2005'
union all
select '9101',2,'01/05/2006'
union all
select '1213',2,'01/02/2005'
union all
select '1213',1,'01/03/2005'
union all
select '1213',3,'01/05/2005'
end

column to show that I need to get that value tied to the filtered item)
*/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-06 : 19:55:30

I don't think your post is complete. You posted tables and sample data, but not the query you already have tried that produces the results you quote. or did you not mean to post it?

edit: also, need sample data for your third table

elsasoft.org

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-06 : 20:22:25

AHHHHHHHHH, here's the correct query


Declare @tmp  Table (ID int not null Identity(1,1),Val varchar(4),Rate money)
Declare @tmp1 Table (ID int not null Identity(1,1),Val varchar(4),dt datetime,Rate money)
Declare @tmp2 Table (ID int not null Identity(1,1),Val varchar(4),Rate money)

begin
Insert Into @Tmp(Val,Rate)
select '1234',5
union all
select '5678',6
end
begin
Insert Into @Tmp1(Val,Rate,Dt)
select '1234',1,'01/01/2005'
union all
select '5678',2,'01/03/2005'
union all
select '5678',3,'01/01/2005'
union all
select '9101',2,'01/05/2006'
union all
select '1213',2,'01/02/2005'
union all
select '1213',1,'01/03/2005'
union all
select '1213',3,'01/05/2005'
end

Insert Into @Tmp2(Val,Rate)
Select a.Val,a.Rate
from	@tmp1 a
where not exists (Select * from @Tmp aa where aa.val = a.Val)
and	 a.ID = (Select top 1 ab.ID from @Tmp1 ab where ab.val = a.val order by dt asc)

select * from @Tmp2

phdiwakar
Starting Member

15 Posts

Posted - 2007-06-07 : 15:24:20

I think, it will be good to look at the exact query that you are using currently including the filtering columns.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources