March 21, 2012 at 3:38 am
I have a table in which there is a column named Transdate.
The values of Transdate column is like this:
Transdate
01/01/2012
07/01/2012
10/01/2012
12/01/2012
18/01/2012
21/01/2012
A new table should be created where Transdate and EffectivedDate should be there.
Format should be as follows:
Transdate EffectivedDate
01/01/2012` 07/01/2012
07/01/2012 10/01/2012
10/01/2012 12/01/2012
12/01/2012 18/01/2012
18/01/2012 21/01/2012
21/01/2012 28/01/2012
If any clue is there, pls help on this query.
March 21, 2012 at 3:49 am
Here's one way: -
BEGIN TRAN
--Create sample data
CREATE TABLE yourTable (Transdate DATE);
INSERT INTO yourTable
SELECT Transdate
FROM (VALUES('2012-01-01'),('2012-01-07'),('2012-01-10'),('2012-01-12'),
('2012-01-18'),('2012-01-21'))a(Transdate);
--Select from sample data
SELECT * FROM yourTable;
--Query to work out the "EffectiveDate"
SELECT Transdate, ISNULL(EffectivedDate,DATEADD(dd,7,Transdate))
FROM yourTable a
OUTER APPLY (SELECT TOP 1 Transdate
FROM yourTable
WHERE a.Transdate < Transdate
ORDER BY Transdate ASC) b(EffectivedDate);
--Create new table
CREATE TABLE yourNewTable (Transdate DATE, EffectivedDate DATE);
INSERT INTO yourNewTable
SELECT Transdate, ISNULL(EffectivedDate,DATEADD(dd,7,Transdate))
FROM yourTable a
OUTER APPLY (SELECT TOP 1 Transdate
FROM yourTable
WHERE a.Transdate < Transdate
ORDER BY Transdate ASC) b(EffectivedDate);
ROLLBACK
March 21, 2012 at 7:34 am
Hi
Check the following.
declare @yourTable table (Id int identity(1,1),Transdate DATE);
INSERT INTO @yourTable
SELECT Transdate
FROM (VALUES('2012-01-01'),('2012-01-07'),('2012-01-10'),('2012-01-12'),
('2012-01-18'),('2012-01-21'))a(Transdate);
Select * from @yourtable
SELECT a.Transdate,case when b.Transdate is null then dateadd(dd,7,a.Transdate) else (b.Transdate) end EffectivedDate
FROM @yourTable a left outer join @yourTable b
on a.Id = b.Id-1
Siva Kumar J
March 21, 2012 at 8:16 am
CELKO (3/21/2012)
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. This is minimal polite behavior on a SQL forum.Sample data is also a good idea, along with clear specifications. You posted one column and it was in the wrong format!
Since you did not bother with specs, this column looks like it holds two kinds of data, in violation of First Normal Form (1NF). Let's tryt o gfix this mess as best we can with what little you gave – not evena table name!
CREATE TABLE Scratch_Paper
(multi_purpose_date DATE NOT NULL PRIMARY KEY);
INSERT INTO Scratch_Paper
VALUES
('2012-01-01'),
('2012-01-07'),
('2012-01-10'),
('2012-01-12'),
('2012-01-18'),
('2012-01-21');
WITH X(multi_purpose_date, seq)
AS
(SELECT multi_purpose_date,
ROW_NUMBER() OVER (ORDER BY multi_purpose_date
FROM Scratch_Paper)
SELECT X1.multi_purpose_date AS trans_date,
X2.multi_purpose_date AS effective_date
FROM X AS X1
LEFT OUTER JOIN
X AS X2
ON X2.seq = X1.seq +1;
Missed a bracket in your code 😉
CREATE TABLE Scratch_Paper
(multi_purpose_date DATE NOT NULL PRIMARY KEY);
INSERT INTO Scratch_Paper
VALUES
('2012-01-01'),
('2012-01-07'),
('2012-01-10'),
('2012-01-12'),
('2012-01-18'),
('2012-01-21');
WITH X(multi_purpose_date, seq)
AS
(SELECT multi_purpose_date,
ROW_NUMBER() OVER (ORDER BY multi_purpose_date )/* Here */
FROM Scratch_Paper)
SELECT X1.multi_purpose_date AS trans_date,
X2.multi_purpose_date AS effective_date
FROM X AS X1
LEFT OUTER JOIN
X AS X2
ON X2.seq = X1.seq +1;
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply