June 10, 2008 at 8:29 am
I am trying to get an avg of the "avg fast slow" days of the latest (top3) for each customer ID. Given the data below is a sample table, I actually have 1000's of customers and over 100k records in this table. As you will see there is no entry in a given month if there are no bills paid, so I am not able to use a greater than query. I have played around with TOP 3 and that works for a particular customer, but not for all customers.
Customer ID Year Month Avg fast slow days
1234 2008 5 -5
1234 2008 4 -3
1234 2008 2 2
1234 2008 1 3
1234 2007 11 1
1234 2007 9 -3
2345 2008 5 -2
2345 2008 3 5
2345 2008 1 -2
2345 2007 12 1
9999 2008 5 2
9999 2008 4 1
9999 2008 3 5
Here is the query so far:
This will give the avg of all of the slow fast day columns, but I only need the top 3 most recent:
Select customer_id, avg(avg_fast_Slow_days)
from Table1
Group by customer_id, avg_fast_slow_days
Order by 2 desc
Then this is where I am at with the Top 3:
Select top 3 customer_id, avg(AVG_Fast_Slow_Days), year_invoiced, month_invoiced
from table1
where customer_ID = 9999
Group by customer_Id, avg_fast_slow_days, year_invoiced, Month_invoiced
Order by 3 desc,4 desc
I know this an obvious one line of code fix, but I just can't seem to get it.
Thanks for the help,
Trent
June 10, 2008 at 8:34 am
Can you give us the expected output for the given sample data please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2008 at 8:41 am
Sure no problem,
I am just looking for a report with one line per customer and avg number of the top 3 most recent months of avg slow fast days.
So
Cust ID | Last 3 Avg fast slow days
1234 | Avg number of the top 3
Sorted by avg fast slow days
Simple, but complex query.
Thanks for the help,
Trent
June 10, 2008 at 9:07 am
I think I am getting closer:
Select customer_id,
(select top 3 avg(AVG_Fast_Slow_Days)
from table1 X
Where x.customer_id = Y.customer_id)
from table1 Y
Group by customer_Id
Order by 1 asc
Though the values still aren't the same as our ERP screen:( Does this look right so far?
June 10, 2008 at 9:07 am
So you want something like this?
Customer Avg
1234 -2
2345 0.33
9999 2.67
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2008 at 9:12 am
Yes exactly. I have over 25 customers in my database, so we would like to put the "None payers" up to the top of the list. The Fast Slow days are the amount of days that customers pay +/- there net terms. Minus being good, and + being bad.
Thanks again for your help, I almost figured it out, but I am still missing one line I think. An extra subquery perhaps...
June 10, 2008 at 9:20 am
I am going to try figuring this out in this manner. It should be possible either way, but this is a nice tutorial. Let me know what you come up with.
Thanks,
Trent
June 10, 2008 at 11:03 am
Well I am still stuck on this. Neither method has given me results that I need.
I have managed to get good data (Top 3) if I hard code in the cust_id:
select top 3 customer_id, (AVG_Fast_Slow_Days), year_invoiced, month_invoiced
from Table1 X
Where x.customer_id =100078
AND (year_invoiced = datepart(yyyy, getdate())AND month_invoiced <= (datepart(mm, getdate())-1)
OR Year_invoiced < datepart(yyyy, getdate()))
order by 3 desc, 4 desc
(This outputs the top 3 results for the customer, but still doesn't avg them)
But when I try to get this to output for all customers, it doesn't work. I can't get the avg to work either. This is really aggravating.
To top that off, I just manually checked a value in the system to see if the calculate even equals what I enter in on a calculator and the value is off. Leave it to a propritary ERP system to provide a value, but then not tell me how it is derrived. ARG.
If anyone can get this to run through all customers and provide one line per customer with the top 3 avg'd to one, I am forever in your debt.
Thanks,
Trent
June 10, 2008 at 11:09 am
Trent - your big issue is to be able to pick out the top 3 per group. Once you have them - the averages are easy.
Well - in this case - the "top 3 by group" is an ordinal rank problem in 2000. Take a look at this:
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 10, 2008 at 2:01 pm
Well with the help of some folks here and over at tek tips I was able to resolve this. It ended up my ERP vendor didn't give a clear explanation on how this number is actually pulled.
Here is the final version of my sql to do what I needed. If you see the post on tek tips it will explain this more.
http://www.tek-tips.com/viewthread.cfm?qid=1479308&page=1
SQL:
Thanks to george over on the other board for providing the backbone for this query!
-- Create a table variable to help us with top 3
Declare @Temp Table(RowId Int Identity(1,1), Customer_Id Int, Year_Invoiced Int, Month_Invoiced Int, amount_Paid Int, sum_days_x_payment Int)
-- Insert in to the table variable. The order by at the end is very important.
Insert Into @Temp(Customer_Id, Year_Invoiced, Month_Invoiced, amount_paid, sum_days_x_payment)
Select Customer_Id, Year_Invoiced, Month_Invoiced, amount_paid, sum_days_x_payment
From table1
Where (year_invoiced = datepart(yyyy, getdate())AND month_invoiced <= (datepart(mm, getdate())-1)
OR Year_invoiced < datepart(yyyy, getdate()))
Order By Customer_Id, Year_Invoiced DESC, Month_Invoiced Desc
-- Now, get the top 3 per customer and average the values. This also protects against dividing by zero
Select T.Customer_Id, SUM(1.0000 * sum_days_x_payment)/
CASE
WHEN SUM(1 * amount_paid) = 0
THEN 1.0000
ELSE SUM(1.0000 * amount_paid)
END
As TheAverage
From @Temp T
Inner Join (
Select Customer_Id, Min(RowId) As MinRowId
From @Temp
Group By Customer_Id
) As A
On T.Customer_Id = A.Customer_Id
Where RowId - MinRowId + 1 <= 3
Group By T.Customer_Id
June 10, 2008 at 11:59 pm
I believe the biggest problem here is the wrong logic.
As per the requirement, "avg number of the top 3 most recent months of avg slow fast days" for the customer 1234 is -5 -3 + 0 = -2.67, not -2.
Because "3 most recent months" are May (-5), April (3), March (0), not May (-5), April (3), February (2).
Trent, are you sure you've your query right?
_____________
Code for TallyGenerator
June 11, 2008 at 7:16 am
HI Sergiy,
The values that I was using as an example was just to show an example of data that I was using. I actually never evaluated that data, nor did I do any calculations on it, so in all honesty I have no idea what the results would have been without actually taking time to figure it out with the test data.
However, I was able to get the exact values of my ERP system using the query that I posted with some tweaking. I had to add a couple extra fields in and change some data types to get the decimal places accurate.
All in all this was one of the most difficult queries I have ever written. I am not a DBA, nor do I claim to be, but I have 99% of the time held my own in writting reports. This report unfortunately was well above my pay grade and thanks to all of you I was able to get it done and 100% accurate. I don't know what I would do without it.
Here is a copy of the final query if you feel like bugging your eyes out on it a while. Note: The actual final value calculation requirements changed mid post, so you would have to read the tek tips post to see why.
Thank you Sergyi for the followup email. I am glad to see someone is double checking my work.
Thanks again,
Trent
-- Create a table variable to help us with top 3
Declare @Temp Table(RowId Int Identity(1,1), Customer_Id decimal(19,0), Year_Invoiced Int, Month_Invoiced Int, amount_Paid decimal(19,2), sum_days_x_payment decimal(19,4), customer_name varchar(255), credit_status varchar(8), net_days decimal(3,0), terms_desc varchar(20))
-- Insert in to the table variable. The order by at the end is very important.
Insert Into @Temp(Customer_Id, Year_Invoiced, Month_Invoiced, amount_paid, sum_days_x_payment,customer_name, credit_status, net_days, terms_desc )
Select
p21_view_customer_Credit_History.Customer_Id,
p21_view_customer_Credit_History.Year_Invoiced,
p21_view_customer_Credit_History.Month_Invoiced,
p21_view_customer_Credit_History.amount_paid,
p21_view_customer_Credit_History.sum_days_x_payment,
p21_view_customer.customer_name,
p21_view_customer.credit_status,
p21_view_terms.net_days,
p21_view_terms.terms_desc
FROM p21_view_customer_Credit_history,
p21_view_customer,
p21_view_terms
WHERE p21_view_customer_credit_History.customer_id = P21_view_customer.customer_id
AND p21_view_customer.terms_id = p21_view_terms.terms_id
AND p21_View_Customer.terms_id IN (3,19,54,58,53)
AND (year_invoiced = datepart(yyyy, getdate())AND month_invoiced <= (datepart(mm, getdate())-1)
OR Year_invoiced < datepart(yyyy, getdate()))
Order By p21_view_customer_Credit_History.Customer_Id, p21_view_customer_Credit_History.Year_Invoiced DESC, p21_view_customer_Credit_History.Month_Invoiced Desc
-- Now, get the top 3 per customer and average the values.
Select T.Customer_Id, SUM(1.0000 * sum_days_x_payment)/
CASE
WHEN SUM(1 * amount_paid) = 0
THEN 1.0000
ELSE SUM(1.0000 * amount_paid)
END
As Last3_Cmplt_Fast_Slow_Avg,
T.customer_name, T.credit_status, T.net_days, T.terms_desc
From @Temp T
Inner Join (
Select Customer_Id, Min(RowId) As MinRowId
From @Temp
Group By Customer_Id
) As A
On T.Customer_Id = A.Customer_Id
Where RowId - MinRowId + 1 <= 3
Group By T.Customer_Id, T.customer_name, T.credit_status, T.net_days, T.terms_desc
order by Last3_Cmplt_Fast_Slow_Avg asc
June 13, 2008 at 7:55 am
EDIT: PLEASE IGNORE THIS...
I'm glad you were able to get a solution, but given that this post is in the SQL Server 7, 2000 portion of the forum, and your solution clearly uses code that could only work on SQL Server 2005 or later, many of us no doubt could have more easily come up with something if we had known that SQL 2005 was available. Also, you kind of hit on this in your last post - you didn't really know exactly what calculation you were after, which kind of makes it hard to get accurate help. If you don't know what you need, even a mind reader isn't going to be able to help much. Fortunately, folks were at least able to get you going in a direction that would help you determine what you needed. Keep an eye out for the proper forum section and you'll likely get even more help, even sooner...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 13, 2008 at 8:05 am
Actually we are only on SQL 2000, and I have actually never even used 2005, so I am not sure what line looks like it should be used in 2005, but this was stricly written for 2000. Also it wasn't my fault that I didn't know what they exact calculation was, that was my ERP's fault. Though it is somewhat true, in reality the underlying problem that I needed resolved was resolved.
I never asked this forum to solve my actual calculation, I needed to find out how to go through a table in a manner to do some form of calculation on related recorded in a looping method, so really the calculation was irrelevant. Any one of us in this forum knows how to sum, avg, or do whatever to data, the problem is getting the data organized in the correct manner first and that was the problem. I have never delt with this type of query where looping was involved, but now I know and have a bit of code to move forward with other projects.
The people that helped me did a great job on the problem and calculation that I gave them and as a result when the calculation changed a bit, I was able to adapt what they had already helped me with and made it work. So I thank all of you again, and will continue to use this forum for SQL 2000 solutions.
Thanks,
Trent
June 13, 2008 at 8:16 am
I'm going to have to SLAP myself... My brain apparently had a senior moment and thought it saw ROW_NUMBER when what it actually saw was an Identity column. Consider my previous post withdrawn, and I offer my most humble apology. If you would be so kind, please prepare my crow medium rare, so as to soften the impact...
Steve
(aka smunson)
:blush::crying:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply