USE [ciswarehouse]
GO
/****** Object:  StoredProcedure [dbo].[spLdd12]    Script Date: 09/15/2011 17:10:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spLdd12]
	AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

IF OBJECT_ID('MyTransformed','U') IS NOT NULL         
truncate table dbo.MyTransformed

Insert into ciswarehouse.dbo.MyTransformed 
(StudentRegister, Activity, Student, MarkDate, ShDate, Mark, Weekdate, Present, Absence, Authorised, Unauthorised)
SELECT dbase1.dbo.table1_studentregister.sr_id, dbase1.dbo.table1_activity.a_id, dbase1.dbo.table1_studentregister.sr_student , DATEADD(wk, n. N - 1,  dbase1.dbo.table1_activity.a_start), DATEADD(dd, datediff(dd, 0, dateadd(wk, n. N - 1, dbase1.dbo.table1_activity.a_start)), 0),'/' , DATEADD(ww, DATEDIFF(ww, 0, DATEADD(wk, n. N - 1, dbase1.dbo.table1_activity.a_start)), 0), '1','0','0','0'
FROM         dbase1.dbo.table1_activity INNER JOIN
                      dbase1.dbo.table1_studentregister ON dbase1.dbo.table1_activity.a_id = dbase1.dbo.table1_studentregister.sr_activity INNER JOIN
                      ciswarehouse.dbo.Tally AS n ON n. N BETWEEN 1 AND LEN(dbase1.dbo.table1_activity.a_weekpattern) AND  

SUBSTRING(dbase1.dbo.table1_activity.a_weekpattern,n.N,1)='1' AND SUBSTRING(dbase1.dbo.table1_studentregister.sr_weekpattern, n. N, 1) 
                      = '/'
WHERE     (dbase1.dbo.table1_activity.a_end > '01-Aug-2011')

Insert into ciswarehouse.dbo.MyTransformed 
(StudentRegister, Activity, Student, MarkDate, ShDate, Mark, Weekdate, Present, Absence, Authorised, Unauthorised)
SELECT  dbase1.dbo.table1_studentregister.sr_id , dbase1.dbo.table1_activity.a_id, dbase1.dbo.table1_studentregister.sr_student , DATEADD(wk, n. N - 1, 
                      dbase1.dbo.table1_activity.a_start), DATEADD(dd, datediff(dd, 0, dateadd(wk, n. N - 1, dbase1.dbo.table1_activity.a_start)), 0),'O' , DATEADD(ww, DATEDIFF(ww, 0, DATEADD(wk, n. N - 1, dbase1.dbo.table1_activity.a_start)), 0), '0','1','0','1'
FROM         dbase1.dbo.table1_activity INNER JOIN
                      dbase1.dbo.table1_studentregister ON dbase1.dbo.table1_activity.a_id = dbase1.dbo.table1_studentregister.sr_activity INNER JOIN
                      ciswarehouse.dbo.Tally AS n ON n. N BETWEEN 1 AND LEN(dbase1.dbo.table1_activity.a_weekpattern) AND  

SUBSTRING(dbase1.dbo.table1_activity.a_weekpattern,n.N,1)='1' AND SUBSTRING(dbase1.dbo.table1_studentregister.sr_weekpattern, n. N, 1) 
                      = 'O'
WHERE     (dbase1.dbo.table1_activity.a_end > '01-Aug-2011')

Insert into ciswarehouse.dbo.MyTransformed 
(StudentRegister, Activity, Student, MarkDate, ShDate, Mark, Weekdate, Present, Absence, Authorised, Unauthorised)
SELECT  dbase1.dbo.table1_studentregister.sr_id , dbase1.dbo.table1_activity.a_id, dbase1.dbo.table1_studentregister.sr_student , DATEADD(wk, n. N - 1, 
                      dbase1.dbo.table1_activity.a_start), DATEADD(dd, datediff(dd, 0, dateadd(wk, n. N - 1, dbase1.dbo.table1_activity.a_start)), 0),' ' , DATEADD(ww, DATEDIFF(ww, 0, DATEADD(wk, n. N - 1, dbase1.dbo.table1_activity.a_start)), 0), '0','0','0','0'
FROM         dbase1.dbo.table1_activity INNER JOIN
                      dbase1.dbo.table1_studentregister ON dbase1.dbo.table1_activity.a_id = dbase1.dbo.table1_studentregister.sr_activity INNER JOIN
                      ciswarehouse.dbo.Tally AS n ON n. N BETWEEN 1 AND LEN(dbase1.dbo.table1_activity.a_weekpattern) AND  

SUBSTRING(dbase1.dbo.table1_activity.a_weekpattern,n.N,1)='1' AND SUBSTRING(dbase1.dbo.table1_studentregister.sr_weekpattern, n. N, 1) 
                      = ' '
WHERE     (dbase1.dbo.table1_activity.a_end > '01-Aug-2011')

..a bunch more selects and insert intos with slightly different WHERE clauses

alter index all on MyTransformed rebuild 
end