Norm-al
asked on
Multiple Max Dates Needed for SQL Join Query
I have a main table DataEntryView that I need joined with other tables that contain the same JobID but have multiple records per JobID, I only want the Max(ActualStartDate) joined to the main DataEntryView. I thought I got it but my view comes up with 'Unsupported Data Type' when I run this query.
SELECT TOP (100) PERCENT dbo.DataEntryView.ctJobID, dbo.DataEntryView.JobNum, dbo.DataEntryView.Part, dbo.DataEntryView.Contractor,
dbo.DataEntryView.Owner, dbo.DataEntryView.Sales_Initial, dbo.DataEntryView.Sales_Amount, dbo.DataEntryView.DesEng_SalesPerHour,
dbo.DataEntryView.loggedin, dbo.DataEntryView.Customer_Expected_Delivery, Bid_LF_Table.Bid_LF
FROM dbo.DataEntryView LEFT OUTER JOIN
(SELECT TOP (1) Actual_Start_Date AS Bid_LF, JobID
FROM dbo.DesEngSchedule AS DesEngSchedule_1
WHERE (Department LIKE 'Custom Design Engineering') AND (TaskName LIKE '02%')
ORDER BY Bid_LF) AS Bid_LF_Table ON dbo.DataEntryView.ctJobID = Bid_LF_Table.JobID
WHERE (dbo.DataEntryView.loggedin IS NOT NULL) AND (dbo.DataEntryView.Finalized_Sales >= GETDATE() - 7 OR
dbo.DataEntryView.Finalized_Sales IS NULL) AND (dbo.DataEntryView.bidresultdate IS NULL) AND (dbo.DataEntryView.Lost_Sales_Date IS NULL) AND
(dbo.DataEntryView.Sales_Initial NOT LIKE 'PD') AND (dbo.DataEntryView.Sales_Initial NOT LIKE 'CS')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window