Solved

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

Posted on 2016-11-12
8
73 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
[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
  • 2
  • +1
8 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41885095
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 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 200 total points
ID: 41885145
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
ID: 41885461
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:brucegust
ID: 41886516
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 41887001
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 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 200 total points
ID: 41887321
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 28

Expert Comment

by:Pawan Kumar
ID: 41905170
Hi,
Any luck with this?
0
 

Author Closing Comment

by:brucegust
ID: 41909368
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

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