• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Adding summary information to aged group items

I have a SQL query (thanks to this forum) that creates a file with the oldest aged item shown in each statement row,

I now need to add a summary line for each line item with totals by aged groups.

I have attached the SQL that I currently use to generate an aged statement and also a spreadsheet that shows the results of the query and additinal fields at the end of each row in red that shows what I need added.

As an example, Cust1 has three rows and each row needs to show the total of all the "Current", "31-60", "61-90", and then the total of the customer buckets.

Any help is appreciated.
declare @cMonth varchar(2)	
declare @cDay varchar(2)	
declare @cYear varchar(4)	
declare @dDateCalc datetime	
declare @cStatementDate varchar(10)	
declare @nTrxAM numeric(18,2)	
	
set @dDateCalc = getdate()	
set @cMonth = RTRIM(MONTH(@dDateCalc))	
set @cDay = RTRIM(Day(@dDateCalc))	
set @cYear = YEAR(@dDateCalc)	
set @cStatementDate = @cYear+'-'+@cMonth+'-'+@cDay	
	
SELECT 	
	RM1.CUSTNMBR as 'CustNumber', 
	RM1.CUSTNAME as 'CustName', 
	RM1.ADDRESS1 as 'CustAdd1', 
	RM1.ADDRESS2 as 'CustAdd2', 
	RM1.ADDRESS3 as 'CustAdd3', 
	RM1.CITY as 'CustCity', 
	RM1.STATE  as 'CustState', 
	RM1.ZIP as 'CustZip', 
	RM2.DOCNUMBR AS 'ReferenceNumber', 
	RM2.RMDTYPAL AS 'RMDPal', 
	RM2.DOCDATE AS 'Date', 
	RM2.ORTRXAMT AS 'OrTRXAmt', 
	RM2.CURTRXAM AS 'CurTRXAmt', 
	RM2.AGNGBUKT AS 'AgingBucket', 
	CN.CRDTMGR AS 'CredManager', 
	RM2.PYMTRMID AS 'Terms', 
	RM2.CSPORNBR AS 'CustomerPO',
	CNR.RepName AS 'CreditRepName',
	CNR.RepPhone AS 'CreditRepPhone',
	CNR.RepEmail AS 'CreditRepEmail',
	CNT.Terms AS 'TermValue',
	(DateDiff(day, RM2.DOCDATE, getdate())) AS 'TermDiff',
	(case when (DateDiff(day, RM2.DOCDATE, getdate())-CNT.Terms) < 30  then RM2.CURTRXAM else 0 end) AS 'Current',
	(case when (DateDiff(day, RM2.DOCDATE, getdate())-CNT.Terms) BETWEEN 30 AND 60 then RM2.CURTRXAM else 0 end) AS '31-60',
	(case when (DateDiff(day, RM2.DOCDATE, getdate())-CNT.Terms) BETWEEN 60 AND 90 then RM2.CURTRXAM else 0 end) AS '61-90',
	(case when (DateDiff(day, RM2.DOCDATE, getdate())-CNT.Terms) > 90  then RM2.CURTRXAM else 0 end) AS 'Greaterthan90',
	T1.Oldest as 'Age',
	@cStatementDate as 'Statement Date'
FROM 	
	KELEG.dbo.CN00500 CN 
	JOIN KELEG.dbo.RM00101 RM1 ON CN.CUSTNMBR = RM1.CUSTNMBR
	JOIN KELEG.dbo.RM20101 RM2 ON RM1.CUSTNMBR = RM2.CUSTNMBR AND CN.CUSTNMBR = RM2.CUSTNMBR AND RM2.CURTRXAM<>0
	JOIN KELEG.dbo.CNTermList CNT ON RM2.PYMTRMID = CNT.TermType
	JOIN KELEG.dbo.CNCreditReps CNR ON CN.CRDTMGR = CNR.CreditCode
JOIN	
	(SELECT R1.CUSTNMBR,MAX(CASE WHEN (DateDiff(day, R2.DOCDATE, getdate())-C.Terms) > 90 then 91
            WHEN (DateDiff(day, R2.DOCDATE, getdate())-C.Terms) BETWEEN 60 AND 90 then 61	
	           WHEN (DateDiff(day, R2.DOCDATE, getdate())-C.Terms) BETWEEN 30 AND 60 then 31
	           WHEN (DateDiff(day, R2.DOCDATE, getdate())-C.Terms) < 30 then 0 end) AS Oldest
	      FROM dbo.RM20101 R2 JOIN dbo.RM00101 R1 ON R2.CUSTNMBR = R1.CUSTNMBR
	      JOIN dbo.CNTermList C ON C.TermType = R2.PYMTRMID  
	     GROUP BY R1.CUSTNMBR) T1 ON T1.CUSTNMBR = RM1.CUSTNMBR
ORDER BY 	
	RM2.CUSTNMBR, 
	RM2.DOCDATE

Open in new window

Statement-ExpertsExchange.xls
0
jdr0606
Asked:
jdr0606
  • 3
  • 2
1 Solution
 
SharathData EngineerCommented:
Check this script. I used a temp table to achieve your expected result.
declare @cMonth varchar(2)	
declare @cDay varchar(2)	
declare @cYear varchar(4)	
declare @dDateCalc datetime	
declare @cStatementDate varchar(10)	
declare @nTrxAM numeric(18,2)	
	
set @dDateCalc = getdate()	
set @cMonth = RTRIM(MONTH(@dDateCalc))	
set @cDay = RTRIM(Day(@dDateCalc))	
set @cYear = YEAR(@dDateCalc)	
set @cStatementDate = @cYear+'-'+@cMonth+'-'+@cDay	
	
SELECT 	
	RM1.CUSTNMBR as 'CustNumber', 
	RM1.CUSTNAME as 'CustName', 
	RM1.ADDRESS1 as 'CustAdd1', 
	RM1.ADDRESS2 as 'CustAdd2', 
	RM1.ADDRESS3 as 'CustAdd3', 
	RM1.CITY as 'CustCity', 
	RM1.STATE  as 'CustState', 
	RM1.ZIP as 'CustZip', 
	RM2.DOCNUMBR AS 'ReferenceNumber', 
	RM2.RMDTYPAL AS 'RMDPal', 
	RM2.DOCDATE AS 'Date', 
	RM2.ORTRXAMT AS 'OrTRXAmt', 
	RM2.CURTRXAM AS 'CurTRXAmt', 
	RM2.AGNGBUKT AS 'AgingBucket', 
	CN.CRDTMGR AS 'CredManager', 
	RM2.PYMTRMID AS 'Terms', 
	RM2.CSPORNBR AS 'CustomerPO',
	CNR.RepName AS 'CreditRepName',
	CNR.RepPhone AS 'CreditRepPhone',
	CNR.RepEmail AS 'CreditRepEmail',
	CNT.Terms AS 'TermValue',
	(DateDiff(day, RM2.DOCDATE, getdate())) AS 'TermDiff',
	(case when (DateDiff(day, RM2.DOCDATE, getdate())-CNT.Terms) < 30  then RM2.CURTRXAM else 0 end) AS 'Current',
	(case when (DateDiff(day, RM2.DOCDATE, getdate())-CNT.Terms) BETWEEN 30 AND 60 then RM2.CURTRXAM else 0 end) AS '31-60',
	(case when (DateDiff(day, RM2.DOCDATE, getdate())-CNT.Terms) BETWEEN 60 AND 90 then RM2.CURTRXAM else 0 end) AS '61-90',
	(case when (DateDiff(day, RM2.DOCDATE, getdate())-CNT.Terms) > 90  then RM2.CURTRXAM else 0 end) AS 'Greaterthan90',
	T1.Oldest as 'Age',
	@cStatementDate as 'Statement Date'
INTO #Temp
FROM 	
	KELEG.dbo.CN00500 CN 
	JOIN KELEG.dbo.RM00101 RM1 ON CN.CUSTNMBR = RM1.CUSTNMBR
	JOIN KELEG.dbo.RM20101 RM2 ON RM1.CUSTNMBR = RM2.CUSTNMBR AND CN.CUSTNMBR = RM2.CUSTNMBR AND RM2.CURTRXAM<>0
	JOIN KELEG.dbo.CNTermList CNT ON RM2.PYMTRMID = CNT.TermType
	JOIN KELEG.dbo.CNCreditReps CNR ON CN.CRDTMGR = CNR.CreditCode
JOIN	
	(SELECT R1.CUSTNMBR,MAX(CASE WHEN (DateDiff(day, R2.DOCDATE, getdate())-C.Terms) > 90 then 91
            WHEN (DateDiff(day, R2.DOCDATE, getdate())-C.Terms) BETWEEN 60 AND 90 then 61	
	           WHEN (DateDiff(day, R2.DOCDATE, getdate())-C.Terms) BETWEEN 30 AND 60 then 31
	           WHEN (DateDiff(day, R2.DOCDATE, getdate())-C.Terms) < 30 then 0 end) AS Oldest
	      FROM dbo.RM20101 R2 JOIN dbo.RM00101 R1 ON R2.CUSTNMBR = R1.CUSTNMBR
	      JOIN dbo.CNTermList C ON C.TermType = R2.PYMTRMID  
	     GROUP BY R1.CUSTNMBR) T1 ON T1.CUSTNMBR = RM1.CUSTNMBR
ORDER BY 	
	RM2.CUSTNMBR, 
	RM2.DOCDATE
 
SELECT t3.*,t2.Total_Current,t2.[Total_31-60],t2.[Total_61-90],t2.Total_Greaterthan90,t2.Total_Statement,t2.Total_Open
  FROM #Temp t3
  JOIN (
         SELECT *,Total_Current + [Total_31-60] + [Total_61-90] + Total_Greaterthan90 AS Total_Statement,
                 [Total_31-60] + [Total_61-90] + Total_Greaterthan90 AS Total_Open 
           FROM (      
                  SELECT CustNumber,
                         SUM([Current]) AS Total_Current,
                         SUM([31-60]) AS [Total_31-60],
                         SUM([61-90]) AS [Total_61-90],
                         SUM(Greaterthan90) AS Total_Greaterthan90
                    FROM #Temp 
                   GROUP BY CustNumber) t1 ) t2
     ON t3.CustNumber = t2.CustNumber 
-- Drop #Temp : Drop the temp table. I commented it if you want to re-run the last query. You can uncomment this.

Open in new window

0
 
jdr0606Author Commented:
You Rock!

Thanks!
0
 
SharathData EngineerCommented:
:) so you got your expected result?
0
 
jdr0606Author Commented:
Fantastic job!

Thanks again!
0
 
SharathData EngineerCommented:
you are most welcome
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now