I need a sol very very urgently ... Reply ASAP

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)

I need a sol very very urgently ... Reply ASAP

Author

Topic

nssjari
Starting Member

46 Posts

Posted - 2005-06-23 : 03:37:48

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

-----------

Jari
Computer Engg

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-23 : 03:43:29

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51494

Please don't cross post, it's rude.
Secondly, posting your question is enough, if someone sees it, and they have time to answer you, they will. Emailing everyone on the first page of the members list is really rude as well.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources