SQLTeam.com | Weblogs | Forums

Generate Report from 3 tables

I have three tables 1] Receive 2]Issue and 3]Melting. I have to calculate balance from these three table

My approach is that union two tables (Issue and Melting) - Receive table but it showing wrong output. I need below output which has been posted.

Receipt ReceiptId VoucherNo
1 VC001 215
2 VC002 490
3 VC003 328
Rdetails ReceiptDetaild ReceiptId ItemId ReceiveWt ReceivePr FineWt
1 1 2 115 92 105.8
2 1 3 60 75 45
3 1 3 40 100 40
4 2 14 350 100 350
5 2 21 140 99.5 139.3
6 3 41 210 99.5 208.95
7 3 48 118 100 118
Melting MeltingId LotNumber
1 22050001
2 22050002
MDetails MDetails Id MeltingId ReceiptId RecDetId ItemType SlipBagNo ItemBagId GrossWt GrossPr FineWt
1 1 1 1 Voucher VC001 2 25 92 23
2 1 1 2 Voucher VC001 3 40 75 30
3 2 2 5 Voucher VC002 21 25 99.5 24.875
Issue IssueId VoucherNo
1 CM001
2 CM002
3 CM003
4 CM004
5 CM005
6 CM006
IDetails IssueDetail_Id IssueId ReceiptId RecDetId ItemType SlipBagNo ItemId IssueWt IssuePr ConvPr FineWt
1 1 1 1 Voucher VC001 2 60 92 92 46
2 1 2
OutPut 2 Date Voucher No Voucher Wt % Fine Used In Lot Number/Voucher No Used Wt Balance Balance Fine
VC001 115 92 85 30
Melting 22050001 25
Voucher CM001 60
VC001 60 75 40 20
Melting 1 40
VC001 40 100 40 0 0
Stock 40
VC002 140 99.5 25 115
Melting 1 25
VC003 210 99.5 0 210
VC003 118 100 0 118

for Used in Select case used depend on value

Regards

Nandan