How to calculate average of qty sold the last 6 months or less
-
Table [Sold]
ID PID Qty Date
1 1 8 jun 1, 2010
2 3 5 jul 1, 2010
3 3 2 aug 1, 2010
4 1 1 sep 1, 2010
5 3 4 sep 30, 2010
6 2 3 oct 8, 2011
7 2 5 nov 1, 2011
8 3 2 dec 1, 2011
9 1 8 jan 1, 2011
10 2 5 feb 1, 2011Hi all, I have difficulty composing a rather complex (for me) query. I would like to know the average qty sold of each PID per day based on the last 6-month data. If data is less than 6 month, then adjust accordingly. So on the data above: Today: Mar 24, 2011 6-month cut-off: Sep 24, 2010 I want to get:
PID Qty/day
1 8/182 = 0.0440
2 (3+5+5)/168 = 0.0774 -> 168: because the oldest sale is oct 8
3 (4+2)/182 = 0.0330Is there any way I can do this? Somebody help me, please? Thanks in advance.
-
Table [Sold]
ID PID Qty Date
1 1 8 jun 1, 2010
2 3 5 jul 1, 2010
3 3 2 aug 1, 2010
4 1 1 sep 1, 2010
5 3 4 sep 30, 2010
6 2 3 oct 8, 2011
7 2 5 nov 1, 2011
8 3 2 dec 1, 2011
9 1 8 jan 1, 2011
10 2 5 feb 1, 2011Hi all, I have difficulty composing a rather complex (for me) query. I would like to know the average qty sold of each PID per day based on the last 6-month data. If data is less than 6 month, then adjust accordingly. So on the data above: Today: Mar 24, 2011 6-month cut-off: Sep 24, 2010 I want to get:
PID Qty/day
1 8/182 = 0.0440
2 (3+5+5)/168 = 0.0774 -> 168: because the oldest sale is oct 8
3 (4+2)/182 = 0.0330Is there any way I can do this? Somebody help me, please? Thanks in advance.
select PID
,sum(qty)/(sysdate-min(date)) <<-- fixed this to account for sign.
from (
select PID
,qty
,date
from your_table
where date >= sysdate - 182
)
group by PID;:)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
modified on Thursday, March 24, 2011 9:13 AM
-
Table [Sold]
ID PID Qty Date
1 1 8 jun 1, 2010
2 3 5 jul 1, 2010
3 3 2 aug 1, 2010
4 1 1 sep 1, 2010
5 3 4 sep 30, 2010
6 2 3 oct 8, 2011
7 2 5 nov 1, 2011
8 3 2 dec 1, 2011
9 1 8 jan 1, 2011
10 2 5 feb 1, 2011Hi all, I have difficulty composing a rather complex (for me) query. I would like to know the average qty sold of each PID per day based on the last 6-month data. If data is less than 6 month, then adjust accordingly. So on the data above: Today: Mar 24, 2011 6-month cut-off: Sep 24, 2010 I want to get:
PID Qty/day
1 8/182 = 0.0440
2 (3+5+5)/168 = 0.0774 -> 168: because the oldest sale is oct 8
3 (4+2)/182 = 0.0330Is there any way I can do this? Somebody help me, please? Thanks in advance.
Assuming you are using you are using sql server
SELECT
PID,
AVG(qty)/DATEDIFF (d,MAX(date), MIN(date))
FROM TableName
WHERE date > DATEADD(-6, GETDATE())
GROUP BY pidThe where clause may not be as accurate as you need. I build this by breaking the requirements down into parts Get the average and no of days for each pid do the calc Then I combined the 2 queries. Note this is untested code.
Never underestimate the power of human stupidity RAH
-
Assuming you are using you are using sql server
SELECT
PID,
AVG(qty)/DATEDIFF (d,MAX(date), MIN(date))
FROM TableName
WHERE date > DATEADD(-6, GETDATE())
GROUP BY pidThe where clause may not be as accurate as you need. I build this by breaking the requirements down into parts Get the average and no of days for each pid do the calc Then I combined the 2 queries. Note this is untested code.
Never underestimate the power of human stupidity RAH
Thanks a lot. Your answer pointed me to the answer I am looking for.
-
select PID
,sum(qty)/(sysdate-min(date)) <<-- fixed this to account for sign.
from (
select PID
,qty
,date
from your_table
where date >= sysdate - 182
)
group by PID;:)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
modified on Thursday, March 24, 2011 9:13 AM
Thanks a lot. Your answer pointed me to the answer I am looking for.
-
select PID
,sum(qty)/(sysdate-min(date)) <<-- fixed this to account for sign.
from (
select PID
,qty
,date
from your_table
where date >= sysdate - 182
)
group by PID;:)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
modified on Thursday, March 24, 2011 9:13 AM
If there is only one entry for a PID and the sell was today, then date == sysdate --> Division by zero ...
-
If there is only one entry for a PID and the sell was today, then date == sysdate --> Division by zero ...
But good catch. Off the top of my head, it was the best I could figure out. Implementation details are left to the user to decide upon. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
But good catch. Off the top of my head, it was the best I could figure out. Implementation details are left to the user to decide upon. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
But wouldn't this be worth a vote ...