T-SQL CASE Check for youngest date and then against the other values

Question

EDIT: Remade the whole question. Brought it the wrong way the first time.

The Results should be like:

MachineName | OrderNo | TaskID | Code | NettoProd | BrutoProd | DiffProd
=========================================================================
F1            123456    101      O      100000      125000      25000
F1            123456    102      P8     1000000     1250000     250000
F1            123456    103      P1     10000       12500       2500
F1            123456    104      P4     100000      125000      25000

The JobSummary has the following columns:

StartDate 
TaskID

The Route has the following columns:

OrderNo 
TaskID

The Resource holds the data about the machines

MachineID

I already added the part of Kyle Gobel, which was the correct answer, if I had provided all details in the first place.

What I would like to accomplish is to show an O when it's the first taskID of the tasks linked to one OrderNo based on the StartDate, which is a DateTime field in the table.

If it's impossible to check for both Route.No and JobSummary.StarDate in one CASE statement, then a separate column for both will do too.

Query

SELECT Resource.DESCRIPTION AS MachineName
    ,Route.OrderNo
    ,Route.TaskID
    ,CASE
        WHEN JobSummary.StartDate = (SELECT MIN(cr.StartDate) FROM JobSummary cr) THEN 'O'
        WHEN Route.No = 1 OR Route.No = 2 THEN 'P1'
        WHEN Route.No = 4 THEN 'P4'
        WHEN Route.No >= 8 THEN 'P8'
        ELSE '*FP*'
    END AS Code
    ,JobSummary.GoodProd As NettoProd
    ,JobSummary.GoodProd + JobSummary.SetupProd + JobSummary.WasteProd As BrutoProd
    ,(JobSummary.SetupProd + JobSummary.WasteProd) As DiffProd

FROM Route
JOIN Resource ON Resource.MachineID = Route.MachineID
JOIN JobSummary ON JobSummary.TaskID = Route.TaskID AND JobSummary.MachineID = Route.MachineID

Answer 1

You can use ROW_NUMBER() with PARTITION BY to identify the first record taskID based on its StartDate for an OrderNo like this ROW_NUMBER()OVER(PARTITION BY Route.OrderNo ORDER BY JobSummary.StartDate ASC

Sample Data and Structure

CREATE TABLE [JobSummary] (MachineID INT,TaskID INT,StartDate DATETIME,GoodProd NUMERIC(18,0),SetupProd NUMERIC(18,0),WasteProd NUMERIC(18,0));
CREATE TABLE [Route] (OrderNo INT,MachineID INT,TaskID INT,[No] INT);
CREATE TABLE [Resource] (MachineID INT,DESCRIPTION CHAR(2));


INSERT INTO [Resource] VALUES(1,'F1');

INSERT INTO [Route] VALUES(123456,1,101,1);
INSERT INTO [Route] VALUES(123456,1,102,9);
INSERT INTO [Route] VALUES(123456,1,103,2);
INSERT INTO [Route] VALUES(123456,1,104,4);

INSERT INTO [JobSummary] VALUES(1,101,'20150101',100000,20000,5000);
INSERT INTO [JobSummary] VALUES(1,102,'20150103',1000000,200000,50000);
INSERT INTO [JobSummary] VALUES(1,103,'20150102',10000,2000,500);
INSERT INTO [JobSummary] VALUES(1,104,'20150103',100000,20000,5000);

Query

;WITH CTE AS 
(
SELECT Route.No
    ,Resource.[DESCRIPTION] AS MachineName
    ,Route.OrderNo
    ,Route.TaskID
    ,JobSummary.GoodProd As NettoProd
    ,JobSummary.GoodProd + JobSummary.SetupProd + JobSummary.WasteProd As BrutoProd
    ,(JobSummary.SetupProd + JobSummary.WasteProd) As DiffProd
    ,ROW_NUMBER()OVER(PARTITION BY Route.OrderNo ORDER BY JobSummary.StartDate ASC) rn
FROM Route
JOIN Resource ON Resource.MachineID = Route.MachineID
JOIN JobSummary ON JobSummary.TaskID = Route.TaskID AND JobSummary.MachineID = Route.MachineID
)
SELECT
    MachineName,
    OrderNo,
    TaskID,
    CASE
        WHEN rn = 1 THEN 'O'
        WHEN No IN (1,2) THEN 'P1'
        WHEN No = 4 THEN 'P4'
        WHEN No >= 8 THEN 'P8'
        ELSE '*FP*'
    END AS Code,
    NettoProd,
    BrutoProd,
    DiffProd
FROM CTE
ORDER BY OrderNo,TaskID

Output

MachineName OrderNo TaskID  Code    NettoProd   BrutoProd   DiffProd
F1  123456  101 O   100000  125000  25000
F1  123456  102 P8  1000000 1250000 250000
F1  123456  103 P1  10000   12500   2500
F1  123456  104 P4  100000  125000  25000

SQL Fiddle

Answer 2

From reading your question, you might be looking for the min aggregate function.

case 
   when route.startDate = (select min(r.startdate) from table r) then 'O'
   when route.no = 1 or route.no = 2 then 'P1'
   ....
end as code