Post reply

Selecting max date

  • March 14, 2007 at 11:28 am

    #117904

    I have a simple enough query:

    SELECT MAX(BRenteredDate), BRid, BRclientOfficeID, BRbillItemId

    FROM billrate GROUP BY BRenteredDate, BRid, BRclientOfficeID, BRbillItemId

    I'm trying to get the most recent entered date. I want it to grab the latest entered date based on a combo of BRclientOfficeID and BRbillItemId.

    Here's an example

    (BRbillItemId, BRclientOfficeId, price, user, date)

    BI37 CO1002 3.0 sam 2005-09-20 09:15:46.000

    BI37 CO1002 3.0 sam 2005-09-28 00:00:00.000

    I want the MOST RECENT date row...

    But everytime I run the above query, it still returns both dates.

    Any ideas on how I could just get the query to return the most recent date?

    Thanks!

  • March 14, 2007 at 11:40 am

    #695031

    Try taking BRenteredDate out of your GROUP BY.

    SELECT MAX(BRenteredDate), BRid, BRclientOfficeID, BRbillItemId

    FROM billrate GROUP BY BRid, BRclientOfficeID, BRbillItemId

  • March 14, 2007 at 12:52 pm

    #695042

    I tried this, and unfortunately it still pulls any and all dates, not just the max date.

    thanks

  • March 14, 2007 at 12:55 pm

    #695043

    I'll bet that BRid is also unique to each record and needs to be excluded from the query.  Your result set that you posted did not include it so we can't know for sure.

    Try

    SELECT MAX(BRenteredDate),  BRclientOfficeID, BRbillItemId

    FROM billrate GROUP BY  BRclientOfficeID, BRbillItemId

  • March 14, 2007 at 1:22 pm

    #695051

    You have a good point, mrpolecat.  I'll bet that's the issue.  However, if Magy wants to keep BRid in the query, it complicates it quite a lot, but it is possible....

    SELECT OuterQuery.BRenteredDate, OuterQuery.BRid, OuterQuery.BRclientOfficeID, OuterQuery.BRbillItemId

     FROM billrate OuterQuery

      INNER JOIN

       (SELECT MAX(BRenteredDate) AS BRenteredDate,  BRclientOfficeID, BRbillItemId

     FROM billrate

     GROUP BY  BRclientOfficeID, BRbillItemId

        ) InnerQuery

       ON OuterQuery.BRenteredDate = InnerQuery.BRenteredDate

       AND OuterQuery.BRclientOfficeID = InnerQuery.BRclientOfficeID

       AND OuterQuery.BRbillItemId = InnerQuery.BRbillItemId

  • March 14, 2007 at 1:26 pm

    #695053

    Oh, now you're just showing off

  • March 14, 2007 at 1:32 pm

    #695058

    Haha!  Not really.  It's just that I wrote a very similar query for someone else today over in this thread, and I figured I could just reuse it just in case that BRid column was important in some way. 

  • March 14, 2007 at 1:42 pm

    #695061

    Hey thanks guys...

    I guess I don't need BRid in there. I just had it in there so I could visually see what it was. But the final program won't need it.

  • March 14, 2007 at 1:45 pm

    #695062

    Well, I think I just complicated things more.

    This query was supposed to be a subquery in a larger query. But now whenever I run it, I get a:

    Server: Msg 116, Level 16, State 1, Line 1

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

  • March 14, 2007 at 1:57 pm

    #695068

    Without seeing the larger query, it's a bit hard to determine why you're getting that error.  Are you using an IN to introduce the subquery?

  • March 14, 2007 at 2:03 pm

    #695071

    yes, I have it written like this:

    SELECT COid, BIid, BRid, COofficeName, CMclientName, BRrate AS 'Client Rate',

    BIstandardRate AS 'Standard Rate', BIdescription

    FROM billrate, clientoffice, clientmaster, billitem

    WHERE 1=1

    AND BRclientOfficeId = COid

    AND BRbillItemID = BIid

    AND COclientMasterID = CMid

    AND BRdeleted '1'

    AND COofficeName IS NOT NULL

    AND COofficeName ''

    AND BRenteredDate IN (SELECT MAX(BRenteredDate), BRclientOfficeID, BRbillItemId

    FROM billrate GROUP BY BRclientOfficeID, BRbillItemId)

    ORDER BY COid, BIid, COofficeName

  • March 14, 2007 at 2:12 pm

    #695073

    Well, this changes everything.    Try this...

    SELECT COid, BIid, BRid, COofficeName, CMclientName, BRrate AS 'Client Rate',

    BIstandardRate AS 'Standard Rate', BIdescription

    FROM billrate A, clientoffice, clientmaster, billitem

    WHERE 1=1

    AND BRclientOfficeId = COid

    AND BRbillItemID = BIid

    AND COclientMasterID = CMid

    AND BRdeleted <> '1'

    AND COofficeName IS NOT NULL

    AND COofficeName <> ''

    AND BRenteredDate IN (SELECT MAX(BRenteredDate)

    FROM billrate B WHERE A.BRclientOfficeID = B.BRclientOfficeID AND A.BRbillItemId = B.BRbillItemId)

    ORDER BY COid, BIid, COofficeName

  • March 15, 2007 at 6:05 am

    #695188

    Awesome! I *think* this is working!

    Thank you!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply