SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help fast!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pitmet
Starting Member

USA
17 Posts

Posted - 12/29/2010 :  21:46:16  Show Profile  Reply with Quote
I work in IT but am a novice in sql programming.Due to budget cutbacks I have been given the assignment of running queries for Marketing. I really need the job so I can't refuse. I originally tried using Crystal but was ready to pull my hair out with it's inflexibility -specifically with null values. I'm using SQL 2000 and query analyzer. Please forgive the clumsiness of my code and the overuse of the and clause. I have commented the problems in the code and an alternative to all the "and" clauses would be nice also. Thanks in advance. The code - I hope it's legible:

select a.account_ID, a.address1a, a.address1b, a.city1, a.state1, a.zip1,a.badaddress1,a.mailflag,p.lastname,p.firstname,p.account_ID,s.meta_ID,s.IDtype,s.stattype,s.gamingdate,s.Twin
from Cds_Account a (nolock)
join Cds_Player p (nolock)
on a.account_ID=p.account_ID
join Cds_Statday s (nolock)
on p.account_ID=s.meta_ID
where s.meta_ID=a.account_ID and s.IDtype='p'
and s.meta_ID=P.account_ID and s.IDType='p'
and s.meta_ID=a.account_ID and a.address1a is not null
and s.meta_ID=a.account_ID and a.address1a<>''
and a.state1='AZ' or a.state1='Arizona'
and s.meta_ID=p.account_ID and s.stattype like 'SL%' --am at home so haven't tried like clause yet - was using = 'slot' and it ------returned both slot and pit (seperate ratings)
and s.meta_ID=a.account_ID and s.stattype not like 'P%' --am at home so haven't tried like yet --same as above
--Unforunately they don't let you run queries from home on gaming db'.


and a.mailflag= 'Y'and a.mailflag<>'N'
and a.badaddress1 = 'N'

and s.gamingdate>=datepart(yy,getdate())
and

s.gamingdate>=datepart(mm,getdate())-3
and
s.twin>=10

--need to be seperate condition
or
s.gamingdate=datepart(yy,getdate()) and s.gamingdate<=datepart(mm, getdate())-3 and s.twin>=100

--need to be seperate condition
or
s.gamingdate>=datepart(yy,getdate())-1 and
s.gamingdate>=datepart(mm,getdate())-3 and
s.twin>=100


group by a.account_ID,s.meta_ID,p.account_ID,a.address1a,a.address1b,a.city1,a.state1,a.zip1,a.badaddress1, a.mailflag,p.lastname,p.firstname, s.IDType,s.stattype,s.twin,s.gamingdate
order by s.gamingdate


MET

Edited by - pitmet on 12/29/2010 22:04:50

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3355 Posts

Posted - 12/29/2010 :  22:29:46  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Report from stored procedures. i.e. make sure all database access is via stored procedures - has a number of benefits. You can change the database structure or queries without afectig the report. You can see what is being executed and also put in logging. Can apply security to the SPs and not need to give access to the tables, can build intermediiate structures, ...

One issue I suspect you have here is mixing and and or
Make sure you put brackets round the or statements.

a and b
and (a or b)
and ((a or b) and (a or b))


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

pitmet
Starting Member

USA
17 Posts

Posted - 12/30/2010 :  17:49:59  Show Profile  Reply with Quote
Thanks, I'll try your suggestions when I return to work. We just had a major snowstorm and am not able to get in today,so it will be Monday. I re-submitted the code with the brackets around the or clauses. It looks to me that it will give the desired result. I know there is some redunancy in some of the conditions particularly in the a.mailflag and s.statday fields but they are returning both values(only two in table) so this is an attempt to eliminate this. Is there a way to write this more cleanly without all of the "and" clauses - maybe case statements or something? and how would I construct this as a stored proc? Thanks again for your speedy help.

MET

Edited by - pitmet on 12/30/2010 18:35:25
Go to Top of Page

pitmet
Starting Member

USA
17 Posts

Posted - 12/30/2010 :  18:01:25  Show Profile  Reply with Quote
nigelrivett,
Thought I would re-submit the code a little more legibly and with your suggeted changes. Will this give me the desired result? Code:

select a.account_ID, a.address1a, a.address1b, a.city1, a.state1, a.zip1,a.badaddress1,a.mailflag,p.lastname,p.firstname,
p.account_ID,s.meta_ID,s.IDtype,s.stattype,s.gamingdate,sum(s.Twin) as Theo
from Cds_Account a (nolock)

join Cds_Player p (nolock)
on a.account_ID=p.account_ID

join Cds_Statday s (nolock)
on p.account_ID=s.meta_ID

where s.meta_ID=a.account_ID and s.IDtype='p'
and s.meta_ID=P.account_ID and s.IDType='p'

and s.meta_ID=a.account_ID and a.address1a is not null
and s.meta_ID=a.account_ID and a.address1a<>''

and a.state1='AZ' or a.state1='Arizona'

and s.meta_ID=p.account_ID and s.stattype like 'SL%'
and s.meta_ID=a.account_ID and s.stattype not like 'P%'


and a.mailflag= 'Y'and a.mailflag<>'N'
and a.badaddress1 = 'N'

and s.gamingdate>=datepart(yy,getdate())
and

s.gamingdate>=datepart(mm,getdate())-3
and
s.twin>=10

--need to be seperate condition

or (s.gamingdate=datepart(yy,getdate()) and s.gamingdate<=datepart(mm, getdate())-3 and s.twin>=100)

--need to be seperate condition
or
(s.gamingdate>=datepart(yy,getdate())-1 and
s.gamingdate>=datepart(mm,getdate())-3 and
s.twin>=100)


group by a.account_ID,s.meta_ID,p.account_ID,a.address1a,a.address1b,a.city1,a.state1,a.zip1,a.badaddress1, a.mailflag,p.lastname,p.firstname, s.IDType,s.stattype,s.twin,s.gamingdate
order by s.gamingdate



MET
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000