View Single Post
  #7 (permalink)  
Old March 25th, 2008, 06:54 PM
Jeff Moden Jeff Moden is offline
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

CrabJoe...

The problem is that you keep describing what you want for a result instead of showing folks and they're not getting it.

Also, if you want faster answers that can easily be tested, you need to create a table and some data like DParsons did... people hate converting poorly formed data on a forum to something they can test with... it just takes to0 bloody long.

With all of that in mind, here's what your post should have looked like for table information and data... and, the answer is also there.

Code:
--===== Create a demo table for the example.  Don't use
     -- table variables... they don't persist for trouble
     -- shooting.  Also notice that the primary key is
     -- indicated.
 CREATE TABLE #MyTable
        (
        RowNum  INT IDENTITY(1,1) PRIMARY KEY,
        Date    DATETIME, 
        SName   VARCHAR(50), 
        Savings INT
        )

--===== Populate the table with some reasonable test data
 INSERT INTO #MyTable
        (Date,SName,Savings)
 SELECT '20080101', 'John'  , 500 UNION ALL
 SELECT '20080101', 'Juliet', 250 UNION ALL
 SELECT '20080101', 'Jane'  , 50  UNION ALL
 SELECT '20080101', 'Joe'   , 100 UNION ALL
 SELECT '20080101', 'Jeff'  , 10  UNION ALL
 SELECT '20080101', 'Jamie' , 400 UNION ALL

 SELECT '20080215', 'John'  , 5  UNION ALL
 SELECT '20080215', 'Juliet', 10 UNION ALL

 SELECT '20080302', 'John'  , 10 UNION ALL
 SELECT '20080302', 'Juliet', 20 UNION ALL
 SELECT '20080302', 'Jane'  , 30 UNION ALL
 SELECT '20080305', 'Joe'   , 60 UNION ALL --3 way tie for 2nd
 SELECT '20080315', 'Jay'   , 60 UNION ALL --3 way tie for 2nd
 SELECT '20080325', 'Jan'   , 60 UNION ALL --3 way tie for 2nd
 SELECT '20080302', 'Jeff'  , 50 UNION ALL
 SELECT '20080302', 'Jamie' , 100 

 --===== And, here's your solution and it works
     -- across years 
 SELECT YEAR(t1.Date) AS Year,
        Month(t1.Date) AS Mo,
        t1.SName,
        t1.Savings
   FROM #MyTable t1
  WHERE t1.RowNum IN (SELECT TOP 3 WITH TIES t2.RowNum
                        FROM #MyTable t2 
                       WHERE DATEDIFF(mm,0,t1.Date) = DATEDIFF(mm,0,t2.Date) 
                       ORDER BY t2.Savings DESC)
  ORDER BY Year, Mo, t1.Savings DESC

DROP TABLE #MyTable
... and that creates an output like this...

Code:
Year        Mo          SName                                              Savings     
----------- ----------- -------------------------------------------------- ----------- 
2008        1           John                                               500
2008        1           Jamie                                              400
2008        1           Juliet                                             250
2008        2           Juliet                                             10
2008        2           John                                               5
2008        3           Jamie                                              100
2008        3           Joe                                                60
2008        3           Jay                                                60
2008        3           Jan                                                60
Any questions on any of that? ;)

--Jeff Moden
Reply With Quote