Min and Max of Date per Day - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Min and Max of Date per Day

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-05-06 : 17:03:40

I have a column of datetime type.
eg:
2010-03-30 08:17:51.490
2010-03-30 08:18:26.110
...............
....................
2010-03-30 16:25:06.180
2010-03-30 16:25:42.130
2010-03-31 08:18:14.670
2010-03-31 08:19:12.830
......................
....................
2010-03-31 11:02:04.630.

I need a way to find out the start times and end times of each day in the column.
How can this be done.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-06 : 17:10:04

[code]-- Sample Data
DECLARE @MyTable TABLE (MyDate DATETIME)

INSERT @MyTable
SELECT '2010-03-30 08:17:51.490'
UNION ALL SELECT '2010-03-30 08:18:26.110'
UNION ALL SELECT '2010-03-30 16:25:06.180'
UNION ALL SELECT '2010-03-30 16:25:42.130'
UNION ALL SELECT '2010-03-31 08:18:14.670'
UNION ALL SELECT '2010-03-31 08:19:12.830'
UNION ALL SELECT '2010-03-31 11:02:04.630'

-- Run Query
SELECT
DATEADD(DAY, DATEDIFF(DAY, 0, MyDate), 0) AS [Day],
MIN(MyDate) AS MinDate,
MAX(MYDate) AS MaxDate
FROM
@MyTable
GROUP BY
DATEADD(DAY, DATEDIFF(DAY, 0, MyDate), 0)[/code]

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-05-06 : 17:29:19

quote:

Originally posted by Lamprey

-- Sample Data
DECLARE @MyTable TABLE (MyDate DATETIME)

INSERT @MyTable
SELECT '2010-03-30 08:17:51.490'
UNION ALL SELECT '2010-03-30 08:18:26.110'
UNION ALL SELECT '2010-03-30 16:25:06.180'
UNION ALL SELECT '2010-03-30 16:25:42.130'
UNION ALL SELECT '2010-03-31 08:18:14.670'
UNION ALL SELECT '2010-03-31 08:19:12.830'
UNION ALL SELECT '2010-03-31 11:02:04.630'

-- Run Query
SELECT 
	DATEADD(DAY, DATEDIFF(DAY, 0, MyDate), 0) AS [Day],
	MIN(MyDate) AS MinDate,
	MAX(MYDate) AS MaxDate
FROM 
	@MyTable
GROUP BY
	DATEADD(DAY, DATEDIFF(DAY, 0, MyDate), 0)

Thanks
I need a bit more help is there a way to add the seconds in the following query in a single statement.
SELECT
DATEADD(DAY, DATEDIFF(DAY, 0, MyDate), 0) AS [Day],
MIN(MyDate) AS MinDate,
MAX(MYDate) AS MaxDate,
DATEDIFF(ss,MIN(MyDate),Max(MyDate)) as seconds,

FROM
@MyTable
GROUP BY
DATEADD(DAY, DATEDIFF(DAY, 0, MyDate), 0)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-06 : 17:44:15

This seems right. Are you getting an error?

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-05-06 : 18:01:23

quote:
Originally posted by vijayisonly

This seems right. Are you getting an error?

Sorry I did not explain properly. The above query runs fine and it gives the seconds in rows depending on the number of days spanned.
I have a select statement
select a,b,
dbo.func_ConvertSeconds(datediff(ss,MIN(MyDate),MAX(MyDate))) as Time
group by a.
The problem is between the min(mydate) and max(mydate) there are break in time. for example people start work in the morning and end in the evening and again start next morning and so on. I need to add all the time they were working not the break time.
Hope I am made it clear.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-07 : 13:22:26

How can you distinguish between a Start and an End date?

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-05-07 : 14:12:12

quote:
Originally posted by Lamprey

How can you distinguish between a Start and an End date?

start date would be the date on the min(mydate) end date will be from max(mydate).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-07 : 14:18:40

can you post your data as in table along with people info? then it will be much help to somebody trying to help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-07 : 15:04:50

quote:
Originally posted by BendJoe

quote:
Originally posted by Lamprey

How can you distinguish between a Start and an End date?

start date would be the date on the min(mydate) end date will be from max(mydate).

Ok, Then how do you determine which Seconds should be subtracted? Or should not be included in the difference between the start and end dates?

Jsut a guess, but I suspect that you mean something like this, based on the sample data you provided. If you can clarify, we can probably help.

2010-03-30 08:17:51.490 = Start - Difference between (08:18:26.110 & 08:18:26.110) = 35 Sec
2010-03-30 08:18:26.110 = End   - Do nothing

2010-03-30 16:25:06.180 = Start - Difference between (16:25:06.180 & 16:25:42.130) = 36 Sec
2010-03-30 16:25:42.130 = End   - Do Nothing

2010-03-31 08:18:14.670 = Start - Difference between (16:25:06.180 & 16:25:42.130) = 58 Sec
2010-03-31 08:19:12.830 = End   - Do Nothing

2010-03-31 11:02:04.630 = Start.. No End.. so 0?

* Just a guess,

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-05-08 : 12:05:39

Table 1,
ProcessID,ProcessName..
Table 2,
TaskID, ProcessID, TaskName,TaskDateTime..

I want to find the amount of time to complete the Process. Process is made up of several tasks.
eg: At times a process gets completed in a day so I can take the difference in Max(taskDateTime) and Min(taskDateTime).
But when a task get spanned across several days I have to take into consideration the break time from evening to nextday morning.

Process Table:
1,Process1....
2.Process2....
Task Table
1,1,Task1forProcess1,2010-05-05 8:30:00:000
2,1,Task2forProcess1,2010-05-05 8:45:00:000
...........................................
n-1,1,taskn-1forProcess1,2010-05-05 4:50:00:000
n,1,TasknforProcess1,2010-05-06 9:00:00:000
.........................................
m,1,TaskmforProcess1,2010-05-06 11:30:00:000
m+1,2,task1forProcess2, 2010-05-06 12:30:00:000
................................

I am using this selec
Select p.ProcessID,p.ProcessName,datediff(ss,MIN(t.TaskDateTime),MAX(t.TaskdateTime))
from process p inner join tasks t
on p.processid=t.processid
group by processid
This will not take into account the break between the last tasktime and the first task time next day. I am trying to find a way to make the query account for the break time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-09 : 02:58:36

you've only a single date value stored against each of tasks of process. then how will you know when that task got over? or is it that task ran all the way till start of next task? then question of break time wont appear at all.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-05-09 : 13:48:43

quote:
Originally posted by visakh16

you've only a single date value stored against each of tasks of process. then how will you know when that task got over? or is it that task ran all the way till start of next task? then question of break time wont appear at all.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

The end time of a task is the start time of the following task.That is not very important. I want the time to finish a process. The start time of the process will be the datetime of the first task in that process and the finish time will be the datetime of the final task within that process. min(datetime) and max(datetime) can get those values. But need to eliminate the break time if a task spans over multiple days.
Is there a way to split the grouping on processid into subgroups grouped by the day part of the datetime column. And then add the time
of those sub groups.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-10 : 11:09:22

You need to provide sample data and expected output in a consumable format, which has already been asked for. If you provide that then we can help. Here is a link to assit you, just in case:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 13:09:07

quote:
Originally posted by BendJoe

The end time of a task is the start time of the following task.

then what does below mean?

I am trying to find a way to make the query account for the break time.

how will break come if tasks are continuos?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-05-10 : 21:01:54

CREATE TABLE [dbo].[Process](
[ProcessID] [int] NOT NULL,
[Processname] [varchar](50) NOT NULL,
CONSTRAINT [PK_Process] PRIMARY KEY CLUSTERED
(
[ProcessID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Tasks](
[TaskID] [int] NOT NULL,
[ProcessID] [int] NOT NULL,
[TaskName] [nvarchar](50) NOT NULL,
[ProcessDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_Tasks] PRIMARY KEY CLUSTERED
(
[TaskID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Tasks] WITH CHECK ADD CONSTRAINT [FK_Tasks_Tasks] FOREIGN KEY([ProcessID])
REFERENCES [dbo].[Process] ([ProcessID])
GO

ALTER TABLE [dbo].[Tasks] CHECK CONSTRAINT [FK_Tasks_Tasks]
INSERT INTO [dbo].[Process]
([ProcessID]
,[Processname])
select 1,'Process1' union all
select 2,'Process2'
GO

INSERT INTO [dbo].[Tasks]
([TaskID]
,[ProcessID]
,[TaskName]
,[ProcessDateTime])
select 1,1,Task1Process1,'2010-03-30 08:17:51.490' union all
select 2,1,Task2Process1,'2010-03-30 08:18:26.110' union all
select 3,1,Task3Process1,'2010-03-30 16:30:00:000' union all
select 4,1,Task4Process1,'2010-03-31 08:00:00:000' union all
select 5,1,Task5Process1,'2010-03-31 13:00:00:000' union all
select 1,2,Task1Process2,'2010-03-31 14:17:51.490' union all
select 2,2,Task2Process2,'2010-03-31 16:18:26.110' union all
select 3,2,Task3Process2,'2010-03-31 16:30:00:000' union all
select 4,2,Task4Process2,'2010-04-01 08:00:00:000' union all
select 5,2,Task5Process2,'2010-04-01 13:00:00:000'

GO
I need the time yaken to complete a process.
Assumptions
1. the timestamp of the first task in a process is the start time of a process.
2 the timestamp of the last task in a process is the finish time of a process.
3.If a process spans more than one day then the time between the last task within that process on a given day and the first task that happens on the following day within that process needs to be omitted from the process completion time calculation.

How can this be done in T-sql

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-11 : 10:54:20

What is your expected output?

FYI, your test data produces several errors.

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-05-11 : 19:07:51

quote:
Originally posted by Lamprey

What is your expected output?

FYI, your test data produces several errors.

ProcessID,ProcessName and time to complete the process.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 10:52:10

[code]
select Processname,CAST(LEFT(CONVERT(varchar(8),DATEDIFF(dd,0,DATEADD(ss,SUM([Time]),0)),108),2) *24+ LEFT(CONVERT(varchar(8),DATEADD(ss,SUM([Time]),0),108),2)*1 AS varchar(10)) + ':'+RIGHT(CONVERT(varchar(8),DATEADD(ss,SUM([Time]),0),108),5)
from
(
select p.Processname,DATEADD(dd,DATEDIFF(dd,0,t.ProcessDateTime),0) AS [Date],
DATEDIFF(ss,MIN(t.ProcessDateTime),MAX(t.ProcessDateTime)) AS [Time]
from Process p
JOIN Tasks t
ON t.ProcessID = p.ProcessID
GROUP BY p.Processname,DATEADD(dd,DATEDIFF(dd,0,t.ProcessDateTime),0)
)r
GROUP BY Processname
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-12 : 11:06:56

quote:
Originally posted by BendJoe

quote:
Originally posted by Lamprey

What is your expected output?

FYI, your test data produces several errors.

ProcessID,ProcessName and time to complete the process.

By expected output, I mean what are the ACTUAL results you expect to get back given the sample data you provided?

My guess would be the code that Visakh has provided, but it seems like you'd loose senconds when a task spans over the day boundry (midnight).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources