Advanced date/time queries

I'm a new member here and love the amount of knowledge available.

1) I have a database schema and it stores repair information for phones, with columns such as ID, Phone manufacturer, model, fault, etc.What query would I have to write to get a query which returns the phone model, its manufacturer (this I know), but also a number of the amount of times that phone model has been raised in the repair table (which is every time a customer hands in a faulty phone). Do I use a count function for the phone model or is there a more efficient way?

2) Another table stores contract information with an expiry date of the contract. How can I write a query that will pick up the contracts due to expire exactly one month for today? I have looked all over the net for some resourced on writing advanced tsql queries, does anyone know of any good websites or even books on the topic?

Thanks

GSS1 wrote:

1) I have a database schema and it stores repair information for phones, with columns such as ID, Phone manufacturer, model, fault, etc.What query would I have to write to get a query which returns the phone model, its manufacturer (this I know), but also a number of the amount of times that phone model has been raised in the repair table (which is every time a customer hands in a faulty phone). Do I use a count function for the phone model or is there a more efficient way?

You could answer this using the query:

Code Snippet

select t.phone_model, t.phone_manuf, count(*) as #faults

from t

group by t.phone_model, t.phone_manuf

GSS1 wrote:

2) Another table stores contract information with an expiry date of the contract. How can I write a query that will pick up the contracts due to expire exactly one month for today? I have looked all over the net for some resourced on writing advanced tsql queries, does anyone know of any good websites or even books on the topic?

This can be done by using query like below:

Code Snippet

select *

from Contracts as c

where c.expiry_date >= dateadd(month, 1, convert(varchar, CURRENT_TIMESTAMP, 112));

The "Inside SQL Server 2005" books are good ones for learning advanced TSQL programming.