January 23, 2014 at 12:57 am
Hi Friends,
i ve the table like
struct:
------
create table inv_tab
(
company_no char(50),
invoice_no varchar(50),
model_ref varchar(20),
item_type char(10),
item_no varchar(35),
inv_qty int
)
insert into inv_tab
(
company_no,
invoice_no,
model_ref,
item_type,
item_no,
inv_qty
)
values
(
'x',
'tr/002/13-14',
'##',
'K',
'6000352',
'50'
)
values
(
'x',
'tr/002/13-14',
'6000352',
'X',
'5000350',
'65'
)
values
(
'x',
'tr/002/13-14',
'6000352',
'x',
'5000342',
'5'
)
values
(
'x',
'tr/004/13-14',
'##',
'K',
'6000350',
'25'
)
values
(
'x',
'tr/004/13-14',
'6000350',
'X',
'5000050',
'35'
)
now i wanna display the o/p like
company invoice_no model_ref item_no sales free
x tr/002/13-14 6000352 5000350 50 15
x tr/002/13-14 6000352 5000342 0 05
x tr/004/13-14 6000350 5000050 25 35
Here the conditions are
1) item_type = 'K' is that product group
2)item_type='x' is referenced item in that group
when i show the sales & free in my o/p wrote query
select
a.company_no,
a.invoice_no,
a.item_no as model_ref,
b.item_no
a.invoice_qty as sales,
b.invoice_qty - a.invoice_qty as free
from
inv_tab a
inner join
inv_tab b
where
a.company_no=b.company_no
and
a.item_no=b.model_ref
and
a.invoice_no=b.invoice_no
its giving the o/p:
company invoice_no model_ref item_no sales free
x tr/002/13-14 6000352 5000350 50 15
x tr/002/13-14 6000352 5000342 50 -45
x tr/004/13-14 6000350 5000050 25 35
how to change my code as my expecting o/p.....
January 23, 2014 at 6:27 am
Where does the zero come from? The way the test data is defined it seams to be correct. If the value is only to be used once (the 50) then that criteria is needed to be known.
January 23, 2014 at 8:02 am
This is a basic cte. Please use google for examples.
Once again you post another thread with partially useful information and calculations with no explanation. How do you expect us to be able to help you?
Take a look at this article. Maybe you just don't have the perspective of other people that are trying to help you. http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply