July 17, 2012 at 6:17 am
i have 2 tables like
"storage"
compny location lotno
-----------------------
A chen d77
A chen c88
A Hyd j99
A che d77
A che c88
A hyd j99
"ACCOUNTS"
compny location amount
-----------------------
A chen 3000
A chen 6899
A Hyd 4500
A che 3000
A che 6899
A hyd 4677
now i wanna create a report for compny name,location,lotno,amount by using joins...... now the problem is some lotno are splitted ,ve give exact AMOUNT for that.....
need output
"FINAL report"
compny location lotno amount
-----------------------------
A chen d77 3000
A chen c88 6899
A Hyd j99 4500
A che d77 3000
A che c88 6899
A hyd j99 4577
pls any give idea????
July 17, 2012 at 6:26 am
How did you decide on the lotno's?
Do you have any other columns in these 2 tables or any other tables associated with these tables?
compny location lotno amount
-----------------------------
A chen d77 3000 -- why should this not be linked to lotno c88?
A chen c88 6899
A Hyd j99 4500
A che d77 3000
A che c88 6899
A hyd j99 4577
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 17, 2012 at 6:46 am
its actually 3 tables......
T1="HEADER "
its has compny,location,issueno
T2="STORED DATA"
it also have company,location,issueno,stockno,lotno
T3="Accounts"
in this table ve company,location,issueno,stockno,lotno,quantity,amount
Now i need full report structr is company,location,stockno,lotno,amount
some of the lotno are splitted,i need correct amount on particular lotno..... but does't working.....
,
July 17, 2012 at 6:52 am
em wrote query like
select a.company,b.location,b.stockno,b.lotno,c.value
from header a
inner join
storage b
on
a.company=b.company
and a.location=b.location
inner join
accounts c
on b.issueno= b.issueno
and a.company=c.company
and b.location=c.location
and b.stockno=c.stockno
July 17, 2012 at 7:42 am
raghuldrag (7/17/2012)
em wrote query likeselect a.company,b.location,b.stockno,b.lotno,c.value
from header a
inner join
storage b
on
a.company=b.company
and a.location=b.location
inner join
accounts c
on b.issueno= b.issueno
and a.company=c.company
and b.location=c.location
and b.stockno=c.stockno
Does the above query work for you?
The table structure you have provided now is different and the results expected are different too
Please provide the proper DDL of all tables involved, some sample data and the expected results
If you don't have an idea on how to do this, check the link in my signature
I am sure you will appreciate the time spent on reading the article when you get tested solutions back.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 17, 2012 at 9:59 pm
if u dnt mind... wr i have to chck link pls tel clearly
July 18, 2012 at 1:10 am
company_no location_no issue_no lot_no quantity confirmed_qt.... value
---------- ------ ------------------ ------- -------------- ------------- -----------
AMRUTFAC MHYD BACK\000000\2008 H1207013 24.00000000 24.00000000 2603.04000000
AMRUTFAC MHYD BACK\000000\2008 C2107065 40.00000000 40.00000000 1643.60000000
AMRUTFAC MHYD BACK\000000\2008 C1707055 40.00000000 40.00000000 3293.20000000
AMRUTFAC MHYD BACK\000001\2008 H040719 19.00000000 19.00000000 1993.48000000
AMRUTFAC MHYD BACK\000001\2008 C0907270 12.00000000 12.00000000 12.00000000
AMRUTFAC MHYD BACK\000001\2008 H1207013 5.00000000 5.00000000 542.30000000
AMRUTFAC MHYD BACK\000001\2008 C2107065 10.00000000 10.00000000 410.90000000
AMRUTFAC MHYD BACK\000001\2008 C1807041 21.00000000 21.00000000 1996.89000000
AMRUTFAC MHYD BACK\000002\2008 H0407211 116.00000000 116.00000000 12170.72000000
AMRUTFAC MHYD BACK\000003\2008 C1807041 6.00000000 6.00000000 570.54000000
AMRUTFAC MHYD BACK\000004\2008 C1807041 10.00000000 10.00000000 950.90000000
hi,
dis my report table
company-no,location-no ,issue_nois==>tabl1
issue_no,lot_no,quantity_no, confirmed_qty_in_stock_uom ===>tabl2
issue_no,valueno====>tabl3 after join the table its gives
above the outputin that"LOT_NO" is repeated but "value"also need for that particular lotno, unfortunatly its displyng
"LOT_NO" "value"
row1 ===== H1207013 ..... 2603.04000000
row6=======H1207013....... 542.30000000
now i need lot_no are splitted 2 times but the value had summarised.... the value also split depends on lot_no appeared...
July 18, 2012 at 2:14 am
raghuldrag (7/18/2012)
company_no location_no issue_no lot_no quantity confirmed_qt.... value---------- ------ ------------------ ------- -------------- ------------- -----------
AMRUTFAC MHYD BACK\000000\2008 H1207013 24.00000000 24.00000000 2603.04000000
AMRUTFAC MHYD BACK\000000\2008 C2107065 40.00000000 40.00000000 1643.60000000
AMRUTFAC MHYD BACK\000000\2008 C1707055 40.00000000 40.00000000 3293.20000000
AMRUTFAC MHYD BACK\000001\2008 H040719 19.00000000 19.00000000 1993.48000000
AMRUTFAC MHYD BACK\000001\2008 C0907270 12.00000000 12.00000000 12.00000000
AMRUTFAC MHYD BACK\000001\2008 H1207013 5.00000000 5.00000000 542.30000000
AMRUTFAC MHYD BACK\000001\2008 C2107065 10.00000000 10.00000000 410.90000000
AMRUTFAC MHYD BACK\000001\2008 C1807041 21.00000000 21.00000000 1996.89000000
AMRUTFAC MHYD BACK\000002\2008 H0407211 116.00000000 116.00000000 12170.72000000
AMRUTFAC MHYD BACK\000003\2008 C1807041 6.00000000 6.00000000 570.54000000
AMRUTFAC MHYD BACK\000004\2008 C1807041 10.00000000 10.00000000 950.90000000
hi,
dis my report table
company-no,location-no ,issue_nois==>tabl1
issue_no,lot_no,quantity_no, confirmed_qty_in_stock_uom ===>tabl2
issue_no,valueno====>tabl3 after join the table its gives
above the outputin that"LOT_NO" is repeated but "value"also need for that particular lotno, unfortunatly its displyng
"LOT_NO" "value"
row1 ===== H1207013 ..... 2603.04000000
row6=======H1207013....... 542.30000000
now i need lot_no are splitted 2 times but the value had summarised.... the value also split depends on lot_no appeared...
Unfortunately, it is still not very clear
Please go through the link below to know how to post sample data, expected results and DDL
http://www.sqlservercentral.com/articles/Best+Practices/61537/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 18, 2012 at 3:10 am
queryoutput:
=============
company_no location_no issue_no stock_no lot_no value
---------- ---------- ------------- ------- --------- -----
AMRUTFAC MHYD BACK\000000\2008 5000054 [highlight=#ffff11]H1207013 2603.04000000 [/highlight]
AMRUTFAC MHYD BACK\000000\2008 5000153 C2107065 1643.60000000
AMRUTFAC MHYD BACK\000001\2008 5000160 H0407190 1993.48000000
AMRUTFAC MHYD BACK\000001\2008 5000054[highlight=#ffff11] H1207013 542.30000000 [/highlight]
AMRUTFAC MHYD BACK\000001\2008 5000153 C2107065 410.90000000
AMRUTFAC MHYD BACK\000005\2008 5000054 H1207013 1084.60000000
AMRUTFAC MHYD BACK\000005\2008 5000161 C0907270 5.00000000
AMRUTFAC MHYD BACK\000006\2008 5000031 C0907086 498.85000000
AMRUTFAC MHYD BACK\000008\2008 5000161 C0907270 24.00000000
AMRUTFAC MHYD BACK\000008\2008 5000151 3 1141.65000000
AMRUTFAC MHYD BACK\000008\2008 5000068 C1807041 3042.88000000
my query is:
-----------
SELECT a.company_no,a.location_no,b.issue_no,b.stock_no,a.lot_no,b.value
from tab2 a
inner join
tab3 b
on
a.li_no=b.li_no
and
a.company_no=b.company_no
and a.location_no=a.location_no
and a.stock_no=b.stock_no
and a.issue_no=b.issue_no
dis my report table
company-no,location-no ,issue_nois==>tabl1
issue_no,lot_no,_no, confirmed_qty_in_stock_uom ===>tabl2
issue_no,valueno====>tabl3 after join the table its gives
above the outputin that"LOT_NO" is repeated but "value"also need for that particular lotno, unfortunatly its displyng
"LOT_NO" "value"
row1 ===== H1207013 ..... 2603.04000000
row6=======H1207013....... 542.30000000
now i need lot_no are splitted 2 times but the value had summarised.... the value also split depends on lot_no appeared...
July 18, 2012 at 3:26 am
raghuldrag (7/18/2012)
=============
company_no location_no issue_no stock_no lot_no value
---------- ---------- ------------- ------- --------- -----
AMRUTFAC MHYD BACK\000000\2008 5000054 [highlight=#ffff11]H1207013 2603.04000000 [/highlight]
AMRUTFAC MHYD BACK\000000\2008 5000153 C2107065 1643.60000000
AMRUTFAC MHYD BACK\000001\2008 5000160 H0407190 1993.48000000
AMRUTFAC MHYD BACK\000001\2008 5000054[highlight=#ffff11] H1207013 542.30000000 [/highlight]
AMRUTFAC MHYD BACK\000001\2008 5000153 C2107065 410.90000000
AMRUTFAC MHYD BACK\000005\2008 5000054 H1207013 1084.60000000
AMRUTFAC MHYD BACK\000005\2008 5000161 C0907270 5.00000000
AMRUTFAC MHYD BACK\000006\2008 5000031 C0907086 498.85000000
AMRUTFAC MHYD BACK\000008\2008 5000161 C0907270 24.00000000
AMRUTFAC MHYD BACK\000008\2008 5000151 3 1141.65000000
AMRUTFAC MHYD BACK\000008\2008 5000068 C1807041 3042.88000000
my query is:
-----------
SELECT a.company_no,a.location_no,b.issue_no,b.stock_no,a.lot_no,b.value
from tab2 a
inner join
tab3 b
on
a.li_no=b.li_no
and
a.company_no=b.company_no
and a.location_no=a.location_no
and a.stock_no=b.stock_no
and a.issue_no=b.issue_no
dis my report table
company-no,location-no ,issue_nois==>tabl1
issue_no,lot_no,_no, confirmed_qty_in_stock_uom ===>tabl2
issue_no,valueno====>tabl3 after join the table its gives
========================================================
hi
the value colum is summarise,if the LOT_NO is reapted 5 times means the value divdied equally....
ex:
H1207013==>2 times came, now i need the value for lot printing ...
July 19, 2012 at 12:36 am
hi
the value colum is summarise,if the LOT_NO is reapted 5 times means the value divdied equally....
ex:
H1207013==>2 times came, now i need the value for lot printing ...
July 19, 2012 at 12:43 am
raghuldrag (7/17/2012)
if u dnt mind... wr i have to chck link pls tel clearly
I am sorry, but I have absolutely no idea what you just said here. It would help if you would use full english wors and sentences, not text speak. This is a professional technical site and much gets lost in the translation when using text speak.
July 19, 2012 at 1:47 am
LOTNO value QUANTITY
============================================
H1207013 2603.0400000024.00000000
C2107065 1643.6000000040.00000000
C1707055 3293.2000000040.00000000
H0407190 1993.4800000019.00000000
C0907270 12.0000000012.00000000
H1207013 542.300000005.00000000
C2107065 410.9000000010.00000000
C1807041 1996.8900000021.00000000
H0407211 12170.72000000116.00000000
C1807041 570.540000006.00000000
C1807041 950.9000000010.00000000
friends now i need the query for, if the LOTNO is reapeted here the value of summarised
we ll make to split has lotno,quantity wise also....
EX:
H1207013===>LOTNO displayed has two times,now the VALUE of "2603" divided has LOTNO
VALUE
============================================
(value/QUANTITY) ,(VALUE/NO.of.LOTNO),AS"VALUE"
July 19, 2012 at 3:05 am
create table #temp
(compny varchar(10),
location varchar(10),
lotno varchar(10),
amount int)
insert into #temp
select distinct a.* ,amount from storage a left outer join ACCOUNTS b
ON a.compny= b.compny
and a.location=b.location
select compny,location,amount from #temp
group by compny,location,amount
..................:-)
Regards,
Harsha
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply