Solved

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

Posted on 2016-10-04
6
36 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 45

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 43
sql server query? 6 28
Caste datetime 2 22
select over clause 1 0
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

759 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

14 Experts available now in Live!

Get 1:1 Help Now