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

Author  Topic 

AKP2008
Starting Member

45 Posts
Posted - 2009-01-05 : 05:08:47
hi,

I need to generate an incremental column.

My table structure is
VALIDFROM VALIDTO
2008-01-23 00:00:00.000 2008-02-22 00:00:00.000
2008-02-22 00:00:00.000 2008-03-23 00:00:00.000
2008-03-23 00:00:00.000 2008-04-23 00:00:00.000
2008-06-23 00:00:00.000 2008-08-13 00:00:00.000
2008-08-13 00:00:00.000 2008-08-24 00:00:00.000
2008-08-24 00:00:00.000 2008-09-15 00:00:00.000
2008-10-1 00:00:00.000 2008-12-20 00:00:00.000

I need an incremental column by comparing validfrom and validto of previous row.

My output should be like this.

VALIDFROM VALIDTO Column1
2008-01-23 00:00:00.000 2008-02-22 00:00:00.000 1
2008-02-22 00:00:00.000 2008-03-23 00:00:00.000 1
2008-03-23 00:00:00.000 2008-04-23 00:00:00.000 1
2008-06-23 00:00:00.000 2008-08-13 00:00:00.000 2
2008-08-13 00:00:00.000 2008-08-24 00:00:00.000 2
2008-08-24 00:00:00.000 2008-09-15 00:00:00.000 2
2008-10-1 00:00:00.000 2008-12-20 00:00:00.000 3

Please help me to get this.

Thanks.

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2009-01-05 : 05:12:03
use datediff(m,col1,col2) for this
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-01-05 : 05:24:03
datediff(month, validfrom, validto)

See here how DATEDIFF function works
http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-05 : 05:32:27
[code]CREATE TABLE #Temp
(
VALIDFROM datetime,
VALIDTO datetime,
Seq int
)
INSERT INTO #Temp (VALIDFROM,VALIDTO)
SELECT '2008-01-23 00:00:00.000','2008-02-22 00:00:00.000' UNION ALL
SELECT '2008-02-22 00:00:00.000', '2008-03-23 00:00:00.000' UNION ALL
SELECT '2008-03-23 00:00:00.000', '2008-04-23 00:00:00.000' UNION ALL
SELECT '2008-06-23 00:00:00.000', '2008-08-13 00:00:00.000' UNION ALL
SELECT '2008-08-13 00:00:00.000', '2008-08-24 00:00:00.000' UNION ALL
SELECT '2008-08-24 00:00:00.000', '2008-09-15 00:00:00.000' UNION ALL
SELECT '2008-10-1 00:00:00.000', '2008-12-20 00:00:00.000' UNION ALL
SELECT '2008-10-15 00:00:00.000', '2008-10-18 00:00:00.000' UNION ALL
SELECT '2008-10-18 00:00:00.000', '2008-12-22 00:00:00.000' UNION ALL
SELECT '2008-11-19 00:00:00.000', '2008-12-20 00:00:00.000' UNION ALL
SELECT '2008-12-20 00:00:00.000', '2008-12-30 00:00:00.000' UNION ALL
SELECT '2008-12-30 00:00:00.000', '2009-01-12 00:00:00.000' UNION ALL
SELECT '2009-01-12 00:00:00.000', '2009-01-20 00:00:00.000'

CREATE CLUSTERED INDEX IDX ON #Temp(VALIDFROM,VALIDTO)


UPDATE #Temp SET Seq=0
DECLARE @ValidTo datetime,@ValidFrom datetime,@Seq int
SELECT TOP 1 @ValidTo=VALIDTO,@ValidFrom=VALIDFROM,@Seq=SEQ
FROM #Temp
ORDER BY VALIDFROM

UPDATE #Temp
SET @Seq=SEQ=CASE WHEN VALIDFROM=@ValidTo THEN @Seq ELSE @Seq+1 END,
@ValidFrom=VALIDFROM,
@ValidTo=VALIDTO

select * from #Temp

drop table #Temp


output
----------------------------------------------------------------
VALIDFROM VALIDTO Seq
2008-01-23 00:00:00.000 2008-02-22 00:00:00.000 1
2008-02-22 00:00:00.000 2008-03-23 00:00:00.000 1
2008-03-23 00:00:00.000 2008-04-23 00:00:00.000 1
2008-06-23 00:00:00.000 2008-08-13 00:00:00.000 2
2008-08-13 00:00:00.000 2008-08-24 00:00:00.000 2
2008-08-24 00:00:00.000 2008-09-15 00:00:00.000 2
2008-10-01 00:00:00.000 2008-12-20 00:00:00.000 3
2008-10-15 00:00:00.000 2008-10-18 00:00:00.000 4
2008-10-18 00:00:00.000 2008-12-22 00:00:00.000 4
2008-11-19 00:00:00.000 2008-12-20 00:00:00.000 5
2008-12-20 00:00:00.000 2008-12-30 00:00:00.000 5
2008-12-30 00:00:00.000 2009-01-12 00:00:00.000 5
2009-01-12 00:00:00.000 2009-01-20 00:00:00.000 5[/code]
Go to Top of Page

AKP2008
Starting Member

45 Posts
Posted - 2009-01-05 : 05:35:24
quote:
Originally posted by Peso

datediff(month, validfrom, validto)

See here how DATEDIFF function works
http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.63"
N 56°04'39.26"


Hi,

Thanks for Your quick response.

But i don't want to get the datediff. I need to generate an incremental column.

For example
First value should be defalutly 1.
second value is getting by comparing validfrom at second row with validto at firstrow if they are equal then 1 else 2.
Then compare validfrom at third row with validto at second row if they are equal same as previous value else previousvalue + 1.

Like this i want to generate.

Thanks.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-01-05 : 05:37:50
quote:
Originally posted by AKP2008

I need an incremental column by comparing validfrom and validto of previous row.

My output should be like this.
VALIDFROM  VALIDTO    Column1
2008-01-23 2008-02-22 1
2008-02-22 2008-03-23 1
2008-03-23 2008-04-23 1
2008-06-23 2008-08-13 2
2008-08-13 2008-08-24 2
2008-08-24 2008-09-15 2
2008-10-01 2008-12-20 3
Which is it?
Your sample data output describes when you compare validto with previous validto, nothing else.

If you want validfrom compared to previous validto, your expected column1 should be this, right?
VALIDFROM  VALIDTO    Column1
2008-01-23 2008-02-22 NULL
2008-02-22 2008-03-23 0
2008-03-23 2008-04-23 0
2008-06-23 2008-08-13 2
2008-08-13 2008-08-24 0
2008-08-24 2008-09-15 0
2008-10-01 2008-12-20 1
If you can't present what you want, how do you expect us to help you?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-01-05 : 05:39:44
quote:
Originally posted by AKP2008

First value should be defalutly 1.
second value is getting by comparing validfrom at second row with validto at firstrow if they are equal then 1 else 2.
Then compare validfrom at third row with validto at second row if they are equal same as previous value else previousvalue + 1.

Like this i want to generate.
Oh, you simply want to calculate islands and gaps for your sequence?
See Visakh16's suggestion posted 01/05/2009 : 05:32:27
Or read this blog post http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx
or this http://weblogs.sqlteam.com/peterl/archive/2008/11/26/Finding-streaks-in-data.aspx

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2009-01-05 : 05:46:16
select identity(int,1,1)as id, * into #temp from urtable

select validfrom ,validto ,case when validto =
(select validfrom from #temp where id = v.id + 1) then 1 else 0 end as col1
from #temp v
Go to Top of Page

AKP2008
Starting Member

45 Posts
Posted - 2009-01-05 : 05:50:08
quote:
Originally posted by visakh16

CREATE TABLE #Temp
(
VALIDFROM datetime,
VALIDTO datetime,
Seq int
)
INSERT INTO #Temp (VALIDFROM,VALIDTO)
SELECT '2008-01-23 00:00:00.000','2008-02-22 00:00:00.000' UNION ALL
SELECT '2008-02-22 00:00:00.000', '2008-03-23 00:00:00.000' UNION ALL
SELECT '2008-03-23 00:00:00.000', '2008-04-23 00:00:00.000' UNION ALL
SELECT '2008-06-23 00:00:00.000', '2008-08-13 00:00:00.000' UNION ALL
SELECT '2008-08-13 00:00:00.000', '2008-08-24 00:00:00.000' UNION ALL
SELECT '2008-08-24 00:00:00.000', '2008-09-15 00:00:00.000' UNION ALL
SELECT '2008-10-1 00:00:00.000', '2008-12-20 00:00:00.000' UNION ALL
SELECT '2008-10-15 00:00:00.000', '2008-10-18 00:00:00.000' UNION ALL
SELECT '2008-10-18 00:00:00.000', '2008-12-22 00:00:00.000' UNION ALL
SELECT '2008-11-19 00:00:00.000', '2008-12-20 00:00:00.000' UNION ALL
SELECT '2008-12-20 00:00:00.000', '2008-12-30 00:00:00.000' UNION ALL
SELECT '2008-12-30 00:00:00.000', '2009-01-12 00:00:00.000' UNION ALL
SELECT '2009-01-12 00:00:00.000', '2009-01-20 00:00:00.000' 

CREATE CLUSTERED INDEX IDX ON #Temp(VALIDFROM,VALIDTO)


UPDATE #Temp SET Seq=0
DECLARE @ValidTo datetime,@ValidFrom datetime,@Seq int
SELECT TOP 1 @ValidTo=VALIDTO,@ValidFrom=VALIDFROM,@Seq=SEQ
FROM #Temp
ORDER BY VALIDFROM

UPDATE #Temp
SET @Seq=SEQ=CASE WHEN VALIDFROM=@ValidTo THEN @Seq ELSE @Seq+1 END,
@ValidFrom=VALIDFROM,
@ValidTo=VALIDTO

select * from #Temp

drop table #Temp


output
----------------------------------------------------------------
VALIDFROM	VALIDTO	Seq
2008-01-23 00:00:00.000	2008-02-22 00:00:00.000	1
2008-02-22 00:00:00.000	2008-03-23 00:00:00.000	1
2008-03-23 00:00:00.000	2008-04-23 00:00:00.000	1
2008-06-23 00:00:00.000	2008-08-13 00:00:00.000	2
2008-08-13 00:00:00.000	2008-08-24 00:00:00.000	2
2008-08-24 00:00:00.000	2008-09-15 00:00:00.000	2
2008-10-01 00:00:00.000	2008-12-20 00:00:00.000	3
2008-10-15 00:00:00.000	2008-10-18 00:00:00.000	4
2008-10-18 00:00:00.000	2008-12-22 00:00:00.000	4
2008-11-19 00:00:00.000	2008-12-20 00:00:00.000	5
2008-12-20 00:00:00.000	2008-12-30 00:00:00.000	5
2008-12-30 00:00:00.000	2009-01-12 00:00:00.000	5
2009-01-12 00:00:00.000	2009-01-20 00:00:00.000	5



Hi,

Thanks for your solution visakh. It works perfectly.

Is it possible to get this with single select statement. Please let me know.

Thanks.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-01-05 : 05:57:14
Yes you can get that in a single statement but performance will suck for a reasonable large amount of records.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-01-05 : 06:03:32
Are you using SQL Server 2005 or SQL Server 2000?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

AKP2008
Starting Member

45 Posts
Posted - 2009-01-05 : 06:48:04
quote:
Originally posted by Peso

Are you using SQL Server 2005 or SQL Server 2000?


E 12°55'05.63"
N 56°04'39.26"


I am using SQL Server 2005.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site