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 7 years, 2 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!)

Problems like this make me really like normalization. =)

After failing at this several times, this is as far as I've gotten. I'd love to see the right answer, because this...

• This reply was modified 7 years, 2 months ago by pietlinden. Reason: forgot to include the link the to the "Many uses of CROSS APPLY" article
• This reply was modified 7 years, 2 months ago by pietlinden.
• This reply was modified 7 years, 2 months ago by pietlinden.

Amen to that! It's a truly awful design. To the OP - performance of queries against a table like this are going to be painfully slow. You should be able...

thanks for the head start... I was curious, so I finished it.

 SELECT nm.item,
 Name = NU.Item 
  FROM test T
  CROSS APPLY SCRIDB.dbo.DelimitedSplit8K(T.name, ';') NM
  CROSS APPLY SCRIDB.dbo.DelimitedSplit8K(T.numbers,...

You need to help you. Read the articles Chris posted. Create some tables, try out the different options - LEFT JOIN, EXCEPT, NOT IN.

I'd probably send a tweet to @psdbatools and see what happens. or #psdbatools

Maybe this will help?

https://social.technet.microsoft.com/wiki/contents/articles/1073.robocopy-and-a-few-examples.aspx

There's not enough data to test your query, but this is the general pattern and should work:

use tempdb;
GO
CREATE TABLE #ToGive (
 GiverID INT,
 NumGifts TINYINT,
 GiverName VARCHAR(10),
 City VARCHAR(15)
 );
GO
INSERT INTO #ToGive...

• This reply was modified 7 years, 2 months ago by pietlinden.