| Author |
 Topic  |
|
|
pitmet
Starting Member
USA
17 Posts |
 Posted - 12/29/2010 : 21:46:16
|
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
3328 Posts |
Posted - 12/29/2010 : 22:29:46
|
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. |
 |
|
|
pitmet
Starting Member
USA
17 Posts |
Posted - 12/30/2010 : 17:49:59
|
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 |
|
|
|
pitmet
Starting Member
USA
17 Posts |
Posted - 12/30/2010 : 18:01:25
|
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 |
|
|
| |
Topic |
|
|
|
Need help fast!
