2577e5fb   
   From: reb01501@NOyahooSPAM.com   
      
   Harry wrote:   
   > On Aug 23, 2:11 pm, "Bob Barrows"  wrote:   
   >> Harry wrote:   
   >>> I have a table with three columns:   
   >>   
   >>> Name Date Action   
   >>> Fred Today this   
   >>> Fred Today this   
   >>> Fred Today that   
   >>> Wilma Today this   
   >>   
   >>> I want to sort by name and by date, but only if 'this' was done more   
   >>> than once on the same day, and not if 'that' was done on the same   
   >>> day,They have to perform this more than once, and they cannot have   
   >>> performed that on the same day.   
   >>   
   >> So the desired result from this sample data would be an empty   
   >> resultset? What do you want to display when the data actually   
   >> contains rows that meet your requirement? All the rows for that name   
   >> and date? Or a single row for that name and date?   
   >> Why don't you try again. Show us some sample data that includes data   
   >> that meets your requirements and then show us what you want the   
   >> desired query to return. The key being "show" - don't just describe   
   >> the desired result: show it to us.   
   >   
   > The raw data would be:   
   >   
   > Name     Date       Proc   
   > Fred         1            stent   
   > Fred         1            stent   
   > Fred         1            unstent   
   > Wilma       2           stent   
   > Wilma       2           stent   
   > Barney      2           stent   
   > Barnet       2           stent   
   > Barney       2          stent   
   > Barney       2          unstent   
   > ...   
   >   
   >   
   > The query would return   
   >   
   > Wilma       2           stent   
   > Wilma       2           stent   
   >   
   >   
   > Because Fred and Barney also had a stent pulled on the same day they   
   > had more than one stent inserted.   
   >   
   Assuming SQL 2005 or greater:   
      
   WITH q AS (   
   SELECT Name,Date   
   ,SUM(CASE WHEN Proc='stent' THEN 1 ELSE 0 END) stents   
   ,SUM(CASE WHEN Proc='unstent' THEN 1 ELSE 0 END) unstents   
   FROM tablename   
   GROUP BY Name, Date)   
   ,q2 AS (   
   SELECT Name,Date FROM q WHERE stents>1 AND unstents=0)   
   SELECT t.Name,t.Date,t.Proc FROM tablename t   
   JOIN q2 ON t.Name=q2.Name and t.Date=q2.Date   
      
   This is untested air code (obviously), solely intended to provide the   
   general idea.   
   Hopefully, you actually haven't used those reserved keywords for your column   
   names ...   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)