Solved

How can I get this column in my query?

Posted on 2016-11-02
2
39 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
2 Comments
 
LVL 49

Accepted Solution

by:
Ryan Chong earned 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Export import database 4 41
T-SQL:  Adding two "select" statement results together 1 24
VB6 ListBox Question 4 32
SQL Server Question 5 25
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 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