Solved

Would coalesce be what I use here and, if so, how?

Posted on 2016-11-12
8
38 Views
Last Modified: 2016-12-01
I've got some scenarios where the first and last name is NULL. I'm assuming, because of the way I've got things grouped, that is why I get some extra rows.

I say "extra rows," because when I eliminate the first and last name criteria, I get 13 rows. When I ask for first and last name, I get 18. The extra rows seem to be coming from those situations where the first and last names are blank. Take a look:

null rows
I need to qualify the "patientname" somehow. Basically, I need the SELECT to go through every encountercode and grab the first name and the last name that isn't empty and not establish a new grouping based on an empty column.

How would I do that?
0
Comment
Question by:brucegust
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
You can use COALESCE() or ISNULL(), but you need to do that on each column, like this:

COALESCE(t1.pfirst,'something') + ' ' + COALESCE(t1.plast,'something')

I don't know whet you would use if either is missing so I have simply used the string "something". In that position you could have a column reference instead.

{+edit}
but I am not sure the number of rows you are getting is determined by the names, there are claimids and dates that are unique to the 3 rows with no name
0
 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 200 total points
Comment Utility
You can use ISNULL or Coalesce in this case. I prefer ISNULL since it is T-SQL function. It is a way of replacing NULL values.

Coalesce and Null are used to check for NULL values and replace them with the new value.

IsNULL can only takes only 2 parameters and Coalesce can take variable number (1-N) of parameters. ISNULL is a T-SQL function and Coalesce is ANSI SQL standard.

Since you dont want change anything in the grouping, You can use one of the below-, I prefer third one if you are using SQL 2012+

--

1. ISNULL(t1.pfirst,'') + '' + ISNULL(t1.plast,'') PatientName

2. CONCAT( ISNULL(t1.pfirst,'') , '' , ISNULL(t1.plast,'') ) PatientName

3. CONCAT( t1.pfirst , '' , t1.plast ) PatientName

--

Open in new window



Notes

- You can eliminate as keyword before column name in each column selected.

E.g. ISNULL(t1.pfirst,'') + '' + ISNULL(t1.plast,'') PatientName

You can also use Concat function instead of adding column values with + sign. E.g. below- << SQL 2012 + >>

E.g. CONCAT( ISNULL(t1.pfirst,'') , '' , ISNULL(t1.plast,'') ) PatientName

- You can also use CONCAT Directly E.g. below- << SQL 2012 + >>



E.g. CONCAT( t1.pfirst , '' , t1.plast ) PatientName
SELECT CONCAT(NULL,'',NULL)
O/p
(No column name)

------------------------




Hope it helps !!
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 50 total points
Comment Utility
Looking at the image in the three rows with NULLs in the names the practclaimid values are different, so the NULLs is not the reason you're getting extra rows.   Just for kicks and giggles please post the full query in a code block so we can pursue that, as the fragment that's visible only shows one table.
0
 

Author Comment

by:brucegust
Comment Utility
OK, guys! We're getting closer!

Here's what I attempted, based on your collective wisdom.

I'm thinking that what I need to do is shift gears and not worry about COALESCE as much as I just need to use MAX. That way, I'm not wasting time worrying about NULL values, I'm just going for those records that have the name and not concerning myself with NULLs.

Now, provided that dog will hunt, here's where I'm at:

SELECT
'Charge' AS txntype,
cast(min(case when t1.type = 'c' then dos else null end) as date) as dos,
MAX(t1.pfirst)+' '+MAX(t1.plast) as patientname,
'charge to account' as description,
sum(case when t1.type = 'c' then amount else 0 end) as amount, 
'Medical Oncology' as physicianid,    
t1.encountercode AS practclaimid,
'0' as priority, 
'' AS cpt, 
'' AS toppriority, 
'' AS balance, 
cast(min(case when t1.type = 'c' then dos else null end) as date) as firstdos 
FROM
 txn t1
LEFT JOIN
chargegroup cg
ON
t1.chargegroupid = cg.id 
/* Charge description join */ 
LEFT JOIN
	( 
	SELECT 
	max(case when (txn.type = 'C') then amount else null end ) as max_charge_amount, 
	accountid, ChargeGroupID 
	
	FROM
	txn group by accountid, chargegroupid ) t2 
	ON
	t2.accountid = t1.accountid 
	AND t2.chargegroupid = t1.chargegroupid 
	WHERE
	t1.accountid = 9949796
	AND 
	reversedref is null 
	AND 
	(cg.status1 not in('rt','hd','rp','fc') or cg.status1 is null) 
	AND cg.id in (select chargegroupid 
	FROM
	txn where accountid = 9949796
	) 
GROUP BY
t1.encountercode, 
pfirst+' '+plast 
HAVING datediff(d, max(t1.created), getDate()) <= 45 or sum(amount) <> 0 
ORDER BY 
dos

Open in new window


Here's where I'm using my MAX idea. The result is 18 rows. I have that result in the attached spreadsheet.

Here's the exact same query sans the first and last name:

SELECT
'Charge' AS txntype,
cast(min(case when t1.type = 'c' then dos else null end) as date) as dos,
--MAX(t1.pfirst)+' '+MAX(t1.plast) as patientname,
'charge to account' as description,
sum(case when t1.type = 'c' then amount else 0 end) as amount, 
'Medical Oncology' as physicianid,    
t1.encountercode AS practclaimid,
'0' as priority, 
'' AS cpt, 
'' AS toppriority, 
'' AS balance, 
cast(min(case when t1.type = 'c' then dos else null end) as date) as firstdos 
FROM
 txn t1
LEFT JOIN
chargegroup cg
ON
t1.chargegroupid = cg.id 
/* Charge description join */ 
LEFT JOIN
	( 
	SELECT 
	max(case when (txn.type = 'C') then amount else null end ) as max_charge_amount, 
	accountid, ChargeGroupID 
	
	FROM
	txn group by accountid, chargegroupid ) t2 
	ON
	t2.accountid = t1.accountid 
	AND t2.chargegroupid = t1.chargegroupid 
	WHERE
	t1.accountid = 9949796
	AND 
	reversedref is null 
	AND 
	(cg.status1 not in('rt','hd','rp','fc') or cg.status1 is null) 
	AND cg.id in (select chargegroupid 
	FROM
	txn where accountid = 9949796
	) 
GROUP BY
t1.encountercode 
--pfirst+' '+plast 
HAVING datediff(d, max(t1.created), getDate()) <= 45 or sum(amount) <> 0 
ORDER BY 
dos

Open in new window


I have a spreadsheet that details the results of both queries. Here's the bottom line:

I've got four new encountercodes. Jim, you mentioned the presence of "new" data. You're right! But why would simply adding another column (patientname) effect the number of rows?

The other row that's being added is an encountercode that is part of the correct recordset (13 rows), but it's been duplicated for some reason.

What do you think?
ee.xlsx
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 48

Accepted Solution

by:
PortletPaul earned 250 total points
Comment Utility
Every column included in a GROUP BY clause can cause additional rows. GROUP BY will form a row for each UNIQUE COMBINATION of data VALUES that the from and where clauses provide to it.

We are not in a position to know your data model, but it seems odd to me that you can have transactions with no patient name. Arbitrarliy forcing a name againat those via MAX() might be wrong.... or it might be ok. I just cannot be sure which.

Providing query results does not help in my opinion. Sample data for each source table might help if you are also able to manually prepare the correct expected output from that sample of data.
0
 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 200 total points
Comment Utility
Try this.. Add a ON clause and Group By.. see if you get same records with and without the group by ISNULL(pfirst,'')+ISNULL(plast,'')

SELECT
'Charge' AS txntype,
cast(min(case when t1.type = 'c' then dos else null end) as date) as dos,
MAX(t1.pfirst)+' '+MAX(t1.plast) as patientname,
'charge to account' as description,
sum(case when t1.type = 'c' then amount else 0 end) as amount,
'Medical Oncology' as physicianid,    
t1.encountercode AS practclaimid,
'0' as priority,
'' AS cpt,
'' AS toppriority,
'' AS balance,
cast(min(case when t1.type = 'c' then dos else null end) as date) as firstdos
FROM txn t1 LEFT JOIN chargegroup cg
ON t1.chargegroupid = cg.id
/* Charge description join */
LEFT JOIN
      (
      SELECT
      max(case when (txn.type = 'C') then amount else null end ) as max_charge_amount,
      accountid, ChargeGroupID
      
      FROM
      txn group by accountid, chargegroupid ) t2
      ON
      t2.accountid = t1.accountid
      AND t2.chargegroupid = t1.chargegroupid
      WHERE
      t1.accountid = 9949796
      AND
      reversedref is null
      AND
      (cg.status1 not in('rt','hd','rp','fc') or cg.status1 is null)
      AND cg.id in (select chargegroupid
      FROM
      txn where accountid = 9949796
      )  ON CLAUSE MISSING
GROUP BY
t1.encountercode , ISNULL(pfirst,'')+ISNULL(plast,'')
HAVING datediff(d, max(t1.created), getDate()) <= 45 or sum(amount) <> 0
ORDER BY
dos
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Hi,
Any luck with this?
0
 

Author Closing Comment

by:brucegust
Comment Utility
Hey, guys!

Sorry for dragging my feet in getting back with you on this. I had my counterpart who's a DBA help me out and the bottom line was the GROUP BY clause. That was adding the additional rows. He was able to craft a query that worked so, between your input and his assistance, the query is working exactly as it needs to.

Thanks!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 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