Aging report - 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 report

Author

Topic

Haz
Starting Member

38 Posts

Posted - 2009-03-15 : 02:01:10

Hi there,

Can anyone help me to fix this..I create summary aging report


SELECT [Customer].*
FROM
(
SELECT 'DB1' as DBName,c.[No_],c.[Name],c.[Payment Terms Code],
(SELECT dcle.Amount 
WHERE (dcle.[Posting Date] BETWEEN  dcle.[Posting Date] AND DATEADD(dd,30,dcle.[Posting Date]) AND
dcle.[Posting Date]  >= '01/01/2008'  and  dcle.[Posting Date]  >= '12/31/2008'))
AS [CurrAmount],
(SELECT dcle.Amount  
WHERE (dcle.[Posting Date] BETWEEN  DATEADD(dd,31,dcle.[Posting Date]) AND DATEADD(dd,60,dcle.[Posting Date]) AND
dcle.[Posting Date]  >= '01/01/2008'   and  dcle.[Posting Date]  >= '12/31/2008'))
AS [31-60Amount],
(SELECT dcle.Amount  
WHERE (dcle.[Posting Date] BETWEEN  DATEADD(dd,61,dcle.[Posting Date]) AND DATEADD(dd,90,dcle.[Posting Date]) AND
dcle.[Posting Date]  >= '01/01/2008'   and  dcle.[Posting Date]  >= '12/31/2008'))
AS [61-90Amount],
(SELECT dcle.Amount  
WHERE (dcle.[Posting Date] BETWEEN  DATEADD(dd,91,dcle.[Posting Date]) AND DATEADD(dd,120,dcle.[Posting Date]) AND
dcle.[Posting Date]  >= '01/01/2008'   and  dcle.[Posting Date]  >= '12/31/2008'))
AS [91-120Amount],
(SELECT dcle.Amount  
WHERE (dcle.[Posting Date] >= DATEADD(dd,121,dcle.[Posting Date]) AND
dcle.[Posting Date]  >= '01/01/2008'   and  dcle.[Posting Date]  >= '12/31/2008'))
AS [Above120Amount]
FROM  [Cust_ Ledger Entry] cle INNER JOIN [Detailed Cust_ Ledg_ Entry] dcle ON 
 cle.[Entry No_] = dcle.[Cust_ Ledger Entry No_] INNER JOIN
[Customer] c ON  cle.[Customer No_] = c.[No_] 
AND(c.Name= 'abc')AND(dcle.[Posting Date] BETWEEN '01/01/2008' AND '12/31/2008') 
)[Customer]

The result that I get like this:
DBName No_ Name PaymentTermsCode CurrAmount 31-60Amount 61-90Amount 91-120Amount Above120Amount
DB1 4952525 ABC 1M(8D) 100 NULL NULL NULL NULL
DB1 4952525 ABC 1M(8D) NULL 200 NULL NULL NULL
DB1 4952525 ABC 1M(8D) NULL NULL 300 NULL NULL
DB1 4952525 ABC 1M(8D) NULL NULL NULL 400 NULL
DB1 4952525 ABC 1M(8D) NULL NULL NULL NULL 500

the result suppose display 1 record only..

DBName No_ Name PaymentTermsCode CurrAmount 31-60Amount 61-90Amount 91-120Amount Above120Amount
DB1 4952525 ABC 1M(8D) 100 200 300 400 500

Thanks and Regards

Haz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-15 : 05:29:56

[code]SELECT 'DB1' as DBName,
c.[No_],
c.[Name],
c.[Payment Terms Code],
SUM(CASE WHEN dcle.[Posting Date] BETWEEN dcle.[Posting Date] AND DATEADD(dd, 30, dcle.[Posting Date]) THEN dcle.Amount ELSE 0 END) AS [CurrAmount],
SUM(CASE WHEN dcle.[Posting Date] BETWEEN DATEADD(dd, 31, dcle.[Posting Date]) AND DATEADD(dd, 60, dcle.[Posting Date]) THEN dcle.Amount ELSE 0 END) AS [31-60Amount],
SUM(CASE WHEN dcle.[Posting Date] BETWEEN DATEADD(dd, 61, dcle.[Posting Date]) AND DATEADD(dd, 90, dcle.[Posting Date]) THEN dcle.Amount ELSE 0 END) AS [61-90Amount],
SUM(CASE WHEN dcle.[Posting Date] BETWEEN DATEADD(dd, 91, dcle.[Posting Date]) AND DATEADD(dd, 120, dcle.[Posting Date]) THEN dcle.Amount ELSE 0 END) AS [91-120Amount]
FROM [Cust_ Ledger Entry] AS cle
INNER JOIN [Detailed Cust_ Ledg_ Entry] AS dcle ON cle.[Entry No_] = dcle.[Cust_ Ledger Entry No_]
AND dcle.[Posting Date] >= '01/01/2008'
AND dcle.[Posting Date] < '1/1/2009'
INNER JOIN [Customer] AS c ON cle.[Customer No_] = c.[No_]
AND c.Name= 'abc'
GROUP BY c.[No_],
c.[Name],
c.[Payment Terms Code][/code]

E 12°55'05.63"
N 56°04'39.26"

Haz
Starting Member

38 Posts

Posted - 2009-03-16 : 10:38:47

Thank you so much.. and I try to make a user can select more than one customer... So I add In operator but I can not predict how many user will select their customer or if a user want to select all their customer name.. any better idea?

..FROM [Cust_ Ledger Entry] AS cle
INNER JOIN [Detailed Cust_ Ledg_ Entry] AS dcle ON cle.[Entry No_] = dcle.[Cust_ Ledger Entry No_]
AND dcle.[Posting Date] >= '01/01/2008'
AND dcle.[Posting Date] < '1/1/2009'
INNER JOIN [Customer] AS c ON cle.[Customer No_] = c.[No_]
AND c.Name IN ('ABC', 'BCD')
GROUP BY c.[No_],c.[Name],c.[Payment Terms Code]

Thanks and Regards,
Haz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 10:54:08

INNER JOIN the table holding the customer information.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 11:16:27

quote:
Originally posted by Haz

Thank you so much.. and I try to make a user can select more than one customer... So I add In operator but I can not predict how many user will select their customer or if a user want to select all their customer name.. any better idea?

..FROM [Cust_ Ledger Entry] AS cle
INNER JOIN [Detailed Cust_ Ledg_ Entry] AS dcle ON cle.[Entry No_] = dcle.[Cust_ Ledger Entry No_]
AND dcle.[Posting Date] >= '01/01/2008'
AND dcle.[Posting Date] < '1/1/2009'
INNER JOIN [Customer] AS c ON cle.[Customer No_] = c.[No_]
AND c.Name IN ('ABC', 'BCD')
GROUP BY c.[No_],c.[Name],c.[Payment Terms Code]

Thanks and Regards,
Haz

just declare a varchar parameter @CustNameList and pass names as comma seperated values like ABC,BCD,...

then use and condition like
AND ',' + @CustNameList + ',' LIKE '%,' + c.Name + ',%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources