If you're creating a stored procedure to put your CTE in, just pass @StartRange DATE, @EndRange DATE...

Kind of depends where you do the limiting... if you do it in the stored procedure, use something like

SELECT...

FROM ...

[SomeDate] >= DATEDIFF(m,-3,GETDATE())

Trying to subtract from a MONTH() value won't work.

Not sure you can show a many-to-many relationship without a child table. That's just the standard way to do it... the standard example is

Invoice--(1,M)--LineItem---(M,1)--Product

where the LineItem table...

In the first/inner query, use a DISTINCT.

SELECT DISTINCT [column list]

and then in the outer query, do your sum

SELECT Col1, Col2, SUM([numericColumn])

FROM

(SELECT...

Try this:

SELECT DocumentNum, LineNum, CustomerNum, MAX(VersionNum) AS MaxVersion

FROM tempSalesOrder

GROUP BY DocumentNum, LineNum, CustomerNum;

that and the windowing functions.

Thanks!

I had a disaster like this once, but it was in [ick] Access. After I restructured and showed them how simple it was to query, they were sold.

petronas40 (10/22/2013)

---

I have to extract the record which has 6 numbers which are together from the string .

Customer ID Address

6237 ...

Just thinking...

what if you did a summary on this:

iphone_id,

seriel_number,

max(date_assigned),

and then joined that result back to the table to return the rest of the info you need?

so

max(date_assigned) = assignments.date_assigned

and...

Use MAX

Group by the stuff before the dash.

CAST the stuff after the dash as a number, get MAX of it.

I have a report which has times in the following format: (11:20:01). I need to have a subtotal row added which has an AVG of all the times. I am...

Having Access able to see/modify data in your database is a terrifying idea, IMO. You might be safe if you grant the users ReadOnly access, but a knucklehead with...

Client want read/write access to all authenticated domain users on table in database .

Explain to them the potential problems with complying with such a request. There are good reasons...

If these are columns in your query...

[Ship To Dealer],[Car Line],Status,Count

SELECT [Ship To Dealer],[Car Line],[Status] As BadReservedWord, COUNT(*) AS LineCount

FROM MyTable

ORDER BY [Ship To Dealer],[Car Line],[Status]

GROUP BY [Ship To Dealer],[Car Line],[Status]

Post enough information so that people can help you.

1. Table definitions (right-click, script CREATE TABLE... to clipboard)... paste here.

2. Some data (a few records - enough so we know what...