Aging query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Aging query

Author

Topic

nizguy
Starting Member

37 Posts

Posted - 2011-07-19 : 18:24:50

Hello all, Is anyone can help me on this query?
I want to create a query for the invoice is over 30 days and the over 30 days balance is exceed 50 % of their acct balance

As the sample query below, the result should only return the acctId 1000. which the 30 days balance of 451.00 is over 50% of their acct balance of 335.75

Acctid, Total Balance, 50% of the total Balance, Balance over 30 days
1000, 671.50, 335.75, 451.00
1588, 421.00, 210.50, 170.50

Acctid, Total Balance, 50% of the total Balance, Balance over 30 days
1000, 671.50, 335.75, 451.00

===============================

CREATE TABLE #AR
(
AcctId int not null,
InvNumber int not null,
Balance money not null,
EnteredDate datetime not null
)

INSERT INTO #AR (AcctId, InvNumber, Balance, EnteredDate) VALUES (1000, 300, 150.50, '3/1/11')
INSERT INTO #AR (AcctId, InvNumber, Balance, EnteredDate) VALUES (1000, 301, 50.00, '3/5/11')
INSERT INTO #AR (AcctId, InvNumber, Balance, EnteredDate) VALUES (1000, 302, 250.50, '3/10/11')
INSERT INTO #AR (AcctId, InvNumber, Balance, EnteredDate) VALUES (1000, 303, 100.00, '7/1/11')
INSERT INTO #AR (AcctId, InvNumber, Balance, EnteredDate) VALUES (1000, 304, 120.50, '7/15/11')

INSERT INTO #AR (AcctId, InvNumber, Balance, EnteredDate) VALUES (1588, 310, 80.00, '5/1/11')
INSERT INTO #AR (AcctId, InvNumber, Balance, EnteredDate) VALUES (1588, 311, 90.50, '5/5/11')
INSERT INTO #AR (AcctId, InvNumber, Balance, EnteredDate) VALUES (1588, 312, 30.00, '7/5/11')
INSERT INTO #AR (AcctId, InvNumber, Balance, EnteredDate) VALUES (1588, 313, 100.00, '7/5/11')
INSERT INTO #AR (AcctId, InvNumber, Balance, EnteredDate) VALUES (1588, 314, 120.50, '7/5/11')

SELECT Acctid, sum(balance)
FROM
#AR
WHERE
(datediff(day, getdate(), EnteredDate) < -30)
GROUP BY AcctId

SELECT Acctid, sum(balance)
FROM
#AR
GROUP BY Acctid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-19 : 20:02:06

[code]
select AcctId,
[Total Balance] = sum(Balance),
[50% of the total Balance] = sum(Balance) * 0.5,
[Balance over 30 days] = sum(case when EnteredDate < dateadd(day, datediff(day, 0, getdate()), -30) then Balance else 0 end)
from #AR
group by AcctId
having sum(case when EnteredDate < dateadd(day, datediff(day, 0, getdate()), -30) then Balance else 0 end)
> sum(Balance) * 0.5
[/code]

KH
[spoiler]Time is always against us[/spoiler]

nizguy
Starting Member

37 Posts

Posted - 2011-07-20 : 10:20:02

That is exactly the result I am looking for.

It still a lot of thing for me to learn. I love this forum, people here are very helpful.

Thank you Khtan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources