Solved

Multiple Max Dates Needed for SQL Join Query

Posted on 2013-01-18

Microsoft SQL Server

1 solution

174 Views

Last Modified: 2013-02-04

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')

Open in new window

Comment

Question by:Bianca

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

2 Comments

LVL 41

Accepted Solution

by:ralmada

ralmada earned 500 total points

ID: 387936212013-01-18

try this way:

SELECT 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 Max(Actual_Start_Date) AS Bid_LF, JobID
      FROM dbo.DesEngSchedule AS DesEngSchedule_1
WHERE Department LIKE 'Custom Design Engineering%' AND TaskName LIKE '02%'
      group by JobID
) 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 in ('PD', 'CS'))

LVL 27

Expert Comment

by:Zberteoc

ID: 387939752013-01-18

You join your table using the jobID and startdate column with a subquery made from DataEntryView view getting the max actualstartdate per JobID:

select 
	tbl.*
from
	YourTable tbl
	inner join 
	(
		Select 
			JobID, 
			max(ActualStartDate) as ActualStartDate 
		from 
			DataEntryView dev
		group by
			JobID
	) maxdates
	ON 
		maxdates.JobID=tbl.JobID
		AND maxdates.ActualStartDate=tbl.StartDate

Open in new window

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to use Slowly Changing Dimension Transformation in SSIS

Article by: Dung

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.

The world is on the move: Electronic commerce to Connected commerce

Article by: Shakshi

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.

Dealing With Dates: Manipulating Dates (SQL Server)

Video by: Steve

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.