Solved

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

Posted on 2016-10-04
6
41 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 46

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

911 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

17 Experts available now in Live!

Get 1:1 Help Now