SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Storing sales against an item?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mark1504
Posting Yak Master

United Kingdom
103 Posts

Posted - 11/17/2005 :  11:42:58  Show Profile  Visit mark1504's Homepage  Send mark1504 a Yahoo! Message  Reply with Quote
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

Edited by - mark1504 on 11/17/2005 11:44:16

ehorn
Flowing Fount of Yak Knowledge

USA
1631 Posts

Posted - 11/17/2005 :  15:10:10  Show Profile  Reply with Quote
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

Edited by - ehorn on 11/17/2005 15:20:15
Go to Top of Page

mark1504
Posting Yak Master

United Kingdom
103 Posts

Posted - 11/17/2005 :  18:42:07  Show Profile  Visit mark1504's Homepage  Send mark1504 a Yahoo! Message  Reply with Quote
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

Edited by - mark1504 on 11/17/2005 18:42:48
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/17/2005 :  23:53:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/18/2005 :  02:25:17  Show Profile  Reply with Quote
"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
Go to Top of Page

mark1504
Posting Yak Master

United Kingdom
103 Posts

Posted - 11/18/2005 :  08:54:52  Show Profile  Visit mark1504's Homepage  Send mark1504 a Yahoo! Message  Reply with Quote
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!
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1631 Posts

Posted - 11/18/2005 :  09:32:25  Show Profile  Reply with Quote
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.

Edited by - ehorn on 11/18/2005 09:32:50
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/18/2005 :  10:09:46  Show Profile  Reply with Quote
"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
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1631 Posts

Posted - 11/18/2005 :  13:41:38  Show Profile  Reply with Quote
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!
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/18/2005 :  19:52:18  Show Profile  Reply with Quote
Do I sound like I'm begging for code to document?!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/19/2005 :  00:37:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/20/2005 :  02:12:34  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/21/2005 :  00:34:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
But. It would ne nice if you post your stuffs there

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000