ZURINET
asked on
Sql update Algorithms
Hi all
I have a table with the following attributes
see attachemt for question details
Thanks in Advance
I have a table with the following attributes
see attachemt for question details
Thanks in Advance
P_Id LastName Address validFrom Valid until status
1 Trinity 26Broad 01.05.2010 29.06.2010 1
2 Mike 20Broad 01.01.2011 29.02.2011 1
3 James 11Broad 01.03.2011 29.04.2011 0
4 Phili 13Broad 01.05.2011 29.06.2011 0
5 Obama 16Broad 01.07.2011 29.08.2011 1
6 Clinton 11Broad 01.03.2010 29.04.2010 0
7 Janet 26Broad 01.01.2010 29.02.2010 1
How can I update this table based on the validFrom and Valid until attribute
or I need to sort this table based on validFrom and Valid until attribute
Then using the P_Id value as a key and insert in another table Sorted ascending (ValidFrom & Valid until)
P_Id Name
7 Janet
6 Clinton
1 Trinity
question.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dear ShogunWade
Thanks for the input..
You mean to say .. If I do the order by stuff...
I could be able to retrive or sort the tables based on the validity..
The key idea behind the question is that .. for a particular sort .. I need to be able to retrive the values..
based on the validity period..
.ie get the first valid period, the second, the third and so on..
Thanks for the input..
You mean to say .. If I do the order by stuff...
I could be able to retrive or sort the tables based on the validity..
The key idea behind the question is that .. for a particular sort .. I need to be able to retrive the values..
based on the validity period..
.ie get the first valid period, the second, the third and so on..
Yes thats correct, using SELECT * FROM MyTable ORDER BY validFrom,[Valid until] will return the rows ordered by the valid from column first and then by valid until.
Say for exmaple you wanted the chronoligical order of entries where the address is 26borad (from your example)
SELECT * FROM MyTable WHERE Address='26Broad' ORDER BY validFrom,[Valid until]
Say for exmaple you wanted the chronoligical order of entries where the address is 26borad (from your example)
SELECT * FROM MyTable WHERE Address='26Broad' ORDER BY validFrom,[Valid until]
SET COLUMN_TO_BE_UPDATED = 'UPDATED'
FROM TABLENAME
WHERE validFrom BETWEEN '2010-01-01' AND '2010-03-01'
AND validuntil BETWEEN '2010-04-01' AND '2010-06-01'