Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 58
  • Last Modified:

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

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
brucegust
Asked:
brucegust
  • 3
  • 2
2 Solutions
 
Craig YellickDatabase ArchitectCommented:
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
 
brucegustPHP DeveloperAuthor Commented:
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
 
brucegustPHP DeveloperAuthor Commented:
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
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!

 
Craig YellickDatabase ArchitectCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
brucegustPHP DeveloperAuthor Commented:
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now