CFML MSSQL creating an aged receivables report

I need to create an aged receivables report.  Search number is the field that controls the group level,  There may be several invoices under one search number.  I need the normal columns 1-30, 31-60, 61-90, and over 90.  And I need a total of those columns.

So far I have;

<cffunction name="QgetAging" returntype="query">
	<cfset local.team = "">
	<cfquery name="local.team" datasource="#request.dsn#">
	SELECT  *
		FROM   QTsiAging
		WHERE     (Invoice_Type = '') AND (Amount > 0) AND (ISNULL(Date_Cash_In, '') = '') AND (isdate(Invoice_Date) = 1) AND (DATEDIFF(mm, CAST(Invoice_Date AS date),
                      CAST(GETDATE() AS DATE)) <> 0) AND (ISNULL(Active_Flag, '') = '')
		order by search_number
</cfquery>
	<cfreturn local.team>
</cffunction>

The view QTSIaging;
i.Id, i.Search_Number, i.Invoice_Number, i.Amount, i.Invoice_Date, i.Date_Cash_In, i.Billing_Type, i.Invoice_Type, i.Active_Flag, c.Name, 
                      CASE WHEN isnull(Date_Cash_In, '') = '' AND (isdate(Invoice_Date)) = 1 AND (DATEDIFF(mm, CAST(Invoice_Date AS date), CAST(GETDATE() AS DATE))) 
                      <= 30 THEN Amount ELSE 0 END AS d30, CASE WHEN isnull(Date_Cash_In, '') = '' AND (isdate(Invoice_Date)) = 1 AND (DATEDIFF(mm, CAST(Invoice_Date AS date), 
                      CAST(GETDATE() AS DATE))) BETWEEN 31 AND 60 THEN Amount ELSE 0 END AS d60, CASE WHEN isnull(Date_Cash_In, '') = '' AND (isdate(Invoice_Date)) = 1 AND 
                      (DATEDIFF(mm, CAST(Invoice_Date AS date), CAST(GETDATE() AS DATE))) BETWEEN 61 AND 90 THEN Amount ELSE 0 END AS d90, CASE WHEN isnull(Date_Cash_In, 
                      '') = '' AND (isdate(Invoice_Date)) = 1 AND (DATEDIFF(mm, CAST(Invoice_Date AS date), CAST(GETDATE() AS DATE))) > 90 THEN Amount ELSE 0 END AS Dover,
                          (SELECT     SUM(Amount) AS Expr1
                            FROM          dbo.Invoices
                            WHERE      (Search_Number = cs.Id)) AS invoicetotal



<cfinvoke component="control.reporting.cfc.basic" method="QgetAging" returnvariable="AgingInfo" />

	<div class="subHeader"> Aging Report</div>

	<div style="padding:10px 2px 5px 20px; ">
		<table width="1000px">
			<tr>
				<td class="tableHead" width="20px">Client</td>
				<td class="tableHead">Total Invoices</td>
				<td class="tableHead" width="80" align="center">1-30</td>
				<td class="tableHead" width="80" align="center">31-60</td>
				<td class="tableHead" width="80" align="center">61-90</td>
				<td class="tableHead" width="80" align="center">> 90</td>
				<td class="tableHead" width="80" align="center">Total Oustanding</td>
			</tr>
			<cfoutput query="AgingInfo" group="search_number">
			<tr>
				<td>#AgingInfo.search_number# -- #AgingInfo.name#</td>
				<td align="right">#AgingInfo.invoicetotal#</td>
				<td align="right">#AgingInfo.d30#</td>
				<td align="right">#AgingInfo.d60#</td>
				<td align="right">#AgingInfo.d90#</td>
				<td align="right">#AgingInfo.dover#</td>
				<td align="right">#outstanding#</td>
				
			</tr>
			</cfoutput>
		</table>
	</div>

Open in new window



Aging Report
Search     Client                    Total Invoices        1-30        31-60        61-90          > 90  Total Oustanding
197492 -- ABC N.V.                 50000.00              0.00          0.00          0.00  16671.00                     16671
197619 -- XYZ Corporation     81000.00              0.00          0.00          0.00  31000.00                     31000
197629 -- DEF, Inc.                  64800.00              0.00          0.00          0.00  32400.00                     32400
197629 -- DEF, Inc.                  64800.00              0.00          0.00          0.00  32400.00                     32400
197644 -- GFI Energy              74800.00              0.00          0.00          0.00  26000.00                     26000
197644 -- GFI Energy              74800.00              0.00          0.00          0.00  24400.00                     24400
197644 -- GFI Energy              74800.00              0.00          0.00          0.00  24400.00                     24400


I need the report to have one line per search number.  And the total of invoices is a sum of those invoices per search number.  I'm having trouble creating a summary report instead of a detailed report.  I need to sum d30, d50, d90, and dover per search number.
lantervAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
The view QTSIaging is using MONTHS (mm) as the unit inside the DATEDIFF() function, e.g.

DATEDIFF(mm, CAST(Invoice_Date AS date), CAST(GETDATE() AS date))
                  ^^^^

Should these columns: 1-30        31-60        61-90 : refer to DAYS?

Also, you unfortunately don't include the whole query for that view (e.g. there is no FROM clause) so the following contains some guesses about that from clause:
SELECT
      i.Search_Number
    , c.Name
    , SUM(CASE
            WHEN ISNULL(Date_Cash_In, '') = '' AND
                  (ISDATE(Invoice_Date)) = 1 AND
                  (DATEDIFF(DAY, CAST(Invoice_Date AS date), CAST(GETDATE() AS date)))
                  <= 30 THEN
                  Amount
            ELSE
                  0
      END) AS d30
    , SUM(CASE
            WHEN ISNULL(Date_Cash_In, '') = '' AND
                  (ISDATE(Invoice_Date)) = 1 AND
                  (DATEDIFF(DAY, CAST(Invoice_Date AS date),
                  CAST(GETDATE() AS date))) BETWEEN 31 AND 60 THEN
                  Amount
            ELSE
                  0
      END) AS d60
    , SUM(CASE
            WHEN ISNULL(Date_Cash_In, '') = '' AND
                  (ISDATE(Invoice_Date)) = 1 AND
                  (DATEDIFF(DAY, CAST(Invoice_Date AS date), CAST(GETDATE() AS date))) BETWEEN 61 AND 90 THEN
                  Amount
            ELSE
                  0
      END) AS d90
    , SUM(CASE
            WHEN ISNULL(Date_Cash_In,
                  '') = '' AND
                  (ISDATE(Invoice_Date)) = 1 AND
                  (DATEDIFF(DAY, CAST(Invoice_Date AS date), CAST(GETDATE() AS date))) > 90 THEN
                  Amount
            ELSE
                  0
      END) AS Dover
    , SUM(i.Amount) AS invoicetotal
FROM dbo.Invoices i
INNER JOIN dbo.clients c
      ON i.clientid = c.id
GROUP BY
      i.Search_Number
    , c.Name

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lantervAuthor Commented:
I seem to be having a clipboard problem.  At any rate,  I worked it out using your "sum" suggestion.  I had to do it in 2 queries.

SELECT     TOP (100) PERCENT Search_Number, SUM(CASE WHEN isnull(Date_Cash_In, '') = '' AND (isdate(Invoice_Date)) = 1 AND (DATEDIFF(dd, CAST(Invoice_Date AS date), 
                      CAST(GETDATE() AS DATE))) < 31 THEN Amount ELSE 0 END) AS d30, SUM(CASE WHEN isnull(Date_Cash_In, '') = '' AND (isdate(Invoice_Date)) = 1 AND (DATEDIFF(dd, 
                      CAST(Invoice_Date AS date), CAST(GETDATE() AS DATE))) BETWEEN 31 AND 60 THEN Amount ELSE 0 END) AS d60, SUM(CASE WHEN isnull(Date_Cash_In, '') = '' AND 
                      (isdate(Invoice_Date)) = 1 AND (DATEDIFF(dd, CAST(Invoice_Date AS date), CAST(GETDATE() AS DATE))) BETWEEN 61 AND 90 THEN Amount ELSE 0 END) AS d90, 
                      SUM(CASE WHEN isnull(Date_Cash_In, '') = '' AND (isdate(Invoice_Date)) = 1 AND (DATEDIFF(dd, CAST(Invoice_Date AS date), CAST(GETDATE() AS DATE))) 
                      > 90 THEN Amount ELSE 0 END) AS Dover
FROM         dbo.Invoices AS i
WHERE     (Invoice_Type = '') AND (Amount > 0) AND (ISNULL(Date_Cash_In, '') = '') AND (isdate(Invoice_Date) = 1) AND (ISNULL(Active_Flag, '') = '') AND (DATEDIFF(dd, 
                      CAST(Invoice_Date AS date), CAST(GETDATE() AS DATE)) <> 0)
GROUP BY Search_Number


and


SELECT  qa.*, c.name, cs.invoiceterms, (SELECT     SUM(Amount) AS Expr1
                            FROM          dbo.Invoices AS i2
                            WHERE      (i2.Search_Number = qa.Search_Number)) AS invoicetotal
		FROM   QTsiAging qa left join
		       client_searches as cs on cs.id = qa.search_number left join
		       companies as c on c.id = cs.company_number 
		order by search_number

Open in new window

0
lantervAuthor Commented:
thank you
0
PortletPaulfreelancerCommented:
Great, glad it helped.

For performance, in general, I would recommend avoiding "correlated subqueries in the select clause" whenever possible.

Here are 2 alternatives:
SELECT
      qa.*
    , c.name
    , cs.invoiceterms
    , i2.invoicetotal
FROM QTsiAging qa
LEFT JOIN client_searches AS cs ON cs.id = qa.search_number
LEFT JOIN companies AS c ON c.id = cs.company_number
LEFT JOIN (
            SELECT
                  i.Search_Number
                , SUM(i.Amount) AS invoicetotal
            FROM dbo.Invoices AS i
            GROUP BY
                  i.Search_Number
          ) AS i2  ON qa.Search_Number = i2.Search_Number
ORDER BY search_number
;

Open in new window


SELECT
      qa.*
    , c.name
    , cs.invoiceterms
    , oa.invoicetotal
FROM QTsiAging qa
LEFT JOIN client_searches AS cs ON qa.search_number = cs.id
LEFT JOIN companies AS c ON cs.company_number = c.id
OUTER APPLY (
            SELECT
                  SUM(I2.Amount) AS invoicetotal
            FROM dbo.Invoices AS i2 
            WHERE (qa.Search_Number = i2.Search_Number)
           ) as oa
GROUP BY
ORDER BY search_number
;

Open in new window


Personally I would use the first one (with a  "derived table" using group by)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.