My primary goal is to meet the stated business requirements for the code, which I don't believe your code does.  You will list the "truePct" for even a single...

Maybe this?:

SELECT  poll_id ,start_date,end_date,
candidate_name, sample_size,
(select avg(pp2.pct) from [president_polls] pp2 
    where pp2.state = pp.state and pp2.poll_id = pp.poll_id 
   ...

Here is the beginning of a solution.  It uses James F. Allen's (no relation) Interval Algebra:  https://en.wikipedia.org/wiki/Allen%27s_interval_algebra.

The amount used from each bin depends on the relation between the purchases...

• This reply was modified 11 months, 1 week ago by drew.allen.

The endpoints of the window can be specified as any of the following in order:

• UNBOUNDED PRECEDING
• <n> PRECEDING
• CURRENT ROW
• <n> FOLLOWING
• UNBOUNDED FOLLOWING

NOTE: The first endpoint must precede...

You should always check your scripts before posting them on the Internet.  You have an extra comma in your INSERT clause.  You should also make sure that others don't need...

Presumably, the ID field is the primary/unique key, so adding DISTINCT will not change the results,...

I think you need to include the leading zero, since this is a string, not an integer.

select ‘Amount’ as Amount, [0] ,[1] ,[2] ,[2] ,[3] ,[4] ,[5]...

You didn't provide sample data and expected results.

This is my best guess:

WITH BRE AS
(
    SELECT Id, WOCode, Site, Section, CompletionDate
    ...

This solution gives similar results, but performs faster, because there are fewer reads.

SELECT f.createdate, f.holiday, f.holidaydate, f.holidayall, h.holiday, h.holidaydate
     , MAX(h.holiday) OVER(ORDER BY...

The STRING_SPLIT() function was introduced in SQL 2016.  It's not as powerful as the solution Johan is pointing to, but it should suffice for your purposes.

Drew

You're missing sample data and expected results.

It looks like a partial rollup.  Without sample data and expected results, I can only hazard a guess.  The following should be close-ish.

            
September 18, 2024 at 2:36 pm
            
#4459594

            
            
                                                
        

I'll guess since you didn't post a way for anyone to post tested code.

Use LEAD to access subsequent data in the current row. Then compare AgentExpDate to the LEAD...

Do it in the other order.  Use UPDATE to update the records using the OUTPUT clause to output your required fields.

Drew

Did you try:

Select 'Restored' as source, AppUser.UserId, usergroup.Name, AppUser.UserName
from AppUser
INNER JOIN RestoredDatabase.dbo.AppUser_UserGroup 
    ON AppUser.UserId = AppUser_UserGroup.UserId
INNER JOIN RestoredDatabase.dbo.UserGroup 
   ...

This "check" field, is this something that you create as a field in a table?  Because I've never seen that done.

What is the reasoning behind not using BIT flags? ...