Results 1 to 14 of 14
  1. #1
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121

    how to use case statement in where clause

    I have simple query. I want to run report to show what POs generated yesterday if I run it on current date. POdate format is 'yyyymmdd'


    SELECT PO, PODATE, from POTABLE WHERE PODATE=CONVERT(nvarchar,GETDATE(), 112)-1

    above query is running fine but If I run report on Monday it should show me from last Friday to Sunday means last 3 days.

    I tried to run this query but it showing incorrect syntex error message =' close to equal sign. What I am missing ?


    select PO, PODATE from POTABLE WHERE
    CASE
    WHEN DATENAME(WEEKDAY, PODATE)='Monday' THEN PODATE =CONVERT(nvarchar,GETDATE(), 112)-1
    ELSE
    WHEN DATENAME(WEEKDAY, PODATE)<>'Monday' THEN PODATE=CONVERT(nvarchar,GETDATE(), 112)-3
    END

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I do not believe you can use CASE statements in queries/SQL code.
    You need to use something else. See: https://stackoverflow.com/questions/...-in-access-sql

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    More like

    select PO, PODATE from POTABLE
    WHERE PODATE =DATEADD(dd,CASE WHEN DATENAME(WEEKDAY, GetDate())='Monday' THEN -3 ELSE -1 END, DATEDIFF(dd, 0, GetDate()))
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Joe, you can use CASE in SQL (in SQL Server anyway).

    Note I used a different method to isolate the date portion of GetDate().
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, and also I reversed the logic on the -1 vs -3.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by pbaldy View Post
    More like

    select PO, PODATE from POTABLE
    WHERE PODATE =DATEADD(dd,CASE WHEN DATENAME(WEEKDAY, GetDate())='Monday' THEN -3 ELSE -1 END, DATEDIFF(dd, 0, GetDate()))
    Thank you very much pbaldy. I tried but it is giving me syntax error near ), please note date my podate is in 'yyyymmdd' format. when I run only SELECT DATEDIFF(DD, 0, GETDATE()) it is gives me dd 43295.

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I just ran this on a table and it returned Friday's data as I would expect:

    SELECT *
    FROM TableName
    WHERE Shift_Date = DATEADD(dd,CASE WHEN DATENAME(WEEKDAY, GetDate())='Monday' THEN -3 ELSE -1 END, DATEDIFF(dd, 0, GetDate()))
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by pbaldy View Post
    I just ran this on a table and it returned Friday's data as I would expect:

    SELECT *
    FROM TableName
    WHERE Shift_Date = DATEADD(dd,CASE WHEN DATENAME(WEEKDAY, GetDate())='Monday' THEN -3 ELSE -1 END, DATEDIFF(dd, 0, GetDate()))
    Awesome query is running but is giving me only last Friday records. What I want if it runs on Monday then it should return me from Friday to Sunday rest of week just previous day.

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    So use Between with that bit as the first date and the DateAdd() without the CASE (just -1) for the second date.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by pbaldy View Post
    So use Between with that bit as the first date and the DateAdd() without the CASE (just -1) for the second date.
    SELECT PO, PODATE
    FROM POTABLE
    WHERE PODATE BETWEEN DATEADD(dd,CASE WHEN DATENAME(WEEKDAY, GetDate())='Monday' THEN -3 ELSE -1 END, DATEDIFF(dd, 0, GetDate())) AND GETDATE()-1


    finally It is running. thanks a lot I will run it tomorrow if it is not Monday. I hope i will get good result. Thanks again.

  11. #11
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. I would not use GETDATE()-1, as that has a time component (unless that won't affect you). This is one way to get only the date for yesterday:

    DATEADD(dd,-1, DATEDIFF(dd, 0, GetDate()))

    FYI, I did test by changing the "Monday" to "Thursday" and as expected only got the previous day's data.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, and depending on SQL Server version this is a simpler way to get just the date (again yesterday):

    CONVERT(Date, GETDATE()-1)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    You can easily get rid of the time part in the getdate function : select cast(getdate()as date)

  14. #14
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In my research there was no advantage of cast over convert (or vice versa) for this purpose.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Where clause in SQL Statement of VBA code
    By Rickochezz in forum Modules
    Replies: 13
    Last Post: 07-16-2017, 11:12 PM
  2. Using a WHERE clause in an SQL statement
    By john134 in forum Programming
    Replies: 3
    Last Post: 09-13-2016, 05:40 PM
  3. CASE STATEMENT or IF-ELSE-THEN? HELP
    By Shakenaw in forum Access
    Replies: 9
    Last Post: 06-08-2015, 11:24 AM
  4. Error FROM clause of your SQL statement
    By Stephanie53 in forum Forms
    Replies: 8
    Last Post: 05-15-2013, 04:04 PM
  5. Switch Statement in WHERE Clause
    By Gray in forum Queries
    Replies: 1
    Last Post: 06-02-2011, 06:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums