Retrival of data

[ashdiip]

Hi!
I am having two similar kind of table, I’m looking for an SQL statement that can give me the following output:
(Pl. note that lines are overlapping in following output due to the space constraint.Infact, the col.names are next to each other).

Zone Branch No.of Lrs. Booking_Frt Delivery_Frt

Zone1 Branch1 35 100 50
Zone1 Branch2 10 30 72
Zone1 Branch3 45 400 170
Zone2 Branch1 11 44 44
Zone2 Branch2 26 62 19

I have the 2 tables as follows:
1) Table Zone (Master Table)
Code Varchar2(3)
Descr Varchar2(15)

2) Branch Table (Master Table)
Code Varchar2(3)
Descr Varchar2(15)

3) Lrbooking (Transaction Table)
Branch_code Varchar2(3)
Lr_No Varchar2(10)
Lr_Date Date
Booking_Frt Number(9.2)

4) LrDelivery (Transaction Table)
Branch_code Varchar2(3)
Lr_No Varchar2(10)
Dlv_Date Date
Delivery_Frt Number(9.2)

In Table no.3 & 4, Branch_code and Lr_Nos. are common. The report needs to be generated on common dates of both the table i.e Lr_date of Lrbooking and Dlv_date of LrDelivery i.e. for the period of ‘01-Apr-02’ to ‘30-Jun-02’

Thanks in advance

[john_burgess]

First point- surely the branch table needs to have a zone code in it as a foreign key reference to the zone table.
Assuming a column zcode in the branch table, does the following meet your needs:

select br.zcode,
br.code,
count(lrb.lr_no),
lrb.booking_Frt,
lrd.Delivery_Frt
FROM
Branch_Table br,
LrBooking lrb, LrDelivery lrd
WHERE br.code = lrb.branch_code
AND lrb.lr_no=lrd.lr_no
AND lrb.lr_date between ‘01-Apr-02’ and ‘30-Jun-02’
AND lrd.dlv_date between
‘01-Apr-02’ and ‘30-Jun-02’
GROUP BY br.code, lrb.booking_frt,
lrd.booking_frt

[ashdiip]

First and foremost thanks lot for answering my query. I am very sorry to say that I forgot to list “Zone” col. under branch table. Thanks for your correction.
Coming back to the subject, pl. note that the query given by you searches only for the common Lr.nos. between LrBooking and LrDelivery tables. This eliminates the LrBooking rows which does not exist in LrDelivery table.

Pl.find my following query, which gives correct LrBooking details, however, count for LrDelivey total Lrs.is showing equivalent to Lrs. of Lrbookings, Total for LrDelivery.Tot_frt is giving more than the actual exists in the LrDelivery Table. Precisely, it is giving me the wrong information for Lr delivery Table.
Sample testing was carried out for one of the branch and was found that 4 transactions under LrBooking were there , where as only 2 transactions. were there for LrDelivery.

Hope you will surely not mind answering my query.

Select
z.code,
br.code,
count(lrb.lr_no),
sum(lrb.tot_Frt),
count(lrd.lr_no),
sum(lrd.tot_Frt)
FROM
Branch br,
LrBooking lrb,
LrDelivery lrd,
Zone z
WHERE
br.code = lrb.branch_code__bkg
AND br.zone=z.code
AND lrb.branch_code__bkg=lrd.branch_code
AND lrb.lr_date=lrd.dlv_date
AND lrb.lr_date between '01-Apr-02' and '02-Apr-02'
AND lrd.dlv_date between '01-Apr-02' and '02-Apr-02'
AND lrb.branch_code__bkg='JSP'
GROUP BY z.code, br.code

[ashdiip]

First and foremost thanks lot for answering my query. Infact, I’m sorry to say that I forgot to list “Zone” col. under branch table. Thanks for your rectification.

Coming back to the subject, pl. note that the query given by you searches only for the common Lr.nos. between LrBooking and LrDelivery tables. This eliminates the LrBooking rows which does not exist in LrDelivery table.

Infact, I am giving my following query, which gives correct LrBooking details, however, count of LrDelivey total Lrs.is showing equivalent to Lrs. of Lrbookings and LrDelivery.Tot_frt is giving more than the actual exists in the LrDelivery Table. Precisely, it is giving me the wrong information for Lr delivery Table.

Sample testing was carried out for one of the branch and was found that 4 transactions under LrBooking were there , where as only 2 transactions. were there for LrDelivery.

I hope you'll not mind answering once again my following query:

Select
z.code,
br.code,
count(lrb.lr_no),
sum(lrb.tot_Frt),
count(lrd.lr_no),
sum(lrd.tot_Frt)
FROM
Branch br,
LrBooking lrb,
LrDelivery lrd,
Zone z
WHERE
br.code = lrb.branch_code__bkg
AND br.zone=z.code
AND lrb.branch_code__bkg=lrd.branch_code
AND lrb.lr_date=lrd.dlv_date
AND lrb.lr_date between '01-Apr-02' and '02-Apr-02'
AND lrd.dlv_date between '01-Apr-02' and '02-Apr-02'
GROUP BY z.code, br.code

[john_burgess]

I think you will need to tell us what the tables are supposed to hold. What is lr_no? I had thought it was something like a line number in an invoice.

Your condition lrb.lr_date=lrd.dlv_date
seems suspicious to me. If its required then you don't need lrd.dlv_date between '01-Apr-02' and '02-Apr-02'

since the condition on lrb.lr_date will ensure this.

If what you want is
zone branch 'total orders to branch' 'total deliveries to branch'
then maybe selecting from selects used in place of tables might do it.

i.e.
select z.code,
br.code,
sum1.cnt,
sum1.tot,
sum2.cnt,
sum2.tot
FROM
Zone z,
Branch br,
(select b1.code,
count(lrb.lr_no),
sum(lrb.tot_Frt)
FROM Branch b1, lrBooking lrb
where b1.code = lrb.branch_code__bkg
GROUP BY code) sum1
FULL OUTER JOIN
(select b2.code,
count(lrd.lr_no),
sum(lrd.tot_Frt)
FROM Branch b2, lrDelivery lrd
where b2.code = lrd.branch_code
GROUP BY code) sum2
WHERE
br.code = nvl( sum1.code, sum2.code )
AND
br.zone=z.code

[john_burgess]

In the previous reply, I forgot the ON clause.

ON sum1.code = sum2.code

and each subqueries where clause also needs
and lr_date between '01-Apr-02' and '01-Apr-03'