Here is my answer!

SELECT pt.[imtPartID], pt.[imtTransactionType],
SUM (CASE WHEN pt.[imtTransactionType] = 1 THEN pt.[imtPurchaseQuantityReceived] ELSE 0.00000 END) AS Receipt, 
SUM (CASE WHEN pt.[imtTransactionType] = 2 THEN pt.[imtPurchaseQuantityReceived] ELSE...

This is great.

So now I need to add another fiend into the mix.  There must be some rules to make this happen.  Basically, I need to add the other field,...

Just one more question...

If there are no values, thus no sums in the Pivot Query below, is there a way to have it return zero's?

SELECT pt.[imtPartID], 
SUM(CASE...

Got it, thanks.  I think part of my dilemma has been those controls are so small, and when I hover over them the descriptions are covered up by Windows icons.

Phil:

Happy to comply, but I'm not sure I know how to do those things.  I will try to figure it out.

Other sites have controls for these things, which I do...

• This reply was modified 3 years, 1 month ago by  stephen.aa.

Just one more thing... The example above spans 29 months.  Is there a good way to separate it out to place three columns for each month within the date range?

I think this does it.

SELECT pt.[imtPartID],
SUM(CASE WHEN pt.[imtTransactionType] = 1 THEN 1 ELSE 0 END) AS Receipt,
SUM(CASE WHEN pt.[imtTransactionType] = 2 THEN 1 ELSE 0 END) AS...

• This reply was modified 3 years, 1 month ago by  stephen.aa.

Jonathan, Thanks again!

SELECT TOP (1000000) [PartTransactions].[imtTransactionDate]
,[PartTransactions].[imtTransactionType]
,[PartTransactions].[imtPartID]
FROM [M1_SU].[dbo].[PartTransactions]
WHERE [imtPartID] = '7000917' AND imtTransactionDate BETWEEN '5/1/2019 3:13:52 PM' AND '10/29/2021 3:13:52 PM'
ORDER BY imtTransactionDate DESC

Ok, here's some data for...

• This reply was modified 3 years, 1 month ago by  stephen.aa.

This is good, except that all the values are zero.  I know for certain there are nine records with valid data here.  Am I supposed to do something with the...

I added a pic.  Unfortunately, lots of red underlines means I cannot get results.

Thank you!

I did figure this out, so much thanks to everyone for the help.  There was zer0 data after all....

• This reply was modified 3 years, 7 months ago by  stephen.aa.

SELECT JobOperations.jmoJobID AS JobNo, JobAssemblies.jmaPartID AS PartID, JobAssemblies.jmaPartShortDescription AS Description, JobOperations.jmoOperationQuantity AS Qty, JobOperations.jmoWorkCenterID AS WorkCenter, JobOperations.jmoProcessID AS Process, SUM(JobOperations.jmoCompletedProductionHours) AS Hours, JobOperations.jmoProductionComplete AS C, CAST(JobOperations.jmoCreatedDate AS DATE) AS StartDate,...

• This reply was modified 3 years, 7 months ago by  stephen.aa.

Thank you.