Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: SR and dateadd function

    I am creating an SP that will select summary data and add three recs to the end. When I try to compile the SP I get
    an error: ''is not a valid datetime option
    I have tried everything, everything works up to the var declarations, near the end of the SP. Maybe the dateadd function ? ?

    Please let me know what you think.

    CREATE PROCEDURE dbo.s_it_tt_getsumweek
    @uid CHAR(10)
    --,@weekending smalldatetime
    AS
    BEGIN
    -- have reached the max number of tables allowed in one query
    -- so added into #temp
    -- then use union to add extra rows
    SELECT week_date AS week_date,
    ISNULL(SUM(sunday+monday+tuesday+wednesday+thursda
    y+friday+saturday),0) AS total,
    holiday = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity a
    WHERE a.acttype = "Holiday" AND a.uid = @uid AND a.week_date = z.week_date),0),
    vacation = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity b
    WHERE b.acttype = "Vacation" AND b.uid = @uid AND b.week_date = z.week_date),0),
    ato = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity c
    WHERE c.acttype = "Authorized Time Off" AND c.uid = @uid AND c.week_date = z.week_date),0),
    mato = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity d
    WHERE d.acttype = "Management Time Off" AND d.uid = @uid AND d.week_date = z.week_date),0),
    personal = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity e
    WHERE e.acttype = "Floating Holiday" AND e.uid = @uid AND e.week_date = z.week_date),0),
    illness = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity f
    WHERE f.acttype = "Illness" AND f.uid = @uid AND f.week_date = z.week_date),0),
    internal = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity g
    WHERE g.acttype = "Internal Work" AND g.uid = @uid AND g.week_date = z.week_date),0),
    externl = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity h
    WHERE h.acttype = "Customer Work" AND h.uid = @uid AND h.week_date = z.week_date),0),
    admin = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity i
    WHERE i.acttype = "Administrative" AND i.uid = @uid AND i.week_date = z.week_date),0),
    dif = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity j
    WHERE j.acttype = "Death in Family" AND j.uid = @uid AND j.week_date = z.week_date),0),
    divday = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity k
    WHERE k.acttype = "Diversity Day" AND k.uid = @uid AND k.week_date = z.week_date),0),
    juryduty = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity l
    WHERE l.acttype = "Jury Duty" AND l.uid = @uid AND l.week_date = z.week_date),0),
    lofab = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity m
    WHERE m.acttype = "Leave of Absence" AND m.uid = @uid AND m.week_date = z.week_date),0),
    unauthab = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity n
    WHERE n.acttype = "Unauthorized Absence" AND n.uid = @uid AND n.week_date = z.week_date),0),
    slterm = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida
    y+saturday)
    FROM it_tt_activity o
    WHERE (o.acttype = "Short Term Disability" OR o.acttype = "Long Term Disability") AND o.uid = @uid AND o.week_date = z.week_date),0)
    INTO #tempActivity
    FROM it_tt_activity z
    WHERE z.uid=@uid
    GROUP BY z.week_date
    ORDER BY z.week_date DESC
    --AND z.week_date>=@weekending


    DECLARE @lastWeekInQ smalldatetime, @addedWeek1 smalldatetime, @addedWeek2 smalldatetime, @addedWeek3 smalldatetime

    SELECT @lastWeekInQ = (SELECT MAX(week_date) FROM #tempActivity)

    SELECT @addedWeek1 = DATEADD('day',7,@lastWeekInQ)
    SELECT @addedWeek2 = DATEADD('day',7,@addedWeek1)
    SELECT @addedWeek3 = DATEADD('day',7,@addedWeek2)

    SELECT week_date, total, holiday, vacation, ato, mato, personal, illness, internal, externl, admin, dif, divday, juryduty, lofab, unauthab, slterm
    FROM #tempActivity
    UNION
    SELECT @addedWeek1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0
    UNION
    SELECT @addedWeek2, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0
    UNION
    SELECT @addedWeek3, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0
    END

  2. #2
    Join Date
    Jan 2003
    Posts
    67

    Works a little, but still ...

    ... need help. I found that I have reached the max allowed tables in a query so I have to remove one field. Now I just have to add it back in. Any ideas how?

Posting Permissions

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