SQL Select Command Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that's as follows.

Report Table
ReportAN bigint (autonumber),
ClientId int,
BureauAN tinyint
DateEntered smalldatetime,

The ClientId is our customer, they will have several reports in this table. I need to get the most recent report for each client for each bureau. There are several bureaus so my output will look something like this.

ReportAN, ClientId, BureauAN, DateEntered
4, 2, 2, 6/10/2004
6, 2, 3, 6/1/2004
7, 3, 2, 6/5/2004

What would my select statement look like for this? Thanks.
 
SELECT ClientID
, BureauID
, MAX(DateEntered)
FROM ReportTable
Group By ClientID
Order By --whatever Field you want ordered by
With Rollup --Or Cube

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
SubstandardSnowman said:
I have a table that's as follows.

Report Table
ReportAN bigint (autonumber),
ClientId int,
BureauAN tinyint
DateEntered smalldatetime,

The ClientId is our customer, they will have several reports in this
Click to expand...
table. I need to get the most recent report for each client for each bureau.
There are several bureaus so my output will look something like this.
 
In addition, you may want to just do this client side and use
DataTable.Compute http://www.knowdotnet.com/articles/expressions.html
You can do some pretty advanced grouping with this too.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
SubstandardSnowman said:
I have a table that's as follows.

Report Table
ReportAN bigint (autonumber),
ClientId int,
BureauAN tinyint
DateEntered smalldatetime,

The ClientId is our customer, they will have several reports in this
Click to expand...
table. I need to get the most recent report for each client for each bureau.
There are several bureaus so my output will look something like this.
 
Back
Top