Solved

Trying to perform SQL query but getting too many results

Posted on 2008-10-28
4
170 Views
Last Modified: 2013-11-30
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

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

Open in new window

0
Comment
Question by:smyers051972
  • 2
4 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22824163
do you need to group by b.slotmast_id and c.meta_id

also, what value is returning for the auditdate?  You may need to format it.  
0
 
LVL 1

Author Comment

by:smyers051972
ID: 22824307


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
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 22828890
I suggest you pick one player and one day and filter on that and look at the data. You'll probably find there's lots and lots of meta_id's for that player/day and thats where your problem is - don't group by that.
0
 
LVL 1

Author Closing Comment

by:smyers051972
ID: 31510848
Resolved

Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

1 Experts available now in Live!

Get 1:1 Help Now