Aging Average calculation

Question

jovillanuev 0 Junior Poster in Training

15 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

database-design microsoft:sql-server

Edited 15 Years Ago by jovillanuev because: n/a

5 Contributors
10 Replies
207 Views
1 Day Discussion Span
Latest Post 15 Years Ago Latest Post by sknake

Dani AI

Generated 7 Months Ago

Short version: compute all period averages in one statement with conditional aggregation, and compute cumulative (accumulated) values from per-bucket sums/counts (don’t simply average the bucket averages — use weighted totals). ’s separate SELECTs and ’s single-row subselect approach point in the right direction; this is a compact, safer alternative that also produces a cumulative (running) average per aging bucket.

A single-row result (one column per range) — assumes you already have AgeDays (or compute it once in a derived table):

SELECT
  CAST(AVG(CASE WHEN AgeDays BETWEEN 0 AND 10   THEN AgeDays END) AS DECIMAL(9,2)) AS [0-10],
  CAST(AVG(CASE WHEN AgeDays BETWEEN 11 AND 30  THEN AgeDays END) AS DECIMAL(9,2)) AS [11-30],
  CAST(AVG(CASE WHEN AgeDays BETWEEN 31 AND 60  THEN AgeDays END) AS DECIMAL(9,2)) AS [31-60],
  CAST(AVG(CASE WHEN AgeDays BETWEEN 61 AND 150 THEN AgeDays END) AS DECIMAL(9,2)) AS [61-150]
FROM dbo.MyTable;

If you want per-bucket rows plus a cumulative (accumulated) average that includes all earlier buckets (what you described as “accumulate to succeeding TAT Age”), aggregate by bucket then compute the cumulative average from the sums and counts (works on older SQL Server versions without advanced window-frame syntax):

WITH bucketed AS (
  SELECT CASE
           WHEN AgeDays BETWEEN 0 AND 10   THEN 1
           WHEN AgeDays BETWEEN 11 AND 30  THEN 2
           WHEN AgeDays BETWEEN 31 AND 60  THEN 3
           WHEN AgeDays BETWEEN 61 AND 150 THEN 4
           ELSE 5 END AS bucket_id,
         AgeDays
  FROM dbo.MyTable
),
agg AS (
  SELECT bucket_id, COUNT(*) AS cnt, SUM(AgeDays) AS sum_age, AVG(CAST(AgeDays AS DECIMAL(9,2))) AS avg_age
  FROM bucketed
  GROUP BY bucket_id
)
SELECT a.bucket_id,
       a.avg_age,
       CAST(SUM(b.sum_age) AS DECIMAL(12,2)) / NULLIF(SUM(b.cnt),0) AS cumulative_avg
FROM agg a
JOIN agg b ON b.bucket_id <= a.bucket_id
GROUP BY a.bucket_id, a.avg_age
ORDER BY a.bucket_id;

Notes: use BETWEEN (or >10 AND <=30) to avoid off-by-one errors (see ’s earlier boundaries); handle empty buckets with NULLIF/COALESCE; cast to decimal for precision; if you compute AgeDays with DATEDIFF on-the-fly, compute it once in a derived table or persist it for large datasets to avoid repeated calculations.

All 10 Replies

Misheki 0 Newbie Poster

15 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

15 Years Ago

You need to use GROUP BY clause in your query.

adam_k 239 Master Poster

15 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

Dani AI

Recommended Answers Collapse Answers

All 10 Replies

Recommended Answers