Solved

Query syntax

Posted on 2014-09-19
3
118 Views
Last Modified: 2014-09-19
Hello,

I have a table that contains rows of data for clinics.  Basically each row has the clinicid and then a load of data regarding performance.

There is also a row where the clinicid is -1 (not a valid clinic) and this holds the summarised data for the whole table.

If I want to select a single clinics data, and the summarized data what would the general syntax be?

Give clinicid of 10 I am looking for:
10, clinic specific data, -1, summerised data

Open in new window


Is the query something like the example below, or is there a better syntax

Given that the column names are clinicid, data1, data2, data3 etc.

Select 
@clinicid as ClinicId
Case clinicid = @clinicid then data1 else null end as clinicData1,
Case clinicid = @clinicid then data2 else null end as clinicData2,
Case clinicid = @clinicid then data3 else null end as clinicData3,
-1 as Summary,
Case clinicid = -1 then data1 else null end as SummaryData1,
Case clinicid = -1 then data2 else null end as SummaryData1,
Case clinicid = -1 then data3  else null end as SummaryData1
From MyTable

Open in new window

0
Comment
Question by:soozh
[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 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40332682
Can you post the table structure and a sample of data? And also an example of the expected output?
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 40332743
I am not sure I understand what you are trying to do.  
But perhaps you can use a subquery as follows:

Select ClinicID, Data, (Select ClinicID from MyTable where ClinicID=-1)
from MyTable
Where ClinicID <>-1

The subquery can pull up your summerized data along side your other records.
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 40333788
If you don't mind having 2 rows:
SELECT 	*
FROM	MyTable
WHERE	clinicid IN (@clinicid,-1)

Open in new window

If both records are needed on the same row:
SELECT	*
FROM	(	SELECT 	*
		FROM	MyTable
		WHERE	clinicid = @clinicid
	) a
CROSS
JOIN	(	SELECT 	*
		FROM	MyTable
		WHERE	clinicid = -1
	) b

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

617 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