Is it possible to do.....Query help

  • 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

  • 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]

  • 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..

  • 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

  • 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

  • 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

  • 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

     

  • 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

  • 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

     

  • 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

     

  • 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

  • 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

     

  • 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

  • Sorry Jan to be pain...

    Everything is ok but showing more lines

    eg quantityinvoiced =2 it showing me three lines ...

    Regards

  • 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 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply