Jeff, yes it will always start on Wednesday.  So the week is from Wednesday thru Tuesday.

Something like this using a tally table or function:

DECLARE @startDate date = '2021-01-06'
DECLARE @nextYear date = DATEADD(year,1,DATEADD(year,DATEDIFF(year,0,@startDate),0));
SELECT DATEADD(WEEK,Number,@startDate ) AS StartDate, DATEADD(DAY,6,DATEADD(WEEK,Number,@startDate )) AS EndDate, Number...

Worked!  Thank you Des.

Actually, I think...

Try using the LocDocAmt only...that's the only column that matters.  Also, LocDocAmt has negative amounts.  The other amount columns will throw off the results in different examples.

• This reply was modified 2 years, 1 month ago by  GrassHopper.

Simply include...

I'm not looking to delete, just exclude.  See my response to Des below.  I made an adjustment to the data.  I left out a column by mistake.

Sorry for my lazyness in not providing the consumable data.  I just realized I left out another column (LocDocAmt ) which should be the correct amt column.  This changes the...

When I run Scott's code, I get this error msg?

Msg 156, Level 15, State 1, Line 6

Incorrect syntax near the keyword 'RowCount'.

Msg 156, Level 15, State 1, Line 8

Incorrect syntax...

This is a sample of the data:

--===== If the test table already exists, drop it
     IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
   ...

Jonathan, This works great!  thanks!

I think it works better with the pivot because I am joining it to create a new table...

Into POInfo 
From MeritusMmPO as PO 
Left Join MeritusMmPOLines as...

This is the input data:

This is what I would like it to look like:

That worked...thanks!

I have a separate related issue now.

The data looks like this

When I run this code:

Select SourceID, PurchaseOrderID, [1]...

I found my own answer!  I had an issue with the ID column.  Fixed that and issue resolved.