I"m going to weigh in on the side of artificial keys.    I have wavered on this issue for a long time, but what ultimately convinced me was the annoying...

Listen to Jeff on this.    He helped me out tremendously, as he has helped countless others.

You first need to do some research on spatial concepts.    You can only consider flight paths to be straight lines over very small distances.    If you are flying...

• This reply was modified 5 years ago by  The Dixie Flatline.

Could we by any chance look at the underlying CTE that you mentioned?

Your solution will get the correct result, but it is running the entire First4Count_Cte twice.    Basically generating a huge number of rows and then filtering them out.    The...

The PIVOT function will do what you want.    You can also google for the old school way of doing a pivot.   It's called a "Crosstab".

I find the syntax easier...

This isn't pretty, but it seems to work.

The code assumes that '^' is a safe character to use to bookmark the position of the ending period.   If not, pick another. ...

• This reply was modified 5 years ago by  The Dixie Flatline.
• This reply was modified 5 years ago by  The Dixie Flatline. Reason: Cleaned up function and revised notes
• This reply was modified 5 years ago by  The Dixie Flatline.

It helps to remember that, in SQL, CASE is an expression, not a flow control statement.   All CASE does is use if/then/else logic to produce a single value.

Your original logic...

And this thread just keeps on keeping on...

This is a pure rant, so read at your own risk.

I have been involved in the interview process for a senior sql developer...

declare @start date = '2018/08/01'

;with tally (N) as (select top 366 row_number() over(order by (select null)) from sys.columns c1 cross join sys.columns c2)
select Dateadd(day,N-1,@start)...

Are you simply wanting to not include weekend days (Saturday, Sunday), or are you trying to find the previous BUSINESS day?

If you are looking for the previous business...

If you aren't afraid of CTEs, use this syntax.   😉

;with cte1 as (select id, pro, [type]
                    , coalesce(qty1,lead(qty1,1) over(partition by pro, [type] order by modifieddate...

Another variation.   For a huge file, I hope there is a covering index on the Pro, Type, and Modified Date columns.

select id, Pro, [Type], Qty1,Qty2,...

Jeff is indeed the MASTER OF THE TALLY TABLE.... and many other things besides.  😉

Not recursive at all, just a cte.    But yes, it finds the first space from the right to pull the strings to be tested.