Combining the Statements.

I need some help on combining these SQL statements.

Any one know how to please tell me ok.

Results wanted:

SELECT [DRAWING PROJECT].JOBNO,
[DRAWING PROJECT].JOBDES,
COUNT([DRAWING DETAILS].DWGNO) AS [TotalDwgno],
AVG(ViewR1aa.TSPERCENT) AS [Averagepercent]
FROM [DRAWING DETAILS] INNER JOIN
[DRAWING PROJECT] ON
[DRAWING DETAILS].JOBNO = [DRAWING PROJECT].JOBNO INNER
JOIN
ViewR1aa ON
[DRAWING DETAILS].DWGNO = ViewR1aa.DWGNO
GROUP BY [DRAWING PROJECT].JOBNO,
[DRAWING PROJECT].JOBDES

ViewR1aa:

SELECT DISTINCT
dbo.ViewR1a.DWGNO, dbo.ViewR1a.maxdate,
dbo.TIMESHEET.TSPERCENT,
dbo.TIMESHEET.STAGECODE
FROM dbo.ViewR1a INNER JOIN
dbo.TIMESHEET ON
dbo.ViewR1a.maxdate = dbo.TIMESHEET.[DATE] AND
dbo.ViewR1a.DWGNO = dbo.TIMESHEET.DWGNO
WHERE (NOT (dbo.TIMESHEET.JOBNO LIKE 'EM-G%')) AND
(dbo.TIMESHEET.TSPERCENT IS NOT NULL)

View R1a:

SELECT DISTINCT
TOP 100 PERCENT DWGNO, MAX([DATE])
AS maxdate
FROM dbo.TIMESHEET
WHERE (DWGNO <> '') OR
(DWGNO IS NOT NULL) AND
(JOBNO NOT LIKE 'EM%') AND
(JOBNO IS NOT NULL)
GROUP BY DWGNO
ORDER BY DWGNO)


Instead of having 3 SQL statement is there any other way to reduce it to 2 or 1 statement.

Thank you.

NHK

I cant't belive this i am replying my self. Anyway ... This is my progress so far. Still none of them come close to what I wanted with those 3 statements. And yeah I have labeled the solutions using version numbers. U see below:


ver3.1

SELECT [Drawing Details].jobno, COUNT([Drawing Details].dwgno)
AS TotalDraw, AVG([TimeSheetDetail].tspercent)
AS AvgPercent
FROM [Drawing Details], TimeSheetDetail
WHERE (TimeSheetDetail.dwgid = [Drawing Details].dwgid) AND
(DWGNO <> '') OR
(DWGNO IS NOT NULL) AND (JOBNO NOT LIKE 'EM%') AND
(JOBNO IS NOT NULL) AND (NOT (JOBNO LIKE 'EM-G%')) AND
(TimeSheeTDetail.TSPERCENT IS NOT NULL)
GROUP BY [Drawing Details].jobno
ORDER BY jobno


Ver3.0

SELECT [Drawing Details].jobno, COUNT([Drawing Details].dwgno)
FROM [Drawing Details]
WHERE EXISTS
(SELECT DISTINCT
TOP 100 PERCENT DWGNO, MAX([DrawnDate])
AS maxdate, TimesheetDetail.tspercent,
timesheetdetail.stagecode
FROM [Drawing Details] INNER JOIN
[TimeSheetDetail] ON
[Drawing Details].Dwgid = [TimeSheetDetail].Dwgid
WHERE (DWGNO <> '') OR
(DWGNO IS NOT NULL) AND
(JOBNO NOT LIKE 'EM%') AND (JOBNO IS NOT NULL)
AND (NOT (JOBNO LIKE 'EM-G%')) AND
(dbo.TIMESHEETDetail.TSPERCENT IS NOT NULL)
GROUP BY dwgno, Timesheetdetail.tspercent,
Timesheetdetail.stagecode)
GROUP BY [Drawing Details].jobno
ORDER BY jobno

I did it!!! I think I did it...

can someone tell me I did it or not? Is this the correct answer?

ver 3.2

SELECT [Drawing Details].jobno, COUNT([Drawing Details].dwgno)
AS TotalDraw, AVG(TimeSheetDetail.tspercent)
FROM [Drawing Details] LEFT JOIN
TimeSheetDetail ON
TimeSheetDetail.dwgid = [Drawing Details].dwgid
WHERE (DWGNO <> '') AND (DWGNO IS NOT NULL) AND
(JOBNO NOT LIKE 'EM%') AND (JOBNO IS NOT NULL) AND
(TimeSheeTDetail.TSPERCENT IS NOT NULL)
GROUP BY [Drawing Details].jobno
ORDER BY jobno


NHK