April 28, 2008 at 8:20 pm
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 key
Status varchar(50)
add_date datetime
Here is the records
User_ID | Status | add_date
user1 | At Work | 4/24/2008 5:00:00 PM
user1 | At Work | 4/24/2008 4:00:00 PM
user1 | Away | 4/24/2008 3:00:00 PM
user1 | At Work | 4/24/2008 10:00:00 AM
user1 | Away | 4/24/2008 9:00:00 AM
user1 | At Work | 4/24/2008 8:00:00 AM
user1 | At Work | 4/23/2008 5:00:00 PM
user1 | At Work | 4/23/2008 4:00:00 PM
user1 | Away | 4/23/2008 3:00:00 PM
user1 | At Work | 4/23/2008 10:00:00 AM
user1 | Away | 4/23/2008 9:00:00 AM
user1 | At Work | 4/23/2008 8:00:00 AM
user1 | At Work | 4/22/2008 5:00:00 PM
user1 | At Work | 4/22/2008 4:00:00 PM
user1 | Away | 4/22/2008 3:00:00 PM
user1 | At Work | 4/22/2008 10:00:00 AM
user1 | Away | 4/22/2008 9:00:00 AM
user1 | At Work | 4/22/2008 8:00:00 AM
user1 | At Work | 4/21/2008 5:00:00 PM
user1 | At Work | 4/21/2008 4:00:00 PM
user1 | Away | 4/21/2008 3:00:00 PM
user1 | At Work | 4/21/2008 10:00:00 AM
user1 | Away | 4/21/2008 9:00:00 AM
user1 | At Work | 4/21/2008 8:00:00 AM
user1 | At Work | 4/20/2008 5:00:00 PM
user1 | At Work | 4/20/2008 4:00:00 PM
user1 | Away | 4/20/2008 3:00:00 PM
user1 | At Work | 4/20/2008 10:00:00 AM
user1 | Away | 4/20/2008 9:00:00 AM
user1 | At Work | 4/20/2008 8:00:00 AM
so 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 Log
user1 | 4/24/2008 5:00:00 PM | 4/24/2008 8:00:00 AM
user1 | 4/23/2008 5:00:00 PM | 4/23/2008 8:00:00 AM
user1 | 4/22/2008 5:00:00 PM | 4/22/2008 8:00:00 AM
user1 | 4/21/2008 5:00:00 PM | 4/21/2008 8:00:00 AM
user1 | 4/20/2008 5:00:00 PM | 4/20/2008 8:00:00 AM
How do I achieve this. Please help.
I do really appreciate your help.
Regards,
Dexter
April 29, 2008 at 12:23 am
[font="Verdana"]
Select user_id, Min(add_date) as [Frist Log], Max(add_date) as [Last Log] From {Table} Group By user_id
by the way, you have ,mistakenly given wrong col heading.
Mahesh
[/font]
MH-09-AM-8694
April 29, 2008 at 7:26 pm
Thanks Mahesh
Your code did not work as what I wanted to be. Anyway, I figured it out with someone's help.
CREATE PROCEDURE Get_userstatus_firstandlastlogday_month
@User_ID varchar(50),
@Year int,
@Month int
AS
declare @errorcode int
SET NOCOUNT ON
SELECT 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 = @@error
SET NOCOUNT OFF
return @errorcode
GO
You are missing the dateadd and datediff in the group by.
Thanks,
Dexter
April 30, 2008 at 5:19 am
Well, since you appear to only be running it for a month at a time, why not simplify it and use DATEPART in your grouping?
SELECTUser_ID,
MIN(add_date) as 'First Log',
MAX(add_date) as 'Last Log'
FROMYourTable
GROUP BY
User_ID,
DATEPART(dd,add_date)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply