date type error?
-
Hi all,
when I run the below script, I receive the error "Incorrect syntax near tp. I'm not sure what I am missing. Any help is appreciated.
declare
@group_id int
/* Get the student group id */
select @group_id=user_id from people where id='student' and type='Group'
/* Get the print jobs for each printer between @start and @end and put the results into a temporary table */
select
pr.name,
total=sum(tp.amount)
into #print
from
printers pr,
transaction_types tt,
transactions tp
where
tp.server_id=pr.server_id and
tp.ref_id=pr.printer_id and
tp.ttype_id=tt.ttype_id and
tt.type='PR' and
tp.time>'01/13/04'
tp.time<'05/15/04'
group by pr.name
/* Get the STUDENT prints for each print between @start and @end and put the results into a temporary table*/
select
pr.name,
student=sum(tsp.amount)
into #stu
from
printers pr,
transaction_types tt,
transactions tsp,
people p
where
tt.type='PR' and
tsp.server_id=pr.server_id and
tsp.ref_id=pr.printer_id and
tsp.ttype_id=tt.ttype_id and
tsp.time>'01/13/04' and
tsp.time<='05/15/04' and
tsp.user_id=p.user_id and
group by pr.name
/* Get the credits applied to each printer between @start and @end and put the results into a temporary table */
select
pr.name,
credit=sum(tcp.amount)
into #credit
from
printers pr,
transaction_types ttc,
acu_print_credits apc,
transactions tcp,
transactions t
where
t.server_id=pr.server_id and
t.ref_id=pr.printer_id and
t.transaction_id=apc.print_id and
tcp.transaction_id=apc.credit_id and
tcp.ttype_id=ttc.ttype_id and
ttc.type='CR' and
tcp.time>'01/13/04' and
tcp.time<='05/15/04'
group by pr.name
-
Not sure. It looks like "tp.ttype_id=tt.ttype_id" might have two t's for type_id.
You may want to run this select on its own, (rather than putting it into #TempTable) and see if you can isolate the problem.
I wasn't born stupid - I had to study.
-
Operator (probably "AND") is missing in one of the WHERE clauses between the two conditions for date:
tt.type='PR' and
tp.time>'01/13/04'
tp.time<'05/15/04'
group by pr.name
-
Good eye, Vladan!!
I wasn't born stupid - I had to study.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply