Solved

How can I retrieve accounts that haven't done anything within the last 30 days?

Posted on 2016-10-22
3
20 Views
Last Modified: 2016-10-22
I've got a table with a column called "created."

I want to retrieve all the rows in the table that constitute an account that hasn't made a payment within the last 30 days.

Right now, I'm using a SELECT statement that looks like this:

SELECT t.*,
cg.status1
FROM
txn t
INNER JOIN
ChargeGroup cg
ON
t.accountid=cg.AccountID
WHERE t.accountid IN
      (select ac.AccountID
      FROM Account ac
      INNER JOIN
      Communication com
      ON
      ac.AccountID=com.accountid
      WHERE
      ac.PracticeID=194
      AND
      com.ended IS NULL
      AND
      com.method='C')
AND
t.payer='P' AND type='P'
AND
t.created>DATEADD(DAY,-30, GETDATE())
AND cg.status1 IN ('AC','DF', 'PP')

How would I incorporate a CASE that looked to see if any activity has occurred within the last 30 days? Right now, I'm getting a recordset that shows me everything that has happened within the last month, but I need to know all of what SHOULD HAVE happened this month, that didn't happen.

I'm thinking something along the lines of looking at a particular account and see if the "created" date is within the last 30 days. If so, I don't need that in the recordset. If not, that's what I need listed.

How would I pull that off?
0
Comment
Question by:brucegust
  • 2
3 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41855688
Try the following:
SELECT
      ac.*
    , CASE WHEN ca.txn30days IS NULL or ca.txn30days = 0 THEN 'No Activity' ELSE 'Activity' END
FROM Account ac
INNER JOIN Communication com ON ac.AccountID = com.accountid
OUTER APPLY (
      SELECT
            COUNT(*) txn30ays
      FROM txn t
      WHERE ac.accountid = t.AccountID
            AND t.created > DATEADD(DAY, -30, CAST(GETDATE() AS date))
      ) ca
WHERE ac.PracticeID = 194
      AND com.ended IS NULL
      AND com.method = 'C'

Open in new window

{+ edits}
The OUTER APPLY query does not have to count (e.g. it could sum a value instead or as well). Using OUTER APPLY should permit acounts with no activity to remain in the result.

{++edits}
By the way as I don't really know what the desired result will look like I have guessed quite a bit. Fundamental to the solution however is that use the accounts table as the FROM, then OUTER JOIN (or OUTER APPLY) to the transactions. Only through this approach will you learn which accounts have not had activity.

also note that "sample data and "expected result" help us to quickly provide solutions without guessing
0
 

Author Comment

by:brucegust
ID: 41855708
Paul!

Here's what I used:

SELECT
      ac.*
    , CASE WHEN ca.created IS NULL or ca.created = 0 THEN 'No Activity' ELSE 'Activity' END
FROM Account ac
INNER JOIN Communication com ON ac.AccountID = com.accountid
OUTER APPLY (
      SELECT
            COUNT(*) created
      FROM txn t
      WHERE ac.accountid = t.AccountID
            AND t.created > DATEADD(DAY, -30, CAST(GETDATE() AS date))
      ) ca
WHERE ac.PracticeID = 194
      AND com.ended IS NULL
      AND com.method = 'C'

Open in new window


I changed txn30days because it was throwing an error as a non existent column name.

You have the points, but could you explain the purpose and the logic behind OUTER APPLY. I googled it and I understand that it's another approach to a LEFT OUTER JOIN, but could you break it down for me and explain WHY your solution works.

Thanks!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41855720
>>"I changed txn30days because it was throwing an error as a non existent column name."
because I had missed a d
txn30ays
txn30days

the alias formed in the apply needs to be the same in the case expression, so you fixed that error.

==========

As I said earlier the essence of what you needed is an OUTER JOIN
i.e. you want all accounts even if there is no transaction (in recent days)

so to get ALL of the accounts place that table as the "from table"

select ...
FROM accounts

Now, the "issue" faced in the next decision is, do I just want one row per account, or one row per transaction?

If you want one row per transaction =>> use LEFT JOIN
if you want one row per account =>> either (OUTER APPLY & subquery) or (LEFT JOIN & GROUP BY)

so the decision being made at this point are really determined by what the "expected result" is

I choose "one row per account" and that I didn't need a group by so OUTER APPLY works in that set of circumstances.

The "apply operator" was really invented to efficiently allow table valued functions into a query
https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

but they are useful for correlated subqueries as well. An added bonus is that any column aliases defined in them can be used elswhere in the query jsut like a normal column alias.

an OUTER apply allows NULLS to be returned and the original row will still be in the final result.
(but a CROSS apply stops original rows from appearing if the subqury has returned null)

Think of this as an alternative to what I did with the OUTER apply:

SELECT
      ac.*
    , (
      SELECT
            COUNT(*) created
      FROM txn t
      WHERE ac.accountid = t.AccountID
            AND t.created > DATEADD(DAY, -30, CAST(GETDATE() AS date))
      )  [activity]
FROM Account ac
INNER JOIN Communication com ON ac.AccountID = com.accountid
WHERE ac.PracticeID = 194
      AND com.ended IS NULL
      AND com.method = 'C'

This is a classic example of a "correlated subquery" BUT it isn't nearly as efficient because it is done as part of the SELECT lause instead as part of the FROM clause & you cannot re-use the column alias [activity] anywhere in that query except an order by clause.

I hope this helps
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Full Text Search string 5 33
Query Missing Money orders... 6 72
Azure SQL DB? 3 21
SQL Server 2012 Express to Full 5 25
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now