locked
Use CASE in HAVING clause RRS feed

  • Question

  • I feel like this should be simple but it I'm just not grasping it. 

    Say I have this query:

    SELECT order_number, order_date FROM orders

    I basically want to pull any order from the last 24 hours.  Well this is fairly easy for Tue-Fri but when I run the query on Mon, it will have zero results since there are no orders from the weekend.  If the query is ran on Mon it should query Fri instead.

    I'm attempting to use case in my having clause but no luck. 

    HAVING (DATEDIFF(dd, GETDATE(), order_date = (CASE order_date WHEN DATENAME(weekday,getdate()) = 'Friday' THEN '3' ELSE '0' END)
    Sure my syntax or parentheses is way off, any help is appreciated.  Thanks.

    Friday, June 20, 2014 11:20 PM

Answers

  • Check out the following CASE expression:

    SELECT CASE WHEN datename(dw,getdate()) = 'Monday' THEN
    CONVERT(DATE,(SELECT dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate())))
    ELSE CONVERT(DATE, DATEADD(dd, -1, getdate())) END;
    
    /********* TEST ****************/
    DECLARE @Dt datetime;
    DECLARE @i int = 0; WHILE (@i < 10) BEGIN
    
    SET @Dt = dateadd(dd, @i, getdate());
    SELECT Today = @Dt, WeekDay=DATENAME(dw,@Dt), PrevBusinessDay= CASE WHEN datename(dw,@Dt) = 'Monday' THEN
    CONVERT(DATE, (SELECT dateadd(d, -((datepart(weekday, @Dt) + 1 + @@DATEFIRST) % 7), @Dt)))
    ELSE CONVERT(DATE, DATEADD(dd, -1, @Dt)) END;
    
    SET @i += 1; END -- WHILE
    /*
    Today	WeekDay	PrevBusinessDay
    2014-06-20 19:46:45.130	Friday	2014-06-19 
    
    Today	WeekDay	PrevBusinessDay
    2014-06-23 19:49:04.817	Monday	2014-06-20
    
    Today	WeekDay	PrevBusinessDay
    2014-06-24 19:46:45.130	Tuesday	2014-06-23 
    
    Today	WeekDay	PrevBusinessDay
    2014-06-25 19:46:45.130	Wednesday	2014-06-24 
    
    Today	WeekDay	PrevBusinessDay
    2014-06-26 19:46:45.130	Thursday	2014-06-25 
    
    Today	WeekDay	PrevBusinessDay
    2014-06-27 19:46:45.130	Friday	2014-06-26 
    
    */

    Datetime functions:   http://www.sqlusa.com/bestpractices/datetimeconversion/

    Consider also implementation with a calendar table.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012







    • Edited by Kalman Toth Saturday, June 21, 2014 12:00 AM
    • Proposed as answer by Elvis Long Wednesday, June 25, 2014 5:37 AM
    • Marked as answer by Elvis Long Wednesday, July 2, 2014 7:25 AM
    Friday, June 20, 2014 11:53 PM

All replies

  • Check out the following CASE expression:

    SELECT CASE WHEN datename(dw,getdate()) = 'Monday' THEN
    CONVERT(DATE,(SELECT dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate())))
    ELSE CONVERT(DATE, DATEADD(dd, -1, getdate())) END;
    
    /********* TEST ****************/
    DECLARE @Dt datetime;
    DECLARE @i int = 0; WHILE (@i < 10) BEGIN
    
    SET @Dt = dateadd(dd, @i, getdate());
    SELECT Today = @Dt, WeekDay=DATENAME(dw,@Dt), PrevBusinessDay= CASE WHEN datename(dw,@Dt) = 'Monday' THEN
    CONVERT(DATE, (SELECT dateadd(d, -((datepart(weekday, @Dt) + 1 + @@DATEFIRST) % 7), @Dt)))
    ELSE CONVERT(DATE, DATEADD(dd, -1, @Dt)) END;
    
    SET @i += 1; END -- WHILE
    /*
    Today	WeekDay	PrevBusinessDay
    2014-06-20 19:46:45.130	Friday	2014-06-19 
    
    Today	WeekDay	PrevBusinessDay
    2014-06-23 19:49:04.817	Monday	2014-06-20
    
    Today	WeekDay	PrevBusinessDay
    2014-06-24 19:46:45.130	Tuesday	2014-06-23 
    
    Today	WeekDay	PrevBusinessDay
    2014-06-25 19:46:45.130	Wednesday	2014-06-24 
    
    Today	WeekDay	PrevBusinessDay
    2014-06-26 19:46:45.130	Thursday	2014-06-25 
    
    Today	WeekDay	PrevBusinessDay
    2014-06-27 19:46:45.130	Friday	2014-06-26 
    
    */

    Datetime functions:   http://www.sqlusa.com/bestpractices/datetimeconversion/

    Consider also implementation with a calendar table.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012







    • Edited by Kalman Toth Saturday, June 21, 2014 12:00 AM
    • Proposed as answer by Elvis Long Wednesday, June 25, 2014 5:37 AM
    • Marked as answer by Elvis Long Wednesday, July 2, 2014 7:25 AM
    Friday, June 20, 2014 11:53 PM
  • I recommend that you don't write code that depends on the language setting of the client computer.  For example, the use of the DateName() function in calculations will fail if it depends on the English language names of the days of the week.  Instead of

    CASE WHEN datename(dw,@Dt) = 'Monday'

    I would recommend

    CASE WHEN datediff(day, '19000101' /* a known Monday */, @Dt) = 0

    That will be true for Monday dates no matter what the client settings are.  I use Jan 1, 1900 because it is a nice round date, but you could use any other date (past or present or future) that is a Monday.

    Tom

    Saturday, June 21, 2014 3:04 AM
  • Hi Gjl_support,

    You have to group by order_date above.


    Many Thanks & Best Regards, Hua Min

    Saturday, June 21, 2014 6:49 AM
  • I think this is what you want

    SELECT order_number, order_date FROM orders
    where order_date >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 >0 THEN -1 ELSE -3 END)
    AND order_Date < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Elvis Long Wednesday, July 2, 2014 7:25 AM
    Saturday, June 21, 2014 8:48 AM