Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Aug 30, 2024

Day Count Query

Hello Experts,

 

I am having issues making a day count query for 3 criteria.

 

I have a 1 table with tblDraws.[FundingDate] and I need to make a day count between the [FundingDate] but the criteria I need is where ProjID = ProjID and Type = Type and [ID] < the current [ID].

So there are 3 criteria (although I might be able to get by with only 2 criteria).  It is a little hard to explain but if you look at the attached excel I think it is  easier to understand.  Its not just a simple day count from the current record and the previous record based on [ID]...I need to consider the other 2 criteria. 

 

I have attached a sample db with only 1 table [tblDraws] and an example excel file for what I am looking for.

 

Grateful for the help.

Let me know if not clear. 

thank you

access
  • Ken_Sheridan's avatar
    Ken_Sheridan
    Sep 01, 2024

    Is this what you mean?

    SELECT D1.*,
        NZ((SELECT MIN(FundingDate)
               FROM tblDraws AS D2
               WHERE D2.ProjID = D1.ProjID
               AND D2.Type = D1.Type
               AND D2.FundingDate > D1.FundingDate)-FundingDate,0) AS DayCount
    FROM tblDraws AS D1
    ORDER BY ProjID, Type, FundingDate;

  • Ken_Sheridan's avatar
    Ken_Sheridan
    Brass Contributor
    Sep 01, 2024

    I think this is what you have in mind:

     

    SELECT D1.*, NZ(FundingDate -

        (SELECT MAX(FundingDate)

         FROM tblDraws AS D2

         WHERE D2.ProjID = D1.ProjID

         AND D2.Type = D1.Type

         AND D2.FundingDate < D1.FundingDate),0) AS DayCount

    FROM tblDraws AS D1;

     

    Note that using the autonumber primary key to determine the chronological sequence of rows might not be reliable.  Instead use the FundingDate column.

    • Tony2021's avatar
      Tony2021
      Steel Contributor
      Sep 01, 2024

      Ken_Sheridan 

      HI Ken, that is amazing.  thank you very much.  The count is accurate.  That is a very good observation about using the [FundingDate] vs the primary key and 100% accurate.  

      I have a follow up question though and wondering if you can help. 

      Can the result be shifted up a record as in the pic below?   I fiddled around with it and its beyond my level of knowledge.  

      thanks again Ken.  

       

       

      • Ken_Sheridan's avatar
        Ken_Sheridan
        Brass Contributor
        Sep 01, 2024

        Is this what you mean?

        SELECT D1.*,
            NZ((SELECT MIN(FundingDate)
                   FROM tblDraws AS D2
                   WHERE D2.ProjID = D1.ProjID
                   AND D2.Type = D1.Type
                   AND D2.FundingDate > D1.FundingDate)-FundingDate,0) AS DayCount
        FROM tblDraws AS D1
        ORDER BY ProjID, Type, FundingDate;

Resources