Hold a sec, the code above is making multiple replacements of the same character.  7 replaces 1 then 5 replaces 7.  The reason seems to be because the order of...

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

This is one of those gray areas... or idk maybe someone can explain why this actually works.  For reasons unknown, afaik when the REPLACE function is used in the righthand...

Yes a calendar table is another workable approach.  What if the minDate and/or maxDate is other than the 1st or last day of the month?  Maybe something like this

            
October 22, 2021 at 12:40 pm
            
#3943234

            
            
                                                
        

Yes sorry.  It should've been included.  The code is from Jeff Moden's article here on SSC. It comes in handy in many places

CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
 ...

For the counting to begin at 1 maybe you could assign the value 1 to rows where the status is null.  Then UNION ALL for the additional rows with the...

There are different date calculations depending on granularity.  This seems to work

drop table if exists #foo;
go
create table #foo(
  [Table]       sysname,
...

Hi Joel, maybe something like this.  It's pretty self-explanatory once you run it

drop table if exists #foo;
go
create table #foo(
  [datetime]    datetime,
  tonsperhour...

Maybe you're looking for something like this.  It assumes there's a primary key column in the OfenbuchVC1212_V10 table called 'OfenbuchId'.  After update of the OfenbuchVC1212_V10 table the trigger first determines...

• This reply was modified 4 years, 1 months ago by Steve Collins. Reason: Added INSERT

[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, 1 months ago by Steve Collins.
• This reply was modified 4 years, 1 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, 1 months ago by Steve Collins. Reason: Typo
• This reply was modified 4 years, 1 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