Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-10-22
3
Medium Priority
?
57 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

597 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