Over clause in SQL Server

Question

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?

Please explain how your second query relates to your first one. And please expand on your question "How can I alter my query..." - what other columns do you need selected along with drugName, and how do you determine which clmid (for example) should be shown alongside each unique drugName? (I'm assuming that you don't want something simple like "select distinct drugName from rx where patid=xxx ....."!) — Lord Peter, Jan 04 '13 at 20:01
The second query relates to the first one because it's the ACTUAL data in the table. You can see that there are five rows in the second query. These five rows correspond to the `distinctFamilies` column in the first screen cap. You can see, however, that there are in actuality only three unique drug names. — wootscootinboogie, Jan 04 '13 at 20:07
If you are on oracle `count(distinct fieldname1) over(partition by fieldname2)` could have solved your problem. However sqlserver8 will give you an error for 'count(distinct fieldname)' so you have to try a different approach here. — Saju, Jan 04 '13 at 20:29

score 3 · Accepted Answer · edited Jan 04 '13 at 20:47

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.

Over clause in SQL Server

1 Answers1