Post reply

Syntax error with <

  • January 8, 2015 at 11:29 am

    #294180

    Hi - the below code works and pulls records where sum of wd.wrkd.minutes is zero, but I need to pull records where sum is <2400. When I replace the 0 value with <2400 as seen in code below, I get syntax error pointing to the '<' sign. I'm a beginning, can anyone help me correct this? Thank you!

    -- active employees w/o hours

    SELECT E.EMP_ID, E.EMP_FULLNAME [EMPLOYEE NAME],

    (CONVERT(VARCHAR, DATEADD(DD, -6, GETDATE()), 101) + ' - ' + CONVERT(VARCHAR, GETDATE(), 101)) AS [WORK WEEK]

    FROM EMPLOYEE E, PAY_GROUP PG

    WHERE E.PAYGRP_ID = PG.PAYGRP_ID AND

    E.EMP_TERMINATION_DATE = '01/01/3000' AND

    E.EMP_HIRE_DATE <= GETDATE() AND

    e.emp_val11 = 'N' AND

    (SELECT COALESCE(SUM(WD.WRKD_MINUTES),<2400)

    FROM WORK_SUMMARY WS, WORK_DETAIL WD

    WHERE E.EMP_ID = WS.EMP_ID AND

    WS.WRKS_ID = WD.WRKS_ID AND

    E.EMP_HIRE_DATE <= WD.WRKD_WORK_DATE AND

    WD.WRKD_WORK_DATE BETWEEN DATEADD(DD, -6, GETDATE()) AND GETDATE()) = 0

    Sandy Tucker

  • January 8, 2015 at 11:32 am

    #1768861

    Change it back to

    select COALESCE(SUM(WD.WRKD_MINUTES), 0)

    and modify the final row:

    ) < 2400

    select E.EMP_ID

    ,E.EMP_FULLNAME [EMPLOYEE NAME]

    ,(CONVERT(varchar, DATEADD(DD, - 6, GETDATE()), 101) + ' - ' + CONVERT(varchar, GETDATE(), 101)) as [WORK WEEK]

    from EMPLOYEE E

    ,PAY_GROUP PG

    where E.PAYGRP_ID = PG.PAYGRP_ID

    and E.EMP_TERMINATION_DATE = '01/01/3000'

    and E.EMP_HIRE_DATE <= GETDATE()

    and e.emp_val11 = 'N'

    and (

    select COALESCE(SUM(WD.WRKD_MINUTES), 0)

    from WORK_SUMMARY WS

    ,WORK_DETAIL WD

    where E.EMP_ID = WS.EMP_ID

    and WS.WRKS_ID = WD.WRKS_ID

    and E.EMP_HIRE_DATE <= WD.WRKD_WORK_DATE

    and WD.WRKD_WORK_DATE between DATEADD(DD, - 6, GETDATE())

    and GETDATE()

    ) < 2400

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • January 8, 2015 at 11:46 am

    #1768863

    Thank you so much SSCertifiable!!!!! That worked 😀

    Sandy Tucker

  • January 8, 2015 at 11:46 am

    #1768864

    Well I'm such a newbie I thought SSCertifiable was your screen name. Sorry Phil LOL. And thanks again.

    Sandy Tucker

  • January 8, 2015 at 12:19 pm

    #1768874

    stucker (1/8/2015)

    Well I'm such a newbie I thought SSCertifiable was your screen name. Sorry Phil LOL. And thanks again.

    Haha, no problem :hehe:

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply