Dont know where to put a subfunction/query into my SQL Script

Hi,

I'm kind of database administrator in our company and have to manage a project database, where people add their work times to a SQL database (Access frontend). Like time they work on a day, on a specific project, vacances etc.).

I made a script which reports me:
- for each person, how long did they work on a project (Auftrag_ID) on a single day
- limited by a period (Monat_von to Monat_bis means month_from to month_to)

The script was fine, but I now had also to output a comment for every week for each person and then for each of the persons Auftrag_IDs (projects) so I inner joined the Table "WO-Zeiterfassung" where this information is stored. But I have to found out for each date, which is the date of the sunday in that week to Innerjoin it on the sunday date (I'm not sure if there is an easier way)... so for the moment I made a "subscript", function whatever you call that, to give me the date of the sunday for each day, which I marked in the code below. But I have no idea where I must put that part into the query AND if it is correct :-) the same goes with the inner join part of the query (also marked with commentary code):

DECLARE @.Auftrag_ID int
DECLARE @.Jahr int
DECLARE @.Monat_von int
DECLARE @.Monat_bis int

DECLARE @.Wochentag int
DECLARE @.DifferenzTage int
DECLARE @.WoSonntagsDatum datetime

SELECT @.Auftrag_ID = '3216'
SELECT @.Jahr = '2006'
SELECT @.Monat_von = '1'
SELECT @.Monat_bis = '4'

SET DATEFIRST 1

/* WHERE TO PUT THE FOLLOWING PART, and is it correct? */
(
SET @.Wochentag = (SELECT Datepart(dw, TKMA.Datum))
SET @.DifferenzTage = ( 7-@.Wochentag )
SET @.WoSonntagsDatum = (SELECT DateAdd(d, @.DifferenzTage, TKMA.Datum))
)
/* ende */

SELECT
Personal.[Personal-ID]
,Personal.[Name] + ' ' + Personal.[Vorname] AS NameVorname
,KstStell.[KostenstellenNo] AS KstSt
,KstStell.[Kostenstellenname] AS KstName
,TKMA.[Auftrag-ID]
,TKMA.[Auftrag-Kurzbezeichnung]
,KstStell.[Kostenstellenverant]
,Personal2.[Name] + ' ' + Personal2.[Vorname] AS ProjVerantwortlicher
,TKMA.Datum
,MONTH(TKMA.Datum) AS Monat
,TKMA.Stunden
,TKMA.ProjektNrIntern AS ProjNrIntern
,TKMA.ProjektNr AS ProjNr
,TKMA.Projektkurzbezeichnung AS ProjBezeichnung
,TKMA.Erf_abgeschlossenJN AS abgeschlossen
,TKMA.KJahr AS Jahr
FROM
Tageskalender_MA_Details TKMA
INNER JOIN
[Personal_maXis] Personal ON Personal.[Personal-ID] = TKMA.[Personal-ID]
INNER JOIN
[Kostenstellen maXis] KstStell ON Personal.[KSTNR-ID] = KstStell.[KSTNR-ID]
INNER JOIN
[Projekte] Prj ON TKMA.[ProjektNrIntern] = Prj.[ProjektNrIntern]
/* AND HERE THE INNERJOIN RELATED PART I ADDED FOR THIS NEW STUFF */
INNER JOIN
[WO-Zeiterfassung] WOZE ON @.WoSonntagsDatum = WOZE.[So-Datum] AND Personal.[Personal-ID] ON WOZE.[Personal-ID] AND TKMA.[Auftrag-ID] ON WOZE.[Auftrag-ID]
/* TILL HERE */
LEFT OUTER JOIN
[Personal_maXis] Personal2 ON Personal2.[Personal-ID] = Prj.[Personal-ID]
WHERE
(TKMA.[Auftrag-ID] = @.Auftrag_ID)
AND
(TKMA.KJahr = @.Jahr)
AND
(MONTH(TKMA.Datum) >= @.Monat_von)
AND
(MONTH(TKMA.Datum) <= @.Monat_bis)
AND
(TKMA.Erf_abgeschlossenJN = '-1')

Here is the error it outputs in query analyzer:
Server: Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'SET'.
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 53
Incorrect syntax near the keyword 'ON'.

I've no clue what I make wrong :-\

Hope someone can help me,

ShihanI think SQL Server is expecting a "From Clause" in the following:

SET @.Wochentag = (SELECT Datepart(dw, TKMA.Datum))