Combine info from different rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combine info from different rows

Author

Topic

krausr79
Starting Member

19 Posts

Posted - 2012-07-03 : 14:48:12

Our parts are checked every so often, and each check produces a new record in the table. There is a check start date and a check finished date. So the relevent fields are PartNumber, StartDate, FinishDate. I'd like to do a query that returns:

PartNumber, Status, LastDate

where:
Each part number is returned exactly once.
The status is 'Open' if the part has any records with a StartDate but no FinishDate and 'Closed' otherwise.
The LastDate is the most recent FinishDate; blank if none.

The part where LastDate data comes from one line if closed and a different line if open has me stuck.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-03 : 15:36:37

Does a blank finishDate imply that it is the most recent row for a given partNumber? Can there be more than one blank FinishDate for a given partNumber? If so is the startDate the tie breaker?

with those assumptions as well as assuming these two tables: (partTable and PartStatusTable) then perhaps:


select p.partNumber
     ,case 
      when ca.finishDate is not null then 'closed'
      else 'open'
      end as Status
     ,ca.finishDate as LastDate
from PartTable p
cross apply (
     select top 1 startDate
               , finishDate
     from      [PartStatusTable]
     where     partNumber = p.partNumber
     order by coalesce(finishDate, '9999-12-31') desc
          ,startDate desc
     ) ca

Be One with the Optimizer
TG

krausr79
Starting Member

19 Posts

Posted - 2012-07-03 : 16:26:59

It didn't like 'apply' and when I changed that to join it didn't like '1' in top 1. I've tried to use with top before but never got it to work. I'm running Excel VBA ADODB macros to do the downloads, so it's possible I don't have access to the same tricks as you. I was able to use rank() this morning though.

I'll probably ask the SQL guy when he's back from vacation.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-03 : 17:13:57

The code eventually hits a MS Sql Server database, right? Perhaps you can create a stored procedure in the db then call that from your code. Or perhaps this:


select partNumber
      ,case 
       when d.finishDate != '9999-12-31' then 'closed'
       else 'open'
       end as Status
     ,nullif(d.finishDate, '9999-12-31') as LastDate
from  (
       select partNumber, max(coalesce(finishDate, '9999-12-31')) finishDate
       from   yourTable
       group by partNumber
      ) d

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources