Query help - 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

SQL Server Development (2000)

Query help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-14 : 09:58:22

Please help me in writing the query.
Thanks for the help in advance..

Table1
------

accid state County cp
----- ----- ----- ---
804102 AR 338 TA
804101 AZ 339 TB

table2
------

accid item1 item2 PE
----- ----- ----- ---
804102 11 03 1
804102 01 04 1
804101 11 03 0

Output:

accid state County cp item1 item2 PE
----- ----- ----- --- ----- ----- ---
804102 AR 338 TA 11 03 0
804101 AZ 339 TB 01 04 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 10:00:37

1.
what is your criteria for choosing

804102 11 03 1

and not

804102 01 04 1

2.
How do you get PE = 0 for 804102 ?

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-14 : 10:14:03

Oops..

The criteria is selecting the max of createddate....

Table 2
-------
accid item1 item2 PE createddate
----- ----- ----- --- -----------
804102 11 03 1 2007-02-02 14:13:43.000
804102 01 04 1 2007-02-02 14:13:31.000
804101 11 03 0 2007-02-02 14:13:31.000

I need the Output:

accid state County cp item1 item2 PE
----- ----- ----- --- ----- ----- ---
804102 AR 338 TA 11 03 1
804101 AZ 339 TB 11 03 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 10:34:51

[code]
declare @Table1 table
(
accid int,
state varchar(2),
County int,
cp varchar(2)
)
insert into @Table1
select 804102, 'AR', 338, 'TA' union all
select 804101, 'AZ', 339, 'TB'

declare @Table2 table
(
accid int,
item1 varchar(2),
item2 varchar(2),
PE int,
createddate datetime
)
insert into @Table2
select 804102, '11', '03', 1, '2007-02-02 14:13:43.000' union all
select 804102, '01', '04', 1, '2007-02-02 14:13:31.000' union all
select 804101, '11', '03', 0, '2007-02-02 14:13:31.000'

select t1.accid, t1.state, t1.County, t1.cp, t2.item1, t2.item2, t2.PE
from @Table1 t1 inner join @Table2 t2
on t1.accid = t2.accid
and t2.createddate = (select max(createddate) from @Table2 x where x.accid = t2.accid)
[/code]

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-15 : 11:58:45

Thanks for your help KH!
My requirements has changed.

declare @Table1 table
(
accid int,
sid bigint,
state varchar(2),
County int,
cp varchar(2)
)
insert into @Table1
select 804102,103, 'AR', 338, 'TA' union all
select 804101,110, 'AZ', 339, 'TB' union all
select 804102,106, 'AR', 338, 'TA'

declare @Table2 table
(
accid int,
sid bigint,
item1 varchar(2),
item2 varchar(2),
PE int,
createddate datetime
)
insert into @Table2
select 804102,103, '11', '03', 1, '2007-02-02 14:13:43.000' union all
select 804102,106, '01', '04', 2, '2007-02-02 14:13:31.000' union all
select 804101,110, '11', '03', 0, '2007-02-02 14:13:31.000'

select t1.accid,t2.sid, t1.state, t1.County, t1.cp, t2.item1, t2.item2, t2.PE,t2.createddate
from @Table1 t1 inner join @Table2 t2
on t1.accid = t2.accid
and t2.createddate = (select max(createddate) from @Table2 x where x.accid = t2.accid)

Output:

accid sid state County cp item1 item2 PE createddate
----------- -------------------- ----- ----------- ---- ----- ----- ----------- -----------------------
804102 103 AR 338 TA 11 03 1 2007-02-02 14:13:43.000
804101 110 AZ 339 TB 11 03 0 2007-02-02 14:13:31.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-15 : 17:19:31

this ?


select distinctt1.accid, t2.sid, t1.state, t1.County, t1.cp, t2.item1, t2.item2, t2.PE, t2.createddate
from   @Table1 t1 inner join @Table2 t2
on     t1.accid       = t2.accid
and    t2.createddate = (select max(createddate) from @Table2 x where x.accid = t2.accid)

if not, please explain what is your new requirement.

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-16 : 09:14:54

Thanks thanks for your help..
My reqs are prod2 table can have two rows with different Csid's for the Custid.I should only pull the latest created_dt record from @prod table.
Finally i am able to get the results with the below query

declare @Cust1 table
(
Custid int,
Csid bigint,
name1 varchar(5),
state varchar(4),
zip varchar(8)
)
insert into @Cust1
select 4102,103, 'Sam', 'NY', '2356' union all
select 4101,110, 'Pat', 'CA', '9526' union all
select 4102,106, 'Rob', 'NY', '2356'

declare @Prod2 table
(

Csid bigint,
Prod1 varchar(4),
Prod2 varchar(4),
EPRT int,
createddate datetime
)
insert into @Prod2
select 103, '11', '03', 1, '2007-02-02 14:13:43.000' union all
select 106, '01', '04', 2, '2007-02-02 14:13:31.000' union all
select 110, '11', '03', 0, '2007-02-02 14:13:31.000'

--With the below query i am able to pull the desired results..
select t1.Custid,t2.csid, t1.state, t1.name1, t1.zip, t2.prod1, t2.prod2, t2.EPRT,t2.createddate
from @Cust1 t1 inner join @Prod2 t2
on t1.Csid = t2.Csid

inner join
(
select custid, max(createddate) as CreatedDate
from @cust1 a
inner join @prod2 b
on a.csid = b.csid
group by a.custid
) t3
on t1.Custid = t3.Custid
and t2.CreatedDate = t3.CreatedDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources