Storing sales against an item? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Storing sales against an item?

Author

Topic

mark1504
Posting Yak Master

103 Posts

Posted - 2005-11-17 : 11:42:58

At first I thought I wanted a best seller chart, and that was quite easy, however what I realise I want is to have those figures stored to each item, with a recent sales (i.e. last 7 days) and overall sales.

What kind of method should I use for writing sales figures to an item file?

It's easy enough to do a single update...
UPDATE Titles SET RecentSales=(SELECT COUNT(*) FROM Sales WHERE ItemID=@ItemID AND DateSold>GETDATE()-7) WHERE ItemID=@ItemID

... but how should I write a procedure that does this update for all items?

Thanks
Mark

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-11-17 : 15:10:10

Maybe something like:

update t
set recentSales = d.salesCount
from titles t
join 
(
	select itemID, count(1) salesCt
	from sales
	where DateSold > getdate()-7
        group by itemid
) d on d.itemID = t.itemID

EDIT: Added group by statement

mark1504
Posting Yak Master

103 Posts

Posted - 2005-11-17 : 18:42:07

Wow! Isn't SQL a beautiful thing?

That's so much better than my crude iteration. I'm not sure I exactly understand it, but thanks to your clear code I have it working anyhow.

Many thanks
Mark

CASE CLOSED

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-17 : 23:53:06

That is the SET based approach in which case CURSOR can be avoided
In SQL Server help file, you can see example for this

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-11-18 : 02:25:17

"That's so much better than my crude iteration"

Best to strive to avoid Iteration in SQL.

"I'm not sure I exactly understand it"

Dunno if this will help:

What ehorns example does is to make a "temporary table" [in Human terms, not necessarily in physical terms!] out of the nested sub-select:

select itemID, count(1) salesCt
from sales
where DateSold > getdate()-7
group by itemid

so now for each ItemID you have a Count - aliased as [salesCt] - of the number of [sales].

This data is then JOINed to the table you want to update - [titles]. So now you have access to all the columns in [titles] and also the corresponding columns in the sub-select (which is aliased as [d]).

So now you can just update you [titles] row with any column (or formula using those columns) based on [titles] or [d] - e.g.

set recentSales = d.salesCount

A key aspect of this is that this functions as a SET - so it all happens in one go, rather than iterating around each product in turn, and SQL is very good at that - it will be orders of magnitude quicker that iterating around a loop.

Kristen

mark1504
Posting Yak Master

103 Posts

Posted - 2005-11-18 : 08:54:52

Kristen, thank you very much for the explanation. I have a couple more similar tasks to do so that will help my understanding. I know people criticize SQL's language but I'm impressed by how powerful and concise it can be.

Again, thanks to all. This place is amazing!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-11-18 : 09:32:25

Kristen, Thanks for the great explanation of the code snippet!

Hey Mark!, Glad that worked out for you, SQL can be quite elegant indeed and you will find very knowledgeable folks at SQLTeam who are more than willing to help you grow your SQL skills.

Kristen
Test

22859 Posts

Posted - 2005-11-18 : 10:09:46

"Kristen, Thanks for the great explanation of the code snippet!"

Well, at least you don't treat me like an automatic code documenting tool!

Kristen

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-11-18 : 13:41:38

quote:
Originally posted by Kristen

"Kristen, Thanks for the great explanation of the code snippet!"

Well, at least you don't treat me like an automatic code documenting tool!

Now that sounds like a business opportunity!

Kristen
Test

22859 Posts

Posted - 2005-11-18 : 19:52:18

Do I sound like I'm begging for code to document?!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-19 : 00:37:08

Kris, do you have your own blog?
If not, why dont you have it with all your GOOD Stuffs?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-11-20 : 02:12:34

Graz kindly gave me a blog here. I have never put anything in it.

I have tried on a couple of occasions, and basically decided that that type of writing is not something I would be any good at, or have the motivation to maintain.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-21 : 00:34:02

But. It would ne nice if you post your stuffs there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources