Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
new_bees
Starting Member
27 Posts |
Posted - 2008-04-28 : 22:05:37
|
Hi Guys,I've been visiting this site for quite a while to get an ideas in some of my previous projects. This current project I'm working right now is blowing my mind. Anyhow, here is the detail of this project. This project use to track user activity in their computer.The table has only 3 columns.User_ID int primary keyStatus varchar(50)add_date datetimeHere is the recordsUser_ID | Status | add_date user1 | At Work | 4/24/2008 5:00:00 PMuser1 | At Work | 4/24/2008 4:00:00 PMuser1 | Away | 4/24/2008 3:00:00 PMuser1 | At Work | 4/24/2008 10:00:00 AMuser1 | Away | 4/24/2008 9:00:00 AMuser1 | At Work | 4/24/2008 8:00:00 AMuser1 | At Work | 4/23/2008 5:00:00 PMuser1 | At Work | 4/23/2008 4:00:00 PMuser1 | Away | 4/23/2008 3:00:00 PMuser1 | At Work | 4/23/2008 10:00:00 AMuser1 | Away | 4/23/2008 9:00:00 AMuser1 | At Work | 4/23/2008 8:00:00 AMuser1 | At Work | 4/22/2008 5:00:00 PMuser1 | At Work | 4/22/2008 4:00:00 PMuser1 | Away | 4/22/2008 3:00:00 PMuser1 | At Work | 4/22/2008 10:00:00 AMuser1 | Away | 4/22/2008 9:00:00 AMuser1 | At Work | 4/22/2008 8:00:00 AMuser1 | At Work | 4/21/2008 5:00:00 PMuser1 | At Work | 4/21/2008 4:00:00 PMuser1 | Away | 4/21/2008 3:00:00 PMuser1 | At Work | 4/21/2008 10:00:00 AMuser1 | Away | 4/21/2008 9:00:00 AMuser1 | At Work | 4/21/2008 8:00:00 AMuser1 | At Work | 4/20/2008 5:00:00 PMuser1 | At Work | 4/20/2008 4:00:00 PMuser1 | Away | 4/20/2008 3:00:00 PMuser1 | At Work | 4/20/2008 10:00:00 AMuser1 | Away | 4/20/2008 9:00:00 AMuser1 | At Work | 4/20/2008 8:00:00 AMso on...The system will check the user activity every hour.What I'm trying to to is to get the MIN(add_date) and MAX(add_date) for each day in a month range. I'm not an advanced SQL programmer so I need your help.The result should look like below.User_ID | First Log | Last Loguser1 | 4/24/2008 5:00:00 PM | 4/24/2008 8:00:00 AMuser1 | 4/23/2008 5:00:00 PM | 4/23/2008 8:00:00 AMuser1 | 4/22/2008 5:00:00 PM | 4/22/2008 8:00:00 AMuser1 | 4/21/2008 5:00:00 PM | 4/21/2008 8:00:00 AMuser1 | 4/20/2008 5:00:00 PM | 4/20/2008 8:00:00 AMHow do I achieve this. Please help.I do really appreciate your help.Regards,Dexter |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-04-28 : 23:48:32
|
Here's one way:select [user_id] ,min(add_date) [first_log] ,max(add_date) [last_log]from <yourTable>where add_date >= '2008-04-01'and add_date < '2008-05-01'group by [user_id] ,dateadd(day, datediff(day, 0, add_date), 0) Be One with the OptimizerTG |
|
|
new_bees
Starting Member
27 Posts |
Posted - 2008-04-29 : 21:21:45
|
TG, thank you very much. Your code is exactly what I'm looking for.Below is my Sproc.CREATE PROCEDURE Get_userstatus_firstandlastlogday_month@User_ID varchar(50),@Year int,@Month intASdeclare @ErrorCode intSET NOCOUNT ONSELECT a1.User_ID, fullname=(a2.Preferred_Name + ' ' + a2.Last_Name), MIN(a1.add_Date) AS FirstLog, MAX(a1.add_Date) AS LastLog FROM UserStatus_Tracker a1 LEFT OUTER JOIN Users_Info a2 ON a1.User_ID = a2.User_ID WHERE a1.User_ID = @User_ID AND (DATEPART([year], a1.add_Date) = @Year) AND (DATEPART([month], a1.add_Date) = @Month) GROUP BY a1.User_ID, a2.Preferred_Name, a2.Last_Name, DATEADD([day], DATEDIFF([day], 0, a1.add_Date), 0) set @ErrorCode = @@errorSET NOCOUNT OFFreturn @ErrorCodeGOThank you and may the god lord bless you.Dexter |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2008-04-30 : 20:07:15
|
quote: Originally posted by new_beesThank you and may the god lord bless you.Dexter
I'll think about itAn infinite universe is the ultimate cartesian product. |
|
|
|
|
|
|
|