If you're fairly new to SQL, I would do this in SSRS, because you can get SSRS to do pretty much all the hard work for you (sorting, grouping, subtotals,...

Okay, read this article and follow the instructions... Not trying to be mean, just trying to teach you how to use this site. Because if you don't follow the...

Since you're new, I'll go easy on you... <g>

One thing to keep in mind when posting questions is that you're the only one with access to your database, so if...

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...