I am hoping you have some unique key that you use to clearly differentiate some of the records. To make things a little easier to read I added a RowId...

Instead of trying to make the values safe during the calculation, test to see if they will work before doing the calculation. If not, simply return a null or some...

i want to check if both rows not exists then do the if.

i need to check for 2 rows in that column if they not exists do something.

the rows will...

Use an ActiveX Script Task to check the DateLastModified of the file. If it meets your criteria use the Send Mail Task to send your message.

Couple of links to...

No, it is not required that the Primary Key is the clustered index. Proof of concept code follows.

Create Table dbo.KeyTest

(

TestId Int Identity(1, 1) Not Null,

TestCode NChar(3)...

Sure. Just change the parameter type.

Declare @StartDate Date;

....

-- Build the CTEs to hold your unpivoted data.

With WeeklyReport (IncidentType, Incidents)

As

(

...

Based off of this I am guessing that you will have standard weeks and that the sample data overlapping was...

I'm going to try and address things in smaller chunks. To cover everything at once can be a bit lengthy and confusing.

Sorry, I'll try and define what I mean...

Sounds like either a cross join or maybe a full join. The outer join...

Couple of questions.

1. How do you define "Reporting Week"? Is each record considered a reporting week? I didn't see any other mechanism for keeping track of your weeks otherwise....

This is not correct. For this process ordering does not matter....

You're almost there. Just missing the From clause to join the tables. When I have a query with multiple tables I tend to use aliases. Personal preference, it isn't required....

Ok. I think I finally understand the full picture.

Select C.ClassName, SubQuery.RegType, SubQuery.RegStatus,

COALESCE(SubQuery.NumofReg, 0) As [NumOfReg], C.EnrollLimit

From Classes As C

Left Join

(

Select Class, RegType, RegStatus,...

Not exactly sure what you mean. Based off of your create script and Lowell's selection script I was getting expected results.

ClassName,EnrollLimit,RegType,RegStatus,Class,NumofReg

Class1,10,STUDENT,<< Not Set >>,Class1,4

Class1,10,STUDENT,<< Not Set >>,Class1,4

Class1,10,TEACHER,<< Not Set...

I'm a bit surprised to hear that the last one didn't return any results for you. Sorry about that. Thought I did a quick sample test before posting code...

If...