DECLARE @AddDate DateTime DECLARE @AddDate2 DateTime SET @AddDate='2009-02-14 00:00:00' SET @AddDate2='2009-02-14 23:59:59' SELECT COUNT(*) FROM Hit WHERE Hit.AddDate>=@AddDate AND Hit.AddDate<=@AddDate2 GROUP BY AddUserThen, from that command, I can execute an update on StatisticsDaily to update the User field with the count of the SQL represented above. So, if my data provider is named loDataProvider, for the execution of the above command, I can do something like this where loDataProvider.nCount is the number of records that was retrieved:
DECLARE @User Integer DECLARE @Numero Integer SET @User=loDataProvider.nCount SET @Numero=234334 UPDATE StatisticDaily SET [User]=@User WHERE Numero=@NumeroThis is good and will be for all upcoming executions of this command for as long as we move forward. But, all previous records have 0 presently on the Statistic table. So, basically, I would like to execute this update by script, the update that will adjust all previous records, as I do not want to build an application to loop thru all records from the StatisticDaily table and execute the command for each day. So, basically, I would like to do all that in one update I can then give to the database administrator so he will execute it in SSMS.
UPDATE StatisticsDaily SET [User]=CountOfDailyFoundInHitPerUser FROM Hit WHERE StatisticDaily.Date=Hit.Date AND Hit.AddUser>0 GROUP BY Hit.AddUserOf course, that command cannot be executed as is. I am just trying to explain what I need to achieve. There are a few obstacles here. First, Statistic.Date is a Datetime field. So, it is something like 2009-02-14 00:00:00, for a daily representation. And, I need to obtain a count as per a group in hit on a daily basis, but a group per Hit.AddUser where Hit.AddUser would be greater than 0.