Create a separate index of just the IDs. Then select a random ID using ORDER BY NEWID(), then use that ID to get the rest of the columns. ...

Do you want the concatenation to be able to cause a match? For example, if the PartnerName ends in "f", and the Address begins with "arm", should that cause...

If the tables are indexed on id, you just have to force SQL to use the obvious MERGE join it should have been using anyway(!) 😀 :

DROP TABLE #TABLE1

DROP TABLE...

Sean Lange (12/8/2015)

---

ScottPletcher (12/7/2015)

---

In this case, there's no real need for CTEs or UNIONs either, just standard FULL joins:

SELECT

COALESCE(t1.PRODUCTID, t2.PRODUCTID, t3.PRODUCTID) AS PRODUCTID,

...

An INSERT to a table won't try to return a result set ... unless you're missing a:

SET NOCOUNT ON

at the start of the trigger. Verify that statement is present.

Edit:...

In this case, there's no real need for CTEs or UNIONs either, just standard FULL joins:

SELECT

COALESCE(t1.PRODUCTID, t2.PRODUCTID, t3.PRODUCTID) AS PRODUCTID,

COALESCE(t3.STATUS,...

SQLPain (12/7/2015)

---

Thanks Scott, I appreciate your help, but not passing the year, wouldn't it do a count of all the years for that month, for example in future if I...

Sure, fine, I give up.

Just be aware that using MONTH or any other function on a column could give you horrible performance.

Month is a datetime type: you need to provide a full date:

@month = '20151001'

The CROSS JOIN is just used to assign an alias name to the constructed date.

SQLPain (12/7/2015)

---

Scott, would the following work, its kind of giving me the correct results:

ALTER PROC Applications

(

@Month AS DATETIME,

@Year AS DATETIME

)

AS

BEGIN

LEFT JOIN (SELECT COUNT(CA.app_id) AS Approved_Count, SO.source_id

...

SQLPain (12/7/2015)

---

Scott, would the following work, its kind of giving me the correct results:

ALTER PROC Applications

(

@Month AS DATETIME,

@Year AS DATETIME

)

AS

BEGIN

LEFT JOIN (SELECT COUNT(CA.app_id) AS Approved_Count, SO.source_id

...

You'll get best performance only from properly clustering the underlying table. Then either a direct query or a view should perform just fine.

TheSQLGuru (12/4/2015)

---

The simplest solution by far would seem to be fix the data. 🙂 Update all NULL start-date values to the minimum for the data type (1/1/1753 for full datetime...

Code below should filter it for a single month, as specified in @month:

LEFT JOIN (SELECT COUNT(CA.app_id) AS Approved_Count, SO.source_id

...