comparison query.

[mr moe]

I have a table that has almost 10,000 records. The table is an imported one from excel. The main two columns are: printer_serial_num and call_date
call date column is the date customers called for trouble shooting.

how can i write a query to give me all printers that have mulitple or more problmes withing five days range. example printer_serial_num: abc1255 had five 3 calls within a week apart.

if someone can help me please, I need to know where to start from! thanks.

 

[modest]

Depends on how you want to write the algorithm. If you wanted to do it week by week, it'd be easier. Doing it by any 5 date span would be a little more tricky.

 

[ByteMyzer]

This post should really be in the Queries forum, and I'm not sure what you mean by "multiple or more", but the following SQL should to the trick (substitute the highlighted items with the actual name):

SELECT DISTINCT T1.printer_serial_num
FROM [b][i]MyTable[/i][/b] AS T1
WHERE EXISTS
 (
  SELECT T2.printer_serial_num
  FROM [b][i]MyTable[/i][/b] AS T2
  WHERE T2.printer_serial_num = T1.printer_serial_num
  AND T2.call_date <= T1.call_date
  AND T2.call_date >= T1.call_date - 5
  GROUP BY T2.printer_serial_num
  HAVING COUNT(T2.call_date) > 1
 )
;