Home » BizTalk Server

Calculate a date based on the date in a previous record

I need to update all the rows in a table with a date that increments by two months each time.

I.e.  Row1 should have a date of 1/3/2010,  Row2 = 1/5/2010, Row3 1/7/2010 

The tricky bit is that the user needs to be able to enter the row number, so that it can start on any row and the start date.

e.g. Input 7 and 1/9/2010 and the query update all rows from 7 onwards with date 1/9/2010 incrementing each time, so row8 is 1/11/2010 etc.

I know how to add months to a date, but I can't work out how to make the query work on the previous record.


Please help!!!


4 Answers Found


Answer 1

I need to update  all the rows  in a table  with a date  that increments by two months  each time.

I.e.  Row1 should have a date of 1/3/2010,  Row2 = 1/5/2010, Row3 1/7/2010 

The tricky  bit is that the user  needs to be able to enter  the row  number, so that it can start  on any row and the start date.

e.g. Input 7 and 1/9/2010 and the query  update all rows from 7 onwards  with date 1/9/2010 incrementing  each time, so row8 is 1/11/2010 etc.

I know how to add  months to a date, but I can't work  out how to make the query work on the previous  record.


Please help!!!

What exactly is the bigger picture that's going on? Generally speaking you store a base date in the record  and adjust it dynamically as its presented. For example and Invoice Date and then showing number  of days past due by doing the math from today's date backwards.

Also, [AND VERY IMPORTANTLY], the order in which records are stored in a database is entirely arbitrary. If you add 10 records one after the other, they may be stored in any order imaginable.


Answer 2

It sounds like a) you're working directly in tables; b) assume that Autonumber values are "row numbers"  (they're not); and c) want to store derived data. All three issues are problematic!

What's the context? What's in this table? Where is the user  going to "input 7"? If you have rows  1, 2 and 3, what about row  228123?



Answer 3

Thanks to you both.

The exact example is ....

I have a table  with a list of tasks (tbl_tasks).  Each task has an issue number  (issue) which is the key. Each task also has a date  it must be completed by (compDate). I've been using DateAdd to add  the 2 months  to the start  date, but can't work  out how to add the two months to the result from the previous.

user  needs to be able to enter  a task number, a start date and then a period (either monthly, bi-monthly, weekly, bi-weekly) an and then automaticlly update  all the rows  from the input  task number with the start date, and then subsequent tasks with the compDate set to the previous  tasks compDate plus the period selected.

e.g. User enters Task 7, start date 1/1/2010, and bi-montly

Task7 compDate should be 1/1/2010, Task8 1/3/2010, Task9 1/5/2010 all the way to the end of the table.

I've been trying to do this use the query  function and linking two queries togther.


Answer 4

Assuming you have a table  of estimated time  for starting a task after the start  of the project, like this:

Estimated      ForTask

24                   2  ' ie, add  24 days to the start of the project to get the start ot task 2

...                  ...

111                  5 

115                  6

125                   7  ' add 125 days to the start of the project to get the start of task 7

145                   8


Assuming all the task are already in the 'running' table,

Assuming that on MODIFYING, say, task 5, you then wish to AUTOMATICALLY update  starting (ending would be similar) tasks 6, 7 and 8


It is then a matter to change date  associated to task 6 with   115-111  days after newStartingDate for task 5

and to associate task 7 with  125-111 + newStartingDate for task 5, ... etc.


So, we need two parameter, the task number  which is modified and the newStartingDate for this task.


UPDATE runningTable    ' not the one with estimated time, but the one with actual data

SET startingDate = [newStartingDate]

         + DLookup("Estimated", "estimatedTimes", "task=" & task)

         - DLookup("Estimated", "estimatedTimes", "task=" & task -1)

WHERE task > [updatedTask]




Basically what I need to do is use VBA in Access 2007 to loop though a set of records in a table to increase a value by a percent (from a user defined form field) if there is no value in that field.  Below is some of my data, and in the field that says "what I need" is what I need the VBA to calculate for me.  This data and the years will always be different, as they are pulled from user defined fields.  I have used VBA before, but this goes a little over my understanding.  Any help would be great.

Job IDDescriptionLevelYearCostWhat I need
1Administrative Analyst12010$55.32 
1Administrative Analyst12011$57.26 
1Administrative Analyst12012$0.00$58.98
1Administrative Analyst12013$0.00$60.75
1Team Lead52010$176.35 
1Team Lead52011$182.52 
1Team Lead52012$0.00$188.00
1Team Lead52013$0.00$193.64
2Analyst Assistant42010$103.35 
2Analyst Assistant42011$106.96 
2Analyst Assistant42012$0.00$110.17
2Analyst Assistant42013$0.00$113.48
2Team Lead52010$151.23 
2Team Lead52011$156.53 
2Team Lead52012$0.00$161.22
2Team Lead52013$0.00$166.06



I am needing help writing a query that only counts certain fields based on previous record.  Let me explain:



PropIDBooking Date Booking Code

11/1/11 0

22/2/11 1

33/3/11 2

44/4/11 1

55/5/11 1

66/6/11 0


So I would like to Count all of the booking codes with Booking code ID =1 .  If a query is ran against this, you will get three.  Very straightforward.  My problem is I don;t want to count the booking code =1 if the previous record was 1 as well.  So when I run the new query, I want the answer to be 2 not three.

This one record set for a property.  I have to apply this logic to multiple properties in the same table.

Any direction/hints will be very appreciated.




Creating a report in visual studios and want to filter out data based on a date field. Want to show the post that are 3 months back in time according to my date field.




I tried finding a QA which already addressed this idea, but with no luck. Sorry if it's been addressed before.


I have a table where sales pricing is managed; it contains history of pricing where every price record has an 'effective date'. Currently when a user needs the 'active' price for a customer/item combination, the table is queried by the record that has the most recent 'EffectiveDate' before GETDATE(). Here is the def for table 'tim_PriceSheet'

ItemKey int, not null

EffectiveDate datetime, notnull

CurrID varchar(3)

ListPrice decimal (15,5)

The primary key is set on ItemKey, EffectiveDate

Sample Data:


ItemKey	EffectiveDate	    CurrID	ListPrice
45	2010-07-01 00:00:00.000	CAD	100.00000
45	2010-09-14 00:00:00.000	CAD	110.00000
45	2010-10-01 00:00:00.000	CAD	112.00000
63	2010-09-01 00:00:00.000	CAD	45.00000
63	2010-09-01 00:00:00.000	USD	45.00000
63	2010-09-18 00:00:00.000	CAD	40.00000
63	2010-10-01 00:00:00.000	USD	40.00000
63	2010-10-15 00:00:00.000	CAD	41.00000
63	2010-10-21 00:00:00.000	USD	41.00000

My challenge is this:

I want to return a record set which shows the current price for each distinct item and currency code; in the example below the result would be based on the date being September 16, 2010. 


ItemKey	EffectiveDate	    CurrID	ListPrice
45	2010-09-14 00:00:00.000	CAD	110.00000
63	2010-09-01 00:00:00.000	CAD	45.00000
63	2010-09-01 00:00:00.000	USD	45.00000

Any ideas how to construct this query?


with the sql statement below, i try to select the records based on the selected date, it return no rows from adapter filled datatable. 
however, when i run the query in sql query in sql server studio management, it returns records. 
date format = 09/28/2010

string[] splitDT = txtSelectedDate.Text.Split('/');

            string strRearrangeDT = splitDT[0] + "/" + splitDT[1] + "/" + splitDT[2];

using (SqlConnection sqlconn = new SqlConnection(connstring))
                DataTable dt = new DataTable();
                string strSQL = "SELECT userclaim_details3.id, userclaim_details3.userclaimid, userclaim_details3.userid, userclaim_details3.childcategorytableid, userclaim_details3.statusid, " +
                         "userclaim_details3.amount AS amountname, userclaim_details3.remark AS remarkname, userclaim_details3.datecreated AS datecreatedname, " +
                         "userclaim_details3.datemodified, approvaltable.id AS Expr1, approvaltable.typename AS statusname, usertable2.id AS Expr2, usertable2.name AS uname, " +
                         "childcategorytable.id AS Expr3, childcategorytable.categoryname AS categorynamename, childcategorytable.categorydescription AS catedescname " +
                        "FROM userclaim_details3 INNER JOIN " +
                         "approvaltable ON userclaim_details3.statusid = approvaltable.id INNER JOIN " +
                         "usertable2 ON userclaim_details3.userid = usertable2.id INNER JOIN " +
                         "childcategorytable ON userclaim_details3.childcategorytableid = childcategorytable.id " +
                         "WHERE CONVERT(varchar,userclaim_details3.datecreated,101) = '" + strRearrangeDT + "'";



I saving a value via a varaible


Private mlngData4 AsObject 

During saving a record...

I'm recording:



Sub TblMainDataBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TblMainDataBindingNavigatorSaveItem.Click

mlngData4 = FldLocationTextBox.Text 









when adding a new record:



Sub BindingNavigatorAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorAddNewItem.Click




IfMe.CheckBox4.Checked = TrueThen

mlngData4 = FldLocationTextBox.Text.ToString

' Program








But my textbox is not getting populated with the value from the previous record.. I debugged and the variable is being retained but not putting a value into the textbox on adding a new record ?





i recorded a new scenario and the generated code was generated under the file uimap.designer.cs.

i took this code and copy it to the fileuimap.cs and i changed it according my needs.

now, i want to add more things to my scenario and since i dont need the previous code (the one i took from uimap.designer.cs) i erased it and started a new record. but when i finish the new record, when i look at the new generated code i can still see the generated code from the old scenario + the new code i just recorded.

is it o.k? why to add the previous code if it's already deleted. how can i prevent it?



Hi All

I have a staging table that contains records from multiple file loads, the way that the files are loaded in the table are by “Chronological Order (List files by the oldest first)”


1 - Chronological Order = C.Order

2- Data Point = DP (it’s like data fields)

3- The PK is on Date and UserID for each file (not on the table).

So what I have in my table is like

C.Order                                 Date                    UserID           DP1           DP2

   1 (File1)           2010-01-01                1                 123            456

   1 (File1)           2010-01-01                2                 147            258

   1 (File1)           2010-01-01                2                 845            211

   1 (File1)           2010-01-01                3                 963            987

   1 (File1)           2010-01-01                4                 758            858

   1 (File1)           2010-01-01                12               487            458

   1 (File1)           2010-01-01                12               758            666


   2 (File2)           2010-01-02                1                 852            456

   2 (File2)           2010-01-01                2                 777            957

   2 (File2)           2010-01-01                4                 963            987

   2 (File2)           2010-01-01                7                 745            916


   3 (File3)           2010-01-01                2                 656            888

   3 (File3)           2010-01-01                3                 758            152

   3 (File3)           2010-01-01                5                 753            951

   3 (File3)           2010-01-02                1                 451            455


Now the business rule is that I have to insert and update (UpSert) the records into a final destination table by the file Order group (First is group 1 then 2 then etc... like a loop).

What I am looking for is to clean the above staging table before my final UpSert in a way that I will have


C.Order                                 Date                    UserID           DP1           DP2

   1 (File1)           2010-01-01                1                 123            456

   1 (File1)           2010-01-01                12               758            666   ß-number 12 is selected by random


   2 (File2)           2010-01-01                4                 963            987

   2 (File2)           2010-01-01                7                 745            916


   3 (File3)           2010-01-01                2                 656            888

   3 (File3)           2010-01-01                3                 758            152

   3 (File3)           2010-01-01                5                 753            951

   3 (File3)           2010-01-02                1                 451            455


What I did is to go through the data files from the biggest C.Order number (number 3) and compare the data with the previous days (All of them)

This needs a filtered INNER join (Recursion join) or maybe a Cursor or a CTE mostly to get the job

I wander does anyone have a script that does this or something like it?????

Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
I have a table with a list of patient id's and there begin date and end dates as follows:

ID     begin_date  end_date
1234 10/09/2009 10/16/2009
1234 12/16/2009 12/22/2009
1234 12/28/2009 12/30/2009
1234 01/13/2010 01/19/2010
1234 04/29/2010 05/05/2010

what i need to do is select the records that have an end_date within 30 days of a begin_date. So in the above example, i would need to get the following records:

ID     begin_date   end_date
1234 12/28/2009   12/30/2009
1234 01/13/2010   01/19/2010

Because 12/28/2009 is 30 days within 12/22/2009 (previous end_date) and 01/13/2010 is within 30 days of 12/30/2009 (previous end_date) and 12/22/2009 (2nd previous end_date).

is there a good way to do this using T-SQL?



I'm new DB programming and am looking for some ideas on how to validate a new record against previously entered records.  I have a datetime field that has to have a value greater than the last entered record for that field.  I've tried creating different dataviews but when I check the "new" dataview it only contains the new record. I've also tried to create a new dataset with a different query via XYZtableadapter.GetBy123 method. This throws a Constraints Violation error.


Some of the information that I've come across suggests that because I'm letting Visual Studio 2008 handle most of the details regarding the data connection and tableadapters that what I'm trying to do isn't possible and that I'm going to have to handle these details manually so can have 2 dataviews of the same table.


Any guidance that you can provide would be greatly appreciated.





The question is about getting previous id's of records using subqueries

-- Write a query for the Orders table and return, for each order, information about the current order, and also the previous order ID
-- Output:
orderid     orderdate                   empid       custid      prevorderid
----------- --------------------------- ----------- ----------- -----------
10248       2006-07-04 00:00:00.000     5           85          NULL
10249       2006-07-05 00:00:00.000     6           79          10248
10250       2006-07-08 00:00:00.000     4           34          10249


Hi everyone,

I have a table like this:


TNT         C585

PNP         B566


TNT        F566

Then I need to fill the empty record with the previous record (PNP on example), obviously

this is variable, I hope my explanation was clearly, thanks for your help



Hi all,

I have a table like this:


TNT         C585

PNP        B566



PNP       S855


TNT       S555

Then I need to fill the empty record with the previous record (PNP on example),

we can have from 1 to more sequencially empty field this is is variable, 

someone provide me a response yesterday but the code fill only the next empty record

this morning i m checked again and i found this problem

Thanks for your help again


(Reference discussion: Fill empty record with previous record using Query)


Article date showing previous date(1 day before actual article date) in sharepoint field in layout pages.

But in cqwp it shows correct date. Does anybody has this issue?


I need some guidance. I am teaching myself VB while creating a basic application I can use at work. Is there something in VB that will calculate a past and future date from a specific date. For instance, an Election Date falls on November 2, 2011. Once this election date is entered I want the program to calculate the reporting dates. The first report due is due exactly 32 days prior to the election. The 30 day post is due 30 days after the election.

Any guidance would be appreciated.

Thank you, Jim Neal


I'm attempting to use the fab 40 attendance template.

I don't need the time to show - Im able to hide that on the forms with jquery (endusersharepoint.com thank you!!)

I WAS ATTEMPTING to create a calculated column called Start Date where the formula simply reads '=[Start Time]'

When it's set to display 'Date Only' the date is off by a day. If I switch it to 'Date & Time' I get the correct date. Huh?



I have a little question about calculated columns in WSS 3.0 (not MOSS 2007)

How can I have a calculated column which is a Date+Time based on a text_column

Converting YYYYMMDD_HHMM (time = 24 hour format) format  ==> Date+Time


20070629_0848 converting to 29/06/2007 08:48

==> I have already a formule, but with teh DATE(...) function, you cannot give the hour-minute-seconds as a parameter, so the time is always 00:00 in WSS 3.0 (and yes, i have selected date+time as result-type for the column)

Is this possible in WSS to calculate a full date out of a text, maybe if i use two seperate text-columns, and convert a to date and one to time and a third which combines the two...

It would be a huge help if somebody could give me som advice to calculate a full date+time out of one or more text-columns....




I'm currently having a problem finding duration between two time fields: time_int and time_rst. The setup:

I currently have an external oracle server as a data source. I created a data set to query several things, among them - service_interruption, service_restored. The query is a bit tricky as I have to do an on the fly conversion from unix time to UTC. The way I've been instructed to do it is with a To_char( statement. Now that I have the data converted by the query, I get the 'mm/dd/yyyy hh24:mm', but I can't seem to find the duration between the two. I created a table in SSRS 2008 R2 that has the fields:

time_int  time_rst  ttr

for the values of those fields, I put the data that comes back from the oracle server and have it convert to date with a CDate arg for both time_int and time_rst. For ttr, I'm trying to get elapsed time. I've tried everything I've found everywhere and the closest I can hours and minutes. When the elapsed time is over a day, it truncates the day.


I'm trying to figure out how to get the formula to calculate the difference between:

10/24/2010  11:00 AM  and 10/25/2010  5:00 PM in the format of  either:

1 06:00   for 1 day 6 hours

30:00 for 30 hours


1 day hh:mm


All fields are dynamically filled by the query: Results would look like


10/24/2010  11:00 AM       10/25/2010  5:00 PM      <one of the results above>


I can't seem to get it to work. Please help with the exp.




when a phone call activity is created from the lead entity

Workflow as to check the due date attribute for the phone cal activity (which is created from the lead entity) if the due date is less than 1 day from the current date

its as to create a task for a phone cal activity owner

How can i achieve this task

Reply as soon as possible






I need to create the Audit records based on the particular opp record.

How can i do that.

Please provide me some c# code.




<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure