Okay, this one really works... Maybe my brain is taking the weekend off...

First things first, we need some data (CREATE TABLE and INSERT scripts)

use tempdb;
GO
CREATE TABLE #Sales...

• This reply was modified 6 years, 4 months ago by pietlinden. Reason: figured out how to do the pivot
• This reply was modified 6 years, 4 months ago by pietlinden.
• This reply was modified 6 years, 4 months ago by pietlinden.

OUTER APPLY

Like this?

Note: if you provide readily consumable data (create table and insert scripts), you're far more likely to get a tested answer.

-- STEP 1: set up some...

• This reply was modified 6 years, 4 months ago by pietlinden. Reason: reformat... minor stupid errors

Got some sample data? That would help a LOT.

Looks like this works. Here's some data... this is what Jeff is talking about... readily consumable data (CREATE TABLE and INSERT SCRIPTS.)

use tempdb;
go
-- set up your table
CREATE...

• This reply was modified 6 years, 4 months ago by pietlinden.
• This reply was modified 6 years, 4 months ago by pietlinden.

Try utteraccess.com

In spite of the name, they discuss all MS Office stuff there.

Does 'GetAllFavoriteItems' exist in your current restored database? Sounds like it can't find it.

Why the DISTINCT?

where ID in

(

select distinct ID from SourceDB.dbo.Table2 where CreateDateTime<@CutOffPeriod)

Doesn't matter if there are duplicates in the list you're matching to. DISTINCT is expensive. Use it only when you...

I got it to work, but I flat out cheated. I used Access (which most everyone here hates), but it was the easiest solution for me.

I created a table in...

You should be able to do this (create calculated columns) inside SSRS... or are you not using SSRS?

Totally forgot about STRING_AGG()

SELECT SUM(Qty) AS Counted 
, Article
, STRING_AGG(Color,', ') AS Colors
FROM Things
GROUP BY Article;

• This reply was modified 6 years, 4 months ago by pietlinden.

I don't think so. I couldn't get the concatenation of Colors to work without FOR XML.

SELECT  SUM(Qty) AS 'Counted'
 , ColorGrp.Article
 , ColorGrp.Colors 
FROM
(SELECT Article
 , STUFF((SELECT ', ' +...

Looks like it should be:

WHERE (TYPE <> 35 AND ACCT1 <> 2)
OR (TYPE IN (30, 31) AND ACCT1 = 6)

unpivot/normalize, filter, repivot?

Thanks Des! (I hope that was a hard query... otherwise, I'm in trouble!)