How to make single datetime column to 2 columns

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)

How to make single datetime column to 2 columns

Author

Topic

avinash.cp
Starting Member

7 Posts

Posted - 2010-01-21 : 01:01:06

How to make single datetime column to 2 columns

I need help on how i can achive the below requirement in MS SQL 2005
Table is something like this
Key Date time Status Flag
1 2010-01-01 00:00:00.000 1
1 2010-01-01 00:05:00.000 1
1 2010-01-01 00:10:00.000 0
1 2010-01-01 00:15:00.000 0
1 2010-01-01 00:20:00.000 1
1 2010-01-01 00:25:00.000 0
1 2010-01-01 00:30:00.000 0
1 2010-01-01 00:35:00.000 1

1 represents application is Available(UP & running state)
0 represents application is Not Available(Down)

I need to represent the downtime in the report as follows

Key Downtime (Start time) Downtime(End time)
1 2010-01-01 00:10:00.000 2010-01-01 00:20:00.000
2010-01-01 00:25:00.000 2010-01-01 00:50:00.000

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-01-21 : 01:40:16

I assume that you want to add 10 minutes to the downtime date and show it in another column,

Use the script,

SELECT DATECol AS [Downtime(Start time)],DATEADD(mi,10,DATECol) AS [Downtime(End time)] FROM CLIENT WHERE Flag=0

Here DATECol is your column name.
If your requirement is different from my assumption, give some more explanation.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

avinash.cp
Starting Member

7 Posts

Posted - 2010-01-21 : 01:55:08

For each 5 mins we will get data either status flag is 1 or 0. When the flag is 0 i need to take that time as starttime and when the status flag is again changed to 1 then put is as endtime. It need not be 10 mins interval it may be for 1hr or more. It may look like as below

Key Date time Status Flag
1 2010-01-01 00:00:00.000 1
1 2010-01-01 00:05:00.000 1
1 2010-01-01 00:10:00.000 0
1 2010-01-01 00:15:00.000 0
1 2010-01-01 00:20:00.000 1
1 2010-01-01 00:25:00.000 0
1 2010-01-01 00:30:00.000 0
1 2010-01-01 00:35:00.000 0
1 2010-01-01 00:40:00.000 0
1 2010-01-01 00:45:00.000 1
1 2010-01-01 00:50:00.000 1
For this i must display 2 rows as
Key (Start time) (End time)
1 2010-01-01 00:10:00.000 2010-01-01 00:20:00.000
2010-01-01 00:25:00.000 2010-01-01 00:45:00.000

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-01-21 : 04:09:50

Use the scripts,

SELECT DT AS ST,(select top 1 DT from CLIENT as C where C.DT>C1.DT and C.Flag=1) AS DT1
INTO TEMP# FROM CLIENT as C1 where C1.Flag=0

SELECT MIN(ST) AS [Downtime(Start time)],
DT1 AS [Downtime(End time)] FROM TEMP# GROUP BY DT1

DROP TABLE TEMP#

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

avinash.cp
Starting Member

7 Posts

Posted - 2010-01-21 : 04:45:30

Thanks for the script it worked fine in all cases. In one particular case there is problem ie when there is no status flag =1 at the maxdate. EX
Key Date time Status Flag
1 2010-01-01 00:00:00.000 1
1 2010-01-01 00:05:00.000 1
1 2010-01-01 00:10:00.000 0
1 2010-01-01 00:15:00.000 0
1 2010-01-01 00:20:00.000 1
1 2010-01-01 00:25:00.000 0
1 2010-01-01 00:30:00.000 0
1 2010-01-01 00:35:00.000 0
1 2010-01-01 00:40:00.000 0
1 2010-01-01 00:45:00.000 1
1 2010-01-01 00:50:00.000 1
1 2010-01-01 00:55:00.000 0
1 2010-01-01 00:60:00.000 0

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-01-21 : 05:23:02

what do you want to show in this case?

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

avinash.cp
Starting Member

7 Posts

Posted - 2010-01-21 : 05:47:52

In this case it should show Endtime as NULL.

avinash.cp
Starting Member

7 Posts

Posted - 2010-01-21 : 05:50:49

like
Key (Start time) (End time)
1 2010-01-01 00:10:00.000 2010-01-01 00:20:00.000
2010-01-01 00:25:00.000 2010-01-01 00:45:00.000
2010-01-01 00:55:00.000 NULL

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-01-21 : 07:05:21

Yes my script resulting NULL , Did you checked?

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-01-21 : 19:35:13

Here's another approach:
[CODE]
;WITH Transitions
as (
select a.Datetime, a.StatusFlag
from MyTable a
inner join
MyTable b
on
a.StatusFlag <> b.StatusFlag
and DateDiff(minutes, a.datetime, b.datetime) = 5
)
select aa.Datetime StartTime, bb.Datetime Endtime
from
Transitions aa
left outer join
Transitions bb
on
aa.StatusFlag = 0
and not exists (
select *
from Transitions x
where aa.Datetime < x.Datetime
and x.Datetime < bb.Datetime
)
[/CODE]

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

avinash.cp
Starting Member

7 Posts

Posted - 2010-01-22 : 01:47:15

quote:
Originally posted by sql-programmers

Yes my script resulting NULL , Did you checked?

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Thanks this query is working fine. i rechecked it again its proper.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-01-22 : 11:26:32

Not that anyone does or even should care BUT....
[CODE]
;WITH Transitions
as (
select b.Datetime, b.StatusFlag
from @MyTable a
inner join
@MyTable b
on
a.StatusFlag <> b.StatusFlag
and DateDiff(minute, a.datetime, b.datetime) = 5
)
select aa.Datetime StartTime, bb.Datetime Endtime
from
Transitions aa
left outer join
Transitions bb
on
aa.StatusFlag = 0
and bb.StatusFlag = 1
and aa.datetime < bb.datetime
and not exists (
select *
from Transitions x
where aa.Datetime < x.Datetime
and x.Datetime < bb.Datetime
)
where
aa.StatusFlag = 0
[/CODE]

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources