Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to work out on these query? Expand / Collapse
Author
Message
Posted Thursday, January 16, 2014 10:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:57 AM
Points: 179, Visits: 448
my table looks like


invoice_no model_ref item_type item_no invoice_qty
tn/0002/13-14 - I 5000350 5
" - K 6000350 10
" 6000350 X 5000242 15
" 6000350 X 5000470 2
MB/0006/13-14 - K 6000370 10
" 6000370 X 5000460 10
" 6000370 X 5000480 1

like i ve lakh of recors in my table

table struct:
+++++++++

create table invoice_item
(
invoice_no varchar(20),
model_ref int,
item_type char(10),
item_no varchar(20),
invoice_qty int
)

insert into invoice_item(invoice_no,model_ref,item_type,item_no,invoice_qty)
values ('tn/0002/13-14','-','I','5000350','5')

' ('tn/0002/13-14','-','K','6000350','10')
' ('tn/0002/13-14','6000350','X','5000242','15')



My EXPECTING O/P:


invoice_no item_no Sales Free

tn/0002/13-14 5000350 5 0
tn/0002/13-14 5000242 10 5
tn/0002/13-14 5000470 0 2
MB/0006/13-14 5000460 10 0
MB/0006/13-14 5000480 0 1


here Item_types "I" directly billed item so it ll reflect free "0" always.
but when we billed along with "K"(kit) highest value takes billed remaining as free(I.E 6000350 kit was billed on tn/0002/13-14 with 10 QTY
actually it contains 5000242 = 15 QTY & 5000470 = 2 QTY
highest values (5000242=15) is less from kit billed. shows like line number 2,3 from my expecting O/P table)


how to do that?
Post #1531920
Posted Friday, January 17, 2014 1:03 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 3,346, Visits: 3,641
If you could let us see how far you've already got, we might be able to help out seeing where the problem is.

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1531944
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse