Aging Average calculation

Question

jovillanuev 0 Junior Poster in Training

12 Years Ago

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

mssql

Edited 12 Years Ago by jovillanuev because: n/a

5 Contributors
10 Replies
193 Views
1 Day Discussion Span
Latest Post 12 Years Ago Latest Post by sknake

All 10 Replies

Misheki 0 Newbie Poster

12 Years Ago

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

debasisdas 580 Posting Genius

12 Years Ago

You need to use GROUP BY clause in your query.

adam_k 239 Master Poster

12 Years Ago

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 ......

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

jovillanuev 0 Junior Poster in Training · Answer 1 · 2011-07-13T17:45:41+00:00

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.

adam_k 239 Master Poster · Answer 2 · 2011-07-14T05:07:23+00:00

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 0 Junior Poster in Training · Answer 3 · 2011-07-14T06:22:50+00:00

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 0 Junior Poster in Training · Answer 4 · 2011-07-14T07:17:24+00:00

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 1,622 Senior Poster Featured Poster · Answer 5 · 2011-07-14T11:04:27+00:00

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 0 Junior Poster in Training · Answer 6 · 2011-07-14T12:55:12+00:00

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 1,622 Senior Poster Featured Poster · Answer 7 · 2011-07-14T20:36:06+00:00

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!

Aging Average calculation

Recommended Answers Collapse Answers

All 10 Replies

Recommended Answers