GilaMonster (5/23/2016)

---

Why this restriction?

Though, from the requirement, it doesn't sound like it needs a union or an apply....

Trace is being deprecated. You are better off using extended events if your version of SQL supports them.

Drew

First, I find it a lot easier to read if you use the Table Value Constructor

insert into @DedTest

VALUES

('California', '56756', '1003375', 80915, 1, cast(2000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),

('California', '56756', '1003375',...

Providing an exact duplicate of your original message is not going to make your objectives any clearer. Specifically, it is not going to answer the question of why you...

Did you try a CROSS APPLY instead of a subquery?

Updating a table is expensive; altering a table is expensive. I updated your query without any updates and in ran in about 1/10 the time.

;

WITH E(n) AS(

...

Why aren't you just calculating your revenue in your main report?

An alternative would be to include your project information in your sub-report.

Drew

The CASE expression makes your join non-SARGable. You are better off using a UNION. Why do you not want to use UNION?

Drew

You cannot use an aggregate in the main query of an UPDATE statement. You'll need to use a CTE, derived table, or subquery.

ROW_NUMBER() will give you a unique sequence...

Rod at work (5/19/2016)

---

Just thought of something else to add to this. Years ago I was working with some other report generating product (I can't remember what it was now),...

Steve Jones - SSC Editor (5/19/2016)

---

drew.allen (5/19/2016)

---

Would it be possible to limit posts based on the number of visits? I noticed that most of the spam comes from people...

You can do this with only one CTE.

;

WITH cteCount AS(

SELECT Port, Vessel, BLNumber, ROW_NUMBER() OVER( PARTITION BY Port, Vessel ORDER BY COUNT(*) DESC ) rn

...

This type of report is usually best left to the presentation layer. It's possible to do this in T-SQL, but it's not the best tool for the job. ...

The report is looking for COMPUTER_NAME, but your original query has ComputerName. This is the main argument for using a CONSISTENT naming convention. I prefer separating pieces with...

Would it be possible to limit posts based on the number of visits? I noticed that most of the spam comes from people with 1 or 2 visits. ...