How to get consecutive columns on a condition

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

Transact-SQL (2000)

How to get consecutive columns on a condition

Author

Topic

Pumkin
Starting Member

20 Posts

Posted - 2005-08-02 : 08:36:46

I have a small problem.. Could anyone help me?
I have a table Test with the columns: Wordd(varchar(20)) and datet (smalldatetime). I need to get to get all wordd that appear on consecutive minutes and the sum of the min and the date that they appear.
for example :
wordd 'bla1' datet '20-07-2005 12:00:00'
wordd 'bla1' datet '20-07-2005 12:30:00'
wordd 'bla1' datet '20-07-2005 12:31:00'
wordd 'bla3' datet '20-07-2005 13:00:00'
wordd 'bla3' datet '20-07-2005 13:09:00'
wordd 'bla3' datet '20-07-2005 13:10:00'
wordd 'bla3' datet '20-07-2005 13:11:00'
It should return bla1 1 min bla3 2 min

Thank you in advanced

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-02 : 09:42:36

this should do it...
you'll have to manage the differnt days and midnight changes yourself...


declare @t table (wordd varchar(20), datet datetime)
insert into @t
select 'bla1', '20-07-2005 12:00:00' union all
select 'bla1', '20-07-2005 12:30:00' union all
select 'bla1', '20-07-2005 12:31:00' union all
select 'bla3', '20-07-2005 13:00:00' union all
select 'bla3', '20-07-2005 13:09:00' union all
select 'bla3', '20-07-2005 13:10:00' union all
select 'bla3', '20-07-2005 13:11:00'

select 	wordd, count(datet)
from 	@t t1
where	datediff(n, datet, (select top 1 datet from @t where t1.datet < datet order by datet asc)) = 1
group by  wordd

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-02 : 09:52:28

Mladen, didnt you get this error?

Server: Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

(0 row(s) affected)

Madhivanan

Failing to plan is Planning to fail

Pumkin
Starting Member

20 Posts

Posted - 2005-08-02 : 09:54:11

Thank you, but

my problem is that i have to sum the minutes when the dates are "consecutive" (on consecutive rows and the difference between them to be one minute). If I Have 3 consecutive dates for the same wordd I have to obtain the startTime (dateT), the wordd, how many minutes(3) and the finishTime.
I hope I can explain clear.

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 09:55:02

Spirit's not going to have a month-first date format in E.Eurpose, is he?

Suprised you have Down South ...

SET DATEFORMAT DMY
should fix it

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-02 : 09:56:14

madhivanan:
you need to do
set dateformat dmy

pumkin:
the best way to help you if you provide more sample data and desired results... the edge conditions...

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-02 : 09:57:40

Kristen:
i resent that E.Europe... just kidding.

we're in central Europe...

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 09:58:31

Pumkin : add

, MIN(datet), DATEADD(Minute, count(datet), MIN(datet))

to the SELECT

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-02 : 09:59:10

>>you need to do
set dateformat dmy

No need
Just change 20-07-2005 to 2005-07-20 and will work for all settings

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 09:59:45

"we're in central Europe"

You're absolutely right, my apologies. I was thinking "East of here" but what I typed was dumb.

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-02 : 10:02:30

it happens to the best of us...

Go with the flow & have fun! Else fight the flow

Pumkin
Starting Member

20 Posts

Posted - 2005-08-02 : 10:16:31

I cannot make myself understood.. I guess :(
I will post an example
The Table is IPP and these are the records
Ip DateT
10.80.20.1 2005-07-21 12:06:00
10.80.20.2 2005-07-21 22:39:00
10.80.20.2 2005-07-21 22:40:00
10.80.20.1 2005-07-22 13:47:00
10.80.20.1 2005-07-22 13:48:00
10.80.20.1 2005-07-22 13:49:00
I need a select that can give me the folowing results
10.80.20.2 2005-07-21 22:39:00 2005-07-21 22:40:00 1 min
10.80.20.1 2005-07-22 13:47:00 2005-07-22 13:49:00 2 min

Thank you for your efforts.

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 10:32:34

That's what Spirit's code, plus my extension to the SELECT does, isn't it?

Here's the modified code if something got lost in teh post ...


SET DATEFORMAT YMD
GO
declare @t table (wordd varchar(20), datet datetime)
insert into @t
SELECT '10.80.20.1', '2005-07-21 12:06:00' UNION ALL
SELECT '10.80.20.2', '2005-07-21 22:39:00' UNION ALL
SELECT '10.80.20.2', '2005-07-21 22:40:00' UNION ALL
SELECT '10.80.20.1', '2005-07-22 13:47:00' UNION ALL
SELECT '10.80.20.1', '2005-07-22 13:48:00' UNION ALL
SELECT '10.80.20.1', '2005-07-22 13:49:00'

select 	wordd, MIN(datet), DATEADD(Minute, count(datet), MIN(datet)), count(datet)
from 	@t t1
where	datediff(n, datet, (select top 1 datet from @t where t1.datet < datet order by datet asc)) = 1
group by  wordd
ORDER BY MIN(datet)

Kristen

Pumkin
Starting Member

20 Posts

Posted - 2005-08-02 : 10:51:18

Thank you all very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources