CODEWITHSUNDEEP
sqlsql-serverinner-join
neo
neo

Reputation: 293

SQL Server INNER JOIN with GROUP BY

I have two tables that I want to join. The first table is named WorkItem:

enter image description here

The second table is WorkItem_Schedule:

enter image description here

I want to get the 1st joined row with ActualEndDate IS NULL.

In case of WorkItemId 3, I only want to join it on WorkItemScheduleId 95. Only the first row!

I tried this SQL statement, but I am having trouble: 

  SELECT 
      W.WorkItemId
      ,MIN(WS.WorkItemScheduleId) test
      ,W.WorkItemName
      ,WS.[PhaseName]
      ,WS.[StartDate]
      ,WS.[EndDate]
      ,WS.[ActualStartDate]
      ,WS.[ActualEndDate]
  FROM 
      WorkItem W
  INNER JOIN 
      WorkItem_Schedule WS ON W.WorkItemId = WS.WorkItemId
  WHERE 
      WS.ActualEndDate IS NULL
  GROUP BY 
      W.WorkItemId;

I get an error

Column 'WorkItem.WorkItemName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I google this error and found out that I need to add other columns in the select list to group by, so I tried this sql, but it return all schedules and not distinct workitemid: 

SELECT 
   W.WorkItemId
  ,MIN(WS.WorkItemScheduleId) test
  ,W.WorkItemName
  ,WS.[PhaseName]
  ,WS.[StartDate]
  ,WS.[EndDate]
  ,WS.[ActualStartDate]
  ,WS.[ActualEndDate]
FROM 
    WorkItem W
INNER JOIN 
    WorkItem_Schedule WS ON W.WorkItemId = WS.WorkItemId
WHERE 
    WS.ActualEndDate IS NULL
GROUP BY 
    W.WorkItemId, WS.PhaseName, WS.StartDate, WS.EndDate, WS.ActualStartDate, WS.ActualEndDate;

Please help! THanks in advance!

Upvotes: 0

Views: 2241

Answers (4)

TommCatt
TommCatt

Reputation: 5636

Joining on a particular row of a spectrum of values is relatively easy and involves no fancy tricks. First, a simple join will show you all of the candidate rows. As you see, you had 80% of the query done:

SELECT
    W.WorkItemId
    ,WS.WorkItemScheduleId
    ,W.WorkItemName
    ,WS.[PhaseName]
    ,WS.[StartDate]
    ,WS.[EndDate]
    ,WS.[ActualStartDate]
    ,WS.[ActualEndDate]
FROM 
    WorkItem W
INNER JOIN 
    WorkItem_Schedule WS 
 ON W.WorkItemId = WS.WorkItemId
WHERE 
    WS.ActualEndDate IS NULL

This gives you the row you want and others as well. Now just filter out the ones you don't want. The one you want has the smallest (earliest) date. Fine. Select that date:

...
WHERE 
    WS.ActualEndDate IS NULL
and WS.StartDate =(
    select  Min( StartDate )
    from    WorkItem_Schedule
    where   WorkItemId = W.WorkItemId
       and  ActualEndDate is NULL );

Don't let the subquery throw you. If your table is properly indexed, it will find your row using only index seeks.

Upvotes: 1

SouravA
SouravA

Reputation: 5243

You could use ROW_NUMBER() function to get the "first" joined row.

SELECT * FROM (
  SELECT 
   W.WorkItemId
  ,WS.WorkItemScheduleId
  ,CASE WHEN WS.ActualEndDate IS NULL 
        THEN ROW_NUMBER() OVER (PARTITION BY W.WorkItemId ORDER BY WorkItemScheduleId)  --ROW_NUMBER() only invoked when the ActualEndDate IS NULL 
        ELSE -1 END AS ROWN
  ,W.WorkItemName
  ,WS.[PhaseName]
  ,WS.[StartDate]
  ,WS.[EndDate]
  ,WS.[ActualStartDate]
  ,WS.[ActualEndDate]
  FROM WorkItem W
INNER JOIN WorkItem_Schedule WS
ON W.WorkItemId = WS.WorkItemId    
GROUP BY W.WorkItemId, WS.PhaseName, WS.StartDate, WS.EndDate, WS.ActualStartDate, WS.ActualEndDate) A
WHERE ROWN = 1 --Getting the "first" instance

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

cross apply is probably the easiest way to do this:

select w.*, ws.*
from WorkItem w cross apply
     (select top 1 ws.*
      from WorkItem_Schedule ws
      where ws.WorkItemId = w.WorkItemId and
            ws.ActualEndDate is null
      order by ws.WorkItemScheduleId
     ) ws;

By "first", I assume you mean the one with the smallest WorkItemScheduleId.

Upvotes: 0

Joe Taras
Joe Taras

Reputation: 15379

Try this:

SELECT 
W.WorkItemId
,W.WorkItemName
,WS.[PhaseName]
,WS.[StartDate]
,WS.[EndDate]
,WS.[ActualStartDate]
,WS.[ActualEndDate]
FROM WorkItem W
JOIN WorkItem_Schedule WS
 ON W.WorkItemId = WS.WorkItemId
WHERE WS.ActualEndDate IS NULL
AND NOT EXISTS(
   SELECT 'PREVIOUS'
   FROM WorkItem_Schedule WS2
   WHERE W2.WorkItemId = WS.WorkItemId
   AND WS2.StartDate < WS.StartDate
   AND WS2.ActualEndDate IS NULL

)

Upvotes: 1

Related Questions