Query to extract when there are multiple dates

[EBOUGHEY]

I need to pull all customers who have not been serviced in more than 6 months. The catch is that there are multiple activity dates per customer. They might have come in last week so they have one record with a recent date and one with a date more than six months ago.

How can I structure the query so it finds all the records properly?

The link fields are customer_no, vin and last_repair_order_date

 

[mwolf00]

SELECT customer_no, max(last_repair_order_date) FROM myTable HAVING max(last_repair_order_date) < getDate() - 180

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[mwolf00]

oopps

SELECT customer_no, max(last_repair_order_date) FROM myTable GROUP BY customer_no HAVING max(last_repair_order_date) < getDate() - 180

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[EBOUGHEY]

Quick Response! Thanks. Worked beautifully.

This is the reason I continue supporting Tek-tips. I always get great support here.

Elena

Have a wonderful day

 

[Bignewbie]

guys,

how about this scenario:

i have a table with these two columns EmailAdd and UserName.

I want to display all EmailAdd entries that has been used more than once by different UserName entries.

im intrigued with your max() directive. I think it has a lot of use like the count(). sorry if i sound like a newbie to sql. that's because i really am.


thanks


biggie






The Man of Tomorrow is forged by his battles today.

 

[mwolf00]

SELECT EmailAdd, count(EmailAdd) FROM myTable GROUP BY EmailAdd HAVING count(EmialAdd) > 1

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[Bignewbie]

hey, im impressed! you do reply fast. a little more complication, if you dont mind.

Actually, I also want to display all EmailAdd entries that has been used more than once by different UserName entries disregarding how many times the EmailAdd has associated by a UserName entry. This time i want to know how many UserName entries are associated with an EmailAdd entry.

thanks in advance!


biggie



The Man of Tomorrow is forged by his battles today.

 

[EBOUGHEY]

Have another question.

How about if I want to select * into a new table that have a clientnumber of '38r002' and having max(lastrepairorderstamp) < getdate() - 180.

I get the error for aggragrate or group by for every field.

Simple fix right?

 

[jby1]

eboughy, it would help if you posted the query that is giving you the error.

 

[mwolf00]

SELECT
customer_no
, field2
, field3
, field4
, max(last_repair_order_date) last_repair_Date
INTO newTable
FROM myTable
WHERE clientnumber = '38r002'
GROUP BY customer_no
, field2
, field3
, field4
HAVING max(last_repair_order_date) < getDate() - 180

OR you might want to do this....

SELECT *
INTO anotherTable
FROM myTable t
JOIN (SELECT customer_no, max(last_repair_order_date) FROM myTable GROUP BY customer_no HAVING max(last_repair_order_date) < getDate() - 180 ) dTab ON t.customer_no = dTab.customer_no
WHERE
clientnumber = '38r002'


Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[mwolf00]

bigNewbie

Try this...

SELECT userName, EmailAdd
FROM myTable
WHERE userName IN
(SELECT userName
FROM myTable
GROUP BY userName HAVING count(emailAdd)>1)
ORDER BY userName, emailAdd

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)