SELECT
'Banquets - All Day' as revName,
SUM(t.c_items_total) AS Banquet_Total,
SUM(t.cover_count) as Total_Covers,
SUM(t.c_items_total) / SUM(t.cover_count) as AvgPer_Cover

FROM dbo.Ticket AS t 
CROSS JOIN dbo.PeriodDefinition AS pd 
WHERE  t.dt_close_time >= pd.dt_start and t.dt_close_time <= pd.dt_finish
  AND pd.i_period_definition_id = 4 
  AND t.i_revcenter_id = 7 
  AND t.i_void_ticket_id IS NULL 
  AND t.b_closed = 1

quote:

---

Originally posted by gbritton

You didn't mention if your time fields are just times or datetimes. I assumed the latter.

SELECT
'Banquets - All Day' as revName,
SUM(t.c_items_total) AS Banquet_Total,
SUM(t.cover_count) as Total_Covers,
SUM(t.c_items_total) / SUM(t.cover_count) as AvgPer_Cover

FROM dbo.Ticket AS t 
CROSS JOIN dbo.PeriodDefinition AS pd 
WHERE  t.dt_close_time >= pd.dt_start and t.dt_close_time <= pd.dt_finish
  AND pd.i_period_definition_id = 4 
  AND t.i_revcenter_id = 7 
  AND t.i_void_ticket_id IS NULL 
  AND t.b_closed = 1

---

FROM dbo.Ticket AS t 
JOIN dbo.PeriodDefinition AS pd 
   ON t.dt_close_time >= pd.dt_start and t.dt_close_time <= pd.dt_finish
  AND pd.i_period_definition_id = 4 
  AND t.i_revcenter_id = 7 
  AND t.i_void_ticket_id IS NULL 
  AND t.b_closed = 1

quote:

---

Originally posted by gbritton

Hmmm... not good practice. So keep the last bit as

FROM dbo.Ticket AS t 
JOIN dbo.PeriodDefinition AS pd 
   ON t.dt_close_time >= pd.dt_start and t.dt_close_time <= pd.dt_finish
  AND pd.i_period_definition_id = 4 
  AND t.i_revcenter_id = 7 
  AND t.i_void_ticket_id IS NULL 
  AND t.b_closed = 1

---

quote:

---

Originally posted by gbritton

Tell me. are pd.dt_start an pd.dt_finish datetime types or just time values? If they have the dates, you should be fine. Post a few samples if you can.

---

quote:

---

Originally posted by gbritton

so, a ticket with a given close time must fall between those times,right?

If a 24-hour period begins a 4 am, a typical one might be

2015-03-11 04:00:00.000 2015-03-12 03:59:59:999

correct?

so, a ticket closed at, say 12:30 on the 12th would fall into this interval, right?

---

quote:

---

Originally posted by gbritton

Why does period 4 start at 10 p.m. and end at 4 a.m. on the same day? shouldn't the end date be the next day?

Also, Change the join predicate to:

ON t.dt_close_time >= pd.dt_start and t.dt_close_time < pd.dt_finish -- strictly less than
AND ...

---

quote:

---

Originally posted by gbritton

Your period table is messed up. I asked:

"Why does period 4 start at 10 p.m. and end at 4 a.m. on the same day? shouldn't the end date be the next day?"

You reiterated that the workday is 4am to 4am but didn't answer my question.

Earlier you posted this example:

LateNight, i_period_definition_id = 4 '1899-12-30 22:00:00.000' to
'1899-12-30 04:00:00.000'

but the end datetime is before the start datetime, since it is the same day. That just can't be correct

but the dt_finish

---