July 2, 2017 at 6:11 am
Hi,
I got this query, that gives me all the products(optionid) that are ready to be shipped:
SELECT T_Order_Detail.OrderID,
T_Order_Detail.OptionID,
T_Order_Detail.Quantity
FROM T_Product_Option
INNER JOIN T_Order_Detail
ON T_Product_Option.id = T_Order_Detail.OptionID
LEFT OUTER JOIN T_Order_Main
ON T_Order_Detail.OrderID = T_Order_Main.ORDERID
WHERE (T_Order_Main.Orderstatus = 7)
AND (T_Product_Option.PackID = 3)
AND (T_Order_Detail.Cost > 0)
ORDER BY T_Order_Detail.OrderID,
OptionID;
it produces this result:
CREATE TABLE #temptable ( [OrderID] int, [OptionID] int, [Quantity] int )
INSERT INTO #temptable
VALUES
( 1689310, 4214, 1 ),
( 1698321, 5497, 1 ),
( 1698321, 5873, 2 ),
( 1703147, 3933, 2 ),
( 1708411, 5497, 1 ),
( 1708500, 3415, 1 ),
( 1709102, 6075, 1 ),
( 1710479, 2903, 1 ),
( 1710642, 3925, 1 ),
( 1710885, 4214, 1 ),
( 1710885, 4215, 1 ),
( 1711090, 2905, 1 ),
( 1711090, 4215, 1 ),
( 1711766, 3383, 1 ),
( 1711859, 3933, 1 ),
( 1712026, 418, 1 ),
( 1712626, 3913, 1 ),
( 1712753, 3926, 1 ),
( 1712924, 4215, 1 ),
( 1713294, 4095, 1 ),
( 1713294, 4213, 1 ),
( 1713294, 6075, 1 ),
( 1713442, 3414, 1 ),
( 1713491, 3933, 2 ),
( 1713491, 4248, 1 ),
( 1713504, 3925, 1 ),
( 1713504, 3933, 5 ),
( 1713551, 2910, 2 ),
( 1713551, 3414, 1 ),
( 1713567, 5873, 1 ),
( 1713579, 3385, 1 ),
( 1713579, 3387, 2 ),
( 1713615, 2905, 2 ),
( 1713615, 5497, 2 ),
( 1713621, 3933, 1 ),
( 1713622, 6075, 2 ),
( 1713624, 5497, 1 ),
( 1713627, 3186, 1 ),
( 1713640, 6075, 2 ),
( 1713665, 3933, 2 ),
( 1713670, 3383, 1 ),
( 1713745, 418, 1 ),
( 1713765, 2901, 2 ),
( 1713765, 3183, 1 ),
( 1713765, 4247, 2 ),
( 1713773, 6155, 2 ),
( 1713811, 2377, 1 ),
( 1713811, 3382, 2 ),
( 1713811, 3913, 1 ),
( 1713821, 4248, 2 ),
( 1713853, 3913, 1 ),
( 1713866, 3383, 1 ),
( 1713870, 2905, 1 ),
( 1713870, 4214, 1 ),
( 1713889, 3913, 1 ),
( 1713890, 3933, 1 ),
( 1713894, 3933, 1 ),
( 1713896, 3933, 1 ),
( 1713905, 2901, 1 ),
( 1713905, 4214, 1 ),
( 1713910, 3933, 1 ),
( 1713926, 3383, 1 ),
( 1713927, 2901, 1 ),
( 1713938, 2901, 1 ),
( 1713938, 4248, 1 ),
( 1713941, 3383, 1 ),
( 1713945, 3913, 1 ),
( 1713946, 4095, 1 ),
( 1713949, 3933, 1 ),
( 1713955, 6155, 1 ),
( 1713965, 4248, 1 ),
( 1713978, 3913, 1 ),
( 1713978, 4011, 1 ),
( 1713980, 2901, 2 ),
( 1713986, 2901, 1 ),
( 1713986, 3933, 1 ),
( 1713988, 3383, 1 ),
( 1714012, 4247, 1 ),
( 1714012, 4248, 1 ),
( 1714023, 3383, 1 ),
( 1714023, 5542, 1 ),
( 1714026, 5497, 1 ),
( 1714032, 2901, 1 ),
( 1714034, 3383, 1 ),
( 1714044, 2910, 1 ),
( 1714054, 3913, 1 ),
( 1714058, 5497, 1 ),
( 1714064, 5873, 1 ),
( 1714069, 5873, 1 ),
( 1714070, 418, 1 ),
( 1714073, 2901, 1 ),
( 1714075, 6075, 1 ),
( 1714082, 5497, 1 ),
( 1714086, 4248, 1 ),
( 1714086, 5873, 1 ),
( 1714087, 3933, 6 ),
( 1714099, 2910, 1 ),
( 1714102, 4247, 1 ),
( 1714109, 3933, 1 ),
( 1714118, 5497, 1 ),
( 1714130, 3383, 2 ),
( 1714132, 6076, 1 ),
( 1714146, 3913, 1 ),
( 1714148, 4180, 1 ),
( 1714158, 4247, 2 ),
( 1714180, 3913, 1 ),
( 1714188, 3933, 1 ),
( 1714195, 1475, 2 ),
( 1714197, 2901, 1 ),
( 1714197, 4011, 1 ),
( 1714197, 4247, 1 ),
( 1714202, 2901, 1 ),
( 1714217, 3933, 1 ),
( 1714223, 5497, 1 ),
( 1714226, 3933, 3 ),
( 1714230, 3933, 1 ),
( 1714244, 3925, 1 ),
( 1714249, 3913, 1 ),
( 1714257, 4248, 2 ),
( 1714262, 4248, 2 ),
( 1714263, 2901, 1 ),
( 1714263, 4247, 1 ),
( 1714270, 3417, 1 ),
( 1714291, 3933, 2 ),
( 1714292, 3933, 2 ),
( 1714305, 2903, 1 ),
( 1714305, 3913, 1 ),
( 1714309, 3933, 1 ),
( 1714314, 4247, 1 ),
( 1714322, 6075, 1 ),
( 1714332, 2901, 1 ),
( 1714339, 3387, 1 ),
( 1714346, 3933, 1 ),
( 1714347, 3913, 1 ),
( 1714365, 3933, 1 ),
( 1714369, 3913, 1 ),
( 1714369, 5497, 1 ),
( 1714374, 5873, 1 ),
( 1714380, 3382, 1 ),
( 1714399, 3925, 1 ),
( 1714400, 4248, 1 ),
( 1714405, 5497, 1 ),
( 1714412, 5578, 2 ),
( 1714448, 5873, 1 ),
( 1714454, 4247, 1 ),
( 1714460, 5497, 1 ),
( 1714461, 2903, 1 ),
( 1714461, 6155, 1 ),
( 1714470, 3933, 1 ),
( 1714485, 5497, 2 ),
( 1714499, 4248, 1 ),
( 1714504, 2901, 1 ),
( 1714504, 3933, 1 ),
( 1714510, 3933, 2 ),
( 1714521, 3933, 1 ),
( 1714522, 4248, 1 ),
( 1714535, 5497, 2 ),
( 1714547, 1475, 1 ),
( 1714560, 6075, 1 ),
( 1714564, 3913, 1 ),
( 1714564, 4247, 1 ),
( 1714595, 2905, 1 ),
( 1714599, 6155, 1 ),
( 1714609, 3925, 1 ),
( 1714613, 5542, 2 ),
( 1714617, 4215, 1 ),
( 1714624, 3926, 1 ),
( 1714634, 2910, 1 ),
( 1714634, 3933, 1 ),
( 1714637, 2377, 1 ),
( 1714637, 2901, 1 ),
( 1714647, 2903, 2 ),
( 1714660, 6155, 1 ),
( 1714661, 3933, 1 ),
( 1714664, 3913, 1 ),
( 1714664, 4247, 1 ),
( 1714699, 3913, 1 ),
( 1714703, 3933, 2 ),
( 1714712, 3913, 1 ),
( 1714731, 4248, 1 ),
( 1714735, 5497, 1 ),
( 1714751, 4247, 1 ),
( 1714758, 3926, 1 ),
( 1714771, 3925, 1 ),
( 1714776, 4095, 1 ),
( 1714776, 4214, 1 ),
( 1714776, 5497, 1 ),
( 1714776, 5873, 1 ),
( 1714792, 2901, 1 ),
( 1714797, 3933, 1 ),
( 1714802, 3925, 1 ),
( 1714807, 2901, 1 ),
( 1714842, 927, 1 ),
( 1714843, 5873, 1 ),
( 1714848, 3933, 1 ),
( 1714848, 4247, 1 ),
( 1714849, 3933, 1 ),
( 1714849, 4011, 1 ),
( 1714849, 4212, 1 ),
( 1714855, 3913, 2 ),
( 1714861, 3913, 1 ),
( 1714872, 3913, 1 ),
( 1714877, 3933, 1 ),
( 1714892, 3913, 2 ),
( 1714894, 3913, 1 ),
( 1714914, 5497, 1 ),
( 1714931, 3913, 1 ),
( 1714934, 3933, 1 ),
( 1714940, 3933, 1 ),
( 1714954, 4247, 1 ),
( 1714958, 2901, 1 ),
( 1714958, 4247, 1 ),
( 1714967, 2903, 1 ),
( 1714974, 4247, 2 ),
( 1714983, 5497, 1 ),
( 1714986, 4248, 1 ),
( 1714991, 3913, 1 ),
( 1714999, 3925, 1 ),
( 1715003, 3180, 1 ),
( 1715004, 2901, 2 ),
( 1715008, 4247, 1 ),
( 1715009, 4248, 1 ),
( 1715036, 3930, 1 ),
( 1715037, 3933, 1 ),
( 1715039, 5497, 1 ),
( 1715044, 3926, 1 ),
( 1715053, 3386, 1 ),
( 1715054, 4247, 1 ),
( 1715064, 2905, 1 ),
( 1715064, 5497, 1 ),
( 1715068, 3387, 2 ),
( 1715070, 2905, 1 ),
( 1715070, 6155, 2 ),
( 1715122, 6075, 1 ),
( 1715133, 2903, 1 ),
( 1715133, 3925, 1 ),
( 1715137, 3933, 1 ),
( 1715145, 3387, 1 ),
( 1715160, 3933, 1 ),
( 1715165, 926, 1 ),
( 1715167, 3933, 1 ),
( 1715170, 5542, 1 ),
( 1715170, 5578, 2 ),
( 1715177, 3913, 1 ),
( 1715177, 4247, 1 )
DROP TABLE #temptable
I want to find the amount of orders that have the exactly the same contents.
Breaking my head for a while.
Result i'm looking for is the OptionID,Quantity have to be the same:
Something that looks like this
ex:
Amount_of_Similiar_Orders, OptionID,Quantity
10 , 4214 , 1
7 , 3383 , 2
5 , 418 , 1
3 , 2901 , 1
3 , 2903 , 1
the first 3 rows are orders with only 1 item.
Last 2 rows represents 3 orders containing 2 items
there must be a easy simple way to do this, with a Pivot or something
any help is very much apriciated
July 2, 2017 at 9:15 am
not exactly sure of what you are asking.....but to start the discussion why cant you use COUNT & GROUP BY
eg
SELECT COUNT(*) , OptionID, Quantity
FROM #temptable
GROUP BY OptionID, Quantity
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 2, 2017 at 12:37 pm
Nope,
I want to know how many orders (can more then 1 row) have the same products.
So the pack department can do all the same orders in 1 go,
July 2, 2017 at 1:03 pm
mikeaspnet - Sunday, July 2, 2017 12:37 PMNope,I want to know how many orders (can more then 1 row) have the same products.
So the pack department can do all the same orders in 1 go,
in your sample data you have 5 rows for OptionId 4214......but you seem to want to return
Amount_of_Similiar_Orders, OptionID,Quantity
10 , 4214 , 1
confused?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 2, 2017 at 2:30 pm
mikeaspnet - Sunday, July 2, 2017 12:37 PMI want to know how many orders (can more then 1 row) have the same products.
So the pack department can do all the same orders in 1 go,
You want to know how many orders have the same product. I think what J. Livingston posted will get you what you describe, unless you want to also know the number of orders. Is this what you're looking for?
SELECT OptionID, Orders = COUNT(OrderID), TotalQuantity = SUM(Quantity)
FROM #temptable
GROUP BY OptionID
HAVING COUNT(OrderID) > 1
ORDER BY COUNT(OrderID);
If this is what you're asking and I'm reading it right, the ideal query would ORDER BY location in the warehouse so the packing department could pull 3 pieces of 1 item and 5 of another in a single trip through the warehouse.
July 27, 2017 at 4:57 pm
The previous answer gives me the total options, but not the GROUP of options contained in each orderCREATE TABLE #temptable ( [OrderID] int, [OptionID] int, [Quantity] int )
INSERT INTO #temptable
VALUES
( 1689310, 4214, 1 ),
( 1698321, 5497, 1 ),
( 1698321, 5873, 2 ),
( 1703147, 3933, 2 ),
( 1708411, 5497, 1 ),
( 1708500, 3415, 1 ),
( 1709102, 6075, 1 ),
( 1710479, 2903, 1 ),
( 1710642, 3925, 1 ),
( 1710885, 4214, 1 ),
( 1710885, 4215, 1 ),
( 1711090, 2905, 1 ),
( 1711090, 4215, 1 ),
( 1711766, 3383, 1 ),
( 1711859, 3933, 1 ),
( 1712026, 418, 1 ),
( 1712626, 3913, 1 ),
( 1712753, 3926, 1 ),
( 1712924, 4215, 1 ),
( 1713294, 4095, 1 ),
( 1713294, 4213, 1 ),
( 1713294, 6075, 1 ),
( 1713442, 3414, 1 ),
( 1713491, 3933, 2 ),
( 1713491, 4248, 1 ),
( 1713504, 3925, 1 ),
( 1713504, 3933, 5 ),
( 1713551, 2910, 2 ),
( 1713551, 3414, 1 ),
( 1713567, 5873, 1 ),
( 1713579, 3385, 1 ),
( 1713579, 3387, 2 ),
( 1713615, 2905, 2 ),
( 1713615, 5497, 2 ),
( 1713621, 3933, 1 ),
( 1713622, 6075, 2 ),
( 1713624, 5497, 1 ),
( 1713627, 3186, 1 ),
( 1713640, 6075, 2 ),
( 1713665, 3933, 2 ),
( 1713670, 3383, 1 ),
( 1713745, 418, 1 ),
( 1713765, 2901, 2 ),
( 1713765, 3183, 1 ),
( 1713765, 4247, 2 ),
( 1713773, 6155, 2 ),
( 1713811, 2377, 1 ),
( 1713811, 3382, 2 ),
( 1713811, 3913, 1 ),
( 1713821, 4248, 2 ),
( 1713853, 3913, 1 ),
( 1713866, 3383, 1 ),
( 1713870, 2905, 1 ),
( 1713870, 4214, 1 ),
( 1713889, 3913, 1 ),
( 1713890, 3933, 1 ),
( 1713894, 3933, 1 ),
( 1713896, 3933, 1 ),
( 1713905, 2901, 1 ),
( 1713905, 4214, 1 ),
( 1713910, 3933, 1 ),
( 1713926, 3383, 1 ),
( 1713927, 2901, 1 ),
( 1713938, 2901, 1 ),
( 1713938, 4248, 1 ),
( 1713941, 3383, 1 ),
( 1713945, 3913, 1 ),
( 1713946, 4095, 1 ),
( 1713949, 3933, 1 ),
( 1713955, 6155, 1 ),
( 1713965, 4248, 1 ),
( 1713978, 3913, 1 ),
( 1713978, 4011, 1 ),
( 1713980, 2901, 2 ),
( 1713986, 2901, 1 ),
( 1713986, 3933, 1 ),
( 1713988, 3383, 1 ),
( 1714012, 4247, 1 ),
( 1714012, 4248, 1 ),
( 1714023, 3383, 1 ),
( 1714023, 5542, 1 ),
( 1714026, 5497, 1 ),
( 1714032, 2901, 1 ),
( 1714034, 3383, 1 ),
( 1714044, 2910, 1 ),
( 1714054, 3913, 1 ),
( 1714058, 5497, 1 ),
( 1714064, 5873, 1 ),
( 1714069, 5873, 1 ),
( 1714070, 418, 1 ),
( 1714073, 2901, 1 ),
( 1714075, 6075, 1 ),
( 1714082, 5497, 1 ),
( 1714086, 4248, 1 ),
( 1714086, 5873, 1 ),
( 1714087, 3933, 6 ),
( 1714099, 2910, 1 ),
( 1714102, 4247, 1 ),
( 1714109, 3933, 1 ),
( 1714118, 5497, 1 ),
( 1714130, 3383, 2 ),
( 1714132, 6076, 1 ),
( 1714146, 3913, 1 ),
( 1714148, 4180, 1 ),
( 1714158, 4247, 2 ),
( 1714180, 3913, 1 ),
( 1714188, 3933, 1 ),
( 1714195, 1475, 2 ),
( 1714197, 2901, 1 ),
( 1714197, 4011, 1 ),
( 1714197, 4247, 1 ),
( 1714202, 2901, 1 ),
( 1714217, 3933, 1 ),
( 1714223, 5497, 1 ),
( 1714226, 3933, 3 ),
( 1714230, 3933, 1 ),
( 1714244, 3925, 1 ),
( 1714249, 3913, 1 ),
( 1714257, 4248, 2 ),
( 1714262, 4248, 2 ),
( 1714263, 2901, 1 ),
( 1714263, 4247, 1 ),
( 1714270, 3417, 1 ),
( 1714291, 3933, 2 ),
( 1714292, 3933, 2 ),
( 1714305, 2903, 1 ),
( 1714305, 3913, 1 ),
( 1714309, 3933, 1 ),
( 1714314, 4247, 1 ),
( 1714322, 6075, 1 ),
( 1714332, 2901, 1 ),
( 1714339, 3387, 1 ),
( 1714346, 3933, 1 ),
( 1714347, 3913, 1 ),
( 1714365, 3933, 1 ),
( 1714369, 3913, 1 ),
( 1714369, 5497, 1 ),
( 1714374, 5873, 1 ),
( 1714380, 3382, 1 ),
( 1714399, 3925, 1 ),
( 1714400, 4248, 1 ),
( 1714405, 5497, 1 ),
( 1714412, 5578, 2 ),
( 1714448, 5873, 1 ),
( 1714454, 4247, 1 ),
( 1714460, 5497, 1 ),
( 1714461, 2903, 1 ),
( 1714461, 6155, 1 ),
( 1714470, 3933, 1 ),
( 1714485, 5497, 2 ),
( 1714499, 4248, 1 ),
( 1714504, 2901, 1 ),
( 1714504, 3933, 1 ),
( 1714510, 3933, 2 ),
( 1714521, 3933, 1 ),
( 1714522, 4248, 1 ),
( 1714535, 5497, 2 ),
( 1714547, 1475, 1 ),
( 1714560, 6075, 1 ),
( 1714564, 3913, 1 ),
( 1714564, 4247, 1 ),
( 1714595, 2905, 1 ),
( 1714599, 6155, 1 ),
( 1714609, 3925, 1 ),
( 1714613, 5542, 2 ),
( 1714617, 4215, 1 ),
( 1714624, 3926, 1 ),
( 1714634, 2910, 1 ),
( 1714634, 3933, 1 ),
( 1714637, 2377, 1 ),
( 1714637, 2901, 1 ),
( 1714647, 2903, 2 ),
( 1714660, 6155, 1 ),
( 1714661, 3933, 1 ),
( 1714664, 3913, 1 ),
( 1714664, 4247, 1 ),
( 1714699, 3913, 1 ),
( 1714703, 3933, 2 ),
( 1714712, 3913, 1 ),
( 1714731, 4248, 1 ),
( 1714735, 5497, 1 ),
( 1714751, 4247, 1 ),
( 1714758, 3926, 1 ),
( 1714771, 3925, 1 ),
( 1714776, 4095, 1 ),
( 1714776, 4214, 1 ),
( 1714776, 5497, 1 ),
( 1714776, 5873, 1 ),
( 1714792, 2901, 1 ),
( 1714797, 3933, 1 ),
( 1714802, 3925, 1 ),
( 1714807, 2901, 1 ),
( 1714842, 927, 1 ),
( 1714843, 5873, 1 ),
( 1714848, 3933, 1 ),
( 1714848, 4247, 1 ),
( 1714849, 3933, 1 ),
( 1714849, 4011, 1 ),
( 1714849, 4212, 1 ),
( 1714855, 3913, 2 ),
( 1714861, 3913, 1 ),
( 1714872, 3913, 1 ),
( 1714877, 3933, 1 ),
( 1714892, 3913, 2 ),
( 1714894, 3913, 1 ),
( 1714914, 5497, 1 ),
( 1714931, 3913, 1 ),
( 1714934, 3933, 1 ),
( 1714940, 3933, 1 ),
( 1714954, 4247, 1 ),
( 1714958, 2901, 1 ),
( 1714958, 4247, 1 ),
( 1714967, 2903, 1 ),
( 1714974, 4247, 2 ),
( 1714983, 5497, 1 ),
( 1714986, 4248, 1 ),
( 1714991, 3913, 1 ),
( 1714999, 3925, 1 ),
( 1715003, 3180, 1 ),
( 1715004, 2901, 2 ),
( 1715008, 4247, 1 ),
( 1715009, 4248, 1 ),
( 1715036, 3930, 1 ),
( 1715037, 3933, 1 ),
( 1715039, 5497, 1 ),
( 1715044, 3926, 1 ),
( 1715053, 3386, 1 ),
( 1715054, 4247, 1 ),
( 1715064, 2905, 1 ),
( 1715064, 5497, 1 ),
( 1715068, 3387, 2 ),
( 1715070, 2905, 1 ),
( 1715070, 6155, 2 ),
( 1715122, 6075, 1 ),
( 1715133, 2903, 1 ),
( 1715133, 3925, 1 ),
( 1715137, 3933, 1 ),
( 1715145, 3387, 1 ),
( 1715160, 3933, 1 ),
( 1715165, 926, 1 ),
( 1715167, 3933, 1 ),
( 1715170, 5542, 1 ),
( 1715170, 5578, 2 ),
( 1715177, 3913, 1 ),
( 1715177, 4247, 1 )
DROP TABLE #temptable
it pretty simple what i want, but its complicated to explain
let replace it with words
ex:
( Jill, lemons, 1kg ),
( James, apples, 1kg ),
( James, oranges, 2kg ),
( Mary, apples, 1kg ),
( Mary, oranges, 1kg )
( John, apples, 1kg ),
( John, oranges, 1kg )
Each persons order:
jill: lemons 1kg
James: apples 1kg and oranges 2kg
Mary: apples 1kg and oranges 1kg
These are are 3 diffrent orders, James and mary have the same items but in diffrent quantity
im looking for the exact same orders items and quantities
Like
Mary and John
Result ex:
orders count, items + quantity
1 , Lemons 1kg
1 , Apples 1kg and oranges 2kg
2 , Apples 1kg and oranges 1kg
.....
there can more then 1 or 2 items in order
the other result im looking for is
ex:
orders count, Persons, items + quantity
1 ,jill, Lemons 1kg
1 ,james, Apples 1kg and oranges 2kg
2 ,mary|John, Apples 1kg and oranges 1kg
I tried my best to explain it 😉
There must some functions in sql 2014 that can achief this in a simple way
July 28, 2017 at 8:00 am
Try this:CREATE TABLE dbo.Orders (
OrderID int,
OptionID int,
Quantity int
);
INSERT INTO dbo.Orders (OrderID, OptionID, Quantity)
VALUES ( 1689310, 4214, 1 ),
( 1698321, 5497, 1 ),
( 1698321, 5873, 2 ),
( 1703147, 3933, 2 ),
( 1708411, 5497, 1 ),
( 1708500, 3415, 1 ),
( 1709102, 6075, 1 ),
( 1710479, 2903, 1 ),
( 1710642, 3925, 1 ),
( 1710885, 4214, 1 ),
( 1710885, 4215, 1 ),
( 1711090, 2905, 1 ),
( 1711090, 4215, 1 ),
( 1711766, 3383, 1 ),
( 1711859, 3933, 1 ),
( 1712026, 418, 1 ),
( 1712626, 3913, 1 ),
( 1712753, 3926, 1 ),
( 1712924, 4215, 1 ),
( 1713294, 4095, 1 ),
( 1713294, 4213, 1 ),
( 1713294, 6075, 1 ),
( 1713442, 3414, 1 ),
( 1713491, 3933, 2 ),
( 1713491, 4248, 1 ),
( 1713504, 3925, 1 ),
( 1713504, 3933, 5 ),
( 1713551, 2910, 2 ),
( 1713551, 3414, 1 ),
( 1713567, 5873, 1 ),
( 1713579, 3385, 1 ),
( 1713579, 3387, 2 ),
( 1713615, 2905, 2 ),
( 1713615, 5497, 2 ),
( 1713621, 3933, 1 ),
( 1713622, 6075, 2 ),
( 1713624, 5497, 1 ),
( 1713627, 3186, 1 ),
( 1713640, 6075, 2 ),
( 1713665, 3933, 2 ),
( 1713670, 3383, 1 ),
( 1713745, 418, 1 ),
( 1713765, 2901, 2 ),
( 1713765, 3183, 1 ),
( 1713765, 4247, 2 ),
( 1713773, 6155, 2 ),
( 1713811, 2377, 1 ),
( 1713811, 3382, 2 ),
( 1713811, 3913, 1 ),
( 1713821, 4248, 2 ),
( 1713853, 3913, 1 ),
( 1713866, 3383, 1 ),
( 1713870, 2905, 1 ),
( 1713870, 4214, 1 ),
( 1713889, 3913, 1 ),
( 1713890, 3933, 1 ),
( 1713894, 3933, 1 ),
( 1713896, 3933, 1 ),
( 1713905, 2901, 1 ),
( 1713905, 4214, 1 ),
( 1713910, 3933, 1 ),
( 1713926, 3383, 1 ),
( 1713927, 2901, 1 ),
( 1713938, 2901, 1 ),
( 1713938, 4248, 1 ),
( 1713941, 3383, 1 ),
( 1713945, 3913, 1 ),
( 1713946, 4095, 1 ),
( 1713949, 3933, 1 ),
( 1713955, 6155, 1 ),
( 1713965, 4248, 1 ),
( 1713978, 3913, 1 ),
( 1713978, 4011, 1 ),
( 1713980, 2901, 2 ),
( 1713986, 2901, 1 ),
( 1713986, 3933, 1 ),
( 1713988, 3383, 1 ),
( 1714012, 4247, 1 ),
( 1714012, 4248, 1 ),
( 1714023, 3383, 1 ),
( 1714023, 5542, 1 ),
( 1714026, 5497, 1 ),
( 1714032, 2901, 1 ),
( 1714034, 3383, 1 ),
( 1714044, 2910, 1 ),
( 1714054, 3913, 1 ),
( 1714058, 5497, 1 ),
( 1714064, 5873, 1 ),
( 1714069, 5873, 1 ),
( 1714070, 418, 1 ),
( 1714073, 2901, 1 ),
( 1714075, 6075, 1 ),
( 1714082, 5497, 1 ),
( 1714086, 4248, 1 ),
( 1714086, 5873, 1 ),
( 1714087, 3933, 6 ),
( 1714099, 2910, 1 ),
( 1714102, 4247, 1 ),
( 1714109, 3933, 1 ),
( 1714118, 5497, 1 ),
( 1714130, 3383, 2 ),
( 1714132, 6076, 1 ),
( 1714146, 3913, 1 ),
( 1714148, 4180, 1 ),
( 1714158, 4247, 2 ),
( 1714180, 3913, 1 ),
( 1714188, 3933, 1 ),
( 1714195, 1475, 2 ),
( 1714197, 2901, 1 ),
( 1714197, 4011, 1 ),
( 1714197, 4247, 1 ),
( 1714202, 2901, 1 ),
( 1714217, 3933, 1 ),
( 1714223, 5497, 1 ),
( 1714226, 3933, 3 ),
( 1714230, 3933, 1 ),
( 1714244, 3925, 1 ),
( 1714249, 3913, 1 ),
( 1714257, 4248, 2 ),
( 1714262, 4248, 2 ),
( 1714263, 2901, 1 ),
( 1714263, 4247, 1 ),
( 1714270, 3417, 1 ),
( 1714291, 3933, 2 ),
( 1714292, 3933, 2 ),
( 1714305, 2903, 1 ),
( 1714305, 3913, 1 ),
( 1714309, 3933, 1 ),
( 1714314, 4247, 1 ),
( 1714322, 6075, 1 ),
( 1714332, 2901, 1 ),
( 1714339, 3387, 1 ),
( 1714346, 3933, 1 ),
( 1714347, 3913, 1 ),
( 1714365, 3933, 1 ),
( 1714369, 3913, 1 ),
( 1714369, 5497, 1 ),
( 1714374, 5873, 1 ),
( 1714380, 3382, 1 ),
( 1714399, 3925, 1 ),
( 1714400, 4248, 1 ),
( 1714405, 5497, 1 ),
( 1714412, 5578, 2 ),
( 1714448, 5873, 1 ),
( 1714454, 4247, 1 ),
( 1714460, 5497, 1 ),
( 1714461, 2903, 1 ),
( 1714461, 6155, 1 ),
( 1714470, 3933, 1 ),
( 1714485, 5497, 2 ),
( 1714499, 4248, 1 ),
( 1714504, 2901, 1 ),
( 1714504, 3933, 1 ),
( 1714510, 3933, 2 ),
( 1714521, 3933, 1 ),
( 1714522, 4248, 1 ),
( 1714535, 5497, 2 ),
( 1714547, 1475, 1 ),
( 1714560, 6075, 1 ),
( 1714564, 3913, 1 ),
( 1714564, 4247, 1 ),
( 1714595, 2905, 1 ),
( 1714599, 6155, 1 ),
( 1714609, 3925, 1 ),
( 1714613, 5542, 2 ),
( 1714617, 4215, 1 ),
( 1714624, 3926, 1 ),
( 1714634, 2910, 1 ),
( 1714634, 3933, 1 ),
( 1714637, 2377, 1 ),
( 1714637, 2901, 1 ),
( 1714647, 2903, 2 ),
( 1714660, 6155, 1 ),
( 1714661, 3933, 1 ),
( 1714664, 3913, 1 ),
( 1714664, 4247, 1 ),
( 1714699, 3913, 1 ),
( 1714703, 3933, 2 ),
( 1714712, 3913, 1 ),
( 1714731, 4248, 1 ),
( 1714735, 5497, 1 ),
( 1714751, 4247, 1 ),
( 1714758, 3926, 1 ),
( 1714771, 3925, 1 ),
( 1714776, 4095, 1 ),
( 1714776, 4214, 1 ),
( 1714776, 5497, 1 ),
( 1714776, 5873, 1 ),
( 1714792, 2901, 1 ),
( 1714797, 3933, 1 ),
( 1714802, 3925, 1 ),
( 1714807, 2901, 1 ),
( 1714842, 927, 1 ),
( 1714843, 5873, 1 ),
( 1714848, 3933, 1 ),
( 1714848, 4247, 1 ),
( 1714849, 3933, 1 ),
( 1714849, 4011, 1 ),
( 1714849, 4212, 1 ),
( 1714855, 3913, 2 ),
( 1714861, 3913, 1 ),
( 1714872, 3913, 1 ),
( 1714877, 3933, 1 ),
( 1714892, 3913, 2 ),
( 1714894, 3913, 1 ),
( 1714914, 5497, 1 ),
( 1714931, 3913, 1 ),
( 1714934, 3933, 1 ),
( 1714940, 3933, 1 ),
( 1714954, 4247, 1 ),
( 1714958, 2901, 1 ),
( 1714958, 4247, 1 ),
( 1714967, 2903, 1 ),
( 1714974, 4247, 2 ),
( 1714983, 5497, 1 ),
( 1714986, 4248, 1 ),
( 1714991, 3913, 1 ),
( 1714999, 3925, 1 ),
( 1715003, 3180, 1 ),
( 1715004, 2901, 2 ),
( 1715008, 4247, 1 ),
( 1715009, 4248, 1 ),
( 1715036, 3930, 1 ),
( 1715037, 3933, 1 ),
( 1715039, 5497, 1 ),
( 1715044, 3926, 1 ),
( 1715053, 3386, 1 ),
( 1715054, 4247, 1 ),
( 1715064, 2905, 1 ),
( 1715064, 5497, 1 ),
( 1715068, 3387, 2 ),
( 1715070, 2905, 1 ),
( 1715070, 6155, 2 ),
( 1715122, 6075, 1 ),
( 1715133, 2903, 1 ),
( 1715133, 3925, 1 ),
( 1715137, 3933, 1 ),
( 1715145, 3387, 1 ),
( 1715160, 3933, 1 ),
( 1715165, 926, 1 ),
( 1715167, 3933, 1 ),
( 1715170, 5542, 1 ),
( 1715170, 5578, 2 ),
( 1715177, 3913, 1 ),
( 1715177, 4247, 1 );
SELECT O.OptionID, O.Quantity, COUNT(DISTINCT O.OrderID) AS OrderCount,
MAX(L.OrderList) AS OrderList
FROM dbo.Orders AS O
CROSS APPLY (SELECT STUFF(
(
SELECT ',' + CONVERT(varchar(10), O2.OrderID)
FROM dbo.Orders AS O2
WHERE O2.OptionID = O.OptionID
AND O2.Quantity = O.Quantity
FOR XML PATH('')
), 1, 1, '') AS OrderList) AS L
GROUP BY O.OptionID, O.Quantity
ORDER BY O.OptionID, O.Quantity;
DROP TABLE dbo.Orders;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2017 at 9:54 am
I think this might be closer to what the OP is after:WITH Summary AS (
SELECT O.OrderID,
STUFF((SELECT ', ' + CAST(sq.OptionID AS varchar(6)) + ' x ' + CAST(sq.Quantity as varchar(3))
FROM Orders sq
WHERE sq.OrderID = O.OrderID
ORDER BY sq.OptionID
FOR XML PATH('')),1,2,'') AS Contents
FROM Orders O
GROUP BY O.OrderID),
Dups AS (
SELECT S.OrderID, S.Contents,
COUNT(*) OVER (PARTITION BY S.Contents) AS DuplicateOrders
FROM Summary S)
SELECT *
FROM Dups D
WHERE DuplicateOrders > 1
ORDER BY Contents, OrderID;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 28, 2017 at 11:55 am
I just edited my previous post as there was a dumb error in it that mucks up the values being shown. I had the wrong table alias in the subquery for the STUFF function. For those of you that looked at it previously, please re-look at it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 30, 2017 at 3:54 pm
That Did the Trick, thanks a lot guys 🙂
August 2, 2017 at 6:11 pm
I trying make a small change. but without success
the result is this now:
OrderID Contents DuplicateOrders
1735733 Apples 1kg 3
1735806 Apples 1kg 3
1735774 Apples 1kg 3
1735756 Oranges 1kg 2
1735580 Oranges 1kg 2
1735676 Apples 1kg, Oranges 1kg 2
1735750 Apples 1kg, Oranges 1kg 2
I want it to look like this
OrderID Contents DuplicateOrders
1735733,1735806,1735774 Apples 1kg 3
1735756 ,1735580 Oranges 1kg 2
1735676,1735750 Apples 1kg, Oranges 1kg 2
the orderIDs CSV
thanks a lot
August 2, 2017 at 8:38 pm
Something like this?
WITH
cte_OptionString AS (
SELECT
t1.OrderID,
OptionString = STUFF(MAX(ox.OptionString), 1, 2, '')
FROM
#temptable t1
CROSS APPLY (
SELECT
CONCAT(', ', t2.OptionID, 'x', t2.Quantity)
FROM
#temptable t2
WHERE
t1.OrderID = t2.OrderID
ORDER BY
t2.OptionID
FOR XML PATH('')
) ox (OptionString)
GROUP BY
t1.OrderID
)
SELECT
os1.OptionString,
OrderString = STUFF(MAX(osx.OrderString), 1, 2, '')
FROM
cte_OptionString os1
CROSS APPLY (
SELECT
CONCAT(', ', os2.OrderID)
FROM
cte_OptionString os2
WHERE
os1.OptionString = os2.OptionString
ORDER BY
os2.OrderID
FOR XML PATH ('')
) osx (OrderString)
GROUP BY
os1.OptionString;
Results... (sorry about the formatting)
OptionString OrderString
---------------------------------------- -----------------------------------------
1475x1 1714547
1475x2 1714195
2377x1, 2901x1 1714637
2377x1, 3382x2, 3913x1 1713811
2901x1 1713927, 1714032, 1714073, 1714202, 1714332, 1714792, 1714807
2901x1, 3933x1 1713986, 1714504
2901x1, 4011x1, 4247x1 1714197
2901x1, 4214x1 1713905
2901x1, 4247x1 1714263, 1714958
2901x1, 4248x1 1713938
2901x2 1713980, 1715004
2901x2, 3183x1, 4247x2 1713765
2903x1 1710479, 1714967
2903x1, 3913x1 1714305
2903x1, 3925x1 1715133
2903x1, 6155x1 1714461
2903x2 1714647
2905x1 1714595
2905x1, 4214x1 1713870
2905x1, 4215x1 1711090
2905x1, 5497x1 1715064
2905x1, 6155x2 1715070
2905x2, 5497x2 1713615
2910x1 1714044, 1714099
2910x1, 3933x1 1714634
2910x2, 3414x1 1713551
3180x1 1715003
3186x1 1713627
3382x1 1714380
3383x1 1711766, 1713670, 1713866, 1713926, 1713941, 1713988, 1714034
3383x1, 5542x1 1714023
3383x2 1714130
3385x1, 3387x2 1713579
3386x1 1715053
3387x1 1714339, 1715145
3387x2 1715068
3414x1 1713442
3415x1 1708500
3417x1 1714270
3913x1 1712626, 1713853, 1713889, 1713945, 1714054, 1714146, 1714180, 1714249, 1714347, 1714699, 1714712, 1714861, 1714872, 1714894, 1714931, 1714991
3913x1, 4011x1 1713978
3913x1, 4247x1 1714564, 1714664, 1715177
3913x1, 5497x1 1714369
3913x2 1714855, 1714892
3925x1 1710642, 1714244, 1714399, 1714609, 1714771, 1714802, 1714999
3925x1, 3933x5 1713504
3926x1 1712753, 1714624, 1714758, 1715044
3930x1 1715036
3933x1 1711859, 1713621, 1713890, 1713894, 1713896, 1713910, 1713949, 1714109, 1714188, 1714217, 1714230, 1714309, 1714346, 1714365, 1714470, 1714521, 1714661, 1714797, 1714877, 1714934, 1714940, 1715037, 1715137, 1715160, 1715167
3933x1, 4011x1, 4212x1 1714849
3933x1, 4247x1 1714848
3933x2 1703147, 1713665, 1714291, 1714292, 1714510, 1714703
3933x2, 4248x1 1713491
3933x3 1714226
3933x6 1714087
4095x1 1713946
4095x1, 4213x1, 6075x1 1713294
4095x1, 4214x1, 5497x1, 5873x1 1714776
4180x1 1714148
418x1 1712026, 1713745, 1714070
4214x1 1689310
4214x1, 4215x1 1710885
4215x1 1712924, 1714617
4247x1 1714102, 1714314, 1714454, 1714751, 1714954, 1715008, 1715054
4247x1, 4248x1 1714012
4247x2 1714158, 1714974
4248x1 1713965, 1714400, 1714499, 1714522, 1714731, 1714986, 1715009
4248x1, 5873x1 1714086
4248x2 1713821, 1714257, 1714262
5497x1 1708411, 1713624, 1714026, 1714058, 1714082, 1714118, 1714223, 1714405, 1714460, 1714735, 1714914, 1714983, 1715039
5497x1, 5873x2 1698321
5497x2 1714485, 1714535
5542x1, 5578x2 1715170
5542x2 1714613
5578x2 1714412
5873x1 1713567, 1714064, 1714069, 1714374, 1714448, 1714843
6075x1 1709102, 1714075, 1714322, 1714560, 1715122
6075x2 1713622, 1713640
6076x1 1714132
6155x1 1713955, 1714599, 1714660
6155x2 1713773
926x1 1715165
927x1 1714842
August 3, 2017 at 10:37 am
worked.
thanks a lot
August 3, 2017 at 1:41 pm
mikeaspnet - Thursday, August 3, 2017 10:37 AMworked.
thanks a lot
Glad to help. 🙂
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply