Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69

    Unanswered: query based on the top MTD sales

    hello,
    I need to write a query based on the top MTD sales in the series of each fabrics within series of Sales Group and Prod Group

    Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales

    Sales Gr: Active
    Prod gr: Adult, Girls, Plus, LG
    Fabric Gr: 1,2,3,4,5,6,7,8,...

    Sales Gr: Dance
    Prod gr: Adult, Girls, Plus, LG
    Fabric Gr: 1,2,3,4,5,6,7,8,...

    Sales Gr: Yoga
    Prod gr: Adult, Girls, Plus
    Fabric Gr: 1,2,3,4,5,6,7,8,...

    Thank you

  2. #2
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69

    query based on the top MTD sales

    hello,
    I need to write a query based on the top MTD sales in the series of each fabrics within series of Sales Group and Prod Group

    Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales
    Each Fabric group has to be orderd by the Top MTD.

    Sales Gr: Active
    Prod gr: Adult, Girls, Plus, LG
    Fabric Gr: a,b,c,d,e,f,g...
    StyleNum: 1,2,3,4,5,6....

    Sales Gr: Dance
    Prod gr: Adult, Girls, Plus, LG
    Fabric Gr: a,b,c,d,e,f,g...
    StyleNum: 1,2,3,4,5,6....

    Sales Gr: Yoga
    Prod gr: Adult, Girls, Plus
    Fabric Gr: a,b,c,d,e,f,g...
    StyleNum: 1,2,3,4,5,6....

  3. #3
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    you need to post some transaction data and the expected output of the query for us to understand where exactly the problem is.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    I'm afraid your description is a little breathless and difficult to follow. I suspect this is why you have not yet had a response. Please could you write in natural English what purpose the query is to serve?

    Ta
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    Hello,
    Attached is a data file in which I need a sort as I said below.

    Thank you
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    Query has to show data in alphabetical order for each group (Sales Group, Product Group). Then the Fabric Group has to be at the highest order first based on the MDT Sales and then the Fabric Group has to be grouped by description itself.
    Then style number and a color code.

    Thank you



    Sales Group Product Group FabricGroup STYLE NUM Retail Price WTD_Unit WTD_Sale WTD Avg Sales MTD_Unit MTD_Sale MTD_Avg_Sales YTD_Units YTD_Sales YTD_Avg_Sales
    ADULT ACTIVE SUPPLEX 1561 $30.00 25 $716.40 $28.66 101 $2,975.40 $29.46 626 $17,896.50 $28.59
    ADULT ACTIVE TRIGROUP 1677 $75.00 6 $344.34 $57.39 39 $2,282.02 $58.51 24 $1,287.60 $53.65
    ADULT ACTIVE TRIGROUP 1677 $75.00 6 $344.34 $57.39 39 $2,282.02 $58.51 100 $5,755.44 $57.55
    ADULT ACTIVE TRIGROUP 1677 $75.00 2 $117.58 $58.79 34 $2,009.67 $59.11 38 $2,125.20 $55.93
    ADULT ACTIVE TRIGROUP 1677 $75.00 2 $117.58 $58.79 34 $2,009.67 $59.11 126 $7,229.39 $57.38
    ADULT ACTIVE SUPPLEX 1562 $31.50 11 $343.35 $31.21 65 $1,997.73 $30.73 585 $17,091.59 $29.22
    ADULT ACTIVE SUPPLEX 1561 $30.00 8 $223.20 $27.90 35 $1,024.80 $29.28 154 $4,335.60 $28.15
    ADULT ACTIVE COTN LYCRA 8150 $21.00 8 $168.00 $21.00 49 $1,019.34 $20.80 404 $8,098.44 $20.05
    ADULT ACTIVE YOGA CVC 5720 $33.00 11 $327.69 $29.79 31 $935.55 $30.18 220 $6,721.77 $30.55
    ADULT ACTIVE SUPPLEX 5145 $39.00 6 $221.91 $36.99 23 $873.99 $38.00 212 $7,781.67 $36.71
    ADULT ACTIVE SUPPLEX 7360 $23.00 14 $318.78 $22.77 39 $868.48 $22.27 178 $3,822.37 $21.47
    ADULT ACTIVE SUPPLEX 1561 $30.00 6 $179.40 $29.90 29 $859.20 $29.63 35 $1,037.40 $29.64
    ADULT ACTIVE TRIGROUP 1847 $48.00 2 $95.04 $47.52 17 $797.76 $46.93 25 $1,168.32 $46.73
    ADULT ACTIVE YOGA CVC 8298 $42.00 3 $115.50 $38.50 19 $761.46 $40.08 177 $6,994.68 $39.52
    ADULT ACTIVE SUPPLEX 7111 $36.00 7 $232.92 $33.27 22 $752.76 $34.22 98 $3,303.36 $33.71
    ADULT ACTIVE TRIGROUP 1672 $48.00 0 $0.00 $0.00 18 $715.82 $39.77 74 $2,806.50 $37.93
    ADULT ACTIVE TRIGROUP 1671 $44.00 2 $69.98 $34.99 19 $660.61 $34.77 82 $2,680.23 $32.69
    ADULT ACTIVE STRETCH COTN 8202 $17.00 13 $209.95 $16.15 39 $635.97 $16.31 103 $1,633.70 $15.86
    ADULT ACTIVE COTN LYCRA 8379 $36.00 2 $71.28 $35.64 19 $635.76 $33.46 175 $5,721.48 $32.69
    ADULT ACTIVE YOGA CVC 5369 $36.00 5 $177.12 $35.42 18 $626.40 $34.80 310 $10,296.72 $33.22
    ADULT ACTIVE COTN LYCRA 8166 $30.00 0 $0.00 $0.00 22 $625.20 $28.42 99 $2,781.60 $28.10
    ADULT ACTIVE TRIGROUP 1845 $75.00 0 $0.00 $0.00 8 $597.00 $74.63 19 $1,401.00 $73.74
    ADULT ACTIVE SUPPLEX 1561 $30.00 3 $90.00 $30.00 20 $596.40 $29.82 28 $817.20 $29.19
    ADULT DANCE SUPPLEX 9963 $20.00 0 $0.00 $0.00 3 $35.49 $11.83 3 $35.49 $11.83
    ADULT DANCE SUPPLEX 9963 $20.00 0 $0.00 $0.00 3 $35.49 $11.83 10 $193.60 $19.36
    ADULT DANCE SUPPLEX 9963 $20.00 0 $0.00 $0.00 3 $35.49 $11.83 21 $293.65 $13.98
    ADULT DANCE NYLON 9090 $15.50 1 $8.81 $8.81 4 $35.42 $8.86 4 $35.42 $8.86
    ADULT DANCE NYLON 9090 $15.50 1 $8.81 $8.81 4 $35.42 $8.86 13 $188.79 $14.52
    ADULT DANCE SATIN TOUCH 2809 $36.00 0 $0.00 $0.00 1 $35.28 $35.28 6 $207.36 $34.56
    ADULT DANCE SATIN TOUCH 2812 $36.00 0 $0.00 $0.00 1 $35.28 $35.28 14 $483.48 $34.53
    ADULT DANCE NYLON 9090 $15.50 0 $0.00 $0.00 4 $35.24 $8.81 17 $244.90 $14.41
    ADULT DANCE NYLON 9090 $15.50 0 $0.00 $0.00 4 $35.24 $8.81 18 $152.02 $8.45
    ADULT DANCE BODY SCULPT 2813 $35.00 0 $0.00 $0.00 1 $35.00 $35.00 9 $300.30 $33.37
    ADULT DANCE BODY SCULPT COLLECTION (2006) 2813 $35.00 0 $0.00 $0.00 1 $35.00 $35.00 11 $359.45 $32.68

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Dupe post
    http://www.dbforums.com/showthread.php?t=1217893

    I still don't get it. There is a sticky at the top of this forum. It asks you to:
    Post table DDL
    Sample data in DML format
    Expected results

    It also explains how to do any of these if you don't know how. Please could you do this?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    FYI (not the OP) - this is also live in SQL Server:
    http://www.dbforums.com/showthread.php?t=1217908

    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    SalesGroup Text 255
    ProductGroup Text 255
    FabricGroup Text 255
    TYPEYR Text 5
    STYLE_NUM Text 50
    STYLE_DESC Text 255
    COLORCODE Text 3
    COLORD Text 255
    Retail Price Currency 8
    WTD_Units_05_25_2006 Double 8
    WTD_Sales_05_25_2006 Currency 8
    WTD_Avg_Sales_05_25_2006 Currency 8
    MTD_Units_May 2006 Double 8
    MTD_Sales_May 2006 Currency 8
    MTD_Avg_Sales_May 2006 Currency 8
    YTD_Units_2006 Double 8
    YTD_Sales_2006 Currency 8
    YTD_Avg_Sales_2006 Currency 8

    SalesGroup (In Alphabetical Order - Adult, Girls, Plus, Mens, Irrs),
    for each Sales gr ther are the
    ProductGroup (Alphabetical Order - Active, Dace, Legwr, Prvt),
    For each Sales Gr and a Prod Group there are the
    FabricGroups (has to apear as the highest total first for each FabricGroup based on the MTD Sales (Suplex, Cotton, Cotton LCRY)
    and then it has to group the FabricGroup together. (If I have 10 entries for the Supplex and it is at the highest total, then the highest total has to be first and the rest apper after)
    StyleID (1,2,3,4,5,6,7..),
    Color (01,02,03,04,05),
    RetailAmount,
    WeeklySales,
    MonthlySales
    YTDSales

    Thank you so much.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the thing to do when there's a cross post is not to put pointers from one to the other, but to merge the threads, as i have done here

    that the merged threads may be more confusing than separate threads is not our problem but the original poster's
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    the thing to do when there's a cross post is not to put pointers from one to the other, but to merge the threads, as i have done here
    Lol - ya The pointers were more to flag up the posts to the Orange users than to create some complex web of a thread.

    Although I do like to alternate my responses from one thread to another when I do get involved. That's just mischief though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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