Creating a timeline - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Creating a timeline

Author

Topic

mac1984
Starting Member

2 Posts

Posted - 2010-07-04 : 02:33:03

Hello,

Can anyone give me some advice on how to create a timeline in sql?

I work in the nhs and I am trying to create a timeline of clinician's activities. Ideally it will show each minute between the two dates as a row and then count whether the clinican was working that minute (based on the consultation start and end dates).

So it would be between the @fromdate and the @todate, eg/ 18:00 to 18:02

and would result something like:

Time | With Patient
18:00 | 1
18:01 | 1
18:02 | 0

Is this possible? Any help would be greatly appreciated.

Thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-04 : 03:16:56

Hi mac,

Please find below an example on how to start with for your reqirement.
This may not be full fledged solution but something to help you to start with.

DECLARE @StartDate Datetime
DECLARE @EndDate Datetime
Declare @MinDiff int
Declare @ConsStDate datetime
Declare @ConsEndDate datetime

set @StartDate = '2010-07-02 14:02' --Start time for Report
set @EndDate = '2010-07-02 16:02' --End time for Report
set @MinDiff = Datediff(Mi,@StartDate,@EndDate) --Local use

set @ConsStDate = '2010-07-02 14:22' --Consulation start time
set @ConsEndDate = '2010-07-02 15:52' --Consulation end time

--INSERT @MyTable (Value,LogTime)
SELECT Convert(varchar,DATEADD(mi,number,@StartDate),108) ,
Case When DATEADD(mi,number,@StartDate) between @ConsStDate and @ConsEndDate Then 1
else 0
End as 'With Patient'
FROM master..spt_values
where [type]='p'
and number BETWEEN 1 AND @MinDiff

It would be better if you can post the table structure, some sample data and your expected output.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-07-04 : 07:08:13

If you could post DDL \ DML . Further , you have some responses on dbforums.com

Jack Vamvas
--------------------
http://www.ITjobfeed.com

mac1984
Starting Member

2 Posts

Posted - 2010-07-06 : 02:42:17

That's ideal, enough to get me started. Thanks so much for your help

The consultation information is stored in the consultation table as the startdate and enddate. So there would lots of consultations.

Thanks again.

quote:
Originally posted by pk_bohra

Hi mac,

Please find below an example on how to start with for your reqirement.
This may not be full fledged solution but something to help you to start with.

DECLARE @StartDate Datetime
DECLARE @EndDate Datetime
Declare @MinDiff int
Declare @ConsStDate datetime
Declare @ConsEndDate datetime

set @StartDate = '2010-07-02 14:02' --Start time for Report
set @EndDate = '2010-07-02 16:02' --End time for Report
set @MinDiff = Datediff(Mi,@StartDate,@EndDate) --Local use

set @ConsStDate = '2010-07-02 14:22' --Consulation start time
set @ConsEndDate = '2010-07-02 15:52' --Consulation end time

--INSERT @MyTable (Value,LogTime)
SELECT Convert(varchar,DATEADD(mi,number,@StartDate),108) ,
Case When DATEADD(mi,number,@StartDate) between @ConsStDate and @ConsEndDate Then 1
else 0
End as 'With Patient'
FROM master..spt_values
where [type]='p'
and number BETWEEN 1 AND @MinDiff

It would be better if you can post the table structure, some sample data and your expected output.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-06 : 12:36:49

welcome mac

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources