Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Insert enddate using startdate Expand / Collapse
Author
Message
Posted Tuesday, August 10, 2010 1:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 19, 2011 6:00 AM
Points: 51, Visits: 110
HI,

I have two date columns in table , startdate and enddate .Enddaet column is null currently .Now i want to update endate column using startdate , when the start date of a row is ‘today’, the end date of the previous row with the same values of primary key column must be yesterday


How to achieve this ?
Post #966406
Posted Tuesday, August 10, 2010 3:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 19, 2011 6:00 AM
Points: 51, Visits: 110
Nobody to help ?
Post #966466
Posted Tuesday, August 10, 2010 3:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:00 AM
Points: 2,433, Visits: 7,501
The reason no-one has posted a response is because you've not made it easy to help you

Read this, post us some sample data and expected results in the format suggested in the article and you'll get some fully tested working solutions.

Remember, we're all volunteers here, so make it easy for us to help you



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #966477
Posted Tuesday, August 10, 2010 3:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
Need Sample data, sample script files (create table, insert into table etc etc) and a CLEAR desired-output.. This is help us to work on this straight away, friend !
Post #966484
Posted Tuesday, August 10, 2010 4:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 19, 2011 6:00 AM
Points: 51, Visits: 110
Hi ,

Here is the sample data .

Enddate is currently null .

What i want is the endate of 1st row should be the yesterday (from startdate ) of 2nd row .

2009-01-01 2009-12-31
2010-01-01 2010-02-14
2010-02-15 ----
-----

hope this would help you .


  Post Attachments 
sample data.txt (11 views, 4.61 KB)
Post #966492
Posted Tuesday, August 10, 2010 4:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
Aspg (8/10/2010)
Hi ,

Here is the sample data .

Enddate is currently null .

What i want is the endate of 1st row should be the yesterday (from startdate ) of 2nd row .

2009-01-01 2009-12-31
2010-01-01 2010-02-14
2010-02-15 ----
-----

hope this would help you .


No, Aspg, it dint help me, atleast! I guess u dint go thro the article pointed by skcadvre..

I will give the link again; please click on below link to see what/how myself and skcadvre are asking :
CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN

Post #966496
Posted Tuesday, August 10, 2010 4:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:00 AM
Points: 2,433, Visits: 7,501
Aspg (8/10/2010)
Hi ,

Here is the sample data .

Enddate is currently null .

What i want is the endate of 1st row should be the yesterday (from startdate ) of 2nd row .

2009-01-01 2009-12-31
2010-01-01 2010-02-14
2010-02-15 ----
-----

hope this would help you .


I built your sample data in your last thread, I simply don't have time to be doing it again. Please read the article I pointed you at, post some sample data and I'll happily have a look. It sounds like the problem is fairly simple, so I suspect if you follow the examples in the article I pointed you at then you'll get a couple of ways to achieve your result.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #966507
Posted Tuesday, August 10, 2010 4:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 19, 2011 6:00 AM
Points: 51, Visits: 110
SEE IF THIS HELPS ....

  Post Attachments 
sample data.txt (16 views, 921 bytes)
Post #966520
Posted Tuesday, August 10, 2010 4:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:02 PM
Points: 1,899, Visits: 18,917
think we need bit more of your original data

does this help?

  USE [TEMPDB]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ASPG]') AND type in (N'U'))
DROP TABLE [ASPG]
GO
CREATE TABLE [ASPG](
[Icode] [int] NULL,
[Tareef] [int] NULL,
[Startdate] [datetime] NULL,
[Volume] [int] NULL,
[Enddate] [datetime] NULL,
[Importdate] [datetime] NULL,
[status] [int] NULL
)

INSERT INTO [dbo].[ASPG]([Icode], [Tareef], [Startdate], [Volume], [Enddate], [Importdate], [status])
SELECT 104567, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104567, 2110, '20100101 00:00:00.000', 7246800, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104568, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104568, 2110, '20100101 00:00:00.000', 3756900, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104569, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104569, 2110, '20100101 00:00:00.000', 6058000, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104570, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104570, 2110, '20100101 00:00:00.000', 7333700, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104571, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104571, 2110, '20100101 00:00:00.000', 3753300, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104572, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104572, 2110, '20100101 00:00:00.000', 7271000, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104573, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104573, 2110, '20100101 00:00:00.000', 8041900, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104574, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104574, 2110, '20100101 00:00:00.000', 6567700, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104575, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104575, 2110, '20100101 00:00:00.000', 7431900, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104576, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104576, 2110, '20100101 00:00:00.000', 3654500, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104577, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104577, 2110, '20100101 00:00:00.000', 3939500, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104578, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104578, 2110, '20100101 00:00:00.000', 5804100, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104579, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104579, 2110, '20100101 00:00:00.000', 6787300, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104580, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104580, 2110, '20100101 00:00:00.000', 4206000, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104581, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104581, 2110, '20100101 00:00:00.000', 4809700, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104582, 2110, '20090101 00:00:00.000', 0, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104582, 2110, '20100101 00:00:00.000', NULL, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104583, 2110, '20090101 00:00:00.000', NULL, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104583, 2110, '20100101 00:00:00.000', NULL, '20091231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104584, 2110, '20090101 00:00:00.000', NULL, '20081231 00:00:00.000', '20100810 15:20:38.000', 0 UNION ALL
SELECT 104584, 2110, '20100101 00:00:00.000', NULL, '20091231 00:00:00.000', '20100810 15:20:38.000', 0




__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #966529
Posted Tuesday, August 10, 2010 4:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:00 AM
Points: 2,433, Visits: 7,501
Aspg (8/10/2010)
SEE IF THIS HELPS ....


Better

CREATE TABLE #sample
(
code VARCHAR(2),
stardate DATE NOT NULL,
enddate DATE NULL
)
INSERT INTO #sample
VALUES ( 1,'2004-01-01',NULL)
INSERT INTO #sample
VALUES ( 2,'2005-01-01',NULL)
INSERT INTO #sample
VALUES ( 3,'2004-01-01',NULL)
INSERT INTO #sample
VALUES ( 3,'2005-01-01',NULL)
INSERT INTO #sample
VALUES ( 4,'2004-01-01',NULL)
INSERT INTO #sample
VALUES ( 4,'2005-01-01',NULL)

UPDATE #sample
SET enddate = Dateadd(dd, -1, stardate)
FROM #sample

SELECT *
FROM #sample




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #966532
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse