April 1, 2011 at 5:37 am
Hi Everybody
This is my first post on this forum.
I have a table called TempTest sorted first by NMI and then by StartDate. There can be multiple records for a value of NMI but the StartDate values will be different for the same values of an NMI. This other fields in this table are ID, Desc and EndDate.
Is there a way using SQL and DML that I can get, for each record in the table, the values of the StartDate and EndDate of the next record for the same value of NMI. This would mean the these fields would be NULL for the last record for a particular value of NMI.
In other words, I would want to see, either in table TempTest or another table, for each record (nth record), all the original fields plus the additional StartDate and EndDate fields showing the values for the next record {(n + 1)th record} for each value of NMI - except the last record for that value of NMI which will have a value of NULL.
Someone has suggested this (but I don't understand this - if someone does, could they please explain it to me) :-\
Update T
Set EndDate = TT.EndDate
from TempTest T inner join TempTest TT on
T.NMI = TT.NMI and
T.EndDate = dateadd(d, -1, TT.Startdate)
while @@rowcount > 0
begin
Update T
Set EndDate = TT.EndDate
from TempTest T inner join TempTest TT on
T.NMI = TT.NMI and
T.EndDate = dateadd(d, -1, TT.Startdate)
end
delete TT
from TempTest T inner join TempTest TT on
T.NMI = TT.NMI and
T.EndDate = TT.Enddate
and T.StartDate < TT.StartDate
Best regards
Deepak Agarwal
April 1, 2011 at 6:21 am
Hi Deepak,
you did a good job and posted a clear question. However, it would make things a lot easier for us if you also included the CREATE TABLE statement for all the tables involved in your case. It would really be great if you could also provide some sample data and the expected result based on the sample data.
You can take a look at the first article in my signature line to see how to do this effectively.
I will try to reply as soon as you provide the info.
-- Gianluca Sartori
April 1, 2011 at 6:40 am
WITH a (NMI,StartDate,ID,[Desc],EndDate,RowNum) AS (
SELECTNMI,StartDate,ID,[Desc],EndDate,ROW_NUMBER() OVER (ORDER BY NMI,StartDate) AS [RowNum]
FROMTempTest
)
SELECT a.NMI,a.StartDate,a.ID,a.[Desc],a.EndDate,a.RowNum,b.StartDate AS [NextStartDate],b.EndDate As [NextEndDate]
FROM a
LEFT JOIN a b ON b.NMI=a.NMI AND b.RowNum=a.RowNum+1
Far away is close at hand in the images of elsewhere.
Anon.
April 1, 2011 at 4:13 pm
Hi Guys
Thanks for your responses.
David
Where is the table definition for your table 'b'? And how do you know that the rows in table 'b' will be ordered in the same way as those in table 'a' - I do not see anywhere where you have specified the ordering of rows in table 'b'?
Could you please explain it to me?
Deepak
P.S.
I don't have any Create table statement - the TempTest table is just coming from an existing table as a subset. The sample data set is here if that helps. There are no primary keys and the combined fields NMI, StartDate and EndDate make each record unique. Hope this helps
ID NMI Desc StartDate EndDate
Test1 1234567 FRMP 17/01/2008 25/02/2008
Test1 1234567 FRMP 30/03/2008 25/07/2008
Test1 1234567 FRMP 26/07/2008 02/02/2009
Test1 1234567 FRMP 03/02/2009 25/05/2010
Test1 1234567 FRMP 30/01/2011 31/12/9999
April 2, 2011 at 9:11 am
deepakagarwalathome (4/1/2011)
P.S.
I don't have any Create table statement - the TempTest table is just coming from an existing table as a subset. The sample data set is here if that helps. There are no primary keys and the combined fields NMI, StartDate and EndDate make each record unique. Hope this helps
ID NMI Desc StartDate EndDate
Test1 1234567 FRMP 17/01/2008 25/02/2008
Test1 1234567 FRMP 30/03/2008 25/07/2008
Test1 1234567 FRMP 26/07/2008 02/02/2009
Test1 1234567 FRMP 03/02/2009 25/05/2010
Test1 1234567 FRMP 30/01/2011 31/12/9999
You may not have one, but you can certainly make one. It's easy! Just throw a CREATE TABLE statement in there with some data types and you're more or less done. After that, convert your data set to select statements with an insert above them so that they can be inserted into your newly created table. It might seem like a bit of extra work, but in many cases, for us to ensure our code is correct, we'll need to test it, and that will require *us* to do that extra work.
For a more specific and detailed explanation of how to post sample data for easy consumption on the forum, see the link in my signature.
April 2, 2011 at 12:37 pm
deepakagarwalathome (4/1/2011)
Where is the table definition for your table 'b'? And how do you know that the rows in table 'b' will be ordered in the same way as those in table 'a' - I do not see anywhere where you have specified the ordering of rows in table 'b'?
Deepak,
He is using a CTE, Common Table Expression, that assigned the row numbers, and then is using it twice in the FROM statement, once as a and once aliased as b. (So a and b are exactly the same "table".) In his example the table that actual contains the data is TempTest.
April 3, 2011 at 2:03 am
Hi SSCommitted
Thanks for letting me know about the CTE - just read a bit about it!
Even so, doesn't table 'b' need to be said (defined) somewhere in the CTE as table has been? Otherwise, how would the system know that table 'b' is also the same as table 'a' in its definition?
Please bear with my ignorance on this? I have not worked with this kind of structure before!
Deepak
P.S. - Please ignore this post - did some further reading on the net and understood David's code! Thanks again! I will see if I can put this to work tomorrow at work and may come back if it doesn't work as I believe it should!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply