Solved

How do I incorporate a CASE and a COUNT with this SELECT?

Posted on 2016-10-04
6
48 Views
Last Modified: 2016-10-14
I've got a SELECT statement that retrieves a recordset according to the last 45 days worth of activity with that account.

There are times, however, where there is no activity. In those situations, I need to change the criteria from the last 45 days worth of activity to everything that's happened since the last statement was mailed.

The following two SELECT statements work just fine. The first one utilizes a function to determine the date of the last statement. It looks like this:

select LTRIM( case when type = 'a' then 'Adjustment' when type = 'p' then 'Payment' when type = 'c' then 'Charge' end ) as txntype, [date], patientname, description, amount, physicianid, practclaimid, priority, cpt, case when practclaimid = '' then 1 else 0 end as toppriority, balance, firstdos from ( select paymentmethod, payerdesc, type, [date] = case when type = 'c' then dos else posted end, pfirst+' '+plast as patientname, LTRIM( case when type = 'a' then payerdesc when type = 'p' then payerdesc when type = 'c' then ISNULL(cpt, '') +' - '+ ISNULL(left(cptdesc,100),'') end ) as description, amount,'Barton County Memorial Hospital' as physicianid, coalesce(cg.encountercode, '') as practclaimid, case when type = 'c' then 0 else 1 end as priority, left(cptdesc,100) as cptdesc, cpt as cpt, (select sum(amount) from txn where t.chargegroupid = txn.chargegroupid and t.accountid = txn.accountid) as balance, (select min(dos) from txn where t.chargegroupid = txn.chargegroupid and t.accountid = txn.accountid and type = 'c') as firstdos from txn t left join chargegroup cg on t.chargegroupid = cg.id where t.accountid = 12159877 and (cg.status1 not in ('rt','hd','rp','fc') or cg.status1 is null) and cg.id in ( select chargegroupid from txn where accountid = 12159877 group by chargegroupid having max(posted) > dbo.ufn_lastpaperstatementbefore(12159877, cast('2050-01-01' as date)) ) and type<>'c' and reversedref is null ) u order by firstdos, practclaimid, priority,[date] 

Open in new window


Take note of "group by chargegroupid having max(posted) > dbo.ufn_lastpaperstatementbefore(12159877, cast('2050-01-01' as date))." That's how the date range is being established.

The other SELECT looks like this:

select LTRIM( case when type = 'a' then 'Adjustment' when type = 'p' then 'Payment' when type = 'c' then 'Charge' end ) as txntype, [date], patientname, description, amount, physicianid, practclaimid, priority, cpt, case when practclaimid = '' then 1 else 0 end as toppriority, balance, firstdos from ( select paymentmethod, payerdesc, type, [date] = case when type = 'c' then dos else posted end, pfirst+' '+plast as patientname, LTRIM( case when type = 'a' then payerdesc when type = 'p' then payerdesc when type = 'c' then ISNULL(cpt, '') +' - '+ ISNULL(left(cptdesc,100),'') end ) as description, amount,'Barton County Memorial Hospital' as physicianid, coalesce(cg.encountercode, '') as practclaimid, case when type = 'c' then 0 else 1 end as priority, left(cptdesc,100) as cptdesc, cpt as cpt, (select sum(amount) from txn where t.chargegroupid = txn.chargegroupid and t.accountid = txn.accountid) as balance, (select min(dos) from txn where t.chargegroupid = txn.chargegroupid and t.accountid = txn.accountid and type = 'c') as firstdos from txn t left join chargegroup cg on t.chargegroupid = cg.id where t.accountid = 12159877 and (cg.status1 not in ('rt','hd','rp','fc') or cg.status1 is null) and cg.id in ( select chargegroupid from txn where accountid = 12159877 group by chargegroupid having max(txn.created) > DATEADD(DAY, -45, GETDATE()) ) and type<>'c' and reversedref is null ) u order by firstdos, practclaimid, priority,[date] 

Open in new window


Here the range of dates is established by "having max(txn.created) > DATEADD(DAY, -45, GETDATE())."

What I need to do is build a CASE situation where if there is no activity within the last 45 days (max(txn.created) > DATEADD(DAY, -45, GETDATE()) ), THEN use "having max(posted) > dbo.ufn_lastpaperstatementbefore(12159877, cast('2050-01-01' as date))."

How?
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
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:CraigYellick
ID: 41828087
The general form would look like:

   having max(txn.created) > case when {1} then {2} else {3} end

Replace {1} with a boolean expression for when you want to use the date in {2}. Use {3} as the alternate when condition {1} is false.
0
 

Author Comment

by:brucegust
ID: 41828330
Craig!

I'm a pig on roller skates with this stuff, so I appreciate your patience.

Here's my attempt at incorporating your logic:

select LTRIM( case when type = 'a' then 'Adjustment' when type = 'p' then 'Payment' when type = 'c' then 'Charge' end ) as txntype, [date], patientname, description, amount, physicianid, practclaimid, priority, cpt, case when practclaimid = '' then 1 else 0 end as toppriority, balance, firstdos from ( select paymentmethod, payerdesc, type, [date] = case when type = 'c' then dos else posted end, pfirst+' '+plast as patientname, LTRIM( case when type = 'a' then payerdesc when type = 'p' then payerdesc when type = 'c' then ISNULL(cpt, '') +' - '+ ISNULL(left(cptdesc,100),'') end ) as description, amount,'Barton County Memorial Hospital' as physicianid, coalesce(cg.encountercode, '') as practclaimid, case when type = 'c' then 0 else 1 end as priority, left(cptdesc,100) as cptdesc, cpt as cpt, (select sum(amount) from txn where t.chargegroupid = txn.chargegroupid and t.accountid = txn.accountid) as balance, (select min(dos) from txn where t.chargegroupid = txn.chargegroupid and t.accountid = txn.accountid and type = 'c') as firstdos from txn t left join chargegroup cg on t.chargegroupid = cg.id where t.accountid = 12159877 and (cg.status1 not in ('rt','hd','rp','fc') or cg.status1 is null) and cg.id in ( select chargegroupid from txn where accountid = 12159877 group by chargegroupid  case when {max(posted) datediff(d,posted, getDate(), getDate()<=45)>0} then {where max(posted) datediff(d,posted, getDate(), getDate()<=45)} else { having max(posted) > dbo.ufn_lastpaperstatementbefore(12159877, cast('2050-01-01' as date))} end and reversedref is null ) u order by firstdos, practclaimid, priority,[date] 

Open in new window


The error I get is: "Incorrect syntax near the keyword 'case'."

The working SELECT is this:

select LTRIM( case when type = 'a' then 'Adjustment' when type = 'p' then 'Payment' when type = 'c' then 'Charge' end ) as txntype, [date], patientname, description, amount, physicianid, practclaimid, priority, cpt, case when practclaimid = '' then 1 else 0 end as toppriority, balance, firstdos from ( select paymentmethod, payerdesc, type, [date] = case when type = 'c' then dos else posted end, pfirst+' '+plast as patientname, LTRIM( case when type = 'a' then payerdesc when type = 'p' then payerdesc when type = 'c' then ISNULL(cpt, '') +' - '+ ISNULL(left(cptdesc,100),'') end ) as description, amount,'Barton County Memorial Hospital' as physicianid, coalesce(cg.encountercode, '') as practclaimid, case when type = 'c' then 0 else 1 end as priority, left(cptdesc,100) as cptdesc, cpt as cpt, (select sum(amount) from txn where t.chargegroupid = txn.chargegroupid and t.accountid = txn.accountid) as balance, (select min(dos) from txn where t.chargegroupid = txn.chargegroupid and t.accountid = txn.accountid and type = 'c') as firstdos from txn t left join chargegroup cg on t.chargegroupid = cg.id where t.accountid = 12159877 and (cg.status1 not in ('rt','hd','rp','fc') or cg.status1 is null) and cg.id in ( select chargegroupid from txn where accountid = 12159877 group by chargegroupid having max(posted) > dbo.ufn_lastpaperstatementbefore(12159877, cast('2050-01-01' as date)) ) and type<>'c' and reversedref is null ) u order by firstdos, practclaimid, priority,[date] 

Open in new window


Where am I blowing it?
0
 

Author Comment

by:brucegust
ID: 41828344
I'm just now realizing that when I copy and paste the SELECT statements, there are no line breaks.

Here's a text file that makes it a little more legible.
craig.txt
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 11

Accepted Solution

by:
CraigYellick earned 250 total points
ID: 41828360
For starters that's a very complex query with many moving parts. When faced with new syntax challenges I start with a query that's too simple to fail (and keep reducing it until it does not fail). Then add a single change at a time and test that, never making it more complex until it works with the recent change.

In your case I'd isolate the GROUP BY..HAVING query and hard-code each case. That is, don't use a CASE, simply use one alternative expression then the other and ensure that by themselves they work. That way you know if an error occurs when trying to implement the CASE you know it's the CASE syntax and not the expressions.

Specific problems in your statement:  

Don't use curly braces, I used those as delimiters in the sample syntax.

Your CASE WHEN expression is: max(posted) datediff(d,posted, getDate(), getDate()<=45)>0

That's not a valid boolean expression.

When the expression is true, your result is: where max(posted) datediff(d,posted, getDate(), getDate()<=45

That's also not a valid expression.  Same goes for the ELSE clause.

Start with a super simple query that works. Once you get the syntax right you can tweak for your more complex context.

Here's a sample progression using the Northwind sample database, Products table:

select count(*), avg(UnitPrice), CategoryID
from Products
group by CategoryID

select count(*), avg(UnitPrice), CategoryID
from Products
group by CategoryID
having count(*) > 5

select count(*), avg(UnitPrice), CategoryID
from Products
group by CategoryID
having avg(UnitPrice) > 20

select count(*), avg(UnitPrice), CategoryID
from Products
group by CategoryID
having count(*) > case when CategoryID in (1,3,5,7) then 20 else 5 end

Build it one step at a time and solve one problem at a time.
0
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41831361
I don't think you need a CASE statement. IMO you need to test first if records exists within the 45 days periods and if not run the global query. Something like:
IF EXISTS (SELECT 1 
        FROM ...
        WHERE ...
        GROUP BY ...
        HAVING max(txn.created) > DATEADD(DAY, -45, GETDATE()))
    SELECT ...
    FROM ...
    WHERE ...
    GROUP BY ...
    HAVING max(txn.created) > DATEADD(DAY, -45, GETDATE()))

ELSE
     (alternative SELECT here)

Open in new window

0
 

Author Comment

by:brucegust
ID: 41844325
Gentlemen! Sorry for dragging my feet on this! I was able to get it figured out thanks to your input.

I appreciate it!
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can we attach PDF to table 2 46
What does "Between" mean? 6 48
Email SQL Applications not able send to 4 or more recipients 2 36
Query Task 8 22
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

751 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