I am trying to query my data so that it obtains the start and end date for each job and cost code. I want one start and end date for each JobNumber and CostCode. If one of the rows contains a Status of 'D' then I would like that StartDate and EndDate to be the final StartDate and EndDate for that JobNumber and CostCode. If a JobNumber and CostCode combination does not contain a Status of 'D' then I would just like to return the minimum StartDate and EndDate of all instances of the JobNumber and CostCode.

The data is grouped by JobNumber, CostCode, and Status so there are no duplicates of these three columns. Additionally only the 'D' Status rows will contain an EndDate so the resultant EndDate should always be the 'D' row or Null. The table looks like this:

And the result should look like this:

I have been stuck on this a while and am not very experienced with SQL so am having trouble. I have attempted joining it to itself and CASE statements with a GROUP BY on JobNumber and CostCode but have had no luck either way. Any help would be greatly appreciated.

CodePudding user response：

SELECT JobNumber, CostCode, Status, StartDate, EndDate
FROM (
    SELECT JobNumber, CostCode, Status, StartDate, EndDate
        , row_number() over 
            (
                partition by JobNumber, CostCode 
                order by case when status='D' then 0 else 1 end, StartDate, EndDate
            ) rn
    FROM [table]
) t
WHERE rn = 1

See it run here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=75cfbf2f3519da7acc116fc8e6047f75

CodePudding user response：

I would use a cursor for each job. If there is a D record for that job then select that start and enddate, else select startdate and endate where the startdate is equal to the min start date for that job.

CodePudding user response：

Use a traditional UNION. And CONCAT for SQL server .

SELECT
   jobNumber,
   costCode,
   max(startdate) as startdate,
   max(enddate) as enddate
from tablename
where status = 'D'
group by jobNumber, costcode
UNION
select
   jobnumber,
   costcode,
   min(startdate) as startdate,
   min(enddate) as enddate
from tablename
where CONCAT(jobNumber,':',costCode) not in (SELECT CONCAT(jobNumber,':',costCode) from tablename where status='D')
group by jobnumber, costcode

Fiddle here