Max (date) and Count Help

Dev Articles Community Forums Sponsor:

March 14th, 2013, 09:47 PM

BooBooDaddy

Registered User

Join Date: Mar 2013

Posts: 2

Time spent in forums: 20 m 57 sec
Reputation Power: 0

Max (date) and Count Help

I need to return a listing that shows for the maximum date or current date - the Part, Expeditor, Forecast, DOS, and the number of times
that the part has appeared in the data (not just for the max date, but for the entire history of the part in the data) from the dataset
called PartLevelDetail

Part Expeditor Forecast DOS # Occurances
1234 A 100 3 5
4567 A 233 2 5
5443 C 222 3 3
9887 D 235 4 1

I am new to SQL. Can anyone help out a newbie?

March 16th, 2013, 06:39 PM

bitsmed

Registered User

Join Date: Jun 2012

Posts: 11

Time spent in forums: 1 h 59 m 53 sec
Reputation Power: 0

Hi,

Replace "thedate" with the name of the date field in your table:

Code:

select a.part
      ,b.expeditor
      ,b.forecast
      ,b.dos
      ,a.partcount
  from (select part
              ,max(thedate) as maxdate
              ,count(part) as partcount
          from partleveldetail
         group by part
       ) as a
       inner join partleveldetail as b
               on b.part=a.part
              and b.thedate=a.maxdate
 order by a.part

March 18th, 2013, 06:49 AM

BooBooDaddy

Registered User

Join Date: Mar 2013

Posts: 2

Time spent in forums: 20 m 57 sec
Reputation Power: 0

Quote:

Originally Posted by bitsmed

Hi,

Replace "thedate" with the name of the date field in your table:

Code:

select a.part
      ,b.expeditor
      ,b.forecast
      ,b.dos
      ,a.partcount
  from (select part
              ,max(thedate) as maxdate
              ,count(part) as partcount
          from partleveldetail
         group by part
       ) as a
       inner join partleveldetail as b
               on b.part=a.part
              and b.thedate=a.maxdate
 order by a.part

Thank you for your help

Viewing: Dev Articles Community Forums > Databases > General SQL Development > Max (date) and Count Help

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump