Aging Average calculation

[jovillanuev]

Hi Guys,

Kindly please help me on how to this in scripts.

How to calculate and get the average aging based on the Aging period.
I could not figure out on how to do this in script.
btw, even the format is not like my sample it's okay, unless i got the correct result.

Here is my sample data.

MyTable
Total_aging_days 
---------0-10 days
6
8
---------11-30 days
15
25
---------31-60 days
40
55
---------61-150 days
65
120
150


Sample Result|
0-10 days|11-30 days|31-60 days|61-150 days --Aging period 
-------------------------------------------
7|20|47.5|111.66  --avg result from mytable

Thank you in Advance.

Jonel

[Misheki]

Something like this maybe

Select AVG(Total_aging_days) FROM mytable WHERE Total_aging_days <= 10

Select AVG(Total_aging_days) FROM mytable WHERE Total_aging_days <= 30 AND Total_aging_days > 10

Select AVG(Total_aging_days) FROM mytable WHERE Total_aging_days <= 60 AND Total_aging_days > 30

Select AVG(Total_aging_days) FROM mytable WHERE Total_aging_days <= 150 AND Total_aging_days > 60

[jovillanuev]

Something like this maybe

Select AVG(Total_aging_days) FROM mytable WHERE Total_aging_days <= 10

Select AVG(Total_aging_days) FROM mytable WHERE Total_aging_days <= 30 AND Total_aging_days > 10

Select AVG(Total_aging_days) FROM mytable WHERE Total_aging_days <= 60 AND Total_aging_days > 30

Select AVG(Totol_aging_days) FROM mytable WHERE Total_aging_days <= 150 AND Total_aging_days > 60

Can be incorporate to one script..(select statement)

thanks for your reply.

[debasisdas]

You need to use GROUP BY clause in your query.

[adam_k]

Can be incorporate to one script..(select statement)

thanks for your reply.

select * from (select 1 as 'abc' avg(field) from table where .... ) a inner join (select 1as 'abc',avg(field) from ....) b on a.abc = b.abc
inner join ......

[adam_k]

You need to use GROUP BY clause in your query.

No, GROUP BY needs to be used only when columns other than the aggregates are included in the select.

[jovillanuev]

select * from (select 1 as 'abc' avg(field) from table where .... ) a inner join (select 1as 'abc',avg(field) from ....) b on a.abc = b.abc inner join ......

Hi Adam_k.

Meaning i have to create 4 sub query to get all the avg?
How to do it in sql script if the total aging accumulate to the 2nd until the last aging period then
claculate the average per aging period.

Thank you for the reply.

JOnel

[jovillanuev]

Sorry guys, I think my objective is not clear.
Btw, thank you for the reply.

My goal is to obtain the TAT Age, that falls in 20 days, 30 days and >100.
this is like aging. based on my raw data how could i obtain the TAT age base on AgeDays using the script?

MyTABLE
Returndate -- Shipdate --AgeDays
==================================
2011-01-04 -- 2010-12-28 -- 7
2011-01-04 -- 2010-12-27 -- 8
2011-03-20 -- 2011-03-11 -- 9
2011-01-04 -- 2010-12-22 -- 13
2011-01-04 -- 2010-12-18 -- 17
2011-02-07 -- 2011-01-17 -- 21
2011-02-08 -- 2011-01-16 -- 23
2011-03-20 -- 2011-02-12 -- 36
2011-03-20 -- 2011-02-11 -- 37
2011-03-21 -- 2010-12-23 -- 88
2011-03-21 -- 2010-10-28 -- 144

Thank you very much.

Jonel

[sknake]

IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table
GO
Create Table #Table
(
  RecordId int identity(1000, 1) PRIMARY KEY,
  ReturnDate DateTime,
  ShipDate DateTime,
)

SET NOCOUNT ON
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-01-04', '2010-12-28')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-01-04', '2010-12-27')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-03-20', '2011-03-11')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-01-04', '2010-12-22')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-01-04', '2010-12-18')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-02-07', '2011-01-17')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-02-08', '2011-01-16')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-03-20', '2011-02-12')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-03-20', '2011-02-11')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-03-21', '2010-12-23')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-03-21', '2010-10-28');
SET NOCOUNT OFF;


WITH TBL AS
(
Select DateDiff(day, ShipDate, ReturnDate) As Age
From #Table
)

Select
(Select Avg(Age) From TBL Where Age > 0 And Age <= 10) As [Age-0-10],
(Select Avg(Age) From TBL Where Age > 11 And Age <= 30) As [Age-11-30],
(Select Avg(Age) From TBL Where Age > 31 And Age <= 60) As [Age-31-60],
(Select Avg(Age) From TBL Where Age > 61 And Age <= 150) As [Age-31-150]

Age-0-10    Age-11-30   Age-31-60   Age-31-60
----------- ----------- ----------- -----------
8           18          36          116

(1 row(s) affected)

[jovillanuev]

IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table
GO
Create Table #Table
(
  RecordId int identity(1000, 1) PRIMARY KEY,
  ReturnDate DateTime,
  ShipDate DateTime,
)

SET NOCOUNT ON
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-01-04', '2010-12-28')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-01-04', '2010-12-27')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-03-20', '2011-03-11')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-01-04', '2010-12-22')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-01-04', '2010-12-18')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-02-07', '2011-01-17')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-02-08', '2011-01-16')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-03-20', '2011-02-12')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-03-20', '2011-02-11')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-03-21', '2010-12-23')
Insert Into #Table (ReturnDate, ShipDate) Values ('2011-03-21', '2010-10-28');
SET NOCOUNT OFF;


WITH TBL AS
(
Select DateDiff(day, ShipDate, ReturnDate) As Age
From #Table
)

Select
(Select Avg(Age) From TBL Where Age > 0 And Age <= 10) As [Age-0-10],
(Select Avg(Age) From TBL Where Age > 11 And Age <= 30) As [Age-11-30],
(Select Avg(Age) From TBL Where Age > 31 And Age <= 60) As [Age-31-60],
(Select Avg(Age) From TBL Where Age > 61 And Age <= 150) As [Age-31-150]

Age-0-10    Age-11-30   Age-31-60   Age-31-60
----------- ----------- ----------- -----------
8           18          36          116

(1 row(s) affected)

Thank you very much sknake.. Thumbs up.
Btw, its possible to accumulate the value to succeding TAT Age.

ex. from 0-10 value it will accumulate to 11-30 value.

regards,

Jonel

[sknake]

I dont understand what you're asking

Please mark this thread as solved if you have found an answer to your (original) question and good luck!