smyers051972
asked on
Trying to perform SQL query but getting too many results
Hi all,
In our casino database I am trying to pull who played, what slot number, and how much coinin they played between a specific date range. For some reason I am getting over 100,000 results and my brain is fried trying to figure out what I am doing wrong.
I attached the code below and what I am attempting to do is a result similar to my example here:
AuditDate
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
AuditDate
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
AuditDate
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
The coinin would be total sums. I also realise in QA that the results will not necessarily be grouped like the above but I can use the query in Crystal Reports as a command and group it that way then.
Thanks
In our casino database I am trying to pull who played, what slot number, and how much coinin they played between a specific date range. For some reason I am getting over 100,000 results and my brain is fried trying to figure out what I am doing wrong.
I attached the code below and what I am attempting to do is a result similar to my example here:
AuditDate
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
AuditDate
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
AuditDate
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
PlayerID SlotNumber Coinin
The coinin would be total sums. I also realise in QA that the results will not necessarily be grouped like the above but I can use the query in Crystal Reports as a command and group it that way then.
Thanks
Use WinOasis
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#slotsinfo]')) DROP TABLE #slotsinfo
create table #slotsinfo
(
auditdate datetime,
slotnumber int,
slotmast_id int,
meta_id int,
elecCoinIn money
)
insert into #slotsinfo
( auditdate,
slotnumber,
slotmast_id,
meta_id,
elecCoinIn
)
select b.auditdate,
a.slotnumber,
b.slotmast_id,
c.meta_id,
sum(b.eleccoinin)
from cds_slotmast A(nolock)
inner join bb_revenue B (nolock) on a.slotmast_id = b.slotmast_id
inner join cds_statdetail C (nolock) on c.slotnumber = a.slotnumber
where b.auditdate
between '2006-07-01 00:00:00.000' and getdate()
and b.period_id = '4'
and a.active = 'Y'
and a.currentrevision = 'Y'
and a.slotnumber
between '252' and '254'
GROUP BY b.auditdate, a.SlotNumber, b.slotmast_id, c.meta_id
-- remarked out have to manually run it for debug purposes
--select * from #slotsinfo (nolock) order by meta_id, auditdate
ASKER
Where I run into the biggest problems is when I try to figure out the coin in total per player id (meta_id) by day per slot machine. I figure there should be 3 results per slot machine per day, I am going back just over 700 days I think. So if I had about 2500 results or so that wouldnt be too bad, I am getting over 200,000 results!! =)
The only thing I really care to do is get the total coinin per player itemized by slot machine number per day.
I hope that helps I confused myself writing this one LOL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Resolved
Thanks!
Thanks!
also, what value is returning for the auditdate? You may need to format it.