Create End dates - 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)

Create End dates

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-09-21 : 10:35:06

Hi

i have a table like

CREATE TABLE [dbo].[dates](
[CODE] [varchar](4) NOT NULL,
[start_DATE] [smalldatetime] NOT NULL,
[end_date] [smalldatetime] NULL,
CONSTRAINT [PK_dates] PRIMARY KEY CLUSTERED
(
[CODE] ASC,
[start_DATE] ASC
)
)

insert into dates select (1, '10/01/1998', null)
insert into dates select (1, '10/01/1999', null)
insert into dates select (1, '10/01/2000', null)
insert into dates select (1, '10/01/2001', null)
insert into dates select (1, '10/01/2002', null)
insert into dates select (1, '10/01/2003', null)
insert into dates select (2, '10/01/1998', null)
insert into dates select (2, '12/01/1999', null)
insert into dates select (2, '10/01/2000', null)
insert into dates select (2, '09/01/2001', null)
insert into dates select (2, '01/01/2002', null)
insert into dates select (2, '10/01/2003', null)

i want to add end_DATE which are 1 day less than the start_date of next row for that code. ANy help

thnx

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-09-21 : 11:12:24

well i was able to do it on my own...

i created a separate table with an PK_ID as identity key and the remaining same columns and used that column to inner join on the same table like this

Update T1
Set T1.end_date = T2.start_date - 1
From [dates_helper] T1
Inner Join [dates_helper] T2 on T1.PK_ID = T2.PK_ID - 1

and last column is not updated so i manually update it and then move the values from the dates_helper to dates

rajarajan
Starting Member

48 Posts

Posted - 2011-09-21 : 11:12:46

declare @F datetime
Declare @E datetime
set @F='10/01/1998'

SELECT DATEADD(yy, DATEDIFF(yy,0,@F), 0),'First Day of the Year'
UNION
SELECT DATEADD(dd,-1,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@F)+1,0))),'Last Day of the Year'
you required like this or
can u show sample date

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-09-25 : 13:25:45

Using row_number, you do not need to create the extra table ...

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-10-27 : 15:59:00

namman,

Could you show me how to do it with row_number?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 06:49:46

you dont need row_number also. you can just use below


UPDATE t
SET t.End_Date=t1.Start_date -1
FROM [dbo].[dates] t
CROSS APPLY (SELECT TOP 1 Start_Date
             FROM [dbo].[dates]
             WHERE CODE = t.CODE
             AND Start_Date > t.Start_Date
             ORDER BY Start_Date ASC) t1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources