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
 General SQL Server Forums
 New to SQL Server Programming
 Using a Loop to exclude records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jtw5074
Starting Member

4 Posts

Posted - 06/20/2013 :  18:52:32  Show Profile  Reply with Quote
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)

Singapore
17642 Posts

Posted - 06/20/2013 :  21:51:11  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  02:04:18  Show Profile  Reply with Quote
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 - 06/21/2013 :  13:38:15  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/22/2013 :  02:28:32  Show Profile  Reply with Quote
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
  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.08 seconds. Powered By: Snitz Forums 2000