473,394 Members | 1,831 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Swapping of Data

There is a database table name tblTenure with following fields :
IDNo (AutoNumber) Primary Key
PersonID (Text)
PersonName (Text)
Place (Text)
FromDate (Date)

I want to create a query based on the above tblTenure, which will display an additional Field/Column i.e. ToDate. The ToDate in the query should automatically be filled up by taking the FromDate of the next row of the person with same PersonID. The ToDate should be less by one day then the next FromDate of the person with same PersonID. The last row of the ToDate Column of the person should display the present date because he is still staying in that city.

IDNo.
PersonID
PersonName
Place
FromDate
ToDate

Example : tblTenure

IDNo PersonID PersonName Place FromDate
1 JN Jhon Mumbai 1/1/1987
2 JK Jack Delhi 7/8/1985
3 JN Jhon Kolkata 7/8/1989
4 JL Jill Pune 8/1/1990
5 JN Jhon Agra 5/10/1990
6 JL Jill Kolkata 10/10/1992
7 JN Jhon Kolkata 11/11/1995

The query should display the above data in following fashion :-

IDNo PersonID PersonName Place FromDate ToDate
1 JN Jhon Mumbai 1/1/1987 6/8/1989
3 JN Jhon Kolkata 7/8/1989 4/10/1990
5 JN Jhon Agra 5/10/1990 10/11/1995
7 JN Jhon Kolkata 11/11/1995 Should display Present Date
2 JK Jack Delhi 7/8/1985 Should display Present Date
4 JL Jill Pune 8/1/1990 9/10/1992
6 JL Jill Kolkata 10/10/1992 Should display Present Date
Nov 13 '10 #1
3 1262
ADezii
8,834 Expert 8TB
Just subscribing for now. I'm sure that I can come up with an answer, but I want to wait and see if a purely SQL solution can be found to this problem.

P.S. - Unless I am mistaken, your Query Results show a difference of 1 Month as opposed to 1 Day. For IDNo 1, the next Row of the person with the same PersonID is in Row #3 with a FromDate of 7/8/1989. In the Query Results for IDNo 1, you show a ToDate of 6/8/1989 (-1 Month) where it should be 7/7/1989 (-1 Day). Am I reading this incorrectly?
Nov 13 '10 #2
Sorry, you have mistaken. The ToDate in the query should automatically be filled up by taking the FromDate of the next row of the person with same PersonID. The ToDate should be less by one day then the next FromDate of the person with same PersonID. The last row of the ToDate Column of the person should display the present date because he is still staying in that city.
Nov 20 '10 #3
ADezii I think your percieving the date format as MM/DD/YYYY instead of DD/MM/YYYY. The examples given are however quite ambiguous as no numbers above 12 are utilized :)
Nov 22 '10 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: lawrence | last post by:
I had some code that worked fine for several weeks, and then yesterday it stopped working. I'm not sure what I did. Nor can I make out why it isn't working. I'm running a query that should return 3...
11
by: Qiangning Hong | last post by:
A class Collector, it spawns several threads to read from serial port. Collector.get_data() will get all the data they have read since last call. Who can tell me whether my implementation correct?...
0
by: NicK chlam via DotNetMonster.com | last post by:
this is the error i get System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement. at System.Data.Common.DbDataAdapter.Update(DataRow dataRows, DataTableMapping tableMapping) at...
3
by: bbernieb | last post by:
Hi, All, Is it possible to access a variable inside of a data binding, without the variable being out of scope? (Note: On the DataBinder line, I get an error message that says "Name 'i' is...
5
by: Gene | last post by:
What can I do if I want to get the result using the sql command? for example, the select command is "select Name from Employee where StaffID=10" How to get the "Name"??? dim Name as string and...
5
by: DC Gringo | last post by:
I am having a problem reading a simple update to the database. Basically I'm testing a small change to the pubs database -- changing the price of the Busy Executive's Database Guide from 19.99 to...
14
by: Rolf Welskes | last post by:
Hello, I have an ObjectDataSource which has as business-object a simple array of strings. No problem. I have an own (custom) control to which I give the DataSourceId and in the custom-control...
11
by: mukeshrasm | last post by:
Hi I want to swap the content of rows up or down based on the corresponding button clicked. <table border="0" width="200"> <tr> <td>first name </td><td>last name</td><td...
0
by: Winder | last post by:
Computer Data Recovery Help 24/7 Data recovering tools and services is our focus. We will recover your data in a cost effective and efficient manner. We recover all operating systems and media....
2
by: montakin | last post by:
I want to know details about swaping in c or c++ language and give me some example code on it....please..
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.