1) You have to remove THEN from the IF statement

2) To assign a value to a variable you have to use SET statement

So, replace this line

IF @NewFund_Id = ''...

SELECT date,

SUM(CASE WHEN typesold = 1 THEN 1 ELSE 0 END) AS count_1,

SUM(CASE WHEN typesold = 2 THEN 1 ELSE 0 END) AS count_2,

SUM(CASE WHEN typesold...

As Jeff said you are right, the third query is sargable.

The second query shows exactly how SQL Server rewrites your initail query and it's not SARGable because it always converts...

For the last index you can use ROW_NUMBER function again with a small change in the OVER clause:

,row_number() over (

partition by AD.UnitNumber ,AD.AccountNumber order by AD.VisitID) as [Index]

,row_number() over...

Yep, as Sean said I assumed that you use SQL 2012 and windows offset functions are a good choice in situations where you want to refer to some other row...

You could create a derived column indicating the year and quarter in which a date belongs to. Something like this:

CAST(YEAR(DateColumn) AS NVARCHAR(4))

+ 'Q' + DATENAME(Quarter, DateColumn) AS YearQuarter

and then...

Here is a piece of code which calculates if the flag IsDateDiffLess14_Calculation should be set or not.

SELECT RowID,

CASE WHEN DATEDIFF(DAY, Date2, LEAD(Date1) OVER(PARTITION BY IDNo ORDER BY RowId)) <...

If you expect only one row as result then you should create an index on the column participating in the WHERE clause in order to improve performance.

Indexes in a...

If the column AddressParseStatus comes for the first time in a query as a part of filter SQL Server automatically creates statistics for the column (if AUTO_CREATE_STATISTICS is ON, which...

In the first execution plan on the Operator Index Seek QueryId_nc you can see that the Estimated Number of Rows is about 4 and Actual Number of Rows is...