Can you explain what you are trying to do with the query?

You cannot return two values fin the THEN and you also cannot OR two strings together ( THEN 'Midwest'...

I don't understand what you are trying to do or your data. But I think the part:

Bank = (Select Bank From Customer Where Customer=t.Customer)

is redundant.

I think this...

select  'Midwest' OR 'Mid-Atlantic'

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'OR'.

Completion time: 2021-11-04T21:11:04.2830349+00:00

IF OBJECT_ID('tempdb..#PartTransactions','U') IS NOT NULL
    DROP TABLE #PartTransactions
GO
SELECT *
INTO #PartTransactions
FROM (VALUES ('2020-12-31 19:00:09',3,7000917),
       ('2020-09-29 12:05:25',1,7000917),
   ...

the 'x' was just there so it's included in the count. You could put anything that's not null.

This is also the equivalent:

SELECT pt.[imtPartID],
    ...

I think this might be what you want:

SELECT pt.[imtPartID],
       COUNT(CASE WHEN pt.[imtPartID] = 1 THEN 'x' ELSE NULL END) AS Receipt,
...

I can't see that your query will work?

You are missing a bracket after PIVOT.

If you could supply your input data and the output expected it would be easier to understand.

CREATE TABLE [dbo].[tblClientService]
(
    [ID]          [int] IDENTITY(1,1) NOT NULL,
    [ClientID]   ...

Thanks Jeff,

Good so see the function is in use. It does make your code much shorter than other methods.

Data type disparity on join conditions etc is not really an after thought I've seen over time, its more like a 'no thought.' You bring up an excellent point...

I think the functions TRY_CAST, TRY_CONVERT and TRY_PARSE might be of use.

I think OUTER APPLY is the way to go.

Here is another way to write it, also using OUTER APPLY:

SELECT p.PatID, 
      ...

This should work

SELECT w2.RCWHS#                       ...

Another method

select *
  from #temp t
 where t.Branch = 'head quarters'
 union all
select *
  from #temp t
 where not exists(select *
    ...

;with cte as
(
    select distinct t.Id
      from #temp t
)
select t.*
  from cte x
 cross apply(select top(1) *
 ...