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
 General SQL Server Forums
 New to SQL Server Programming
 Using a Loop to exclude records

Author  Topic 

jtw5074
Starting Member

4 Posts

Posted - 2013-06-20 : 18:52:32
I need help with automating a process in SQL Server. I want to use a loop to exclude certain records.

Calculate the impact of each record to the “Average Selling Price” in the attached excel file.
For example, If you were to exclude the Aguirre purchase of SKU A in the US, what would be the “Average Selling Price” for A in the US?
Additionally, after itinerating over the 4 transaction for SKU B in the US, what is the ranking of each transaction regarding the impact to the ASP?


country sku customer units sales unit price Average Selling Price
us a aguirre 10 100 10 15.55555556
us a wirt 100 2000 20
us a emrich 3 50 16.66667
us b aguirre 34 100 2.941176 9.190627744
us b wirt 67 2000 29.85075
us b emrich 34 50 1.470588
us b jory 20 50 2.5


?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-20 : 21:51:11
quote:
If you were to exclude the Aguirre purchase of SKU A in the US, what would be the “Average Selling Price” for A in the US?

typically, you can add a WHERE condition to your query, example
WHERE customer <> 'aguirre'


quote:
Additionally, after itinerating over the 4 transaction for SKU B in the US, what is the ranking of each transaction regarding the impact to the ASP?

What do you want to see / result that you want ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 02:04:18
what is the ranking of each transaction regarding the impact to the ASP

sounds like usage of rank(),dense_rank() functions to me. One question here is on what basis you determine the impact?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jtw5074
Starting Member

4 Posts

Posted - 2013-06-21 : 13:38:15
1) I am trying to figure out how to use a loop to see how the average selling price is affected if you take out one of the customer's orders. For example, if you were to exclude one of the Aguirre purchases of SKU A, what would be the “Average Selling Price” for A in the US? I would like to use a loop so I can automate a process.

2) I am trying to figure out the ranking of each order's ASP. Meaning which order's ASP affects the total ASP of the product as a whole.

Sorry for the confusion earlier. I pretty much copied and pasted his email.

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-22 : 02:28:32
1, you dont need a loop for that. You can apply a correlated subquery to find out the Average Selling Price excluding the selected SKU
2, Again the earlier subquery method with a RANK query will give you ranking after excluding it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -