April 20, 2015 at 6:26 pm
Please, no links. I have looked at many examples and websites, and I am not understanding it.
DECLARE
@dtstart_time DATETIME ,
@dtend_time DATETIME
DECLARE @parts TABLE
(
i_period_definition_id INT ,
s_name NVARCHAR(50) ,
dt_start DATETIME ,
dt_finish DATETIME ,
std_day_part_ordinal INT ,
s_weekdayssunday1 NVARCHAR(7)
)
INSERT INTO @parts
SELECT *
FROM dbo.dwf_getdaypartsfordaterange(@dtstart_time, @dtend_time)
BEGIN
SELECT
Rev_Name = 'Banquet - All Day',
Net = ISNULL(SUM(ti.c_ticketitem_net_price), 0),
Total_Covers = (SELECT SUM(cover_count)
FROM Ticket
WHERE i_revcenter_id = 7 and dt_close_time >= @dtstart_time AND
dt_close_time < @dtend_time and b_closed = 1 and i_void_ticket_id IS NULL)
--Avg_Cover = SUM(@cover / ti.c_ticketitem_net_price), 0)
FROM
TicketItem AS ti
INNER JOIN ticket t ON ti.i_ticket_id = t.i_ticket_id
INNER JOIN (
MenuItem AS mi
INNER JOIN RevenueClass AS rc ON rc.i_class_id = mi.i_revenue_class
) ON ti.i_menu_item_id = mi.i_menu_item_id
JOIN @parts parts ON dt_ti_ticket_close_time >= parts.dt_start AND
dt_ti_ticket_close_time <= parts.dt_finish
WHERE
ti.i_ti_revcenter_id = 7 AND
ti.i_void_item_id IS NULL AND
ti.i_ti_void_ticket_id IS NULL AND
ti.dt_ti_ticket_close_time >= @dtstart_time AND
ti.dt_ti_ticket_close_time < @dtend_time AND
t.b_closed = 1
END
Any help is very appreciated.
Thank you
Chris
April 20, 2015 at 6:34 pm
You were 90% of the way there:
CREATE PROCEDURE YourProcName
(
--DECLARE
@dtstart_time DATETIME ,
@dtend_time DATETIME
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @parts TABLE
(
i_period_definition_id INT ,
s_name NVARCHAR(50) ,
dt_start DATETIME ,
dt_finish DATETIME ,
std_day_part_ordinal INT ,
s_weekdayssunday1 NVARCHAR(7)
);
INSERT INTO @parts
SELECT *
FROM dbo.dwf_getdaypartsfordaterange(@dtstart_time, @dtend_time);
SELECT
Rev_Name = 'Banquet - All Day',
Net = ISNULL(SUM(ti.c_ticketitem_net_price), 0),
Total_Covers = (SELECT SUM(cover_count)
FROM Ticket
WHERE i_revcenter_id = 7 and dt_close_time >= @dtstart_time AND
dt_close_time < @dtend_time and b_closed = 1 and i_void_ticket_id IS NULL)
--Avg_Cover = SUM(@cover / ti.c_ticketitem_net_price), 0)
FROM
TicketItem AS ti
INNER JOIN ticket t ON ti.i_ticket_id = t.i_ticket_id
INNER JOIN (
MenuItem AS mi
INNER JOIN RevenueClass AS rc ON rc.i_class_id = mi.i_revenue_class
) ON ti.i_menu_item_id = mi.i_menu_item_id
JOIN @parts parts ON dt_ti_ticket_close_time >= parts.dt_start AND
dt_ti_ticket_close_time <= parts.dt_finish
WHERE
ti.i_ti_revcenter_id = 7 AND
ti.i_void_item_id IS NULL AND
ti.i_ti_void_ticket_id IS NULL AND
ti.dt_ti_ticket_close_time >= @dtstart_time AND
ti.dt_ti_ticket_close_time < @dtend_time AND
t.b_closed = 1;
END
Note that I added semi-colon statement delimiters where appropriate to do so.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 20, 2015 at 6:39 pm
Thanks...
Now my final issue is getting the Avg_Covers...
CoverAvg = SUM(Net / Total_Covers)
But I keep getting this:
Msg 207, Level 16, State 1, Line 30
Invalid column name 'Net'.
Msg 207, Level 16, State 1, Line 30
Invalid column name 'Total_Covers'.
I dont understand, how can those 2 not allow me to divide by them? They hold values for peet's sake!
April 21, 2015 at 12:08 pm
dwain.c
Can you assist?
Thank you very much,
Chris
April 21, 2015 at 12:22 pm
You cannot use the alias within the select. (ORDER BY yes)
There are several ways to do this. Some are recalculate, use a CTE, create a temp table
April 21, 2015 at 12:28 pm
Try this:
CREATE PROCEDURE YourProcName
(
--DECLARE
@dtstart_time DATETIME ,
@dtend_time DATETIME
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @parts TABLE
(
i_period_definition_id INT ,
s_name NVARCHAR(50) ,
dt_start DATETIME ,
dt_finish DATETIME ,
std_day_part_ordinal INT ,
s_weekdayssunday1 NVARCHAR(7)
);
INSERT INTO @parts
SELECT *
FROM dbo.dwf_getdaypartsfordaterange(@dtstart_time, @dtend_time);
SELECT
Rev_Name = 'Banquet - All Day',
Net = ISNULL(SUM(ti.c_ticketitem_net_price), 0),
Total_Covers = (SELECT SUM(cover_count)
FROM Ticket
WHERE i_revcenter_id = 7 and dt_close_time >= @dtstart_time AND
dt_close_time < @dtend_time and b_closed = 1 and i_void_ticket_id IS NULL),
CoverAvg = ISNULL(SUM(ti.c_ticketitem_net_price), 0) / (SELECT SUM(cover_count)
FROM Ticket
WHERE i_revcenter_id = 7 and dt_close_time >= @dtstart_time AND
dt_close_time < @dtend_time and b_closed = 1 and i_void_ticket_id IS NULL)
--Avg_Cover = SUM(@cover / ti.c_ticketitem_net_price), 0)
FROM
TicketItem AS ti
INNER JOIN ticket t ON ti.i_ticket_id = t.i_ticket_id
INNER JOIN (
MenuItem AS mi
INNER JOIN RevenueClass AS rc ON rc.i_class_id = mi.i_revenue_class
) ON ti.i_menu_item_id = mi.i_menu_item_id
JOIN @parts parts ON dt_ti_ticket_close_time >= parts.dt_start AND
dt_ti_ticket_close_time <= parts.dt_finish
WHERE
ti.i_ti_revcenter_id = 7 AND
ti.i_void_item_id IS NULL AND
ti.i_ti_void_ticket_id IS NULL AND
ti.dt_ti_ticket_close_time >= @dtstart_time AND
ti.dt_ti_ticket_close_time < @dtend_time AND
t.b_closed = 1;
END
April 21, 2015 at 12:33 pm
Lynn Pettis (4/21/2015)
Try this:
CREATE PROCEDURE YourProcName
(
--DECLARE
@dtstart_time DATETIME ,
@dtend_time DATETIME
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @parts TABLE
(
i_period_definition_id INT ,
s_name NVARCHAR(50) ,
dt_start DATETIME ,
dt_finish DATETIME ,
std_day_part_ordinal INT ,
s_weekdayssunday1 NVARCHAR(7)
);
INSERT INTO @parts
SELECT *
FROM dbo.dwf_getdaypartsfordaterange(@dtstart_time, @dtend_time);
SELECT
Rev_Name = 'Banquet - All Day',
Net = ISNULL(SUM(ti.c_ticketitem_net_price), 0),
Total_Covers = (SELECT SUM(cover_count)
FROM Ticket
WHERE i_revcenter_id = 7 and dt_close_time >= @dtstart_time AND
dt_close_time < @dtend_time and b_closed = 1 and i_void_ticket_id IS NULL),
CoverAvg = ISNULL(SUM(ti.c_ticketitem_net_price), 0) / (SELECT SUM(cover_count)
FROM Ticket
WHERE i_revcenter_id = 7 and dt_close_time >= @dtstart_time AND
dt_close_time < @dtend_time and b_closed = 1 and i_void_ticket_id IS NULL)
--Avg_Cover = SUM(@cover / ti.c_ticketitem_net_price), 0)
FROM
TicketItem AS ti
INNER JOIN ticket t ON ti.i_ticket_id = t.i_ticket_id
INNER JOIN (
MenuItem AS mi
INNER JOIN RevenueClass AS rc ON rc.i_class_id = mi.i_revenue_class
) ON ti.i_menu_item_id = mi.i_menu_item_id
JOIN @parts parts ON dt_ti_ticket_close_time >= parts.dt_start AND
dt_ti_ticket_close_time <= parts.dt_finish
WHERE
ti.i_ti_revcenter_id = 7 AND
ti.i_void_item_id IS NULL AND
ti.i_ti_void_ticket_id IS NULL AND
ti.dt_ti_ticket_close_time >= @dtstart_time AND
ti.dt_ti_ticket_close_time < @dtend_time AND
t.b_closed = 1;
END
Wow, I feel silly, that was kinda right in my face the entire time!
Thanks Lynn!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply