Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #11078

Re: Row numbers in a query to keep track of absences?

Show all headers | View raw

Am 04.09.2015 um 20:37 schrieb sara.mallicoat@genco.com:
> Okay, but now I have a problem - I also need to know how many days since their last DateOfAbs?  I had a previous query that would calculate that with an Int(Now()-[dateofabs]), but is there a way to combine these into this query as well?
>
> It would be awesome if I could, but I am not sure how at the moment?
>
>
> On Friday, September 4, 2015 at 1:05:41 PM UTC-5, Ulrich Möller wrote:
>> Am 04.09.2015 um 19:27 schrieb sara.mallicoat@genco.com:
>>
>> <snip>
>>> I am just a little confused why there is one gap for Chris?  Why?
>>> CODE:
>>>
>>> SELECT t1.ID, t1.Teammate, t1.DateOfAbsence, (SELECT top 1 DateOfAbsence
>>> FROM [Attendance] as t2
>>> WHERE t1.Teammate=t2.Teammate and t2.DateofAbsence < t1.DateOfAbsence
>>> ORDER BY DateOfAbsence desc, ID) AS prevDateOfAbs, DateDiff("d",[prevDateOfAbs],[DateOfAbsence]) AS DiffAbs
>>> FROM Attendance AS t1;
>>>
>>> 1	Ben	1/20/2015		
>>> 2	Ben	1/25/2015	20-Jan-15	5
>>> 3	Ben	4/20/2015	25-Jan-15	85
>>> 4	Ben	4/21/2015	20-Apr-15	1
>>> 5	Ben	8/20/2015	21-Apr-15	121
>>> 6	Chris	2/15/2015		
>>> 7	Chris	2/16/2015	15-Feb-15	1
>>> 8	Chris	2/28/2015	16-Feb-15	12
>>> 9	Chris	6/5/2015	28-Feb-15	97
>>> 10	Chris	6/6/2015	05-Jun-15	1
>>> 11	Chris	6/7/2015	06-Jun-15	1
>>> 12	Chris	8/20/2015		
>>> 13	Chris	8/22/2015	07-Jun-15	76
>>> 14	Don	1/25/2015		
>>> 15	Don	1/26/2015	25-Jan-15	1
>>> 16	Don	3/20/2015	26-Jan-15	53
>>> 17	Don	5/31/2015	20-Mar-15	72
>>> 18	Don	6/10/2015	31-May-15	10
>>> 19	Don	8/20/2015	10-Jun-15	71
>>>
>> <snip>
>>
>> I just tested your query and it worked as expected. Actually there
>> should not be a gap. Maybe there are something unexpected data in your
>> fields, especially in the row with the id 12, because this row is not
>> identified as a previous row. Based on the subselect's  where condition
>> i assume the contents of the field teammate or DateOfAbsence have some
>> additional not visible blank characters? You can test this with an art
>> hoc query like:
>>
>> SELECT Attendance.Teammate, Len([Teammate]) AS LenOfTeammate,
>> Attendance.DateOfAbsence, Format$(IsDate([DateOfAbsence]),"Yes/No") AS
>> IsDate
>> FROM Attendance;
>>
>> Ulrich
Yes it should be possible. Two ideas out of my head:
Create a second subquery in the same manner, where you pic up the max 
DateOfAbsendce by the given teammate name and then calculate the the 
difference with datediff. Instead using now() i would prefer the date() 
function.

Another way is to join the base table with a separate query and then do 
some calculations:

Create a new query named LastDayOfAbsence:

SELECT Teammate, Max(DateOfAbsence) AS MaxDateOfAbsence
FROM Attendance
GROUP BY Teammate;

and extend the primarily query with a join on this query:

SELECT t1.ID, t1.Teammate, t1.DateOfAbsence,
     (
     SELECT top 1 t2.DateOfAbsence
     FROM [Attendance] as t2
     WHERE t1.Teammate=t2.Teammate and t2.DateofAbsence < t1.DateOfAbsence
     ORDER BY DateOfAbsence desc,ID
     ) AS prevDateOfAbs,
     DateDiff("d",[prevDateOfAbs],
     t1.[DateOfAbsence]) AS DiffAbs,
     DateDiff("d",[t3].[MaxDateOfAbsence],Date()) AS DiffLast
FROM Attendance AS t1 LEFT JOIN LastDayOfAbsence AS t3
     ON (t1.DateOfAbsence = t3.MaxDateOfAbsence) AND (t1.Teammate = 
t3.Teammate);

I have not tried to combine the additional query into the first sql 
because it is more clearly and maintainable.

Ulrich

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-01 13:23 -0700
  Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-02 02:39 +0200
    Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-02 08:08 -0700
      Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-02 19:47 +0200
        Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-04 10:27 -0700
          Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-04 20:05 +0200
            Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-04 11:17 -0700
            Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-04 11:37 -0700
              Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-04 22:11 +0200
                Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-04 23:59 +0200
                Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-09 12:21 -0700
                Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-09 23:37 +0200
  Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-10 10:19 -0700
    Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-16 09:12 -0700
      Re: Row numbers in a query to keep track of absences? Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-09-16 09:50 -0700
      Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-16 21:02 +0200
        Re: Row numbers in a query to keep track of absences? "Phil" <phil@stantonfamily.co.uk> - 2015-09-16 20:42 +0100
        Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-16 13:12 -0700
          Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-16 13:18 -0700
            Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-17 04:05 +0200
              Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-17 13:49 -0700
                Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-18 11:21 +0200
                Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-18 08:48 -0700
                Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-18 19:44 +0200
                Re: Row numbers in a query to keep track of absences? Sara M <sara.mallicoat@genco.com> - 2015-09-18 13:43 -0700
                Re: Row numbers in a query to keep track of absences? Sara M <sara.mallicoat@genco.com> - 2015-09-22 11:43 -0700
                Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-23 00:39 +0200
                Re: Row numbers in a query to keep track of absences? Sara M <sara.mallicoat@genco.com> - 2015-09-23 06:11 -0700
                Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-23 18:23 +0200
                Re: Row numbers in a query to keep track of absences? Sara M <sara.mallicoat@genco.com> - 2015-09-23 09:29 -0700
                Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-24 01:00 +0200

csiph-web