Don't re-use @I.  Come up with a new variable to store the counter for the inner...

To fix the first issue you could add 'order_num' to the GROUP BY clause.

Not sure why 'Dry Feed' doesn't show.  It's using an equivalent of the 'Dry Feed' WHERE clause...

Why is this necessary?  Here's a solution using (two of) the fnTally function to generate the rows instead of WHILE loops.  Re-using declared variables is generally not a good...

Sorry it's been a busy day!  Please give this a try

declare
  @start      datetime='2020-11-29 00:00:00.000',
  @end     ...

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

The second post makes the first one clearer.  In the first post the 2nd query in the statement containing UNION ALL is the only place where 'Dry Feed' is defined...

It's not fully explained imo.  The SELECT above the UNION ALL is summarizing 'Wet Feed' tons per hour?  The SELECT below the UNION ALL is 'Dry Feed'?  It appears you're...

The OP said they're going to migrate to a newer version. ...

After the update the code could look something like this

drop TABLE if exists #Invoices
go
CREATE TABLE #Invoices(
[InvoiceID] [int] IDENTITY(1,1) NOT NULL,
[InvoiceDate] [smalldatetime] NULL,
[Total] [decimal](18, 2) NULL,
CONSTRAINT [PK_Invoices] PRIMARY...

The lazy programmers' way to delete from complex data structures is to get a data comparison tool to generate the SQL DELETE transaction.  Where I work we've used 2 different...

Steve... the OP has posted in a 2005 forum which is before SUM() OVER had the ability to produce a running sum.  I'm thinking we're going to need another...

You could try something like this

drop table if exists #MyTempTable
go
create table #MyTempTable(
  EE        int not null,
  EffDate ...

When "trying to get all combinations..." it usually means CROSS JOIN.  In this case the distinct year/month dates are selected in the CTE.  Then the CTE is 'row multiplied' or...

Oh wait I think I see what you're saying now.  It should subtract 2 days from the current month 2nd Thursday to compare with the current date.  The query could...

It depends on what the "today" date is, no?

CASE 2 - If Today is 1 Dec 2020 - Then, The output should be (12 Nov 2020) -

Reason,  10 Dec 2020...

Here's a tvf

drop function if exists dbo.fn_test_scnd_thurs;
go
create function dbo.fn_test_scnd_thurs(
  @test_date          date)
returns table
as return 
select dateadd(day, 9, calc_dt.dt)...

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