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

x
?
Solved

How Can I Add a Row to this SELECT?

Posted on 2016-11-01
2
Medium Priority
?
76 Views
Last Modified: 2016-11-02
Here's the SELECT...

FYI: This is a mess, but I figured it would be wise to start with the whole thing and then break it down to that place where I want to make a change.

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, 
total_charge 
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,
	'Medical Oncology' 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 and type='C' GROUP by type) as total_charge, 
	(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
	 --type<>'C'
	 --AND
	 t.accountid = 9949388 and (cg.status1 not in ('rt','hd','rp','fc') or cg.status1 is null) 
	 AND cg.id in ( select chargegroupid from txn where accountid = 9949388 
	 GROUP BY chargegroupid having max(txn.created) > DATEADD(DAY, -45, GETDATE()) ) and reversedref is null 
	 ) u 
ORDER BY firstdos, practclaimid, priority,[date], amount 

Open in new window


The results look like the spreadsheet that I have attached.

When you look at the spreadsheet, you'll see how I've got "charges" highlighted in grey...

charges
Those same rows are "combined" in the second tab of the same spreadsheet that's entitled "new and improved."

Smell that?

That's the aroma of a different kind of SELECT!

Now, here' what I've got planned:

I want to take the original SELECT and get everything except those rows that have a "C" type. 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, 
total_charge 
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,
	'Medical Oncology' 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 and type='C' GROUP by type) as total_charge, 
	(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
	 type<>'C'
	 AND
	 t.accountid = 9949388 and (cg.status1 not in ('rt','hd','rp','fc') or cg.status1 is null) 
	 AND cg.id in ( select chargegroupid from txn where accountid = 9949388 
	 GROUP BY chargegroupid having max(txn.created) > DATEADD(DAY, -45, GETDATE()) ) and reversedref is null 
	 ) u 
ORDER BY firstdos, practclaimid, priority,[date], amount 

Open in new window


I still have my "total_charge," even though I'm not listing any of the "C" transaction types. Not sure if that makes a difference, but I was glad to see that when I first started playing with this.

What I'm thinking would be good now would be to add a row for every for every pactclaimed so the end result is what I have on the tab on my spreadsheet entitled "new and improved."

What I'm envisioning (I think) is a UNION of two SELECT statements into one, glorious recordset.

How?
experts_exchange.xlsx
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
2 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 41869134
I can't tell how you decided which charges to combine as "charge to patient" and which you left separate.

But you can reduce overhead and get all the two sums and the min in a single query:

...
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,
      'Medical Oncology' 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,
    txn_totals.total_charge, txn_totals.balance, txn_totals.firstdos
    FROM
    txn t inner join (
           SELECT chargegroupid, accountid,
               sum(case when type = 'C' then amount else 0 end) as total_charge,
               sum(amount) as balance,
               min(case when type = 'C' then dos end) as firstdos
           FROM txn
           GROUP BY chargegroupid, accountid
       ) as txn_totals on txn_totals.chargegroupid = t.chargegroupid and txn_totals.accountid = t.accountid
       left join chargegroup cg on t.chargegroupid = cg.id
      
       WHERE
       t.accountid = 9949388 and (cg.status1 not in ('rt','hd','rp','fc') or cg.status1 is null)
       AND cg.id in ( select chargegroupid from txn where accountid = 9949388
       GROUP BY chargegroupid having max(txn.created) > DATEADD(DAY, -45, GETDATE()) )
       AND reversedref is null
       ) u
ORDER BY firstdos, practclaimid, priority,[date], amount
0
 

Author Closing Comment

by:brucegust
ID: 41870349
Thanks, Scott!

I've got another question - would love your expertise on it!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.

722 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