CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlanalytic-functions
wootscootinboogie
wootscootinboogie

Reputation: 8705

Over clause in SQL Server

I have the following query

select * from 
(
        SELECT distinct 
        rx.patid
       ,rx.fillDate
       ,rx.scriptEndDate
       ,MAX(datediff(day, rx.filldate, rx.scriptenddate)) AS longestScript
       ,rx.drugClass
       ,COUNT(rx.drugName) over(partition by rx.patid,rx.fillDate,rx.drugclass) as distinctFamilies
       FROM [I 3 SCI control].dbo.rx
       where rx.drugClass in ('h3a','h6h','h4b','h2f','h2s','j7c','h2e')
       GROUP BY rx.patid, rx.fillDate, rx.scriptEndDate,rx.drugName,rx.drugClass
      
) r
order by distinctFamilies desc

which produces results that look like enter image description here

This should mean that between the two dates in the table the patID that there should be 5 unique drug names. However, when I run the following query:

select distinct *
    from rx 
    where patid = 1358801781 and fillDate between '2008-10-17' and '2008-11-16' and drugClass='H4B'

I have a result set returned that looks like

enter image description here

You can see that while there are in fact five rows returned for the second query between the dates of 2008-10-17 and 2009-01-15, there are only three unique names. I've tried various ways of modifying the over clause, all with different levels of non-success. How can I alter my query so that I only find unique drugNames within the timeframe specified for each row?

Upvotes: 3

Views: 2514

Answers (1)

ic3b3rg
ic3b3rg

Reputation: 14927

Taking a shot at it:

   SELECT DISTINCT
  patid, 
  fillDate, 
  scriptEndDate, 
  MAX(DATEDIFF(day, fillDate, scriptEndDate)) AS longestScript,
  drugClass,
  MAX(rn) OVER(PARTITION BY patid, fillDate, drugClass) as distinctFamilies
FROM (
  SELECT patid, fillDate, scriptEndDate, drugClass,rx.drugName,
  DENSE_RANK() OVER(PARTITION BY patid, fillDate, drugClass ORDER BY drugName) as rn
  FROM [I 3 SCI control].dbo.rx
  WHERE drugClass IN ('h3a','h6h','h4b','h2f','h2s','j7c','h2e')
)x
GROUP BY x.patid, x.fillDate, x.scriptEndDate,x.drugName,x.drugClass,x.rn
ORDER BY distinctFamilies DESC

Not sure if DISTINCT is really necessary - left it in since you've used it.

Upvotes: 3

Related Questions