January 4, 2017 at 11:43 am
I have the following query where a column A can be the same but I have an order in which I want them returned based on values in a column B. Below is what I have but I'm getting an error
select distinct GCM_BUSS_FUNC_NAME,chart_finder_id, hic , HP_MBR_ID , MBR_LAST_NAME , MBR_FIRST_NAME, SOURCE_SYSTEM_PROV_ID , PROVIDER_TIN, EXTRACT(YEAR FROM DOS_THRU_DT) AS dos, GCM_PROJECT_YEAR
from GCMGODS.GCM_RPT_CHARTOPS_MV
where GCM_BUS_FUNC_STATUS = 'LOGICAL REJECT'
and GCM_CLIENT_CD = 'HUMANA' and GCM_PROJECT_YEAR > 2015
and Case When ((GCM_BUSS_FUNC_NAME = 'SLR') then true
ELSE When (GCM_BUSS_FUNC_NAME = 'Coding') then true
Else when (GCM_BUSS_FUNC_NAME = 'Retrieval') then true
END)
January 4, 2017 at 11:44 am
You need to provide an error message, and some sample data with the results you are getting and the results you need.
We can't see your database or data.
January 4, 2017 at 11:55 am
Ok, say the table is as such
City State
Danville KY
Danville FL
Danville TN
Marthasville KY
Marthasville FL
I want to select the row based on the state column value in the following order: TN, FL, KY
the query results would be as such
Danville TN
Marthasville FL
January 4, 2017 at 12:24 pm
This can be accomplished with a ROW_NUMBER().
DECLARE @cities TABLE (
city VARCHAR(25),
stCHAR(2)
)
INSERT @cities(city, st)
VALUES
('Danville', 'KY')
,('Danville', 'FL')
,('Danville', 'TN')
,('Marthasville', 'KY')
,('Marthasville', 'FL')
;
WITH cities AS (
SELECT c.city, c.st, ROW_NUMBER() OVER(PARTITION BY c.city ORDER BY s.state_priority) AS rn
FROM @cities c
INNER JOIN (
SELECT st,state_priority
FROM (
VALUES
('TN', 1)
,('FL', 2)
,('KY', 3)
) state_priorities(st, state_priority)
) s
ON c.st = s.st
)
SELECT c.city, c.st
FROM cities c
WHERE c.rn = 1
I've used a derived table to set the priorities, but you could also use a CASE statement to set the priorities.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 4, 2017 at 12:28 pm
dndaughtery (1/4/2017)
I have the following query where a column A can be the same but I have an order in which I want them returned based on values in a column B. Below is what I have but I'm getting an errorselect distinct GCM_BUSS_FUNC_NAME,chart_finder_id, hic , HP_MBR_ID , MBR_LAST_NAME , MBR_FIRST_NAME, SOURCE_SYSTEM_PROV_ID , PROVIDER_TIN, EXTRACT(YEAR FROM DOS_THRU_DT) AS dos, GCM_PROJECT_YEAR
from GCMGODS.GCM_RPT_CHARTOPS_MV
where GCM_BUS_FUNC_STATUS = 'LOGICAL REJECT'
and GCM_CLIENT_CD = 'HUMANA' and GCM_PROJECT_YEAR > 2015
and Case When ((GCM_BUSS_FUNC_NAME = 'SLR') then true
ELSE When (GCM_BUSS_FUNC_NAME = 'Coding') then true
Else when (GCM_BUSS_FUNC_NAME = 'Retrieval') then true
END)
A CASE expression cannot return a Boolean value (true/false), because SQL Server does not support the Boolean data type. Also, your CASE expression is evaluating the rows separately, but you need to evaluate them in the context of other rows with the same value.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply