April 21, 2002 at 10:39 pm
G'day from Australia. I am running SQL7 and am a little stuck at the minute. What I have is two tables, one with customer data, another with logged job information. The common join is a unique id in both tables (customer ID). The result set I am looking for is to return all customer ID's which have not logged a job in the last 12 months. Any ideas? Matt.
April 22, 2002 at 1:28 am
Hi
I think the following query should work. Try and let me know.
select * from customer_log where DATEDIFF(year, last_login_date, getdate()) > 1
I am not sure that this works, but anyhow, let me know.
Regards,
Anbarasan Mani
April 22, 2002 at 1:43 am
Hi Blue use this query
Select customerID from customer_data where customerid not in (select customerid from customer_log where last_login_date > dateadd(day, -365, getdate())
The above query will solve your problem. All the best.
April 22, 2002 at 4:30 am
Sorry to say this one is a bit overkill
quote:
Select customerID from customer_data where customerid not in (select customerid from customer_log where last_login_date > dateadd(day, -365, getdate())
the other does a datediff which should be fine however you may also want to do the following so your time is based on midnight 1 year ago instead of 1 year ago at the time you run this. I assume you are using British date format so it would look something like this.
SELECT customerid FROM customer_log WHERE last_login_date > DATEADDT(YEAR, -1, CONVERT(VARCHAR,GETDATE(),103))
See BOL about convert and datetime formats without time values on end.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 22, 2002 at 5:55 pm
Thanks for your help guys, problem solved!
April 22, 2002 at 11:05 pm
Further to the above post, is their a simple query to run that will give you a list of all records in a table EXCLUDING duplicates? I want to display only one instance of a record, not have it showing 500 times.
April 23, 2002 at 4:25 am
If the entire record being displayed is duplicated then use SELECT DISTINCT. The distinct keyword will elimnate duplicates. If the entire row is not duplicated you will have to find someway to determine which single record will be output using one of the fields (usually a datefield that will always be unique works but any will do as long as you can uniquely id the row), but there are ways to generate unique numbers if this is not the case. Check the FAQs and Scripts as well as other threads, there is always some topic on this recently. If thou you cannot find what you need post a sample of what you get and what you expect so someone can help.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply