Coffee sales for just 7am to 8am but for the entire year?

  • select * from TicketItem
    where s_item IN('Coffee', 'Espresso', 'Double Espresso', 'Decaf Espresso', 'Cappucino', 'Latte', 'Iced Coffee', 'sm coffee pot', 'lg coffee pot') and dt_when between '2017-01-01 07:00:00.000' and '2017-01-01 08:00:00.000'

    Actually I need it for each Quarter of the year. Is this where GetTime() comes into play? Firsuring out how to write the dateTime part is my only blocker.

    Thanks.

  • Not 100% sure what you need, but, for example, this gives sales for the latest quarter for >=7AM and <8AM:


    select *
    from TicketItem
    where s_item IN('Coffee', 'Espresso', 'Double Espresso', 'Decaf Espresso', 'Cappucino', 'Latte', 'Iced Coffee', 'sm coffee pot', 'lg coffee pot')
      and dt_when >= dateadd(quarter, datediff(quarter, 0, getdate()), 0)
      and hour(dt_when) = 7

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher - Tuesday, December 19, 2017 11:19 AM

    Not 100% sure what you need, but, for example, this gives sales for the latest quarter for >=7AM and <8AM:


    select *
    from TicketItem
    where s_item IN('Coffee', 'Espresso', 'Double Espresso', 'Decaf Espresso', 'Cappucino', 'Latte', 'Iced Coffee', 'sm coffee pot', 'lg coffee pot')
      and dt_when >= dateadd(quarter, datediff(quarter, 0, getdate()), 0)
      and hour(dt_when) = 7

    And for all of 2017? Thanks for the response, btw. Just change Quarter to Year?

    Looking for sales totals for those menu items for each Q of the 2017 calendar year but only between 7am to 8am, everyday.

  • ScottPletcher - Tuesday, December 19, 2017 11:19 AM

    Not 100% sure what you need, but, for example, this gives sales for the latest quarter for >=7AM and <8AM:


    select *
    from TicketItem
    where s_item IN('Coffee', 'Espresso', 'Double Espresso', 'Decaf Espresso', 'Cappucino', 'Latte', 'Iced Coffee', 'sm coffee pot', 'lg coffee pot')
      and dt_when >= dateadd(quarter, datediff(quarter, 0, getdate()), 0)
      and hour(dt_when) = 7

    Msg 195, Level 15, State 10, Line 15
    'hour' is not a recognized built-in function name.

    Ideas?

  • CREATE TABLE mytable(
     i_ticket_id   INT NOT NULL PRIMARY KEY
    ,s_item    VARCHAR(50) NOT NULL
    ,dt_when    DATETIME NOT NULL
    ,c_price    MONEY NOT NULL
    ,c_discount_amountMONEY NOT NULL
    );
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164230,'Latte','2017-03-01 07:23:42.310',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164241,'Cappucino','2017-03-01 08:11:05.800',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164239,'Espresso','2017-03-01 08:23:45.903',6.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164242,'Espresso','2017-03-01 09:25:27.910',6.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164245,'Latte','2017-03-01 09:28:01.330',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164248,'Latte','2017-03-01 09:31:26.720',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164248,'Latte','2017-03-01 09:31:26.803',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164242,'Espresso','2017-03-01 09:40:26.087',6.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164242,'Espresso','2017-03-01 10:02:26.780',6.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164253,'Cappucino','2017-03-01 10:21:47.083',7.00,1.75);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164263,'Latte','2017-03-01 12:21:43.513',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164266,'Double Espresso','2017-03-01 13:51:13.003',8.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164275,'Latte','2017-03-01 14:04:02.710',7.00,1.75);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164378,'Sm Coffee Pot','2017-03-02 05:55:34.193',8.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164379,'Sm Coffee Pot','2017-03-02 06:04:46.210',8.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164381,'Lg Coffee Pot','2017-03-02 06:26:39.187',12.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164392,'Latte','2017-03-02 08:05:02.310',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164395,'Latte','2017-03-02 08:13:02.680',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164397,'Sm Coffee Pot','2017-03-02 08:20:33.860',8.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164401,'Cappucino','2017-03-02 08:36:30.353',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164402,'Latte','2017-03-02 08:38:36.657',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164402,'Latte','2017-03-02 08:38:36.660',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164404,'Cappucino','2017-03-02 08:41:42.840',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164395,'Latte','2017-03-02 09:02:01.063',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164413,'Latte','2017-03-02 10:19:28.853',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164416,'Espresso','2017-03-02 10:45:29.847',6.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164418,'Lg Coffee Pot','2017-03-02 11:33:20.280',12.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164420,'Espresso','2017-03-02 12:26:14.910',6.00,1.50);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164420,'Espresso','2017-03-02 12:26:14.947',6.00,1.50);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164424,'Cappucino','2017-03-02 13:35:07.143',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164424,'Cappucino','2017-03-02 13:35:07.160',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164472,'Iced Coffee','2017-03-02 16:34:57.690',5.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164485,'Iced Coffee','2017-03-02 20:29:01.117',5.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164547,'Sm Coffee Pot','2017-03-03 07:33:24.243',8.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164550,'Iced Coffee','2017-03-03 07:50:02.023',5.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164565,'Latte','2017-03-03 09:12:33.853',7.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164570,'Sm Coffee Pot','2017-03-03 09:38:59.373',8.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164573,'Lg Coffee Pot','2017-03-03 10:34:59.517',12.00,0.00);
    INSERT INTO mytable(i_ticket_id,s_item,dt_when,c_price,c_discount_amount) VALUES (164657,'Cappucino','2017-03-03 15:50:17.227',7.00,0.00);

  • select *
    from TicketItem
    where s_item IN('Coffee', 'Espresso', 'Double Espresso', 'Decaf Espresso', 'Cappucino', 'Latte', 'Iced Coffee', 'sm coffee pot', 'lg coffee pot')
    and dt_when >= dateadd(Quarter, datediff(quarter, 0, getdate()), 0)
    and hour(dt_when) = 7

    This query chokes on the 'hour' giving:
    Msg 195, Level 15, State 10, Line 15
    'hour' is not a recognized built-in function name.

  • chef423 - Tuesday, December 19, 2017 12:01 PM

    select *
    from TicketItem
    where s_item IN('Coffee', 'Espresso', 'Double Espresso', 'Decaf Espresso', 'Cappucino', 'Latte', 'Iced Coffee', 'sm coffee pot', 'lg coffee pot')
    and dt_when >= dateadd(Quarter, datediff(quarter, 0, getdate()), 0)
    and hour(dt_when) = 7

    This query chokes on the 'hour' giving:
    Msg 195, Level 15, State 10, Line 15
    'hour' is not a recognized built-in function name.

    Because it isn't, use datepart, for example: DATEPART(HOUR,GETDATE())

  • Lynn Pettis - Tuesday, December 19, 2017 12:11 PM

    chef423 - Tuesday, December 19, 2017 12:01 PM

    select *
    from TicketItem
    where s_item IN('Coffee', 'Espresso', 'Double Espresso', 'Decaf Espresso', 'Cappucino', 'Latte', 'Iced Coffee', 'sm coffee pot', 'lg coffee pot')
    and dt_when >= dateadd(Quarter, datediff(quarter, 0, getdate()), 0)
    and hour(dt_when) = 7

    This query chokes on the 'hour' giving:
    Msg 195, Level 15, State 10, Line 15
    'hour' is not a recognized built-in function name.

    Because it isn't, use datepart, for example: DATEPART(HOUR,GETDATE())

    Thanks

    select *
    from TicketItem
    where s_item IN('Coffee', 'Espresso', 'Double Espresso', 'Decaf Espresso', 'Cappucino', 'Latte', 'Iced Coffee', 'sm coffee pot', 'lg coffee pot')
    and dt_when >= dateadd(Quarter, datediff(quarter, 0, getdate()), 0)
    and DATEPART(hour,dt_when) = 7

  • Lynn Pettis - Tuesday, December 19, 2017 12:11 PM

    chef423 - Tuesday, December 19, 2017 12:01 PM

    select *
    from TicketItem
    where s_item IN('Coffee', 'Espresso', 'Double Espresso', 'Decaf Espresso', 'Cappucino', 'Latte', 'Iced Coffee', 'sm coffee pot', 'lg coffee pot')
    and dt_when >= dateadd(Quarter, datediff(quarter, 0, getdate()), 0)
    and hour(dt_when) = 7

    This query chokes on the 'hour' giving:
    Msg 195, Level 15, State 10, Line 15
    'hour' is not a recognized built-in function name.

    Because it isn't, use datepart, for example: DATEPART(HOUR,GETDATE())

    If I want to choose Q1 / Q2 / Q3 from the same year? -1 -2 -3?

  • Sorry about the HOUR(), that's the one function I always forget doesn't exist.  Per quarter for the current year, something like this:


    select
      DATEPART(quarter, dt_when) as quarter,
      SUM(sales_amount) AS sales
    from TicketItem
    where s_item IN('Coffee', 'Espresso', 'Double Espresso', 'Decaf Espresso', 'Cappucino', 'Latte', 'Iced Coffee', 'sm coffee pot', 'lg coffee pot')
    and dt_when >= dateadd(year, datediff(year, 0, getdate()), 0)
    and datepart(hour, dt_when) = 7
    group by DATEPART(quarter, dt_when)

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Thank you to you all! Helps bigtime. I need to really learn date and time functions.

  • chef423 - Tuesday, December 19, 2017 1:00 PM

    Thank you to you all! Helps bigtime. I need to really learn date and time functions.

    Books Online is a great resource.

  • Assuming you are in oracle, I will do it like this:

    SELECT *
    FROM TicketItem
    WHERE CAST(SUBSTR(TO_CHAR(dt_when, 'YYYY-MM-DD HH24:MI:SS'),12,2) AS INT) BETWEEN 7 AND 8
    AND TO_CHAR(dt_when, 'Q') = 'Whatever Quarter You Want'

    Actually, you can declare a variable to specify the quarter you want or maybe you can use a case statement in the where clause.
    The where clause gives the records during 7 and 8 each day for the quarter you want. 

    Thanks

  • qilianzangao - Thursday, December 21, 2017 10:18 AM

    Assuming you are in oracle, I will do it like this:

    SELECT *
    FROM TicketItem
    WHERE CAST(SUBSTR(TO_CHAR(dt_when, 'YYYY-MM-DD HH24:MI:SS'),12,2) AS INT) BETWEEN 7 AND 8
    AND TO_CHAR(dt_when, 'Q') = 'Whatever Quarter You Want'

    Actually, you can declare a variable to specify the quarter you want or maybe you can use a case statement in the where clause.
    The where clause gives the records during 7 and 8 each day for the quarter you want. 

    Thanks

    Considering that this is posted on a website dedicated to SQL Server and it's posted in a forum specifically for SQL 2014, the chances that he is on Oracle are slim to none.

    Also, the DATEPART(HOUR, ....) that was already suggested seems much more efficient than using three functions to get the same result.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • ScottPletcher - Tuesday, December 19, 2017 12:51 PM

    Sorry about the HOUR(), that's the one function I always forget doesn't exist.  Per quarter for the current year, something like this:


    select
      DATEPART(quarter, dt_when) as quarter,
      SUM(sales_amount) AS sales
    from TicketItem
    where s_item IN('Coffee', 'Espresso', 'Double Espresso', 'Decaf Espresso', 'Cappucino', 'Latte', 'Iced Coffee', 'sm coffee pot', 'lg coffee pot')
    and dt_when >= dateadd(year, datediff(year, 0, getdate()), 0)
    and datepart(hour, dt_when) = 7
    group by DATEPART(quarter, dt_when)

    This function will work fine today.  However, in just 11 days from now when the calendar rolls over to 1/1/2018, it will no longer consider 2017 and will instead query data for dates in 2018.

    A better way to describe this query is to "query the total sales for these items by quarter for the current year".  This would limit your ability to query the total sales for the 4th quarter to 12/31/2017 after 8:00 AM, but before the year flipped over to 2018.

    If you want to query quarterly sales for year and quarter from 2017 and later, I'd suggest something like this:

    SELECT SalesYear = DATEPART(year, dt_when),
      SalesQuarter = DATEPART(quarter, dt_when),
      SUM(sales_amount) AS sales
    FROM dbo.TicketItem
    WHERE s_item IN ('Coffee', 'Espresso', 'Double Espresso', 'Decaf Espresso', 'Cappucino', 'Latte', 'Iced Coffee', 'sm coffee pot', 'lg coffee pot')
      AND dt_when >= '01/01/2017'
      AND DATEPART(hour, dt_when) = 7
    GROUP BY DATEPART(year, dt_when), DATEPART(quarter, dt_when)
    ORDER BY DATEPART(year, dt_when), DATEPART(quarter, dt_when);

    If you want to limit it to only 2017, then simply make the dt_when predicate = instead of >= comparison.

Viewing 15 posts - 1 through 14 (of 14 total)

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