Results 1 to 2 of 2
  1. #1
    Join Date
    May 2006
    Location
    Zurich, Switzerland
    Posts
    1

    Unanswered: Don't 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):

    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,

    Shihan

  2. #2
    Join Date
    Dec 2004
    Posts
    46
    I think SQL Server is expecting a "From Clause" in the following:

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •