Query Help

Question

Post reply

Query Help

khushbu

SSCarpal Tunnel

Points: 4591
More actions
August 12, 2010 at 2:11 am

#144606

Hi
I have a requirement to update my enddate with value which is 1 day less than other row start date.
My table looks like
Rowiddeccode tariefStartdateEnddate
101012743002005-01-01NULL
201012743002006-01-01NULL
301012743002008-01-01NULL
401022743002005-01-01NULL
501022743002006-01-01NULL
And it should have updated ENDDATE AS
Rowiddeccode tarief Startdate Enddate
101012743002005-01-012005-12-31
201012743002006-01-012007-12-31
301012743002008-01-012004-12-31
401022743002005-01-012005-12-31
501022743002006-01-012099-01-01

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

How do you know which row the enddate should be derived from?

Are there gaps in the sequence suggested by column RowID?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 2

There are no gaps in rowid.

The enddate for row = 1 should come from rowid = 2,.... and for last rowid there is default value

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

If RowID has no gaps and can be reliably used to identify "the other row", then something like this should work for you:

CREATE TABLE #MyTable (

Rowid INT,

deccode CHAR(6),

tarief INT,

Startdate DATETIME,

Enddate DATETIME)

INSERT INTO #MyTable (Rowid, deccode, tarief, Startdate, Enddate)

SELECT 1, '010127', 4300, '2005-01-01', NULL UNION ALL

SELECT 2, '010127', 4300, '2006-01-01', NULL UNION ALL

SELECT 3, '010127', 4300, '2008-01-01', NULL UNION ALL

SELECT 4, '010227', 4300, '2005-01-01', NULL UNION ALL

SELECT 5, '010227', 4300, '2006-01-01', NULL

SELECT s.Rowid, s.deccode, s.tarief, s.Startdate,

e1.Startdate,

e2.Startdate,

AnEndDate = DATEADD(d,-1, s.Startdate)

FROM #MyTable s

-- Guessing there's a partition on deccode

LEFT JOIN #MyTable e1 ON e1.deccode = s.deccode

AND e1.Rowid = s.Rowid+1

LEFT JOIN #MyTable e2 ON e2.deccode = s.deccode

AND e2.Rowid = s.Rowid-1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 4

The query does not give result as desired.

The enddate for rowid = 1 should be 2005-12-31 while it gives 2004-12-31.

The query seems to be substracting 1 from the same rowid's startdate and not the next row's start date.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

khushbu (8/12/2010)
The query does not give result as desired.
The enddate for rowid = 1 should be 2005-12-31 while it gives 2004-12-31.
The query seems to be substracting 1 from the same rowid's startdate and not the next row's start date.

Of course.

At the time the query was written, your requirements were unclear. The column "AnEndDate" merely shows you how to subtract one day from a datetime.

The column you want to use for your enddate is either e1.Startdate or e2.Startdate, with one day subtracted.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 6

e1.startdate or e2.startdate can be used, but dont know why, there is NULL in some rows, while there exists data in all the rows of s.startdate.

I know NULL can come in last row if e1.startdate and last 2 rows of e2.startdate but in between NUll is what I fail to undertsand

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

khushbu (8/12/2010)
e1.startdate or e2.startdate can be used, but dont know why, there is NULL in some rows, while there exists data in all the rows of s.startdate.
I know NULL can come in last row if e1.startdate and last 2 rows of e2.startdate but in between NUll is what I fail to undertsand

<<e1.startdate or e2.startdate>> only one is correct. You must decide which.

There are nulls in other rows because I've joined the table to itself using deccode as well as the rowid. Is the data partitioned on deccode? Can the startdate of one deccode be adjusted then used as the enddate for a different deccode? You must decide.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 8

the data is partitioned on deccode, tarief and startdate

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

khushbu (8/12/2010)
the data is partitioned on deccode, tarief and startdate

That's a description of a row, not a partition, which is a group of rows with a common value in one of the columns.

Your column deccode has two values in the sample you posted. Can a startdate from one deccode be used as the enddate for another?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 10

Thanks for all the help!

But may be I did not get the partition part .

No, a startdate from one deccode cannot be used as the enddate for another.

The end date should always be 1 day less than other row (irrespective of any deccode) start date.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

khushbu (8/12/2010)
Thanks for all the help!
But may be I did not get the partition part .
No, a startdate from one deccode cannot be used as the enddate for another.
The end date should always be 1 day less than other row (irrespective of any deccode) start date.

Ok, so the enddates within one deccode must all be derived from startdates from within the same deccode.

Next question for you - please define "other row".

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 12

posting a sample output required.

Rowid deccode tarief Startdate Enddate

1 010127 4300 2005-01-01 2005-12-31

2 010127 4300 2006-01-01 2007-12-31

3 010128 4300 2008-01-01 2004-12-31

4 010229 4300 2005-01-01 2005-12-31

5 010230 4300 2006-01-01 2099-01-01

when rowid = 1, start date = 2005-01-01 then for same rowid end date = 2005-12-31 (which came from startdate=2006-01-01 - 1 of rowid = 2 )

and this cycle repeats

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

khushbu (8/12/2010)
posting a sample output required.
Rowid deccode tarief Startdate Enddate
1 010127 4300 2005-01-01 2005-12-31
2 010127 4300 2006-01-01 2007-12-31
3 010128 4300 2008-01-01 2004-12-31
4 010229 4300 2005-01-01 2005-12-31
5 010230 4300 2006-01-01 2099-01-01
when rowid = 1, start date = 2005-01-01 then for same rowid end date = 2005-12-31 (which came from startdate=2006-01-01 - 1 of rowid = 2 )
and this cycle repeats

Ok, so you don't partition on deccode. Then the code should look something like this:

SELECT s.Rowid,

s.deccode,

s.tarief,

s.Startdate,

Enddate = DATEADD(d,-1, e1.Startdate)

FROM #MyTable s

LEFT JOIN #MyTable e1 ON e1.Rowid = s.Rowid + 1

Note that this is a SELECT, converting it to an UPDATE is simple once it has been checked and verified.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Where does the end-date come from, for rowid 3?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden