So a lot of folks have written articles where no one has posted to the discussions in well over a year.  If someone reads and article and has a...

duplicate

After following Ken and Jeff's advice first maybe have a look at the error handling and flow of control.  You've got TRY/CATCH all over the place and GOTO and it's...

The explanation seems somewhat open to interpretation.  Maybe you could explain why Brazil's ItemCount is 300 and not 500?  If 500 is the correct number for Brazil then maybe something...

• This reply was modified 3 years, 9 months ago by Steve Collins.
• This reply was modified 3 years, 9 months ago by Steve Collins.

You have an explicit transaction called from within BEGIN TRY/END TRY yet the ROLLBACK (which presumably corresponds to the transaction) is in the CATCH block.  Checking XACT_STATE in...

Cheers...

Hello Everyone,

I just started a developing a new application for hospitals (for maintaining hospital staff, patient, appointments etc.). I started with the database part first.

I will use APIs as...

I totally agree, Grant.

I'm looking at introducing "software engineering" as a topic to discuss at work with the data engineers, and introduce them to all of those concepts like...

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 3 years, 10 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 3 years, 10 months ago by Steve Collins.
• This reply was modified 3 years, 10 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 3 years, 10 months ago by Steve Collins.
• This reply was modified 3 years, 10 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...