Help setting up ms sql query

[TooncesLives]

For work I've been told to setup a new crystal report (later to become part of .net site that will replace all our crystal reports) that will retrieve all open work orders that are over 90 days without being worked on, based on the shop that the open labor is assigned to. I'm having a hard time getting this to pull all the correct data (through a manual audit I have a list of everything that should be displayed on the report). I'm looking for some help or suggestions on how I can go about retrieving my desired result. I can put this in a view, stored procedure...whatever might work. I don't typically handle this kind of problem, so any help is appreciated

The structure of the two tables with the data is:

DEMAND TABLE (basically shows all work orders that haven't been closed yet): DMID (PRIMARY KEY), DATESTARTED (DATE the job was opened), JOBNO

LABOR TABLE: LABORID (PRIMARY KEY), DMID (FOREIGN KEY--MULTIPLE ENTRIES), WORKSTARTED, WORKCOMPLETED, LABORCODE (i.e. CA for carpenters), EMPLOYEENO (which if an employee isn't assigned, gets the value of the shop that it will go to).

How I've thought about it is I'll first have to have a query that will get the most recent LABOR.WORKSTARTED date grouped by shop (possibly make that into a view) where WORKSTARTED is <= GETDATE()-90. That would have to be joined with the DEMAND table in order to only retrieve the records that are listed as being open. I also need to make sure that if only the shop is assigned in EMPLOYEENO field, that it is included in the report. That's about as far as I've been able to get. I get partial data, but can't seem to get it all accurately (I've been doing this in a view, and then in crystal telling it to only select the records relating to the shop I want based off LABORCODE). I'm sure a better explanation will be needed...I'm having a difficult time even describing what we're after. Basically it's any open work order that hasn't had any work done on it in over 90 days based on the shop it was assigned to (rather that shop is assigned in LABORAD or EMPLOYEE NO). If the shop is assigned to the job multiple times (which most jobs do have), and one entry from the shop has worked on the job within 90 days, and one hasn't, then the job should not be included in my result.

Thanks for any help or suggestions on how I can go about this.

 

[calebb]

How about something like this:

SELECT
	LT.LABORID,
	LT.LABORCODE,
	LT.WORKSTARTED,
	DT.DATESTARTED
FROM
	LABORTABLE LT
INNER JOIN DEMANDTABLE DT
ON
	LT.LABORID = DT.LABORID AND dateadd(day, -90, GetDate()) < DT.DATESTARTED
WHERE
	LT.WORKCOMPLETED IS NULL
ORDER BY DT.DATESTARTED DESC
GROUP BY LT.LABORCODE