Use Temp Table as critera to find records

[sabloomer]

I am looking for suggestions on how best to solve this problem. I need to create a stored proc that finds the last 5 times a certain type of item was ordered and caclulate the average price.

My first thought was to make a temp table with a list of the desired items then somehow loop through them and use the item as the criteria to find the last 5 purchases and append the results into a table. I am not sure how to loop like that using a temp table inside a stored proc.

After reading thread183-1381093 I as wondering if there was a more "elegent" sql solution that I can achive with some combination of join types and sub queries. The trick again is that I only want the last 5 purchases. Any suggestions?

Thank You,

sabloomer

 

[Qik3Coder]

Did you find an answer to this?

If not, could you post at least some basic table structure, so that i don't have to come up with a contrived answer?

-The answer to your problem may not be the answer to your question.

 

[sabloomer]

Qik3Coder,

Let me start with thank you! There are a two tables in play here. First is the "Items" table. It has a lot of fields, but the three that are meaningfull here are

ItemNumber
MakeorBuy
ItemKey

I need to limit my search my search to "buy" items.

The second table holds purchases. This also has many fields, but the important ones are...

ItemKey
PurchaseDate
OrderQty
ReceiptQty

From this table I need to find the last 5 times each Buy item was purchased, and the add the results together.

Example:
If the last five records are...

Key,Date,Order,Receipt
001,06/20/2007,100,95
001,06/10/2007,50,35
001,06/01/2007,40,40
001,05/25/2007,60,60
001,05/15/2007,20,10

I want to write the following record to my results table...

Key,Order,Receipt
001,270,240

Does that help?

 

[gmmastros]

Are you using SQL 2000 or SQL 2005. The ideal solution depends on the version you are using.

-George

"the screen with the little boxes in the window." - Moron

 

[sabloomer]

I am using SQL Server 2000.

 

[gmmastros]

Things get a little messy with SQL 2000. But, here's an example to get you started.

In this example, I created a table variable named (@Temp) to reproduce your sample data. This means you can copy/past this to Query Analyzer to study how this works. If you are satisfied that it does work, then you just need to modify this to accomodate your real tables.

[COLOR=green]-- Sample Data
[/color][COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color]([[COLOR=blue]Key[/color]] [COLOR=blue]varchar[/color](10), [COLOR=blue]Date[/color] [COLOR=#FF00FF]DateTime[/color], [[COLOR=blue]Order[/color]] [COLOR=blue]int[/color], Receipt [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'001'[/color],[COLOR=red]'06/20/2007'[/color],100,95)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'001'[/color],[COLOR=red]'06/10/2007'[/color],50,35)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'001'[/color],[COLOR=red]'06/01/2007'[/color],40,40)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'001'[/color],[COLOR=red]'05/25/2007'[/color],60,60)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'001'[/color],[COLOR=red]'05/15/2007'[/color],20,10)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'001'[/color],[COLOR=red]'05/15/2006'[/color],20,10)

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'002'[/color],[COLOR=red]'06/20/2007'[/color],100,95)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'002'[/color],[COLOR=red]'06/10/2007'[/color],50,35)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'002'[/color],[COLOR=red]'06/01/2007'[/color],40,40)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'002'[/color],[COLOR=red]'05/25/2007'[/color],60,60)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'002'[/color],[COLOR=red]'05/15/2007'[/color],20,10)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'002'[/color],[COLOR=red]'05/15/2006'[/color],20,10)


[COLOR=green]-- Query starts here.
[/color][COLOR=blue]Declare[/color] @Data [COLOR=blue]Table[/color]([COLOR=#FF00FF]RowId[/color] [COLOR=blue]Int[/color] [COLOR=blue]Identity[/color](1,1), Rank [COLOR=blue]Int[/color], [[COLOR=blue]Key[/color]] [COLOR=blue]varchar[/color](10), [COLOR=blue]Date[/color] [COLOR=#FF00FF]DateTime[/color], [[COLOR=blue]Order[/color]] [COLOR=blue]int[/color], Receipt [COLOR=blue]Int[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Data([[COLOR=blue]Key[/color]], [COLOR=blue]Date[/color], [[COLOR=blue]order[/color]], Receipt)
[COLOR=blue]Select[/color] [[COLOR=blue]key[/color]], [COLOR=blue]Date[/color], [[COLOR=blue]Order[/color]], Receipt
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Order[/color] [COLOR=blue]By[/color] [[COLOR=blue]key[/color]],[COLOR=blue]Date[/color] [COLOR=#FF00FF]DESC[/color]

[COLOR=blue]Update[/color]	A
[COLOR=blue]Set[/color]     A.Rank = [COLOR=#FF00FF]RowId[/color] - MinRowId + 1
[COLOR=blue]From[/color]    @Data A
        [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
          [COLOR=blue]Select[/color] [COLOR=#FF00FF]Min[/color]([COLOR=#FF00FF]RowId[/color]) [COLOR=blue]As[/color] MinRowId, [[COLOR=blue]Key[/color]] 
          [COLOR=blue]From[/color]   @Data
          [COLOR=blue]Group[/color] [COLOR=blue]By[/color] [[COLOR=blue]Key[/color]]
          ) [COLOR=blue]As[/color] B
          [COLOR=blue]On[/color] A.[[COLOR=blue]key[/color]] = B.[[COLOR=blue]key[/color]]

[COLOR=blue]Select[/color] 	[[COLOR=blue]key[/color]], Sum([[COLOR=blue]Order[/color]]) [COLOR=blue]As[/color] [[COLOR=blue]Order[/color]], Sum(Receipt) [COLOR=blue]As[/color] Receipt
[COLOR=blue]From[/color]    @Data
[COLOR=blue]Where[/color]	Rank <= 5
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] [[COLOR=blue]key[/color]]

-George

"the screen with the little boxes in the window." - Moron