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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Select MAX and MIN for each day date in a month

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 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

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 Optimizer
TG
Go to Top of Page

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 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


Thank you and may the god lord bless you.

Dexter
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-04-30 : 20:07:15
quote:
Originally posted by new_bees


Thank you and may the god lord bless you.

Dexter



I'll think about it



An infinite universe is the ultimate cartesian product.
Go to Top of Page
   

- Advertisement -