CODEWITHSUNDEEP
sqlreporting-services
Greg rock
Greg rock

Reputation: 13

New and learning, Need help cleaning this up

I'm completely new to SQL and have been reading and learning a lot from this forum. I've written the following query and it seems like its close, can anyone help?

Inc_main occup_id type completed staff_id 000001 200 22-Jan-09 MCGI01 000001 200 27-Feb-09 MCGI01 000003 200 30-Dec-08 CRIT01

Occ_main occup_id name insp_dist zone number street st_type 000003 Business Name 62 1 10945 street name RD 000004 Business Name 61 2 9730 street name RD 000005 Business Name 62 1 10965 street name RD

SELECT 
  occ_main.insp_dist, Ins_main.Occup_id, Ins_main.Completed, Ins_main.Type, Ins_main.staff_id,DATEDIFF  (Day,Ins_main.completed,GETDATE())AS diffdate
FROM
  Occ_main INNER JOIN Ins_main.occup_id ON Occ_main.occup_id = Ins_main.occup_id INNER JOIN
  Ins_main INNER JOIN(SELECT ins_main.Occup_id, MAX(ins_main.Completed) as maxdate
    FROM Ins_main
    group by ins_main.Occup_id) c2
    ON c2.Occup_id = Ins_main.Occup_id
    and c2.maxdate = Ins_main.Completed


GROUP BY
  occ_main.insp_dist,
  Ins_main.occup_id,
  Ins_main.completed,
  Ins_main.type,
  Ins_main.staff_id

Upvotes: 0

Views: 36

Answers (1)

R. Richards
R. Richards

Reputation: 25161

Your join is a little messed up. Try this:

SELECT 
  occ_main.insp_dist, Ins_main.Occup_id, Ins_main.Completed, Ins_main.Type, Ins_main.staff_id, 
  DATEDIFF(Day,Ins_main.completed, GETDATE()) AS diffdate
FROM
  Occ_main INNER JOIN Ins_main 
  ON Occ_main.occup_id = Ins_main.occup_id 
  INNER JOIN (SELECT ins_main.Occup_id, MAX(ins_main.Completed) as maxdate
    FROM Ins_main
    group by ins_main.Occup_id) c2
    ON c2.Occup_id = Ins_main.Occup_id
    and c2.maxdate = Ins_main.Completed 
GROUP BY
  occ_main.insp_dist,
  Ins_main.occup_id,
  Ins_main.completed,
  Ins_main.type,
  Ins_main.staff_id

Upvotes: 1

Related Questions