Database

Declare @Table Table
(
    row int,    EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select 1, 'Abc','07/03/2010',6000,0 UNION ALL
Select 2, 'xyz', '07/03/2011',1500,0 UNION ALL
Select 3, 'Rameez', '07/03/2011',2000,0 UNION All
Select 4, 'Abhijit', '07/03/2011',1000,0; --UNION ALL


 
select xx.*
,(select sum(Amount) from @Table xy where xy.row <=xx.row )  RunningTotal 
from  @Table xx 

Declare @Table Table
(
    row int identity(1,1),    EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
(
EmployeeName
,joiningDate
,Amount
,Balance
)
Select   'Abc','07/03/2010',6000,0 UNION ALL
Select   'xyz', '07/03/2011',1500,0 UNION ALL
Select   'Rameez', '07/03/2011',2000,0 UNION All
Select   'Abhijit', '07/03/2011',1000,0; --UNION ALL


 
select xx.*
,(select sum(Amount) from @Table xy where xy.row <=xx.row )  RunningTotal 
from  @Table xx 

Declare @Table Table
(
       EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select 'Abc','07/03/2010',6000,0 UNION ALL
Select 'xyz', '07/03/2011',1500,0 UNION ALL
Select 'Rameez', '07/03/2011',2000,0 UNION All
Select 'Abhijit', '07/03/2011',1000,0;  


with CTE01 as 
(select *
,ROW_NUMBER() over (order by joiningdate) row 
 from @Table )

select xx.*
,(select sum(Amount) from CTE01 xy where xy.row <= xx.row )  RunningTotal 
from  CTE01 xx 

Instructor Table: InstructorID, FirstName, LastName,...<br />
Course Table: CourseID, Subject, DateStart, DateEnd,.... <br />
InstructorCourse Table: InstructorID, CourseID  //This is a new table with just two fields

AnnieMacD wrote:

which only contains the two primary keys

AnnieMacD wrote:

But the Primary Key can be a composite.

smcnulty2000 wrote:

Yes, and we all know that.

CREATE TABLE [dbo].[CourseInstructor](
	[CourseID] [int] NOT NULL,
	[InstructorID] [int] NOT NULL,
 CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED 
(
	[CourseID] ASC,
	[InstructorID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CourseInstructor]  WITH CHECK ADD  CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO

ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Course]
GO

ALTER TABLE [dbo].[CourseInstructor]  WITH CHECK ADD  CONSTRAINT [FK_CourseInstructor_Instructor] FOREIGN KEY([InstructorID])
REFERENCES [dbo].[Instructor] ([InstructorID])
GO

ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Instructor]
GO

nbgangsta wrote:

My SP uses a temp table since it uses nestes SP's.

nbgangsta wrote:

the moment I close it the temp table gets dropped.

ALTER PROCEDURE test
...

-- Create a temp table to house the run info of the procs --
CREATE TABLE #Results(msg VARCHAR(200), LogTime SMALLDATETIME DEFAULT GETDATE())


-- Start time --
INSERT INTO #Results ("Start first child proc")
-- Execute the stored proc
EXEC sp_Test_First_Child_Proc
-- Completion Time --
INSERT INTO #Results ("Completed first child proc")
-- Start time --
INSERT INTO #Results ("Start second child proc")
-- Execute the stored proc
EXEC sp_Test_Second_Child_Proc
-- Completion Time --
INSERT INTO #Results ("Completed second child proc")

-- Ect.
...

-- Select the results from the temp table, this will return the result back to the SSIS package --
-- This is the important step, if you dont as a last step select the results out for return then SSIS will never the them and when the proceedure completes the #Results table will be dropped automaticly --
SELECT msg, LogTime FROM #Results