Looping through to build a table? 2

[gbaughma]

I have to build a historical table of sales. I have the main portion working fine, but now I need to "Loop" through by week of the year.

In other words, I have to grab all of the sales for week 1 of 2009, and put them into a table. Then week 2, week 3, etc, continuing to build the "master sales" table.

Is there a simple way to do this? Something like "For each @week..."

Thanks in advance!


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

Why do you need to do this week by week? Are you aggregating the data to store in the master sales table?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

I'm collecting the data so that we can do historical trend analysis by week. Unfortunately, this means iterating through individual sales orders and totaling up sales by product for a week, because the accounting system only keeps historical sales by month.

After I get this "big import" made, then I will be firing off a weekly task to add to the table each week, from the previous weeks' sales.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[SQLBill]

Microsoft SQL Server doesn't care what order the data is in. So if you aren't aggregating it or otherwise changing it as you are moving it to the new table, there's no need to pull it by the week. Move all the data at one time and then on a weekly basis just pull the new data.

Index on the date and write your query to group by the week.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gbaughma]

I evidently didn't make myself clear on this one.
I realize that SQL doesn't care what order the data is in...
The problem of it is that our current tables don't have sales history broken down by week. It's broken down by month.

I need to iterate through all of the sales orders looking at the date of the sales order (after I calculate the date range for a particular week of a particular year), sum the sales by product together (representing sales of a particular product for that week), and put the year, week number, product SKU, and total sales into another table.

I then need to repeat that for every week in the year, since 2009.

The boss will then take that information by weekly sales, and do a weighted trend analysis for projected sales by SKU, look at current inventory levels, account for lead time, account for minimum order amounts, and generate a report of what we *should* order based on prior trends and so forth.

I have the query written so that I can pull the sales orders by week by year, and do all of the calculations. However, to build the initial table, I need to loop through for each week and repeat the process until the table is built. Once it is built, then I will maintain it with a weekly stored procedure.

Am I making more sense now?


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[SQLScholar]

gbaughma,

I dont think SQLBill is getting his point across.

There is no need to iterate for the query you have described. Iteration generally means looping, which generally means cursors which means SLOW.

If you give us more defintion (example data in the table, example output you require) we might be able to help you with a query that doesnt iterate.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

You can set up a loop like this:

Declare @StartWeek DateTime

Set @StartWeek = DateAdd(Week, DateDiff(Week, 0, '19900101'),0)

While @StartWeek < '20200101'
	Begin
	
		Set @StartWeek = DateAdd(Day, 7, @StartWeek)
	
		-- Put your stuff here
	
	End

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

Thanks George. A While Loop is exactly what I was looking for.

SQLScholar: This is a one-time dump of data. As I said, our accounting system doesn't store sales by week; it stores it by month. So the table that I'm building is a historical sales table by week that overcomes a shortcoming of the accounting system.

Once the historical data is looped through, and all of the weeks of sales pulled and put into another table, I won't have to do this again, other than once a week update it with the previous weeks' sales.

I know that looping through a table is a time consuming thing, and I obviously wouldn't want to do it every time I did a query; however this is a one-time event to collate historical sales data.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[JonFer]

To continue the suggestion of NOT looping, it will be MUCH more efficient if you calculate the "week" date for any sales date and generate all of your data at once. One method is:

DateAdd(day, DatePart(weekday,SalesDate)-1,SalesDate) as WeekDate

You can adjust if necessary if you want the "week" date to be a Sunday or Monday or whatever.

Another method would be to group on DatePart(year,SalesDate) and DatePart(week,SalesDate) and/or use these 2 date parts to calculate the "week" date.

 

[JonFer]

Correction - the first method to calculate the "week" date should be:

DateAdd(day, -1*(DatePart(weekday,SalesDate)-1), SalesDate)