Greg rock Greg rock - 5 months ago 9
SQL Question

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

Answer

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
Comments