How do I merge the following two queries into one? I'm having problems with comparing iJob & iClient to themselves, which is why I'm inserting them in the second statement using ASP
Query 1 :
SELECT DISTINCT iClient, iJob, (SELECT tblClients.Name FROM tblClients WHERE Logbook.iClient = tblClients.ID) as Name
FROM Logbook
WHERE EXISTS (SELECT * FROM tblStatements WHERE tblStatements.iJob = Logbook.iJob AND tblStatements.iClient = Logbook.iClient)
Query 2 :
SELECT
(SELECT Sum(DateDiff('n',pStart,pFinish)) FROM Logbook WHERE DateDiff('ww',pDate,'" & Now & "') = 1 AND iEmployee = 1 AND iJob = " & iJob & " AND iClient = " & iClient & ") as Dan,
(SELECT Sum(DateDiff('n',pStart,pFinish)) FROM Logbook WHERE DateDiff('ww',pDate,'" & Now & "') = 1 AND iEmployee = 2 AND iJob = " & iJob & " AND iClient = " & iClient & ") as Angela,
(SELECT Sum(DateDiff('n',pStart,pFinish)) FROM Logbook WHERE DateDiff('ww',pDate,'" & Now & "') = 1 AND iEmployee = 3 AND iJob = " & iJob & " AND iClient = " & iClient & ") as Matt,
(SELECT Sum(DateDiff('n',pStart,pFinish)) FROM Logbook WHERE DateDiff('ww',pDate,'" & Now & "') = 1 AND iEmployee > 3 AND iJob = " & iJob & " AND iClient = " & iClient & ") as Other
FROM Logbook
And if you have any suggestions to clean this up (like having one datediff instead of 4 *hint hint*), I'd love to hear them
Topic Options
Post Options
Thanks(0)
