[Edit] I posted some code that didn't look right so I updated.  Maybe something like this

select w2.RCWHS# as DC, w2.RCITM# as [Item Number],
    ...

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

Maybe something like this

;with
cte_ntile(ReadingDateTime, ReadingValue, [Group]) AS (
    select *, ntile(2) over(order by ReadingDateTime asc) AS [Group]
    from #SIDataGroup),
cte_rn(ReadingDateTime, ReadingValue, [Group],...

• This reply was modified 4 years, 6 months ago by Steve Collins. Reason: Typo
• This reply was modified 4 years, 6 months ago by Steve Collins. Reason: Simplified query: replaced UNION ALL in subquery with SELECT TOP(n)

It turns out the median is derived from the ordered set.   My query only calculated the midpoint.  If the number of rows in the [Group] (calculated as 'grp_count') is even...

Thanks for the feedback.  Nice I'm happy if the code helps.  Precision-wise it's maybe not ideal.  I tried it with nanoseconds and there was an overflow error.  Maybe microseconds would...

Maybe something like this.  The 'median_dt' column calculation adds half the difference in seconds between the min and max ReadingDateTime values within the [Group] group to the min ReadingDateTime

            
October 6, 2021 at 2:01 pm
            
#3938084

            
            
                                                
        

Imo you could maybe start with a query like this.  The 'final_cte' CTE selects the row(s) with the final status for each Deal.  Then OUTER APPLY is used to count...

It's not a good logical model.  What is the maximum number of rows per Deal?  If it's 2 you could join the table to itself

I just ran into this same issue.  The short answer is to create a #temp table instead and it works (because the temp table exists within the same execution context...

The other comment.  Your comment was very on topic 🙂

Off topic much?  A couple is 2 and a few is 3 and more than a few is 5 but...

Well a lot could said about this topic.  The classic threaded forum/subforum organization might seem straightforward it depends on many things.  If by "threaded" it means "nested html" then that's...

Maybe you could use a CTE to make sure the right table source of the JOIN in the UPDATE statement contains appropriate unique priorityCode's

with unq_priority_cte(priorityid,priorityCode,priorityname) as (
...

It...

Ken McKelvey this looks familiar

https://www.sqlservercentral.com/forums/topic/need-to-create-gaps-in-a-table-of-date-ranges

Again one "brute force" way to do this would be to expand all the date ranges and use the set operator EXCEPT to remove the (also...

It's a one time only type query or it's intended to be run frequently?  The id value in the last row of the sample data should be 5, no?