Hi there,
Thanks all for the help. I will find out if Crystal (developer) is installed on that box and try to run it with that and see what I get.
There is no record selection formula. The SP handles all record selection and Crystal just spits out what's produced. When I try the report on my local machine or run the SP through Query Analyzer I get expected results. But something on that box is preventing the return of records when the performance type is "IC". Hopefully I'll find it when I run the report on that box in developer, if I can.
However, I'm including the code from the SP here, just in case you guys see anything wierd about it. I don't think it's that (otherwise, I'd get some error or no records or something when I run the SP in Query Analyzer) but since I'm not sure, I'm posting it.
Thanks again for all your help!!!
create PROCEDURE usp_hst014
@BeginPermitholder INT,
@EndPermitholder INT,
@BeginDate smallDATETIME,
@EndDate smallDATETIME,
@PType varchar(512)
AS
set nocount on
declare @BeginDateString varchar(16)
declare @EndDateString varchar(16)
set @BeginDateString = convert(varchar(16),@BeginDate,1)
set @EndDateString = convert(varchar(16),@EndDate,1)
declare @strTmp as varchar(218)
declare @strWhere as varchar(256)
declare @strSub as varchar(16)
declare @intChar as int
declare @intStart as int
--declare @intflag as int
--set @intflag = 0
if (substring(@PType,1,3) = 'All')
begin
set @strWhere = '1=1'
end
else
begin
set @intStart = 1
set @strWhere = ''
set @strTmp = @PType
set @intChar = charindex(',',@strTmp,1)
while @intChar > 0
begin
set @strSub = substring(@strTmp,@intStart,@intChar - @intStart)
--if upper(RTrim(LTrim(@strSub))) = 'C'
--begin
--set @intflag = 1
--end
--print @strSub
set @strWhere = @strWhere + 'dtetype = ''' + RTrim(LTrim(@strSub)) + ''' or '
set @intStart = @intChar + 1
set @intChar = charindex(',',@strTmp,@intChar + 1)
end
set @strSub = substring(@strTmp,@intStart,100)
--if upper(RTrim(LTrim(@strSub))) = 'C'
--begin
--set @intflag = 1
--end
--print @strSub
set @strWhere = @strWhere + 'dtetype = ''' + RTRim(LTrim(@strSub)) + ''''
--print @strWhere
end
create table #working_table (
dtascode int,
dtastype char(1),
--dtetype char(2),
pmw_handle bigint,
numraces smallint,
number_of_performances smallint,
number_of_racing_days smallint,
total_paid_attendance int,
admission_tax decimal(10,2),
admission_tax_credit decimal(10, 2),
public_winnings decimal (12,2),
minus_breakage decimal (14, 2),
guest_takeout decimal (10,2),
daily_license_fee decimal (10, 2),
tax_on_handle decimal (12, 2),
tax_credits decimal (12,2),
state_breakage decimal (14,2),
--retained_by_track decimal (14,2),
purses decimal (12,2),
p_and_e_purses decimal (13,2),
owners_awards decimal (13, 2),
track_breakage decimal (14,2),
players_awards_breaks decimal (14,2),
breeders_assn_awards decimal (13,2)
)
DECLARE primary_pull CURSOR FOR
select distinct dtascode
from R301_handle_dist
where dtascode >= @BeginPermitholder
and dtascode <= @EndPermitholder
and dtpdate between @BeginDateString and @EndDateString
--step through each of the permitholders listed in the above cursor. For each of these
-- run the SQL Query below
declare @CurrentPH INT
--declare @CurrentType varchar(3)
declare @execStr varchar(8000)
declare @strTmpWhere varchar(256)
open primary_pull
fetch next from primary_pull into @currentPH
while @@FETCH_STATUS = 0
BEGIN
set @execStr = ''
set @execStr = '
INSERT INTO #working_table (dtascode,dtastype, pmw_handle,numraces,
number_of_racing_days, total_paid_attendance, admission_tax,
public_winnings, minus_breakage, daily_license_fee, tax_on_handle,
tax_credits, purses)
select
dtascode,dtastype,
sum(dttaxhandl) as pmw_handle,
sum(dtnumraces) as numraces,
count(distinct dtpdate) as number_of_racing_days,
sum(dttotpdat) as total_paid_attendance,
sum(dtadtax) as admission_tax,
sum(dtpublic) as public_winnings,
sum(dtmnsbrk) as minus_breakage,
sum(dtdaylic) as daily_license_fee,
sum(dtstcomm) as tax_on_handle,
sum(dttaxcr) as tax_credits,
sum(dtpurses) as purses
from R301_handle_dist where dtascode = ' + cast(@CurrentPH as char(3)) + ' and
(dtpdate between ''' + @BeginDateString + ''' and ''' + @EndDateString + ''') and (' + @strWhere + ')
group by dtascode,dtastype
order by dtascode'
--print @execStr
exec (@execStr)
-- update number_of_performances
set @execStr = 'update #working_table set number_of_performances = (select count(dtpnumber) from R301_handle_dist where dtascode = '
+ cast(@CurrentPH as char(3)) + ' and dtpdate between ''' + @BeginDateString + ''' and ''' + @EndDateString
+ ''' and dtattendancereq = ''Y''
and (' + @strWhere + '))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)
set @execStr = 'update #working_table set admission_tax_credit =
(CASE
when dtastype = 3 then 0.00
when dtastype = 4 then 0.00
when dtastype <> 3 then (select sum(dtadtax) from r301_handle_dist
where dtascode >= ' + cast(@BeginPermitholder as char(3)) + ' and dtascode <= ' + cast(@EndPermitholder as char(3)) + '
and dtpdate between ''' + @BeginDateString + ''' and ''' + @EndDateString + '''
and dtadtax < dttaxcr and dtadtax > 0.0
and (' + @strWhere + '))
END)
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)
set @execStr = 'update #working_table set admission_tax_credit = admission_tax_credit +
(CASE
when dtastype = 3 then 0.00
when dtastype = 4 then 0.00
when dtastype <> 3 then (select sum(dttaxcr) from r301_handle_dist
where dtascode >= ' + cast(@BeginPermitholder as char(3)) + ' and dtascode <= ' + cast(@EndPermitholder as char(3)) + '
and dtpdate between ''' + @BeginDateString + ''' and ''' + @EndDateString + '''
and dtadtax >= dttaxcr and dtadtax > 0.0
and (' + @strWhere + '))
END)
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)
-- update state breakage
set @execStr = 'update #working_table set state_breakage =
(isnull((select sum(dtbrk) from r301_handle_dist
where dtascode = ' + cast(@currentPH as char(3)) + '
and (' + @strWhere + ')
and dtpdate between + ''' + @BeginDateString + ''' and ''' + @EndDateString + ''' and dtbrksflag = ''P''), 0.00))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)
-- update p&e breakage
set @execStr = 'update #working_table set p_and_e_purses =
(isnull((select sum(dttakeoutamt) from r301_handle_dist_takeout
where dtascode = ' + cast(@currentPH as char(3)) + '
and (' + @strWhere + ')
and dtpdate between + ''' + @BeginDateString + ''' and ''' + @EndDateString + ''' and dttakeoutcode = ''7''), 0.00))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)
-- update owners awards
set @execStr = 'update #working_table set owners_awards =
(isnull((select sum(dttakeoutamt) from r301_handle_dist_takeout
where dtascode = ' + cast(@currentPH as char(3)) + '
and (' + @strWhere + ')
and dtpdate between + ''' + @BeginDateString + ''' and ''' + @EndDateString + ''' and dttakeoutcode = ''6''), 0.00))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)
-- update track breakage
set @execStr = 'update #working_table set track_breakage =
(isnull((select sum(dtbrk) from r301_handle_dist
where dtascode = ' + cast(@CurrentPH as char(3)) + '
and (' + @strWhere + ')
and dtpdate between + ''' + @BeginDateString + ''' and ''' + @EndDateString + ''' and dtbrksflag = ''I''), 0.00))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)
-- update player breakage
set @execStr = 'update #working_table set players_awards_breaks =
(isnull((select sum(dtbrk) from r301_handle_dist
where dtascode = ' + cast(@CurrentPH as char(3)) + '
and (' + @strWhere + ')
and dtpdate between + ''' + @BeginDateString + ''' and ''' + @EndDateString + ''' and dtbrksflag = ''S''), 0.00))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)
-- update breeders association awards
set @execStr = 'update #working_table set breeders_assn_awards =
(isnull((select sum(dttakeoutamt) from r301_handle_dist_takeout
where dtascode = ' + cast(@CurrentPH as char(3)) + '
and (' + @strWhere + ')
and dtpdate between + ''' + @BeginDateString + ''' and ''' + @EndDateString + ''' and dttakeoutcode = ''P''), 0.00))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)
set @strTmpWhere = replace(@strWhere,'dtetype','gtdtetype')
-- update guest takeout
set @execStr = 'update #working_table set guest_takeout =
(isnull((select sum(gtdtgtcomm) from r309_guest_handle where gtdthost = ' + cast(@CurrentPH as char(3)) + '
and gtdtpdate between ''' + @BeginDateString + ''' and ''' + @EndDateString + '''
and (' + @strTmpWhere + ')),0.00))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)
fetch next from primary_pull into @CurrentPH
END
--close the cursor
close primary_pull
deallocate primary_pull
select
sum(pmw_handle) as pmwHandle,
sum(numraces) as num_races,
sum(number_of_performances) as numPerformances,
sum(number_of_racing_days) as numRacingDays,
sum(total_paid_attendance) as totalPaidAtt,
sum(admission_tax) as admTax,
sum(admission_tax_credit) as admTaxCredit,
sum(public_winnings) as pubWin,
sum(minus_breakage) as minusBreakage,
sum(guest_takeout) as guestTakeout,
sum(daily_license_fee) as dailyLicFee,
sum(tax_on_handle) as taxHandle,
sum(tax_credits) as taxCredit,
sum(state_breakage) as stateBreakage,
sum(purses) as Purse,
sum(p_and_e_purses) as pePurse,
sum(owners_awards) as ownerAwards,
sum(track_breakage) as trackBreakage,
sum(players_awards_breaks) as playerAwards,
sum(breeders_assn_awards) as breederAwards
from #working_table
drop table #working_table
-- once finally done, output the results (in the working table) to the report
GO
-= Hypermommy =-
