Oops, I edited the code above and posted instead grr.  Preemptively, imo concatenation is preferable to replacement when the strings are not complicated (such as this one here) because it...

• This reply was modified 4 years, 2 months ago by Steve Collins.

This seems to return the correct results and could be "more optimized" (depending on cardinalities, indexes, etc.)

select C1
from #Base
group by C1
having sum(iif(C2='122', 1, 0))>nullif(sum(iif(C2='123', 1, 0)), 0);

Something like this maybe

with
unq_itemno_cte(itemno) as (
    select distinct itemno
    from #data),
pvt_cte(itemno, string) as...

• This reply was modified 4 years, 2 months ago by Steve Collins.
• This reply was modified 4 years, 2 months ago by Steve Collins.

Maybe try starting here for examples of the old way

One modern element I would add would be to use CONCAT_WS ("concat...

Obviously there's no need for nullable columns or a cursor

drop table if exists #data;
go
create table #data (
  businessday        ...

• This reply was modified 4 years, 2 months ago by Steve Collins.
• This reply was modified 4 years, 2 months ago by Steve Collins.

I have gone through the two parts of Jeff's articles. But I still couldn't figure out the solution. here's my sample data:

businessday category itemno value
2022-01-01 xyz...

In which table is the 'job' column a unique key?  Probably v_job_operations.  The only column being SELECT'ed from v_job_operations_wc is 'workcenter'.  It appears there are 3 different 'workcenter' values in...

Something I've always wondered is how is data loaded in a normalized OLTP database? One example that I think about is an ATM transaction because it captures data ranging...

Syntax-wise the CASE expressions are both missing the keyword END.  Also, it's not good to enclose column labels in single quotes because although SSMS and VS have no issues with...

It seems safer to avoid LEAD and LAG because start/stop conditions might repeat within ranges of groups

with 
gap_cte as (
    select *, iif(v.sgn<>lag(v.sgn) over...

On first read it's not very clear.  Having the sample data and expected results makes all the difference.   Not sure about the ORDER BY tho

 with
 Parts_cte(PartId)...

Questions are offered quite acceptable solutions if only some representative sample data is provided.  Make it easy to copy/paste temp table(s) and data.   If it's reduced to "here is the...

Since you're not providing minimally representative data here's a minimally representative query.  One important thing missed in the previous attempt was the 'Bias' indicates over/under vs budget based on +/-1. ...

In general the SIGN function can be useful for "over and under" type situations.  It seems like the question was edited maybe idk.  Anyway, it seems you're looking for the...

The LAG function takes up to 3 parameters:

1) Column name (required)
2) Row offset (optional)
3) Default value (optional)

To find 3 +/- consecutive rows with the same sign maybe...