date type error?

Question

date type error?

Shelley Allen

SSCrazy

Points: 2146
More actions
April 21, 2005 at 3:48 pm

#164052

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
  p.group_id=@group_id
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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

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.

Vladan SSC-Insane Points: 22072 More actions · Answer 2

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Good eye, Vladan!!

I wasn't born stupid - I had to study.