code 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

Transact-SQL (2000)

code help

Author

Topic

vux
Starting Member

45 Posts

Posted - 2004-07-12 : 06:27:14

ok I posted before, now I try to put my question different:

TABLE x

travelID | SeqNb | Dep | Arr | Name
1 1 02 03 peter
2 1 02 03 hans
3 1 02 03 mike
3 2 02 04 mike
3 3 02 05 mike
4 1 05 07 chris

So from table x I want to take out all Dep and Arr dates to write them into a new table. As you can see mike (TravelID=3) has multiple entries, the latest one is the one with SeqNb=3) - Consequentally I only want the the row where the Seq Nb is 3 (highest).

This is the code I have so far, but I am not sure about the syntax and use of alias:

SELECT
tvrq0_extract.tvrq_doc_id AS 'Travel ID',

(SELECT itnh0_extract.itnh_f_tvrq_doc_id, itnh0_extract.itnh_deprt_date
FROM itnh0_extract
WHERE (((itnh0_extract.itnh_f_tvrl_seq_num)=(SELECT MAX(itnh0_extract.itnh_f_tvrl_seq_num) FROM itnh0_extract ali WHERE itnh0_extract.itnh_f_tvrq_doc_id = ali.itnh_f_tvrq_doc_id ))))AS 'Departure Date',

(SELECT itnh0_extract.itnh_f_tvrq_doc_id, itnh0_extract.itnh_retrn_date
FROM itnh0_extract
WHERE (((itnh0_extract.itnh_f_tvrl_seq_num)=(SELECT MAX(itnh0_extract.itnh_f_tvrl_seq_num) FROM itnh0_extract ali WHERE itnh0_extract.itnh_f_tvrq_doc_id = ali.itnh_f_tvrq_doc_id ))))AS 'Return Date'

FROM bla bla

WHERE bla bla (joints)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-12 : 07:39:46

select a.TravelID, a.Seqnb, a.dep, a.arr, a.name
from tablex a
join (select name, max(seqnb) from tablex group by name) as b
on a.name = b.name
and a.seqnb = b.seqnb

Duane.

vux
Starting Member

45 Posts

Posted - 2004-07-12 : 23:27:45

Hello Ditch,
thanks for your suggestion, makes sense! I have to learn all this join stuff..

can i leave the (SELECT a.itnh_f_tvrq_doc_id) out of the record but still use it for the joins?

i took this code and included it to rthe rest but I still get Syntax errors (sya incorrect syntax near "AS", "," - dont know why:

SELECT
tvrq0_extract.tvrq_doc_id AS 'Travel ID',
tvrq0_extract.calc_traveller_name AS 'Name',
tvrq0_extract.tvrq_creat_date AS 'Request Date',
(SELECT a.itnh_f_tvrq_doc_id , a.itnh_deprt_date AS 'Departure Date', a.itnh_retrn_date AS 'Return Date', a.itnh_f_tvrl_seq_num
FROM dbo_ithn0_extract a
JOIN (SELECT itnh_f_tvrq_doc_id, MAX(itnh_f_tvrl_seq_num) FROM dbo_itnh0_extract GROUP BY itnh_f_tvrq_doc_id) AS b
ON a.itnh_f_tvrq_doc_id = b.itnh_f_tvrq_doc_id AND a.itnh_f_tvrl_seq_num = b.itnh_f_tvrl_seq_num),
itnh0_extract.itnh_itny_descr AS 'Itinerary',
tvrq0_extract.tvrq_auth_remarks AS 'Itinerary Description',
tvrq0_extract.trva_oblg_cnvrt_amt AS 'Total Costs',
proj0_extract.proj_descr AS 'Project Description',
fund0_extract.fund_descr AS 'Fund Description',
tvrq0_extract.trva_f_fnlp_fscl_yr AS 'Fiscal Year',
fund0_extract.fund_id_code AS 'Fund ID Code',
tvrq0_extract.trva_f_orgu_id_code AS 'Organisation Code',
proj0_extract.proj_id_code AS 'Project ID',
proj0_extract.proj_external_symbol AS 'External Project Symbol',
tvrq0_extract.trva_f_pgmm_id_code AS 'Programme ID'
FROM extracts.dbo.fund0_extract fund0_extract, extracts.dbo.itnh0_extract itnh0_extract, extracts.dbo.proj0_extract proj0_extract, extracts.dbo.tvrq0_extract tvrq0_extract
WHERE tvrq0_extract.tvrq_doc_id = itnh0_extract.itnh_f_tvrq_doc_id AND tvrq0_extract.trva_f_fund_id_code = fund0_extract.

fund_id_code AND ((tvrq0_extract.trva_f_proj_id_code = proj0_extract.proj_id_code) AND (tvrq0_extract.trva_f_orgu_id_code =

proj0_extract.proj_f_orgu_mnbr_id_code)) AND ((tvrq0_extract.tvrq_apprv_trav_ind=1) AND (tvrq0_extract.

tvrq_f_refx_trpn_seq_num=1) AND (itnh0_extract.itnh_deprt_date>={ts '2004-01-01 00:00:00'}))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources