=DateDiff( between multiple dates?????

[weeblebiker]

Hi, first I'm marginally access literate but functionally SQL illiterate

I have a repair database which include [serialnumber] and [date in].
there may be multiple repairs on a unit so the table has duplicate [serialnumber]s and a different [date in] for each of the [serialnumber]for each time the unit has been in for repair

I want to find the time interval(s) between the dates as in date 1 and date 2, date 2 and date 3, and NOT date 1 and date 3

I've played with the =DateDiff( function and can get it to work between two dates.

I have no clue how to get the time between the first date and second date, second date and third date, etc,,

I think I need some form of repeating function (Macro?) selecting the first , next date then a looping last date used in function, next date, repeat till no more dates, but hove no idea how to write it and where to put it.

 

[pr2-eugin]

Okay to use DateDiff function it requires two dates as input and the interval (days/months/years etc.). You mentioned that it has a DateIn, but nothing about another date..

Then yes, you might need to SELECT all the Dates for that Serial number (probably into a Recordset object) and then loop through it finding the difference and adding them all up..

 

[weeblebiker]

Then yes, you might need to SELECT all the Dates for that Serial number (probably into a Recordset object) and then loop through it finding the difference and adding them all up..

this is the part I need help understanding
and how to "loop" through the dates where the differences between "a and b", "b and c", are calculated but not "a and c"

 

[Brianwarnock]

If there is only a date and presumably time in but no date/time out, then is it assumed that the out is equal to the next in, if so then you only need the difference between the max and min date/time

Brian

 

[pr2-eugin]

Taking the following example..

serialNumber    DateIn
111        01/01/2013
111        02/02/2013
111        01/03/2013
222        01/04/2013

So we find DateDiff("d", 01/01/2013, 02/02/2013), then DateDiff("d", 02/02/2013, 01/03/2013).. add them and the result would be 59? What happens to records with only one date?

 

[weeblebiker]

once I get a column of differences between dates I plan on using sum(IIf statements to categorize the results into 0-90 days, 90-180, 180-365, 365-730, and 730+ days.

 

[Brianwarnock]

Hello

Was my post invisible?

Paul how do you propose to do the arithmetic between different records?

Brian

 

[weeblebiker]

Taking the following example..

serialNumber    DateIn
111        01/01/2013
111        02/02/2013
111        01/03/2013
222        01/04/2013

So we find DateDiff("d", 01/01/2013, 02/02/2013), then DateDiff("d", 02/02/2013, 01/03/2013).. add them and the result would be 59? What happens to records with only one date?

above is what I'm looking for except not adding the datediff. also I'm not interested in one repair date entries (the repair was correct)

does this help?

Serial number Datein DateDiff
111 1/1/13
111 2/11/13 40
111 2/11/14 365
116 4/1/13
121 2/1/12
121 3/1/12 28

 

[Brianwarnock]

I don't know why I'm bothering but are your days not inclusive?

Brian

 

[pr2-eugin]

Hello

Was my post invisible?

I honestly did not see your post Brian, I apologize, I hit post reply and went on to another post..

Paul how do you propose to do the arithmetic between different records?

Well, I am just asking was this something the OP was looking for.. But based on the description, I guess not..

 

[weeblebiker]

I don't know why I'm bothering but are your days not inclusive?

Brian

uhm shure?

 

[Brianwarnock]

My comment was aimed at the op, I think that he has not thought this through but if he is happy with taking the next datin as the previous date out then he does not really need to add each one, and if he is not counting inclusive dates were did all the days go?

Brian

 

[Brianwarnock]

uhm shure?

What does that mean

 

[weeblebiker]

My comment was aimed at the op, I think that he has not thought this through but if he is happy with taking the next datin as the previous date out then he does not really need to add each one, and if he is not counting inclusive dates were did all the days go?

Brian

what is meant by inclusive and exclusive dates?

I think "taking the next datin as the previous date out" is what I want to do until there are no more dates, for each record with the same serial number.

 

[weeblebiker]

sorry for the confusion, I'm not literate enough in SQL/VBA to even form the question properly

 

[Brianwarnock]

Inclusive dates means 1 Jan to 2 Jan is two days, Datediff would return 1, thus your answer in the example you gave was a total of 405 days when it should be 407 for inclusive dates.

Now that you have answered the question i posed in my first post as affirmative, then it is simple

SELECT Table1.serialnumber, Max(Table1.datin) AS maxdatin, Min(Table1.datin) AS mindatin, DateDiff("d",[mindatin],[maxdatin])+1 AS totaldays
FROM Table1
GROUP BY Table1.serialnumber;

Replace Table1 with your table name

Note that I have not included blanks in any object name as they can lead to syntax errors and you have to include them in []

Brian

 

[weeblebiker]

thanks! it appears more simple than I suspected,
one last Q;
Where do I put it?
Field, Total, Sort, Criteria??? someplace else?

 

[Brianwarnock]

In your first post you did admit to being SQL illiterate so let's see if we can explain.

You probably use the design grid to create your queries, and owing to the way Access works when you create a new query it opens the design grid and won't let you switch to SQL view until you have dragged a table into the grid, well in my level anyway.

So do that and then switch to SQL view and copy and paste my code over the code there, make the change to table name and any other object names and then switch back to design view and you will see how it is coded in that view.

Posters will post the SQL. View because it is simple to do, a pity we cannot bypass the grid for new queries, don't get me wrong I did it in the grid, it would just make it simpler for people copying in code.

Brian

 

[Brianwarnock]

BTW I'm glad that you admit your ignorance of things, we are all ignorant of many things and knowing the target audience helps to get things right.

Brian

 

[weeblebiker]

thanks,

I did fumble around and right clicked and found the SQL View

looks like the code is returning DateDiff between the first [Date in] and last [Date in] for each [Serial #], not DateDiff Between [Date in] 1 and [Date in] 2, [Date in] 2 and [Date in] 3

Here is the code

SELECT [XRS-3 repair].[Serial #], Max([XRS-3 repair].[Date in]) AS maxdatein, Min([XRS-3 repair].[Date in]) AS mindatein, DateDiff("d",[mindatein],[maxdatein])+1 AS totaldays
FROM [XRS-3 repair]
GROUP BY [XRS-3 repair].[Serial #];


I don't know how to do the code upload thingy