It not a fully asked question imo.  It's a good one tho.  Hopefully some of that medication was prescribed.  You're looking for more efficient code.  It comes down to demonstrating...

It hasn't been necessary for my work so...

Ha my exception is for VIEWS with INSTEAD OF triggers

Having performance issues while filtering a view is not uncommon.   Is the view indexed?  How much latitude is there for making changes?

Still works

select *
from drview
where field2 = 'x';

Works.  Not sure what the issue is

drop view if exists drview;
go
create view drview
AS
with cte as (
  select *
  from (values ('x', 'x'), ('x', 'x'), ('x',...

HNY SSC!  Thank you Steve for being a great host.  All the things 😉

I could be wrong but that looks like it will do a full scan of the table for each grouping of rows.  Why not just do a single pass...

One approach could be to use a WHILE loop to increment grp values based on row counts and the DATEDIFF interval

Starting with Jeff's code:

--=================================================================================================
--   ...

The fellow with the "Accepted" answer made the same mistake and worse because he used DENSE_RANK on the days.  Unfortunately for him, there were enough days in the original...

Before the pivot it needs to enumerate the rows partitioned within portfoliocode(s).  Once you have the 1's and 2's (if any) you could pivot using conditional aggregation, or the built-in...

I want to defer this process as long as possible. To me that means always aiming for the latest and greatest version. SQL Server releases roughly every 2-3 years,...

Not sure about SSIS specifically but in T-SQL the following seems to be equivalent afaik and unless it can be proven otherwise 🙂

select case when '1900-01-01'='1900-01-01' then...

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

Imo this is a guessing puzzle unless there's more of an explanation.  What are the rules to apply here?  Why not remove all of the dashes '-' and then concatenate...

What's the new question?