August 10, 2010 at 1:47 am
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 ?
August 10, 2010 at 3:18 am
Nobody to help ?:w00t:
August 10, 2010 at 3:32 am
The reason no-one has posted a response is because you've not made it easy to help you 😉
Read this[/url], 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 😛
August 10, 2010 at 3:44 am
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 !
August 10, 2010 at 4:08 am
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 .
August 10, 2010 at 4:12 am
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 :
August 10, 2010 at 4:28 am
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.
August 10, 2010 at 4:40 am
SEE IF THIS HELPS ....
August 10, 2010 at 4:48 am
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
and remember....every day is a school day
August 10, 2010 at 4:50 am
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
August 10, 2010 at 4:54 am
I have already done this but thats not the answer.
I want
PREVIOUS DAY OF STARTDATE OF CODE 2 SHOULD BE ENDDATE OF CODE 1
Check the sample output please.
August 10, 2010 at 4:58 am
For the ease of coding, i included an extra IDENTITY column so as to identify each record.. now, the code that will update enddate column with a value of ( startdate - 1 ) of the second row..
Here is the code:
declare @Sample table
( Rowid int identity(1,1) ,
code varchar(2),
Stardate datetime Not nUll,
Enddate datetime 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 S1
SET S1.EndDate = DATEADD(dd, -1,S2.stardate)
--SELECT *
FROM@Sample S1
CROSS JOIN@Sample S2
WHERE
S1.Rowid = S2.Rowid - 1
SELECT *
FROM@Sample S1
August 10, 2010 at 5:29 am
I dont have identity column in the table .
I am using cursor as the data is large .
so do i need to create table variable in cursor ?
Actually i am doing this in Execute sql of SSIS package .
August 10, 2010 at 6:28 am
Aspg (8/10/2010)
I dont have identity column in the table
Why not an IDENTITY column or even an ID column to mark rows ... hmmm.. anyways, take this ; this uses ROW_NUMBER function to assign row numbers on the fly.
; WITH CTE AS
(
SELECTROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN ,
code,
Stardate,
Enddate
FROM @Sample
)
UPDATE S1
SET S1.EndDate = DATEADD(dd, -1,S2.stardate)
--SELECT *
FROM CTE S1
CROSS JOIN CTE S2
WHERE
S1.RN = S2.RN - 1
SELECT *
FROM @Sample S1
To learn more about what ROW_NUMBER is, follow these articles :
ROW_NUMBER(): An Efficient Alternative to Subqueries - By Francis Rodrigues[/url]
Paging and Versioning Using ROW_NUMBER() - By Lawrence Moore[/url]
SQL Server Ranking Functions - By WayneS[/url]
Aspg (8/10/2010)
the data is large
If your table is very large, then break the update to happen in batches.
Aspg (8/10/2010)
I am using cursor as the data is large .so do i need to create table variable in cursor ?
Please try to avoid Cursors as possible.. thy are potential performance-degraders..
Please go through this 2-part wonderful article set from RBarryYoung on how to replace cursors :
There Must Be 15 Ways To Lose Your Cursors... part 1[/url]
Hope this helps!!
August 12, 2010 at 3:27 am
HI ,
While updating this data , the update is going in endless loop .
It doesnt found the rownt-1 row when rowcnt is 1
1-1 = 0
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply