As Scott points out moving the ON condition of a left join to the WHERE will make it an inner join.

But it is a good idea to try rewriting the...

I have a list of transactions in a table. Each transaction has a unique id which is a Guid, a date, a product that was ordered and an amount....

I have a list of transactions in a table. Each transaction has a unique id which is a Guid, a date, a product that was ordered and an amount....

You can export the data as text files as long as you re-import the files to check it goes smoothly. People often find commas in csv fields or...

How could I incorporate this into a report?

Can I add this to the bottom of the query?

IF OBJECT_ID('tempdb..#source_table', 'U') IS NOT NULL DROP TABLE #source_table;

IF OBJECT_ID('tempdb..##new_table', 'U') IS NOT...

if a global temp table works, that would be fantastic.  When, how does a global temp table get dropped.

P.S. I'm thinking of putting this code into a report, which...

This works using a global temporary table:

IF OBJECT_ID('tempdb..#source_table', 'U') IS NOT NULL DROP TABLE #source_table;
IF OBJECT_ID('tempdb..##new_table', 'U') IS NOT NULL DROP TABLE ##new_table;

DECLARE @sql NVARCHAR(MAX);
DECLARE @cols NVARCHAR(MAX);
DECLARE @insert_sql...

Error - Windowed functions cannot be used in the context of another windowed function or aggregate.

SELECT ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account") AS "RowNo", A."Account" AS "Account", (A."Debit")...

RIGHT_SHIFT and LEFT_SHIFT are CPU instructions implemented in hardware using barrel shifters, making them extremely fast, significantly faster than multiplying or dividing by 2 raised to an integer power. However,...

The POWER function is relatively resource-intensive, so a more efficient approach is to hardcode the 30 values directly.

CREATE OR ALTER FUNCTION dbo.ufn_GetStatusMSB_NF 
(
    @status...

Sorry, I should have been more specific; the reason I don’t use a temp table is the CTE is in an inline TVF which takes @status as a parameter;...

I just tried this and it works

DROP TABLE IF EXISTS #Numbers
-- Create a temporary table
CREATE TABLE #Numbers (
    Num INT 
);

-- Populate the temporary...

.

• This reply was modified 6 months, 4 weeks ago by Jonathan AC Roberts. Reason: duplicate

Ive also tried

SELECT top 1000 [cp_initialreviewId] ,[cp_AssessmentChannelOptions] ,(SELECT [ChannelName] FROM OPENJSON(cp_AssessmentChannelOptions) WITH (ChannelName NVARCHAR(50) '$.ChannelName')) ,(SELECT [Removed] FROM OPENJSON(cp_AssessmentChannelOptions) WITH (Removed BIT '$.Removed'))

FROM [PIP_MSCRM_MI_DB].[dbo].[cp_initialreviewBase]

But I get the error

Subquery returned...

You can use OPENJSON in SQL Server to parse the JSON string and extract the required fields dynamically.

Solution

DECLARE @json NVARCHAR(MAX) = 
'[{"ChannelName":"LOT4 - Telephone","ChannelRecordId":"42c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false},
  {"ChannelName":"LOT4 -...