Nesting While Loops, Or? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nesting While Loops, Or?

Author

Topic

DaveJ86
Starting Member

2 Posts

Posted - 2012-03-02 : 12:57:49

I am nesting while loops to navigate a data set, and nesting another while loop to navigate a specific data element inside. and open to alternative solutions. I have multiple carts containing multiple products in various quantities. At the end of the day I need to group by cart type with a count of carts configured the same way (products & quantities).

Here is a sample table:

Cart Product Qty

Cart1 Product-A 15
Cart1 Product-B 5
Cart1 Product-C 5
Cart2 Product-D 10
Cart2 Product-E 10
Cart2 Product-F 5
Cart3 Product-D 10
Cart3 Product-E 10
Cart3 Product-F 5
Cart4 Product-D 15
Cart4 Product-E 10
Cart5 Product-A 15
Cart5 Product-B 5
Cart5 Product-C 5
Cart6 Product-D 10
Cart6 Product-E 10
Cart6 Product-F 5
Cart7 Product-A 15
Cart7 Product-B 5
Cart7 Product-C 5
Cart8 Product-F 25

With the data sorted by Cart, Product I am using a While loop to navigate the data. I then use another While loop to navigate each cart. Inside each cart I build a string by concatenating the product and quantity of each item on the cart. to return something like this.

Cart CartType

Cart1 CartYype1
Cart2 CartType2
Cart3 CartType2
Cart4 CartType3
Cart5 CartType1
Cart6 CartType2
Cart7 CartType1
Cart8 CartType4

And then to produce a report that does the group by:

CartType Qty

CartType1 3
CartType2 3
CartType3 1
CartType4 1

The problem I am having is getting the internal while loop to behave. I am now wondering if I am making way to much work out of this. Because I still have to create another loop to compare the cartType strings! Is there a better way?

Dave J.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 15:06:00

sorry your requirement is not clear. how are you getting carttype values? also how do you get final Qty valueS?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-02 : 16:51:45

A Cart Type is predicated on the combination of Product and Quantity. So, as an example, if you look at Cart1 you see that it has:
Product-A 15
Product-B 5
Product-C 5

That is a Cart Type. And, if you look at Cart7, you'll see it has the same Products and associated Quantities. Thus, all Carts with the same Cart Type (CartType1) are summed to give you a Qty of 3 (Cart1, Cart5 and Cart7).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 16:59:26

then isnt this be enough?


;With CartDetails
AS
(
SELECT Cart,STUFF((SELECT ',' + Product	+ ':' + CAST( Qty AS varchar(10)) FROM table WHERE Cart = t.Cart ORDER BY Product FOR XML PATH('')),1,1,'') AS CartPattern
FROM (SELECT DISTINCT Cart FROM table)t
)
SELECT CartPattern,
COUNT(*) 
FROM CartDetails
GROUP BY CartPattern

if you want the patterns to be named as CartType1,CartType2 etc you can put it in another table and generate it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DaveJ86
Starting Member

2 Posts

Posted - 2012-03-05 : 12:10:39

Thanks visakh16, that is perfect. The first cursor creates a unique id from product and quantity, and the second cursor concatenating those unique ID's to uniquely identify the Carts. Then, in the report they can be grouped by which was the ultimate goal. I like your solution a lot better then mine. Thanks.

Dave J.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources