Does that take care of the whole "season" of dates the OP identified

I need to create a query where  the first of March will be  Week 1...

No need to overly complicate this.  Just calc the starting Monday date, then determine week# displaced off that "base" date.

I moved the time adjustment into a CROSS APPLY only so...

Either method will work.

Personally I prefer using the TimeTaken column.  Triggers with transactions can be used to ensure that the times are kept in sync.  Of course you'd want a...

No.  For an explicit transaction, you need an explicit COMMIT or ROLLBACK.

 

SELECT 
    CoName,
    MAX(CASE WHEN Class = 'aaa' THEN 'Yes' ELSE '' END) AS aaa,
    MAX(CASE WHEN Class...

I need single row per invoicenumber

Yet you're showing two lines of output for the same invoice number??

Put comments inside the code, not before it.

frederico_fonseca stated why:

(1) it's a royal pain trying to make automated changes if comments precede the CREATE / ALTER

(2) it's odd to consider code...

It sounds like maybe you need to make this adjustment to the code?:

...

WHERE g.Year = @year and g.Month = @month AND g.FailureCount <> 0

...

Are you exec'ing the code from a different db with a lower compatibility?

This really looks like a compatibility issue.  Check the compatibility level of all dbs on that instance.

There are some that are a negative number

That seems logical, if it went from 1 to 0, the change would be -1.

display the Largest positive number first.

Could you explain this...

If I understand the problem correctly, I think all you need is a standard LAG.  You may need to adjust the PARTITIONing columns and/or the ORDER BY:

DECLARE...

If you can get rid of the DISTINCT, you can do as below.  If not, let me know and I will adjust code to work around the DISTINCT.

            
August 17, 2022 at 2:02 pm
            
#4077412

            
            
                                                
        

 

SELECT
    MIN(Emp_code) + CASE WHEN COUNT(*) > 1 THEN '_' + MAX(Emp_code) ELSE '' END AS Emp_code,
    MAX(Employee_Name) AS Employee_Name,
 ...

Put the conditions in a separate table.  Then use a JOIN to UPDATE the original table based on the matching condition, or the first matching condition if multiple conditions could...

I recommend using another db -- let's call it the "history_db" -- to hold the prior months' data.  As each month passes, you move its table to the history_db.  Also,...