I have table like this in my database :
ID (pk,int,not null) UserID (int,null) HitDate (datetime,null)
Every time a user has any transacation in my application one hit inserted in this table.
Now my client want to find out how many new users I have per day from the beginning of application.
As I find out I have to group user by "UserID" and get the first
HitDate, then remove other hit dates for that user. The first one will be the first login into system. Then I count using this method of all user per day.
My problem is this table has a huge row number, near 1,800,000,000 rows !!!!
The only solution I find at the moment is to break it into few day and insert it into a temporary table, then remove duplicates from it.
This is my code : (ID is Autoincrement)
INSERT INTO temptable (UserID , HitDate ) Select vs.UserID , vs.HitDate From UserHitTbl vs where LEN(vs.UserID ) > 1 and vs.HitDate > '2013-06-12 14:32:59.783' and vs.HitDate < '2013-07-12 14:32:59.783' DELETE FROM temptable WHERE ID NOT IN ( SELECT MAX(ID) FROM t6 GROUP BY UserID )
The above gives the following error:
Could not allocate space for object '' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup