Man!  You are so right!  This is going to be totally liberating for me!

To commemorate my new found joyous method for doing my job without being a bottleneck,...

To add to what Grant has stated, I agree that "new" voices are a bonus on any forum but I have seen some "new" people come in with...

My apologies.  I wanted to Quote Jeff's answer, but accidentally hit the REPORT link instead.  Are Admins/Mods able to please undo my error

This was the post (copied from my mail)

To...

Declare @DBID Int Set @DBID = DB_ID ()
Checkpoint
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FLUSHPROCINDB (@DBID) WITH NO_INFOMSGS

That will clear execution plans and data from memory, which would...

NOLOCK and READUNCOMMITTED only affect reads.  This proc is doing inserts and updates, so it MAY have an impact if all other procs use NOLOCK/READ UNCOMMITTED.

My gut says that changing...

You are formatting your input string with style 101, and then asking SQL to interpret it as style 100.

You need to match your style to your input string

            
January 14, 2020 at 5:20 pm
            
#3715322

            
            
                                                
        

Take a look at the SQL CONVERT function for formatting the dates for display.

But PLEASE PLEASE PLEASE do not save the dates as varchar.  You will be opening yourself...

• This reply was modified 5 years, 8 months ago by DesNorton.

I like the simplicity of finding the February 29 dates, then subtracting that DesNorton posted.  But it didn't calculate correctly when I used the start date of 01/01/2019 end...

This can be made more dynamic with a function that gets the FEB29 days

CREATE FUNCTION dbo.GetLeapYearDates(@Date1 AS date, @Date2 AS date)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
  WITH
 ...

If something isn't a set, it often helps to turn it into a set.  Here's an example:

DECLARE @val1 nvarchar(40) = 'Hello';
DECLARE @val2 nvarchar(40) = 'Treacle';
DECLARE @val3...

Here is another option

DECLARE @val1 nvarchar(40) = 'Hello';
DECLARE @val2 nvarchar(40) = 'Treacle';
DECLARE @val3 nvarchar(40) = NULL;
DECLARE @val4 nvarchar(40) = 'Rhubarb';

DECLARE @nth int = 2;

WITH cteNonNullData AS (
...

This sounds very much like a homework or interview question.

I would suggest researching the TOP key word

Your problem is the "," before the "ORDER BY" in your "OVER" clauses

WITH T1 (ID1, ID2)
AS
(
SELECT 1,1 UNION ALL
SELECT 1,1 UNION ALL
SELECT 1,1 UNION ALL
SELECT 1,2
)
SELECT *,
RANK()...

You could also do it without the CTE

SELECT Q.Quater as 'Quarter', Q.Year as 'Year'
FROM Quaters as Q
WHERE Q.Year   = DATEPART(YEAR, GETDATE())
  AND Q.Quater =...

Depending on your indexes, this may help

; WITH CTE AS
(
SELECT DATEPART(QUARTER, GETDATE()) as 'Quarter', DATEPART(YEAR, GETDATE()) as 'Year'
)
SELECT CTE.Quarter, CTE.Year
FROM CTE
INNER JOIN Quaters as Q
  ...