July 15, 2010 at 10:20 am
Hi,
I would like to take the average of size if the month and year measure date are the same for the same ClientId. For example, client 100 product measured date doesn't have the same month and year so, display all the data. But for example, Client 101 has multiple product size measured in the month of 6/2010, so I would like to take the average of size in the month of 6/2010 and display the data. Basically, I would like the data to look like below:
More Info about data: Two different Clients can have the same product name. A client can only have one product name.
Display
ClientIDProductNameSizeMeasuredDate
100bfcxb48.382/2010
100bfcxb49.653/2010
100bfcxb50.774/2010
100bfcxb51.125/2010
100bfcxb35.126/2010
100bfcxb36.877/2010
101tkczfbag2.067/2010
101tkczfbag1.366/2010
Data
MeasurIDClientIDProductName Size MeasuredDate
84 100 bfcxb 48.38 2/15/2010
101 100 bfcxb 49.65 3/22/2010
123 100 bfcxb 50.77 4/14/2010
148 100 bfcxb 51.12 5/14/2010
149 100 bfcxb 35.12 6/8/2010
150 100 bfcxb 36.87 7/4/2010
124 101 tkczfbag 2.06 7/14/2010
34 101 tkczfbag 1.01 6/23/2010
102 101 tkczfbag 1.89 6/29/2010
85 101 tkczfbag 1.73 6/25/2010
69 101 tkczfbag 1.52 6/24/2010
50 101 tkczfbag 1.52 6/24/2010
52 101 tkczfbag 1.53 6/24/2010
30 101 tkczfbag 1.01 6/23/2010
22 101 tkczfbag 1.01 6/23/2010
24 101 tkczfbag 1.01 6/23/2010
16 101 tkczfbag 1.02 6/23/2010
18 102 bfcxb 15.15 6/23/2010
19 102 bfcxb 15.15 6/23/2010
25 102 bfcxb 15.15 6/23/2010
32 102 bfcxb 15.15 6/23/2010
54 102 bfcxb 15.15 6/24/2010
36 102 bfcxb 15.15 6/23/2010
70 102 bfcxb 15.15 6/24/2010
86 102 bfcxb 15.15 6/25/2010
103 102 bfcxb 15.26 6/29/2010
126 102 bfcxb 26.94 7/14/2010
125 103 tkcmp 24.45 7/14/2010
104 103 tkcmp 24.59 6/29/2010
105 104 tkcmp 1.69 6/29/2010
87 104 tkcmp 1.69 6/25/2010
71 104 tkcmp 1.69 6/24/2010
56 104 tkcmp 1.69 6/24/2010
38 104 tkcmp 1.69 6/23/2010
31 104 tkcmp 1.69 6/23/2010
July 15, 2010 at 10:36 am
Is that display intented to match the sample data you provided?
Because the sample you showed for client 101, product tkczfbag , month june, you said you want it to show an average size of 1.35, but all of your sample detail records for that product/client/month were 1.01. Am I missing something here?
July 15, 2010 at 11:09 am
Yes, sorry I posted the wrong data and average measurement... that is what happened when you're in a hurry;-). I have edited the original post. Thanks for that.
July 15, 2010 at 12:53 pm
Can't you just do a group by clause and use avg for this?
select ClientId, ProductName, Avg(Size), dateadd(month, datediff(month, 0, MeasuredDate),0)
from WHATEVERYOURTABLEISCALLED
where WHATEVER YOUR RESTRICTIONS ARE
group by ClientId, ProductName, dateadd(month, datediff(month, 0, MeasuredDate),0)
This may not be perfect as the decimal places in the average could need rounding depending on the data type of "Size", and the date is not yet formatted the way you have it in your example (like this each month will have a record for the first day of that month), but its generally the way you'd accomplish what you're looking for.
July 15, 2010 at 7:26 pm
ejbatu1,
I know you're new here. Welcome to the community.
First thing, it really helps people to help you if you include your sample data in a quickly usable format such as this:
CREATE TABLE #table
(MeasurIDINT
,ClientIDINT
,ProductNameVARCHAR(12)
,SizeDECIMAL(6,2)
,MeasuredDateDATETIME)
INSERT INTO #table
SELECT 84,100,'bfcxb',48.38,'2/15/2010' UNION
SELECT 101,100,'bfcxb',49.65,'3/22/2010' UNION
SELECT 123,100,'bfcxb',50.77,'4/14/2010' UNION
SELECT 148,100,'bfcxb',51.12,'5/14/2010' UNION
SELECT 149,100,'bfcxb',35.12,'6/8/2010' UNION
SELECT 150,100,'bfcxb',36.87,'7/4/2010' UNION
SELECT 124,101,'tkczfbag',2.06,'7/14/2010' UNION
SELECT 34,101,'tkczfbag',1.01,'6/23/2010' UNION
SELECT 102,101,'tkczfbag',1.89,'6/29/2010' UNION
SELECT 85,101,'tkczfbag',1.73,'6/25/2010' UNION
SELECT 69,101,'tkczfbag',1.52,'6/24/2010' UNION
SELECT 50,101,'tkczfbag',1.52,'6/24/2010' UNION
SELECT 52,101,'tkczfbag',1.53,'6/24/2010' UNION
SELECT 30,101,'tkczfbag',1.01,'6/23/2010' UNION
SELECT 22,101,'tkczfbag',1.01,'6/23/2010' UNION
SELECT 24,101,'tkczfbag',1.01,'6/23/2010' UNION
SELECT 16,101,'tkczfbag',1.02,'6/23/2010' UNION
SELECT 18,102,'bfcxb',15.15,'6/23/2010' UNION
SELECT 19,102,'bfcxb',15.15,'6/23/2010' UNION
SELECT 25,102,'bfcxb',15.15,'6/23/2010' UNION
SELECT 32,102,'bfcxb',15.15,'6/23/2010' UNION
SELECT 54,102,'bfcxb',15.15,'6/24/2010' UNION
SELECT 36,102,'bfcxb',15.15,'6/23/2010' UNION
SELECT 70,102,'bfcxb',15.15,'6/24/2010' UNION
SELECT 86,102,'bfcxb',15.15,'6/25/2010' UNION
SELECT 103,102,'bfcxb',15.26,'6/29/2010' UNION
SELECT 126,102,'bfcxb',26.94,'7/14/2010' UNION
SELECT 125,103,'tkcmp',24.45,'7/14/2010' UNION
SELECT 104,103,'tkcmp',24.59,'6/29/2010' UNION
SELECT 105,104,'tkcmp',1.69,'6/29/2010' UNION
SELECT 87,104,'tkcmp',1.69,'6/25/2010' UNION
SELECT 71,104,'tkcmp',1.69,'6/24/2010' UNION
SELECT 56,104,'tkcmp',1.69,'6/24/2010' UNION
SELECT 38,104,'tkcmp',1.69,'6/23/2010' UNION
SELECT 31,104,'tkcmp',1.69,'6/23/2010'
Now, as to your question, based on what you've described I think this is what you're looking for:
SELECT ClientID,
ProductName,
CAST(AVG(Size) AS DECIMAL(6,2)) as Size,
CAST(MONTH(MeasuredDate) AS VARCHAR) + '/' + CAST(YEAR(MeasuredDate) AS VARCHAR) as MeasuredDate
FROM #table
GROUP BY ClientID, ProductName, MONTH(MeasuredDate), YEAR(MeasuredDate)
ORDER BY ClientID, YEAR(MeasuredDate), MONTH(MeasuredDate)
July 16, 2010 at 10:43 am
Nevyn thank you that worked.
Okay I'll work on that bteraberry, thanks
I have a different question along the same line... not sure if I should create the separate post or not.
My question is, after finding the average, how can I find the average changes for each client, for example, if we take client 100:
The change from 2/2010 and 3/2010 is 1.27
The change from 3/2010 and 4/2010 is 1.12
The change from 4/2010 and 5/2010 is 0.35
The change from 5/2010 and 6/2010 is -16
The change from 6/2010 and 7/2010 is 1.75
Then calculate the average excluding any negative changes would be: 1.1225
Rounded to 1.12 to be displayed
For Client 101 the change from 6/2010 and 7/2010 is 0.7
Then the average would be (obviously) 0.7
Rounded to 0.7 to be displayed
The display would be
ClientIDProductNameAvrageChange
100bfcxb1.12
101tkczfbag0.7
July 16, 2010 at 1:23 pm
Could you post the script you have so far and the table definition? This one is a tad more complicated, and just easier to show using the actual parameters and such you're using in your query.
But basically what you can do is create a CTE of the above query, and then do a query joining the CTE to itself joining the record from one month to the next where there is an increase, and avg the difference in the values. One thing to be careful of, though, is your rounding. If you round at each stage, then you'll end up with averages of averages and the numbers will be less accurate.
PS> Are these really being run as standalone queries or automated jobs? They seem like reporting style data, and are the type of thing most people would calculate in a reporting tool or custom front-end app, as opposed to having the DB figure out. If you need this at the db level, thats fine, just asking as it seemed odd.
July 16, 2010 at 2:22 pm
Below is the table definition. I wish I had a script to share, but I didn't know where to begin. I even did a search online for similar scenario, but I was not able to find anything.
Not sure what CTE is, but yes, I was planning on using this information as a report.
CREATE TABLE [dbo].[ProdSIZE](
[MeasurID] [int] IDENTITY(10,1) NOT NULL,
[ClientID] [int] NOT NULL,
[ProductName] [nvarchar](128) NOT NULL,
[Size] [float] NOT NULL,
[MeasuredDate] [datetime] NOT NULL,
CONSTRAINT [PK_ProdSIZE] PRIMARY KEY CLUSTERED
July 16, 2010 at 3:09 pm
Are you connecting it to a reporting tool? Crystal or SSRS would probably do this easier than straight SQL, while providing nicer formatting.
Heck, even just doing a raw query output to excel and using formulas there might be better.
A CTE is a 'common table expression'. In this case it just makes things a bit cleaner as were joining a grouped query to itself
Anyway, here is the test that I ran. Looks like it is working
CREATE TABLE #table
(MeasurID INT
,ClientID INT
,ProductName VARCHAR(128)
,Size float
,MeasuredDate DATETIME)
INSERT INTO #table
SELECT 84,100,'bfcxb',48.38,'2/15/2010' UNION
SELECT 101,100,'bfcxb',49.65,'3/22/2010' UNION
SELECT 123,100,'bfcxb',50.77,'4/14/2010' UNION
SELECT 148,100,'bfcxb',51.12,'5/14/2010' UNION
SELECT 149,100,'bfcxb',35.12,'6/8/2010' UNION
SELECT 150,100,'bfcxb',36.87,'7/4/2010' UNION
SELECT 124,101,'tkczfbag',2.06,'7/14/2010' UNION
SELECT 34,101,'tkczfbag',1.01,'6/23/2010' UNION
SELECT 102,101,'tkczfbag',1.89,'6/29/2010' UNION
SELECT 85,101,'tkczfbag',1.73,'6/25/2010' UNION
SELECT 69,101,'tkczfbag',1.52,'6/24/2010' UNION
SELECT 50,101,'tkczfbag',1.52,'6/24/2010' UNION
SELECT 52,101,'tkczfbag',1.53,'6/24/2010' UNION
SELECT 30,101,'tkczfbag',1.01,'6/23/2010' UNION
SELECT 22,101,'tkczfbag',1.01,'6/23/2010' UNION
SELECT 24,101,'tkczfbag',1.01,'6/23/2010' UNION
SELECT 16,101,'tkczfbag',1.02,'6/23/2010' UNION
SELECT 18,102,'bfcxb',15.15,'6/23/2010' UNION
SELECT 19,102,'bfcxb',15.15,'6/23/2010' UNION
SELECT 25,102,'bfcxb',15.15,'6/23/2010' UNION
SELECT 32,102,'bfcxb',15.15,'6/23/2010' UNION
SELECT 54,102,'bfcxb',15.15,'6/24/2010' UNION
SELECT 36,102,'bfcxb',15.15,'6/23/2010' UNION
SELECT 70,102,'bfcxb',15.15,'6/24/2010' UNION
SELECT 86,102,'bfcxb',15.15,'6/25/2010' UNION
SELECT 103,102,'bfcxb',15.26,'6/29/2010' UNION
SELECT 126,102,'bfcxb',26.94,'7/14/2010' UNION
SELECT 125,103,'tkcmp',24.45,'7/14/2010' UNION
SELECT 104,103,'tkcmp',24.59,'6/29/2010' UNION
SELECT 105,104,'tkcmp',1.69,'6/29/2010' UNION
SELECT 87,104,'tkcmp',1.69,'6/25/2010' UNION
SELECT 71,104,'tkcmp',1.69,'6/24/2010' UNION
SELECT 56,104,'tkcmp',1.69,'6/24/2010' UNION
SELECT 38,104,'tkcmp',1.69,'6/23/2010' UNION
SELECT 31,104,'tkcmp',1.69,'6/23/2010'
;
WITH MonthClientCTE (ClientID,ProductName,SizeAvg,QueryMonth)
AS
(SELECT ClientID,ProductName,Avg(Size) AS SizeAvg,dateadd(month, datediff(month, 0, MeasuredDate),0) AS QueryMonth
FROM #table
GROUP BY ClientID,ProductName,dateadd(month, datediff(month, 0, MeasuredDate),0))
SELECT c.ClientID,c.ProductName,isnull(d.SizeAvg,0)
FROM
MonthClientCTE AS c
LEFT OUTER JOIN
(SELECT a.ClientID,a.ProductName,avg(b.SizeAvg-a.SizeAvg) AS SizeAvg
FROM MonthClientCTE AS a
INNER JOIN MonthClientCTE AS b
ON a.ClientID = b.ClientID
and dateadd(month,1,a.QueryMonth)=b.QueryMonth
and b.SizeAvg >= a.SizeAvg
group by a.ProductName,a.clientID)
AS d
ON c.ClientID = d.ClientID
group by d.SizeAvg,c.ProductName,c.clientID
drop table #table
What this does (as briefly explained earlier), get the monthly client totals, then compare each month to the month following it (ignoring any months where the average went down, as you specified), and averages the difference for each client.
The "common table expression" is just a bit cleaner than repeating the original grouping twice as derived tables.
NOTE: there are some assumptions in this solution. It assumes that each client has data for all the months of the report period, and that Size goes up at least once month over month for each client. So for example clients 103 (size goes down) and 104 (one month of data only) don't appear in the results list.
I'll take a quick look at cleaning this up
UPDATEI edited the above so that now it shows a zero average for clients without two months to compare. I did that fix quick and dirty (just rejoined my summary to the CTE with an outer join to get rows for Clients with no avg), so there is probably a better performing way of accomplishing that. If you wanted it to handle skipped months (june and august for a client, no july), the script still won't do that. It is possible to do it that way, but it makes the query very ugly.
July 19, 2010 at 8:43 am
Hey Nevyn thank you so much for your help... this works as I expected it.
Yes, I was thinking about using SSRS... how would you do this in SSRS easier?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply