May 18, 2007 at 7:53 am
Hi All
I need to run strange report for my client hope somebody can help...
If we invoiced 10 same items in a singel invoice my client wants to see 10 lines dulicate lines with qty one in every line.
eg like if invoice no=A1234 and itemcode=xyz and item invoiced =3
i want output like
A1234 xyz 1
A1234 xyz 1
A1234 xyz 1
In my table i have output in a single line
A1234 xyz 3
Any help please
May 18, 2007 at 8:21 am
I don't know if this is a viable solution but one of the things you could do is to create a table with something like this
CREATE TABLE itemmultiplier ( items INT)
INSERT INTO itemmultiplier ( items) VALUES ( 1)
INSERT INTO itemmultiplier ( items) VALUES ( 2)
INSERT INTO itemmultiplier ( items) VALUES ( 2)
INSERT INTO itemmultiplier ( items) VALUES ( 3)
INSERT INTO itemmultiplier ( items) VALUES ( 3)
INSERT INTO itemmultiplier ( items) VALUES ( 3)
INSERT INTO itemmultiplier ( items) VALUES ( 4)
INSERT INTO itemmultiplier ( items) VALUES ( 4)
INSERT INTO itemmultiplier ( items) VALUES ( 4)
INSERT INTO itemmultiplier ( items) VALUES ( 4)
etc, etc...
and then join this table with your table on item invoiced. This way you artificially blow up your item as many times as you need. Of course if your items values range is very broad tha this itemmulitpier table will be quite big.
This is quick and dirty solution but it should work.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 18, 2007 at 12:12 pm
You can use a cursor to select invoice no,itemcode with group by and having count of item invoiced >1. If the criteria satisfies the display like invoice no,itemcode,1 (You can hardcode 1 here)
HTH..
May 18, 2007 at 12:41 pm
Here goes the Full Script for you..
-- I have created TestInv for testing the script..you have to use the table you are refering in the question.
create table TestInv (itemcode int,itemname varchar(100),invoiced int)
Insert into TestInv values(1,'a',1)
Insert into TestInv values(2,'b',2)
Insert into TestInv values(3,'c',3)
Insert into TestInv values(4,'d',4)
Insert into TestInv values(5,'e',1)
select * into #testInv from TestInv where invoiced=1
Declare @itemcode int,
@Itemname varchar(100),
@Invoiced int,
@intCnt int
Declare csrItems CURSOR FOR
select itemcode,itemname,max(Invoiced) invoiced from TestInv where invoiced > 1 group by itemcode,itemname
OPEN csrItems
FETCH NEXT FROM csrItems Into @itemcode,@Itemname,@Invoiced
WHILE @@FETCH_STATUS = 0
BEGIN
Set @intcnt =1
while @intcnt <= @Invoiced
begin
insert into #testInv values(@itemcode,@Itemname,1)
set @intcnt= @intcnt + 1
end
FETCH NEXT FROM csrItems Into @itemcode,@Itemname,@Invoiced
END
Close csrItems
DeAllocate csrItems
select * from #testInv order by itemcode
drop table #testInv
May 18, 2007 at 10:13 pm
Try
DECLARE @T TABLE (Invoice char(5), Item char(3), Quantity int)
INSERT INTO @T VALUES ('A1234', 'ABC', 5)
INSERT INTO @T VALUES ('A1234', 'MMM', 9)
INSERT INTO @T VALUES ('A1234', 'XYZ', 3)
DECLARE @N TABLE (N int)
DECLARE @I int
SELECT @I = 1
WHILE @I = A.N
ORDER BY B.Item
K. Matsumura
May 19, 2007 at 7:09 am
Vandy,
Cursors incur an overhead and temporary tables involve always io in tempdb (also an 'in-memory table' which is actually a misnomer). Here is a solution that just uses simple tsql:
-- your testcase
CREATE TABLE test3( InvoiceID char(5), Itemcode char(3), nbr int)
INSERT test3 (InvoiceID, ItemCode, nbr) VALUES ('A1234','xyz',3)
Keep a table around with just unique natural numbers. You could build it at runtime with a function if you like.
For performance reasons it can be a real table. It can be in another db using a synonym if you like.
CREATE TABLE nums (num int) PRIMARY KEY
INSERT nums(num) VALUES (1)
INSERT nums(num) VALUES (2)
INSERT nums(num) VALUES (3)
INSERT nums(num) VALUES (4)
INSERT nums(num) VALUES (5)
... numbers can be much higher according to needs
-- then you simply select
SELECT InvoiceID,ItemCode, 1
FROM test3
CROSS JOIN nums
WHERE num <= 3
Of course you can use this if the amount of sublines varies per order:
SELECT InvoiceID,ItemCode, 1
FROM test3
CROSS JOIN nums
WHERE num <= test3.nbr
Hope this helps. Courtasy to Itzik Ben-Gan; you realy should all read his books and/or attend his courses 😉
Jan
May 22, 2007 at 2:44 am
Hi All
Thanks for your help.
Apologies for my ignorance actually first time i am working on sql server so need more help. I have around 10,000 lines for one day and I am running this table for Month to date data I am joining four tables to show output of 20 columns. How should i create table?
Regards
May 22, 2007 at 7:17 am
Can you provide the structure of these four tables, and possibly the query that you are using? With this, we can provide you more detailed help.
Regards,
MCTS
May 22, 2007 at 7:54 am
This is the query
SELECT NBillingF.OrderNumber, NBillingF.Customerorderreference, AccountOwner.company, NewCARORDER.Orderaddressline5, Jcustomers.company AS Expr1, NewCARORDER.Orderaddressname, Product.[Manufacturer Part Number], Product.[Product Description], NBillingF.QuantityInvoiced, NewCARORDER.SystemOrderDate, NBillingF.DateInvoiced, NBillingF.Unitprice, NBillingF.InvoicedValueBase
FROM NBillingF INNER JOIN
AccountOwner ON NBillingF.Customer = AccountOwner.account INNER JOIN
NewCARORDER ON NBillingF.Customer = NewCARORDER.Customer AND NBillingF.Deliveryaddresscode = NewCARORDER.Deliveryaddresscode AND
NBillingF.OrderNumber = NewCARORDER.Ordernumber AND NBillingF.Despatchnotenumber = NewCARORDER.Despatchseq INNER JOIN
Jcustomers ON NBillingF.Customer = Jcustomers.account AND NBillingF.Deliveryaddresscode = Jcustomers.delsequence INNER JOIN
Product ON NBillingF.ItemCode = Product.[Westcoast Part Number]
WHERE (NBillingF.Customer like ('RR%')) AND (NBillingF.Month=DatePart(mm,getdate()))
If i have quantityInvoiced=6 then i need to show six duplicate lines qith quantityinvoiced 1 in everyline. Does it make sense?
Thanks
May 22, 2007 at 7:54 am
This is the query
SELECT NBillingF.OrderNumber, NBillingF.Customerorderreference, AccountOwner.company, NewCARORDER.Orderaddressline5, Jcustomers.company AS Expr1, NewCARORDER.Orderaddressname, Product.[Manufacturer Part Number], Product.[Product Description], NBillingF.QuantityInvoiced, NewCARORDER.SystemOrderDate, NBillingF.DateInvoiced, NBillingF.Unitprice, NBillingF.InvoicedValueBase
FROM NBillingF INNER JOIN
AccountOwner ON NBillingF.Customer = AccountOwner.account INNER JOIN
NewCARORDER ON NBillingF.Customer = NewCARORDER.Customer AND NBillingF.Deliveryaddresscode = NewCARORDER.Deliveryaddresscode AND
NBillingF.OrderNumber = NewCARORDER.Ordernumber AND NBillingF.Despatchnotenumber = NewCARORDER.Despatchseq INNER JOIN
Jcustomers ON NBillingF.Customer = Jcustomers.account AND NBillingF.Deliveryaddresscode = Jcustomers.delsequence INNER JOIN
Product ON NBillingF.ItemCode = Product.[Westcoast Part Number]
WHERE (NBillingF.Customer like ('RR%')) AND (NBillingF.Month=DatePart(mm,getdate()))
If i have quantityInvoiced=6 then i need to show six duplicate lines qith quantityinvoiced 1 in everyline. Does it make sense?
Thanks
May 22, 2007 at 8:13 am
SELECT NBillingF.OrderNumber, NBillingF.Customerorderreference,
AccountOwner.company, NewCARORDER.Orderaddressline5,
customers.company AS Expr1, NewCARORDER.Orderaddressname,
Product.[Manufacturer Part Number], Product.[Product Description],
1, NewCARORDER.SystemOrderDate,
NBillingF.DateInvoiced, NBillingF.Unitprice, NBillingF.InvoicedValueBase
FROM NBillingF INNER JOIN AccountOwner
ON (NBillingF.Customer = AccountOwner.account)
INNER JOIN NewCARORDER
ON (NBillingF.Customer = NewCARORDER.Customer
AND NBillingF.Deliveryaddresscode = NewCARORDER.Deliveryaddresscode
AND NBillingF.OrderNumber = NewCARORDER.Ordernumber
AND NBillingF.Despatchnotenumber = NewCARORDER.Despatchseq)
INNER JOIN Jcustomers
ON (NBillingF.Customer = Jcustomers.account
AND NBillingF.Deliveryaddresscode = Jcustomers.delsequence)
INNER JOIN Product
ON NBillingF.ItemCode = Product.[Westcoast Part Number]
CROSS JOIN dbo.nums
quantityInvoiced
WHERE (NBillingF.Customer like ('RR%'))
AND (NBillingF.Month=DatePart(mm,getdate()))
AND nums.num <= quantityInvoiced
And you first populate the nums table with a numer of items > MAX(quantityInvoiced).
This solution from Itzik Ben-Gan is the simplest, most elegant and it will give you the best query plan, without need to use slower cursors or tempotary tables. I like it because it is a setbased solution, not a procedural one.
regards,
Jan
May 22, 2007 at 8:49 am
Sorry Still not working may be doing something wrong
It showing fully duplicate lines but i need quantity invoiced always one ..
if quantity invoiced=6
it showing every time 6 but i need 1 six times.....
Kind Regards
May 22, 2007 at 9:01 am
Yes, I also changed the quantity to a constant 1 in the query, but I forgot to color it....
SELECT NBillingF.OrderNumber, NBillingF.Customerorderreference,
AccountOwner.company, NewCARORDER.Orderaddressline5,
customers.company AS Expr1, NewCARORDER.Orderaddressname,
Product.[Manufacturer Part Number], Product.[Product Description],
1, NewCARORDER.SystemOrderDate,
NBillingF.DateInvoiced, NBillingF.Unitprice....
Of course, this is not very interesting information, being always 1 😉
regards,
Jan
May 22, 2007 at 9:36 am
Sorry Jan to be pain...
Everything is ok but showing more lines
eg quantityinvoiced =2 it showing me three lines ...
Regards
May 22, 2007 at 9:42 am
Oops Done...
Thanks a lot Jan it's done working fine
It happened because of zero when i replaced that from my num table it's working fine.
Thanks a lot
Kind Regards
Vandy
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply