December 19, 2017 at 11:11 am
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.
December 19, 2017 at 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
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 19, 2017 at 11:22 am
ScottPletcher - Tuesday, December 19, 2017 11:19 AMNot 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.
December 19, 2017 at 11:41 am
ScottPletcher - Tuesday, December 19, 2017 11:19 AMNot 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?
December 19, 2017 at 11:59 am
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);
December 19, 2017 at 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.
December 19, 2017 at 12:11 pm
chef423 - Tuesday, December 19, 2017 12:01 PMselect *
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) = 7This 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())
December 19, 2017 at 12:16 pm
Lynn Pettis - Tuesday, December 19, 2017 12:11 PMchef423 - Tuesday, December 19, 2017 12:01 PMselect *
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) = 7This 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
December 19, 2017 at 12:39 pm
Lynn Pettis - Tuesday, December 19, 2017 12:11 PMchef423 - Tuesday, December 19, 2017 12:01 PMselect *
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) = 7This 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?
December 19, 2017 at 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)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 19, 2017 at 1:00 pm
Thank you to you all! Helps bigtime. I need to really learn date and time functions.
December 19, 2017 at 1:15 pm
chef423 - Tuesday, December 19, 2017 1:00 PMThank you to you all! Helps bigtime. I need to really learn date and time functions.
Books Online is a great resource.
December 21, 2017 at 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
December 21, 2017 at 11:41 am
qilianzangao - Thursday, December 21, 2017 10:18 AMAssuming 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
December 21, 2017 at 1:41 pm
ScottPletcher - Tuesday, December 19, 2017 12:51 PMSorry 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