Problem with query - LOOP - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with query - LOOP

Author

Topic

volyn
Starting Member

30 Posts

Posted - 2010-05-13 : 17:47:25

Hello,

I am struggling over 1 day with that issue. I have table with columns CLIENT_ID,GOODS_ID,Quantity_Ordered,Week_of_order.

I need to find all CLIENT_ID, that were ordering any GOODS_ID in actual week and also at least 5 times in last ten weeks. Result should be list grouped by clients to each client all GOODS_ID with such condition.

I am lost,trying a lot of thing but I dont know what is the easies way how to get such information. Primary I am looking for any solution. Secondary I want to know, if solution could be find without creating temporary table (CREATE TABLE) -> simply said, if it could solved in one complex query and used in SQL Reporting services.

Thanks for any help

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-13 : 17:49:10

Can you please show some sample data and expected output.

volyn
Starting Member

30 Posts

Posted - 2010-05-13 : 18:07:11

week Customer_ID Goods_ID
1 1 A1
2 1 A1
3 1 A1
7 1 A1
8 1 A1
10 1 A1
10 1 A2
1 2 A1
3 2 A1
5 2 A1

Oki here you go, I avoid qty column, it is not so important right now. Just think, that actual week is week=10. Result of my case will be just one row
Customer_ID Goods_ID
1 A1

Because only client "1" Ordered in actual week (10} goods A1 and he also ordered this goods at least 5 times in last 10 weeks (1,2,3,7,8).

volyn
Starting Member

30 Posts

Posted - 2010-05-13 : 18:08:11

sorry not visible well, source tabel has 3 columns, result has only 2 columns

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-14 : 09:25:33

this maybe?

select customer_id,goods_id from @t
where [week] <= @week
group by customer_id,goods_id 
having count(*) > 5

volyn
Starting Member

30 Posts

Posted - 2010-05-15 : 05:44:58

I didnt write it correctly, problem was that original dataset wasnt grouped by week, so having count(*) > 5 could be also case,when customer order material 5 times in one week, right now I discovered possibility of creating variable of datatype TABLE, so I can group it into variable and then use your code on it.
Seems easy now. Thank you

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-15 : 07:50:14

Please post your solution also so that we can understand full scenario...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-16 : 07:49:02

Seems like what he want is


SELECT Client_ID
FROM Table
GROUP BY Client_ID
HAVING COUNT(DISTINCT CASE WHEN Week_of_order>= @CurrWeek - 10 AND Week_of_order < = @CurrWeek-1 THEN Week_of_order ELSE NULL END)>=5
AND COUNT(CASE WHEN Week_of_order= @CurrWeek THEN Week_of_order ELSE NULL END)>0

[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources