USP_DATALIST_DAILYADMISSIONSALES_BYDATE

CREATE procedure dbo.USP_DATALIST_DAILYADMISSIONSALES_BYDATE
(
  @CONTEXTID uniqueidentifier
)
as
  set nocount on;

  select 
    sum(SOI.QUANTITY) - isnull(sum(CI.QUANTITY),0) QUANTITY,
    --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

    cast(cast(SO.TRANSACTIONDATE as date) as datetime) as TRANSACTIONDATE
  from dbo.SALESORDERITEM SOI
  left outer join dbo.CREDITITEM CI on CI.SALESORDERITEMID = SOI.ID
  inner join dbo.SALESORDER SO on SOI.SALESORDERID = SO.ID
  inner join dbo.SALESORDERITEMTICKET SOIT on SOIT.ID = SOI.ID
  inner join dbo.PROGRAM P on SOIT.PROGRAMID = P.ID
  where P.ID = @CONTEXTID
  and exists(select 1 from dbo.TICKET where SALESORDERITEMTICKETID = SOIT.ID and STATUSCODE <> 2)
  and SO.STATUSCODE = 1
  group by cast(cast(SO.TRANSACTIONDATE as date) as datetime)
  order by cast(cast(SO.TRANSACTIONDATE as date) as datetime);

  return 0;