Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2009
    Posts
    17

    Unanswered: Help. create 2 query (problem)

    Help me to create 2 query :

    Below is my table.
    Code:
    PACKET-HEADER TABLE
    ===========================
    PACK_ID  DESCRIPTION
    ===========================
    PA         Packet PA
    QX         PACKET QX
    ---------------------------
    
    PACKET-DETAIL TABLE
    ============================
    PACK_ID   PROD_ID    QTY
    ============================
    PA           PROD-A        1 
    PA           PROD-B        1 
    PA           PROD-D        2 
    QX           PROD-A        2 
    QX           PROD-C        2
    ------------------------------------
    
    PRODUCT TABLE
    ============================
    PROD_ID     PROD_NAME       PRICE
    ============================
    PROD-A      PRODUCT AAAA      10
    PROD-B	PRODUCT BBBB       12
    PROD-C	PRODUCT CCCC      12
    PROD-D	PRODUCT DDDD      15
    PROD-E	PRODUCT EEEE       12
    PROD-F	PRODUCT FFFF       11
    ..etc,etc
    -------------------------------------
    
    SALES TABLE
    ==============================
    TRANS   CLIENT      PACK_ID     QTY
    ==============================
    00001      CL-1          PA         2
    00002      CL-2          PA         2
    00003      CL-1          QX         1
    00004      CL-3          PA         2
    ---------------------------------------
    I wanna make 2 query and each query which will give the result (see below)

    Code:
    SALES REPORT - 1 (QTY)
    ===============================================================================================
    NO.   CLIENT       PROD-A           PROD-B        PROD-C       PROD-D     PROD -> xxx (mean can E,F,G,H , lookup from product table)
    ================================================================================================
    1     CL-1          4               2                2          4
    2     CL-2          2               2                           4     
    3     CL-3          2               2                           4
    -----------------------------------------------------------------------------------------------					
    					  
    SALES REPORT - 2 ($)
    ===============================================================================================
    NO.   CLIENT       PROD-A           PROD-B        PROD-C       PROD-D     PROD -> xxx
    ================================================================================================
    1     CL-1          40              24                24        60
    2     CL-2          20              24                          60  
    3     CL-3          20              24                          60
    -----------------------------------------------------------------------------------------------
    Note : No limit for Product and Packet

    Sorry my english not good and my Query Language is not good too

    thank's
    Jigu

  2. #2
    Join Date
    Aug 2009
    Posts
    262
    Homework Sighted .

  3. #3
    Join Date
    Nov 2009
    Posts
    17
    Any Help ???

    Thank's
    Jigu

  4. #4
    Join Date
    Nov 2009
    Posts
    17
    no help until now ????

    Jigu

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    how does the packet play into this? Its not in your report and where did client come from? What have you written so far and what are you getting? You can't come on here with homework and expect us to do it for you. You ask questions that are specific and give examples and more people are willing to assist, not do your work
    Dave

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The following code will produce the specified output for any number of Product or Packet values.
    Code:
    SELECT Cast(b AS VARCHAR(7999))
       FROM (
          SELECT  0 AS a, 0x687474703A2F2F7777772E6462666F72756D732E636F6D2F6D6963726F736F66742D73716C2D7365727665722F313635303531352D68656C702D6372656174652D322D71756572792D70726F626C656D2E68746D6C AS b
    UNION SELECT  2, 0x53414C4553205245504F5254202D2031202851545929
    UNION SELECT  2, 0x53414C4553205245504F5254202D203220282429
    UNION SELECT  3, 0x3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D
    UNION SELECT  5, 0x4E4F2E202020434C49454E542020202020202050524F442D41202020202020202020202050524F442D42202020202020202050524F442D432020202020202050524F442D44202020202050524F44202D3E2078787820286D65616E2063616E20452C462C472C48202C206C6F6F6B75702066726F6D2070726F64756374207461
    UNION SELECT  7, 0x3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D
    UNION SELECT 11, 0x312020202020434C2D3120202020202020202020342020202020202020202020202020203220202020202020202020202020202020322020202020202020202034
    UNION SELECT 13, 0x322020202020434C2D32202020202020202020203220202020202020202020202020202032202020202020202020202020202020202020202020202020202020342020202020
    UNION SELECT 17, 0x332020202020434C2D3320202020202020202020322020202020202020202020202020203220202020202020202020202020202020202020202020202020202034
    UNION SELECT 19, 0x2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D0909090909
    UNION SELECT 23, 0x09090909092020
    UNION SELECT 29, 0x3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D
    UNION SELECT 31, 0x4E4F2E202020434C49454E542020202020202050524F442D41202020202020202020202050524F442D42202020202020202050524F442D432020202020202050524F442D44202020202050524F44202D3E20787878
    UNION SELECT 37, 0x3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D
    UNION SELECT 41, 0x312020202020434C2D312020202020202020202034302020202020202020202020202020323420202020202020202020202020202020323420202020202020203630
    UNION SELECT 43, 0x322020202020434C2D3220202020202020202020323020202020202020202020202020203234202020202020202020202020202020202020202020202020202036302020
    UNION SELECT 47, 0x332020202020434C2D332020202020202020202032302020202020202020202020202020323420202020202020202020202020202020202020202020202020203630
    UNION SELECT 53, 0x2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D
    ) AS z
       WHERE 0 < a
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    May 2008
    Location
    Los Angeles
    Posts
    348
    Pat, That was hilarious! I think you deserve the nickname "Doctor Evil"

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Hey, I aim to please! When the poster provides detailed specifications of what they want, show what they've done so far, and give me good data I can work wonders. The obfuscation was just a bit of icing on this particular cake.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    @ Pat,
    I'm surprised that you didn't just tell him that the answer was on page 392.

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    heh, heh, heh...

    I think you have too much time on your hands, Pat. It's nice you take the time to help the beginners out though
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  11. #11
    Join Date
    Nov 2009
    Posts
    17
    Quote Originally Posted by dav1mo View Post
    how does the packet play into this? Its not in your report and where did client come from? What have you written so far and what are you getting? You can't come on here with homework and expect us to do it for you. You ask questions that are specific and give examples and more people are willing to assist, not do your work
    Dave
    coz i don't know where to start ..

    should i use pivot technique ???


    Below is my query,

    Code:
    select transno,client,productid,sum(unit),sum(total)  from (
    SELECT     sales.Transno, sales.client, sales.packetId AS ProductId, sales.qty, Packet_Header.PacketName, Product_Detail.Unit, 
                          product.unitPrice, product.ProductName,  (product.unitPrice * Product_Detail.Unit) as Total
    FROM         Packet_Header INNER JOIN
                          Product_Detail ON Packet_Header.PacketId = Product_Detail.PacketId INNER JOIN
                          sales ON Packet_Header.PacketId = sales.packetId INNER JOIN
                          product ON Product_Detail.ProductID = product.ProductId ) as T
    Group by transno,client,productid
    this is the result :

    Code:
    1         	CL-01     	PA        	4	70
    2         	CL-02     	PA        	4	70
    3         	CL-01     	QX        	4	80

    i don't know .. is query correct or not ?

    my expectation the result like this

    Code:
    SALES REPORT - 1 (QTY)
    ===============================================================================================
    NO.   CLIENT       PROD-A           PROD-B        PROD-C       PROD-D     PROD -> xxx (mean can E,F,G,H , lookup from product table)
    ================================================================================================
    1     CL-1          4               2                2          4
    2     CL-2          2               2                           4     
    3     CL-3          2               2                           4
    -----------------------------------------------------------------------------------------------
    thank's
    Jigu

  12. #12
    Join Date
    Oct 2009
    Posts
    27
    Hi,

    I have a blog article to maybe assist you with your question:

    Blog - SQL Server Programmers - Using PIVOT and UNPIVOT

  13. #13
    Join Date
    Nov 2009
    Posts
    17
    Quote Originally Posted by sql-programmers View Post
    Hi,

    I have a blog article to maybe assist you with your question:

    Blog - SQL Server Programmers - Using PIVOT and UNPIVOT
    thank's
    Jigu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •