Need help fast! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help fast!

Author

Topic

pitmet
Starting Member

17 Posts

Posted - 2010-12-29 : 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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-29 : 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

17 Posts

Posted - 2010-12-30 : 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

pitmet
Starting Member

17 Posts

Posted - 2010-12-30 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources