Two questions, do you need just the products that had growth for five consecutive days? Secondly is it exactly 5 days and only 5 days? And does the idea have to be good? I guess that's three questions.
I think one may be able to do something like this, but it just feels like a bad idea. SELECT ProdID A.SalesQty AS 1Day B.SalesQty As 2Day FROM ProdSalesTBL A INNER JOIN (SELECT TOP 1 PRODID FROM ProdSalesTBL WHERE SalesDate < A.SalesDate and SalesQty < A.SalesQty SORT BY SalesDate DESC) B -------------- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning ....-----Original Message----- ....From: won lee [mailto:[EMAIL PROTECTED] ....Sent: Tuesday, March 08, 2005 7:02 AM ....To: CF-Community ....Subject: Counting Consecutive Days a condition is met .... ....This is a SQL question. Hopefully no one will be too offended. Grin. .... ....Let's say you are the DBA for one store retailer. Every sale is recorded ....into your DB (SQL Server) farm. The VP of purchasing asks you, "I need ....to know all the products we sold more of today then the day before for at ....least 5 consecutive business days. I also need to know the number of ....days we have seen growing sales in that particular product." .... ....*IE Today you sold 10,000 bags of chips. The day before you sold 9,999 ....bags of chips. The day before that you sold 8,000 bags of chips. The ....day before that you sold 7,783 bags of chips. And the day before that ....you sold 6,000 bags of chips etc. And the day before that you sold 5,432 ....bags of chips. .... .... .... ....The wrench in the system is this. This store frequently doesn't open for ....myriad reasons. The Boss has unusal religious holidays. So the store ....was closed on 3/7/05 but was open 3/6/05 and 3/8/05. In this case ....growing sales on 3/6/05 and 3/8/05 means that the growing sales is valid. ....The DB is 100% correct at all times. .... ....Table Structure looks something like this .... ....Sales ....======== ....SalesID (PK) ....productID (FK) ....SalesDate ....NumSales .... .... ....Anyone a lot smarter then me can get this? .... .... .... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:5:149708 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/5 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:5 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.5 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
