worked great!  Thank you!

Jeff,  We have an existing table that houses this information for other years.  I was tasked with adding the dates for year 2021 to this existing table, but I didn't...

Yes, I have the fnTally function.  When I run your code the way it is, it errors "Invalid column name 'Number'

Sorry. His version uses "N". I modified my version...

Do you have Jeff's fnTally function and did you test it? It is a TVF that in this case returns numbers 1-52...

after I fixed the script to return Number +...

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 11 months, 3 weeks 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!