?
Solved

How can I get this column in my query?

Posted on 2016-11-02
2
Medium Priority
?
80 Views
Last Modified: 2016-11-02
I want to have a column called "patientname" as part of my recordset. My strategy is to include what I'm working on with another query, so the columns have to match.

This works:

working query
This does not:

problem
The challenge is the name. I want to combine the first name (fname) with the last name (lname) and include that as a column in my recordset. But I can't do that without including "patientname" as part of the GROUP BY clause and that's where I'm running into problems.

How can I fix that?

SELECT
'C' AS txntype,
SUM(amount) AS amount,
dos,
pfirst+' '+plast as patientname, //how do I include "patientname" as a column when it's not a field in my table
'Medical Oncology' AS physicianid,
encountercode
FROM [PCAR_Data].[dbo].[txn]
WHERE
type='C'
AND
accountid=9949388
GROUP BY encountercode, dos
having max(txn.created) > DATEADD(DAY, -45, GETDATE())
ORDER by dos ASC
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 53

Accepted Solution

by:
Ryan Chong earned 2000 total points
ID: 41870366
try this:

SELECT
'C' AS txntype,
SUM(amount) AS amount,
dos,
pfirst+' '+plast as patientname, //how do I include "patientname" as a column when it's not a field in my table
'Medical Oncology' AS physicianid,
encountercode
FROM [PCAR_Data].[dbo].[txn]
WHERE
type='C'
AND
accountid=9949388
GROUP BY encountercode, dos, pfirst+' '+plast
having max(txn.created) > DATEADD(DAY, -45, GETDATE())
ORDER by dos ASC
0
 

Author Closing Comment

by:brucegust
ID: 41870430
That'll do it!

Thank you!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

771 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