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,...

To flip 0/1 and vice versa:

UPDATE dbo.table_name SET column = ABS(column - 1)

and how will the OP create a non clustered...

• This reply was modified 3 years, 4 months ago by ScottPletcher.

Create a unique clustered index on ( TNR_DATE, GSN_ID ).  You can still have a nonclustered PK on GSN_ID alone.

If you prefer, just point the old name to the new table; you can, and definitely should, still continue to clean up the old name after that, but it should...