How to retrieve and ID that occured the maximum number of times in a date range.

• John Peter Fernandes

  SSC Veteran

  Points: 255

  More actions

  May 5, 2010 at 10:50 am

  #97283

  Hi,

  i want to select the Highest occurence of a userID in a table (mt_temp) for the last week.

  the below query return all data from the last week.

  select * from mt_temp

  where UploadDate < convert (char(12),(dateadd(day,(0-(select datepart(weekday,getdate()))),getdate())))

  and UploadDate > convert (char(12),(dateadd(day,((0-(select datepart(weekday,getdate())))-7),getdate())))

  how do i go about check which id occured the most. Please help. thanks in advance.

  Table structure:

  CREATE TABLE [dbo].[mt_temp](

  [id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

  [UserId] [numeric](18, 0) NOT NULL,

  [dummyData] [varchar](250) NOT NULL,

  [UploadDate] [datetime] NOT NULL

  ) ON [PRIMARY]

  Dummy Data:

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'A', '2010-04-25 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'B', '2010-04-25 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'C', '2010-04-25 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'D', '2010-04-25 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (3, 'E', '2010-04-25 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'F', '2010-04-25 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'G', '2010-04-26 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'H', '2010-04-26 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'I', '2010-04-26 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (3, 'J', '2010-04-26 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'K', '2010-04-26 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'L', '2010-04-26 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (3, 'M', '2010-04-26 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'N', '2010-04-27 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (4, 'O', '2010-04-27 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'P', '2010-04-27 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (4, 'Q', '2010-04-27 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'R', '2010-04-27 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'S', '2010-04-27 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'T', '2010-04-27 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'U', '2010-04-27 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (3, 'V', '2010-04-28 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (4, 'W', '2010-04-28 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'X', '2010-04-28 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'Y', '2010-04-28 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (4, 'Z', '2010-04-28 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'A0', '2010-04-28 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'A1', '2010-04-28 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (3, 'A2', '2010-04-28 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'A3', '2010-04-28 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'A4', '2010-04-29 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'A5', '2010-04-29 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'A6', '2010-04-29 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'A7', '2010-04-29 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (3, 'A8', '2010-04-29 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (3, 'A9', '2010-04-29 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (3, 'B0', '2010-04-29 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'B1', '2010-04-30 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'B2', '2010-04-30 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'B3', '2010-04-30 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'B4', '2010-04-30 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'B5', '2010-04-30 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'B6', '2010-04-30 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'B7', '2010-04-30 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'B8', '2010-04-30 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'B9', '2010-05-01 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (5, 'C0', '2010-05-01 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (5, 'C1', '2010-05-01 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'C2', '2010-05-01 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'C3', '2010-05-01 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'C4', '2010-05-01 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'C5', '2010-05-01 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'C6', '2010-05-01 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'C7', '2010-05-02 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'C8', '2010-05-02 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'C9', '2010-05-02 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'D0', '2010-05-02 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'D1', '2010-05-02 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'D2', '2010-05-02 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'D3', '2010-05-02 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'D4', '2010-05-02 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'D5', '2010-05-03 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'D6', '2010-05-03 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'D7', '2010-05-03 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'D8', '2010-05-03 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'D9', '2010-05-03 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'E0', '2010-05-03 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'E1', '2010-05-03 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'E2', '2010-05-04 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'E3', '2010-05-04 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (3, 'E4', '2010-05-04 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (3, 'E5', '2010-05-04 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'E6', '2010-05-04 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'E7', '2010-05-04 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (2, 'E8', '2010-05-05 00:00:00.000');

  INSERT INTO mt_temp (UserId, dummyData, UploadDate ) VALUES (1, 'E9', '2010-05-05 00:00:00.000');

  Thanks.

  John

  John P Fernandes
  Enterprise Premier Support | Networking | Microsoft India GTSC

• Dan.Humphries

  SSChampion

  Points: 12125

  More actions

  May 5, 2010 at 12:45 pm

  #1162696

  This would select the top UserID.

  Select Top 1 t.* from

  (

  select Userid,count(*) as UseridCount

  from mt_temp

  where

  UploadDate < convert (char(12),(dateadd(day,(0-(select datepart(weekday,getdate()))),getdate())))

  and UploadDate > convert (char(12),(dateadd(day,((0-(select datepart(weekday,getdate())))-7),getdate())))

  group by Userid

  ) t

  order by UseridCount desc

  Dan

  If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

• John Peter Fernandes

  SSC Veteran

  Points: 255

  More actions

  May 6, 2010 at 8:32 am

  #1163258

  Thanks.. 🙂

  i tried this and it works just fine.

  John P Fernandes
  Enterprise Premier Support | Networking | Microsoft India GTSC