Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-10-04
6
Medium Priority
?
52 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:Craig Yellick
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 11

Accepted Solution

by:
Craig Yellick earned 1000 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 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

715 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