Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How can I get this column in my query?

Posted on 2016-11-02
2
Medium Priority
?
89 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

636 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