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)
 Can u pl help me with this simple SQL query

Author  Topic 

nssjari
Starting Member

46 Posts
Posted - 2005-06-26 : 06:32:20
Documents have revision number but Transmittals do not ... in this case.

-------------------------------------

Create Table "E0362CSD" ( CSD_ORIG Char( 2 ), CSD_SUBJ Char( 4 ), CSD_TYPE Char( 1 ), CSD_NUMB Char( 4 ), CSD_REVI Char( 1 ), CSD_LABL Char( 50 ), CSD_STAGE Char( 1 ), CSD_ISSU Date, CSD_ALTR Char( 22 ), CSD_REQD Date, CSD_OWNR Char( 5 ), CSD_TDES Char( 6 ), CSD_ACLAS Char( 1 ));

INSERT INTO "E0362CSD" VALUES( 'BS', 'MSUB', 'M', '0001', '0', 'ROCKS FROM AL SHAALI CRUSHERS - 0001', NULL, '2003-09-17', 'BSMSUB00010', NULL, 'BESIX', NULL, 'M' );
INSERT INTO "E0362CSD" VALUES( 'BS', 'TRAN', 'T', '0003', '4', 'TRANSMITTAL', NULL, '2003-09-18', 'BSTRAN00034', NULL, 'BESIX', NULL, 'T' );
INSERT INTO "E0362CSD" VALUES( 'NA', 'TRAN', 'T', '0000', '1', 'TRANSMITTAL', NULL, '2003-09-20', 'DT/001', NULL, 'NAKHE', NULL, 'T' );

INSERT INTO "E0362CSD" VALUES( 'BS', 'MSUB', 'M', '0002', '0', 'ROCKS FROM BARTAWI CRUSHERS', NULL, '2003-09-17', 'BSMSUB00020', NULL, 'BESIX', NULL, 'M' );
INSERT INTO "E0362CSD" VALUES( 'BS', 'TRAN', 'T', '0005', '0', 'TRANSMITTAL', NULL, '2003-09-18', 'BSTRAN00050', NULL, 'BESIX', NULL, 'T' );
INSERT INTO "E0362CSD" VALUES( 'NA', 'TRAN', 'T', '0000', '2', 'TRANSMITTAL', NULL, '2003-09-20', 'DT 001', NULL, 'NAKHE', NULL, 'T' );

INSERT INTO "E0362CSD" VALUES( 'BS', 'MSUB', 'M', '0012', '0', '10mm AGGREGATES - COARSE AGGREGATE FOR CONCRETE', NULL, '2003-09-22', 'BSMSUB00120', NULL, 'BESIX', NULL, 'M' );
INSERT INTO "E0362CSD" VALUES( 'BS', 'TRAN', 'T', '0002', '3', 'TRANSMITTAL', NULL, '2003-09-22', 'BSTRAN00023', NULL, 'BESIX', NULL, 'M' );
No entry for this, coz no reply ...


Indexes
Main Uniq: csd_orig + csd_subj + csd_type + csd_numb + csd_revi

-------------------------------------

Create Table "E0362APR" ( APR_DOCU Char( 12 ), APR_PART Char( 5 ), APR_TYPE Char( 1 ), APR_TRN2 Char( 12 ), APR_STAT Char( 1 ), APR_REQD Date);

INSERT INTO "E0362APR" VALUES( 'BSMSUBM00010', 'NAKHE', 'A', 'NATRANT00001', '3', '2003-09-24' );
INSERT INTO "E0362APR" VALUES( 'BSMSUBM00020', 'NAKHE', 'A', 'NATRANT00002', '3', '2003-09-24' );
INSERT INTO "E0362APR" VALUES( 'BSMSUBM00120', 'NAKHE', 'A', NULL, NULL, '2003-09-29' );

Indexes
Main Uniq: apr_docu + apr_part

-------------------------------------

Create Table "E0362TRA" ( TRA_TRNO Char( 12 ), TRA_DRGN Char( 12 ), TRA_MEDI Char( 2 ), TRA_COPY Numeric( 2 ,0 ), TRA_APPR Logical, TRA_STAG Char( 1 ), TRA_PART Char( 5 ), TRA_TYPE Char( 1 ));

INSERT INTO "E0362TRA" VALUES( 'BSTRANT00034', 'BSMSUBM00010', 'A4', 1, True, NULL, 'NAKHE', 'A' );
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00034', 'BSMSUBM00010', NULL, 0, False, NULL, 'FSP', 'I' );
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00034', 'BSMSUBM00010', NULL, 0, False, NULL, 'RECOR', 'F' );
INSERT INTO "E0362TRA" VALUES( 'NATRANT00001', 'BSMSUBM00010', 'A4', 1, True, NULL, 'BESIX', 'I' );

INSERT INTO "E0362TRA" VALUES( 'BSTRANT00050', 'BSMSUBM00020', 'A4', 1, True, NULL, 'NAKHE', 'A' );
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00050', 'BSMSUBM00020', NULL, 0, False, NULL, 'FSP', 'I' );
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00050', 'BSMSUBM00020', NULL, 0, False, NULL, 'RECOR', 'F' );
INSERT INTO "E0362TRA" VALUES( 'NATRANT00002', 'BSMSUBM00020', 'A4', 1, False, NULL, 'BESIX', 'I' );

INSERT INTO "E0362TRA" VALUES( 'BSTRANT00023', 'BSMSUBM00120', 'A4', 1, True, NULL, 'NAKHE', 'A' );
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00023', 'BSMSUBM00120', NULL, 0, False, NULL, 'FSP', 'I' );
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00023', 'BSMSUBM00120', NULL, 0, False, NULL, 'RECOR', 'F' );
No entry for this, coz no reply ...

Indexes
Main Uniq: tra_trno + tra_part + tra_drgn

-------------------------------------

Create Table "E0362TRD" ( TRD_TRNO Char( 12 ), TRD_PART Char( 5 ), TRD_LOCK Logical, TRD_COPY Numeric( 2 ,0 ), TRD_MEDI Char( 2 ), TRD_TYPE Char( 1 ), TRD_CC Char( 1 ), TRD_RECD Date, TRD_DSET Char( 3 ));

INSERT INTO "E0362TRD" VALUES( 'BSTRANT00034', 'NAKHE', NULL, 1, 'A4', 'A', '0', '2003-09-18', NULL );
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00034', 'RECOR', NULL, 1, 'A4', 'F', '1', NULL, NULL );
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00034', 'FSP', NULL, 1, 'A4', 'I', '1', NULL, NULL );
INSERT INTO "E0362TRD" VALUES( 'NATRANT00001', 'BESIX', NULL, 1, 'A4', 'I', '0', '2003-09-22', NULL );

INSERT INTO "E0362TRD" VALUES( 'BSTRANT00050', 'NAKHE', NULL, 1, 'A4', 'A', '0', '2003-09-18', NULL );
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00050', 'RECOR', NULL, 1, 'A4', 'F', '1', NULL, NULL );
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00050', 'FSP', NULL, 1, 'A4', 'I', '1', NULL, NULL );
INSERT INTO "E0362TRD" VALUES( 'NATRANT00002', 'BESIX', NULL, 1, 'A4', 'I', '0', '2003-09-22', NULL );

INSERT INTO "E0362TRD" VALUES( 'BSTRANT00023', 'NAKHE', NULL, 2, 'A4', 'A', '0', '2003-09-23', NULL );
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00023', 'RECOR', NULL, 1, 'A4', 'F', '1', NULL, NULL );
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00023', 'FSP', NULL, 1, 'A4', 'I', '1', NULL, NULL );
No entry for this, coz no reply ...


Indexes
Main Uniq: trd_trno + trd_cc + trd_part

-------------------------------------

Now I want this above info for all the three documents in a fashion shown below
Doc_No - DOC_DESC - DOC_ISSUE - TRAN_NO - TRAN_ISSUE - TRAN_RECD - REPLY_EXPECTED - REPLYTRAN_NO - REPLYTRAN_ISSUE - REPLYTRAN_RECD - DOC_STATUS

Material Submittal Report has to look like this with the final query ...

Doc_No , DOC_DESC , DOC_ISSUE , TRAN_NO , TRAN_ISSUE , TRAN_RECD , REXPECTED , REPLYNO , REPLYISSUE , REPLY_RECD , DOC_STATUS
csd_altr , csd_labl , csd_issu , csd_altr , csd_issu , trd_recd , apr_reqd , csd_altr , csd_issu , trd_recd , apr_stat

BSMSUB00010 , ROCKS FROM AL SHAALI CRUSHERS - 0001 , 2003-09-17 , BSTRAN00034 , 2003-09-18 , 2003-09-18 , 2003-09-24 , DT/001 , 2003-09-20 , 2003-09-22 , 3
BSMSUB00020 , ROCKS FROM BARTAWI CRUSHERS , 2003-09-17 , BSTRAN00050 , 2003-09-18 , 2003-09-18 , 2003-09-24 , DT 001 , 2003-09-20 , 2003-09-22 , 3
BSMSUB00120 , 10mm AGGREGATES - COARSE AGGREGATE FOR CONCRETE , 2003-09-22 , BSTRAN00023 , 2003-09-22 , 2003-09-23 , 2003-09-29 , Nil , Nil , Nil , Nil
...
...
...

Query I tried is like this ...
But does not give me complete result ... Reply Transmittal Alternate Number, Reply Transmittal Issue Date & Reply Transmittal Received Date
-------------------------------

select
csd1.csd_altr, csd1.csd_labl, csd1.csd_issu,
csd2.csd_altr, csd2.csd_issu,
trd1.trd_recd,
apr1.apr_reqd, apr1.apr_trn2, apr1.apr_stat

from
e0362csd csd1,
e0362tra tra1,
e0362csd csd2,
e0362trd trd1,
e0362apr apr1

where

csd1.csd_orig = 'BS' and
csd1.csd_subj = 'MSUB' and
csd1.csd_type = 'M' and

tra1.tra_trno = csd2.csd_orig + csd2.csd_subj + csd2.csd_type + csd2.csd_numb + csd2.csd_revi and
tra1.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
tra1.tra_part = 'NAKHE' and
tra1.tra_type = 'A' and

trd1.trd_trno = tra1.tra_trno and
trd1.trd_part = tra1.tra_part and
trd1.trd_cc = '0' and
trd1.trd_type = 'A' and

apr1.apr_docu = tra1.tra_drgn and
apr1.apr_part = tra1.tra_part and
apr1.apr_type = 'A'

order by
csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi

neerajjariwala.besix@gmail.com

Jari
Computer Engg
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site