need help to build this "update" store procedure

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 2000 Forums

SQL Server Development (2000)

need help to build this "update" store procedure

Author

Topic

pmak
Starting Member

41 Posts

Posted - 2006-08-22 : 21:17:11

I have the table which store the sales details information such as storeID, sales_date, item etc. I need to create a new column to category the sales entry if there are more than one items sold on the SAME date of that storeID. I am thinking to create the column "Sales Entry" with the datatype "int" and assign the numeric to this column starting from 1 to each row of the sales information. For example with the earliest sales date for storeId 1, there are three items sold then "1" will be assigned to all three rows for the "sales entry" column, then "2" for the next sales date for the same storeId for each of the item sold. The logic will be applied to all other storeID. Thanks.

Paul Mak

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-23 : 00:58:32

Sometihng like this? You, of course, have to change the column names and table names to reflect your reality.
At least your environment.

ALTER TABLE xxx ADD SalesEntry INT DEFAULT 0

UPDATE		y
SET		y.SalesEntry = 1
FROM		xxx y
INNER JOIN	(
			SELECT		StoreID,
					SalesDate
			FROM		yyy
			GROUP BY	StoreID,
					SaleDate
			HAVING		COUNT(*) > 1
		) q ON q.StoreID = y.StoreID AND q.SalesDate = y.SalesDate

Peter Larsson
Helsingborg, Sweden

pmak
Starting Member

41 Posts

Posted - 2006-08-23 : 02:45:07

Thank you for your reply. However it does not work.....here is the sample of the data...

id storeid sales_date item sales_entry
----------- ----------- ----------- ----------- ---------------
1 1 2006-12-07 car 1
2 1 2006-12-07 boat 1
3 1 2006-12-07 plane 1
4 1 2006-12-15 car 2
5 1 2006-12-16 bike 3
6 2 2006-10-15 bike 1
7 2 2006-11-16 car 2
8 2 2006-11-16 house 2

etc

UPDATE y
SET y.SalesEntry = 1
FROM xxx y
INNER JOIN (
SELECT StoreID,
SalesDate
FROM yyy
GROUP BY StoreID,
SaleDate
HAVING COUNT(*) > 1
) q ON q.StoreID = y.StoreID AND q.SalesDate = y.SalesDate

Paul Mak

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-23 : 03:37:26

Sample data? You mean you want the result to be as the posted data?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-23 : 03:41:06

[code]UPDATE s1
SET s1.Sales_Entry = (SELECT 1 + COUNT(DISTINCT Sales_Date) FROM YourTable s2 WHERE s2.StoreID = s1.StoreID AND s2.Sales_Date < s1.Sales_Date)
FROM @YourTable s1[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources