Here's a solution that'll return the Fiscal year, month, and week based on a given year and the number of years to consider:

DECLARE @FY char(4)='2014',

@NumberOfYears TINYINT = 10

;

WITH cte AS...

Looks like the usage of OUTER APPLY and the CASE expression will help here.

Depends on the query itself. Not enough information from my point of view...

Based on the relational theory of RDBMS there's no "previous" row.

Whether a row is considered as "previous" or "next" depends on the order of rows.

What you could do is to...

It depends on the SQL version you're using...

If you use SS2K8 (as indicated by the forum you posted in), LAG is not available...

Even though it looks like an issue at the app layer, here's a link to a question over at stackoverflow including a query that'll list all user permissions including those...

I would have guessed that the user is in another role that deny the execution of that sproc...

Did you check the effective permission for that sproc?

EXECUTE AS USER = 'User';

SELECT...

Based on the logic provided so far, send the mail if @Result <> ''

You could use the following parameter in your email:

@query =

'SELECT ''Process Name: '' + CONVERT(VARCHAR(25),Process_Name...

"So the month ends on the first Sunday of the next month." does not match your sample data:

Month 5 (August) doesn't end at the first Sunday of the next month...

The only "useful" way to assign more than one row to a variable would be an xml data type (or varchar).

Obviously, we're still missing the whole picture. In your first...

Your description "we start a new month on the FIRST Sunday of the month." Does not match your sample "as it is the day after the first Sunday of the...

Based on Eirikurs solution here's a slightly modified version.

Differences: The code below does only one SELECT against the Process table and the result of the SELECT ist stored in a...

Hi Nancy,

obviously you presented just a piece of the puzzle.

It seems like somewhere in your code you're trying to assign a value to a variable or to use it as...

CHARINDEX returns the first position of a given character, counted from the left side.

The sample data that return data have a similar pattern: the length of the last name is...

What SQL Server version do you use?

Just wrap it in a CTE:

; WITH cte AS

(

SELECT DISTINCT EDI_PARTNER_CD

FROM MG_EDI_PARTNER

UNION ALL

SELECT TOP 1 '(NULL)', EDI_PARTNER_CD

FROM ...