Re: Group By Earliest Date

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Prasad Koukuntla (prasad.koukuntla_at_scapromo.com)
Date: 03/25/04 

Date: Thu, 25 Mar 2004 12:35:29 -0600

This is called correlated subquery. Please read about this in BOL We are
using this format so that we can get more columns than the ones involved in
the Group by clause.

Regarding the error, please change your MIN and group by sections as follows
and try again. Sorry about this. I have not tested this query.

MIN(B.CourseDate)

GROUP BY A.SAPNumber, B.CourseLocation

"mj" <anonymous@discussions.microsoft.com> wrote in message
news:13b5301c41293$f5b663c0$a101280a@phx.gbl...
> I'm a little new to this so bear with me if you can :) I'm
> getting a "syntax error near CourseLocation".
>
> The part where I get a little lost is with the Aliases A
> and B in the WHERE statement. I understand that we're
> trying to select only those values where
> tblPhysicianRegistration.CourseDate is equal to the
> minimum CourseDate value from tblPhysicianRegistration but
> why do we have the A and the B after
> dbo.tblPhysicianAccounts below?
>
> FROM dbo.tblPhysicianAccounts A RIGHT OUTER JOIN
> dbo.tblPhysicianRegistration B ON A.PhysicianID =
> B.PhysicianID
>
> >-----Original Message-----
> >Get the minimum CourseDate for a SAPNumber and
> CourseLocation combination.
> >Try something similar to this.
> >
> >SELECT dbo.tblPhysicianAccounts.SAPNumber,
> >dbo.tblPhysicianRegistration.CourseDate,
> >dbo.tblPhysicianRegistration.CourseLocation
> >FROM
> >dbo.tblPhysicianAccounts RIGHT OUTER JOIN
> >dbo.tblPhysicianRegistration ON
> dbo.tblPhysicianAccounts.PhysicianID =
> >dbo.tblPhysicianRegistration.PhysicianID
> >WHERE
> >(dbo.tblPhysicianRegistration.CourseAttended = 'Yes') AND
> >(NOT (dbo.tblPhysicianAccounts.SAPNumber IS NULL))
> >AND tblPhysicianRegistration.CourseDate =
> > (SELECT MIN
> (tblPhysicianRegistration.CourseDate)
> > FROM
> > dbo.tblPhysicianAccounts A RIGHT
> OUTER JOIN
> > dbo.tblPhysicianRegistration B ON
> A.PhysicianID =
> >B.PhysicianID
> > WHERE
> > tblPhysicianAccounts.SAPNumber =
> A.SAPNumber AND
> >
> tblPhysicianRegistration.CourseLocation=
> >A.CourseLocation AND
> >
> (dbo.tblPhysicianRegistration.CourseAttended =
> >'Yes') AND
> > (NOT
> (dbo.tblPhysicianAccounts.SAPNumber IS NULL))
> > GROUP BY
> >
> dbo.tblPhysicianAccounts.SAPNumber,
> >
> dbo.tblPhysicianRegistration.CourseLocation)
> >
> >
> >
> >
> >"mj" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:ecf401c41286$39450a00$a001280a@phx.gbl...
> >> Hi. Any help with this would be great. I have a query
> that
> >> lists a bunch of accounts, when they trained on a
> medical
> >> procedure, and where. Some account trained on more than
> >> one date and/or at more than on place. I'm trying to
> show
> >> just the earliest date on which they trained which I was
> >> able to do using "First" in an Access query, but I'm not
> >> sure how to do it in SQL Server. Any thoughts? Thanks!
> >>
> >> The query looks like this:
> >>
> >> SELECT
> >> dbo.tblPhysicianAccounts.SAPNumber,
> >> dbo.tblPhysicianRegistration.CourseDate,
> >> dbo.tblPhysicianRegistration.CourseLocation
> >> FROM
> >> dbo.tblPhysicianAccounts RIGHT OUTER JOIN
> >> dbo.tblPhysicianRegistration ON
> >> dbo.tblPhysicianAccounts.PhysicianID =
> >> dbo.tblPhysicianRegistration.PhysicianID
> >> WHERE
> >> (dbo.tblPhysicianRegistration.CourseAttended = 'Yes')
> AND
> >> (NOT (dbo.tblPhysicianAccounts.SAPNumber IS NULL))
> >> GROUP BY
> >> dbo.tblPhysicianAccounts.SAPNumber,
> >> dbo.tblPhysicianRegistration.CourseDate,
> >> dbo.tblPhysicianRegistration.CourseLocation
> >
> >
> >.
> >

Relevant Pages

  • B.Course Location
    ... >This is called correlated subquery. ... >> The part where I get a little lost is with the Aliases A ... >> minimum CourseDate value from tblPhysicianRegistration ... >> FROM dbo.tblPhysicianAccounts A RIGHT OUTER JOIN ...
    (microsoft.public.sqlserver.programming)
  • Re: Formatting dates on insert or update - Help required please
    ... Using those local format is a total violation of a tiered architecture ... Uses an OUTER JOIN to find invalid ... CREATE TABLE FixBadDates ... bad_date CHAR NOT NULL, ...
    (comp.databases.ingres)
  • Re: Group By Earliest Date
    ... Get the minimum CourseDate for a SAPNumber and CourseLocation combination. ... > The query looks like this: ... > dbo.tblPhysicianAccounts RIGHT OUTER JOIN ...
    (microsoft.public.sqlserver.programming)
  • Joins in details
    ... inner join and the second join will be outer join. ... Links to study Joins in details (except BOL i have gone through that.) ...
    (microsoft.public.sqlserver.programming)
  • formatting numbers after using the nz function
    ... i created an outer join in access 2003. ... in a report as standsrd with two decimal places. ... to change the nulls to zeros, which works fine but afterwords I cannot format ... the numbers in either the query or the report. ...
    (microsoft.public.access.queries)