CODEWITHSUNDEEP
sqldistinct
tequilaras
tequilaras

Reputation: 279

MS SQL distinct function

I have the query below

SELECT distinct
count(patient.id)
CAST(YEAR(completiondate) AS VARCHAR(4)) + '-' +  CAST(MONTH(completiondate) AS VARCHAR(2))   AS Mjesec

FROM ....

WHERE 
incident.completionDate Between Convert(smalldatetime, '01/10/2007', 103) and Convert(smalldatetime, '01/11/2013', 103) 
and servicecharges.serviceid in (47)

and incident.status != 6
and ServiceRequestDescription LIKE 'something'
and chargeDescr = 'test'

group by CAST(YEAR(completiondate) AS VARCHAR(4)) + '-' +  CAST(MONTH(completiondate) AS VARCHAR(2))

the result i take, have unique rows of patient.id AND Year-Month. That means that i can have multiple rows with the same patient.id and several year-month.

how can i change that query, in order to take the results, with each patient.id only once, with the first completiondate with the specific elements?

Upvotes: 1

Views: 190

Answers (2)

code save
code save

Reputation: 1106

Please Try it

WITH CTE AS
(
SELECT *,RN=ROW_NUMBER() OVER (PARTITION BY patient ORDER BY patient DESC) FROM tablename 
)
select * from CTE where RN>1

Upvotes: 0

valex
valex

Reputation: 24144

I guess it should be: 

SELECT 
patient.id
MIN(
CAST(YEAR(completiondate) AS VARCHAR(4)) + '-' +  CAST(MONTH(completiondate) AS VARCHAR(2)) 
)
  AS Mjesec

FROM ....

WHERE 
incident.completionDate Between Convert(smalldatetime, '01/10/2007', 103) and Convert(smalldatetime, '01/11/2013', 103) 
and servicecharges.serviceid in (47)

and incident.status != 6
and ServiceRequestDescription LIKE 'something'
and chargeDescr = 'test'

group by patient.id

Upvotes: 3

Related Questions