Database

ALTER PROCEDURE [EventPortion].[24Start]
 AS  

IF object_id('[IntegratedTest1].[EventPortion].[244Q1]') is not null 
DROP TABLE [IntegratedTest1].[EventPortion].[244Q1]

IF object_id('[IntegratedTest1].[EventPortion].[244Q2]') is not null 
DROP TABLE [IntegratedTest1].[EventPortion].[244Q2]

IF object_id('[IntegratedTest1].[EventPortion].[244Q3]') is not null 
DROP TABLE [IntegratedTest1].[EventPortion].[244Q3]


SELECT * Into [IntegratedTest1].[EventPortion].[244Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q1

While (object_id('[IntegratedTest1].[EventPortion].[244Q1]') is null)
Begin
WAITFOR DELAY '00:00:00.001';
End

Begin
WAITFOR DELAY '00:00:01.000';
End

SELECT * Into [IntegratedTest1].[EventPortion].[244Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q2


While (object_id('[IntegratedTest1].[EventPortion].[244Q2]') is null)
Begin
WAITFOR DELAY '00:00:00.001';
End
SELECT * Into [IntegratedTest1].[EventPortion].[244Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q3

ALTER PROCEDURE [EventPortion].[24Start]
 AS
DROP TABLE [IntegratedTest1].[EventPortion].[244Q1]
DROP TABLE [IntegratedTest1].[EventPortion].[244Q2]
DROP TABLE [IntegratedTest1].[EventPortion].[244Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[244Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q1
SELECT * Into [IntegratedTest1].[EventPortion].[244Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q2
SELECT * Into [IntegratedTest1].[EventPortion].[244Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q3
DROP TABLE [IntegratedTest1].[EventPortion].[245Q1]
DROP TABLE [IntegratedTest1].[EventPortion].[245Q2]
DROP TABLE [IntegratedTest1].[EventPortion].[245Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[245Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q1
SELECT * Into [IntegratedTest1].[EventPortion].[245Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q2
SELECT * Into [IntegratedTest1].[EventPortion].[245Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q3

private void ParseStartProcedure(string spText)
        {            
            spText = spText.Replace("\r", " ");            
            spText = spText.Replace("\t", " ");           
            spText = spText.Trim();

            // Take everything after the first "AS"
            string[] queryPieces = Regex.Split(spText.ToLower(), " as ");
            string[] queryLines = Regex.Split(queryPieces[1], "\n"); //split on the new line character

            for (int i = 0; i < queryLines.Length; i++)
            {
                if (queryLines[i] != "")
                {
                    string query = queryLines[i];
                    SqlCommand cmd = new SqlCommand(query, m_dbSettings.sqlMetadataDbConn);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        //TODO: Add more robust error handling to handle errors other than "Failed to drop, etc.
                        string message = ex.Message;
                    }
                }
            }            
        }

Item(Id_Item,ItemNo,ItemName)
 Lot(Id_Lot,Item_Id,LotNo)
 Process(Id_Process, ProcessNo, ProcessName)
 Lot_Process(Id,Lot_Id,Process_Id)
 Material(Id,Process_Id,MaterialName, Quantity)

CREATE TABLE ps_data.test (
  Test_ID int(11) NOT NULL AUTO_INCREMENT,
  Idx int(11) NOT NULL,
  Grp int(11) DEFAULT NULL,
  PRIMARY KEY (Test_ID)
)
ENGINE = INNODB
AUTO_INCREMENT = 10
AVG_ROW_LENGTH = 1820
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(1, 1, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(2, 2, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(3, 3, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(4, 4, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(5, 5, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(6, 6, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(7, 7, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(8, 8, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(9, 9, 2);

UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test) tbl WHERE tbl.Grp=1)
WHERE Test_ID=1 

UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test WHERE Grp=1) tbl)
WHERE Test_ID=1 

UPDATE test t, (SELECT * FROM test WHERE Grp=1) tbl
SET t.Idx= (SELECT COALESCE(MAX(tbl.Idx), 0)+1)
WHERE t.Test_ID=1 

Declare	@FundCodes VarChar(Max)='Bateleur|Tower Fund',--Tower Fund
		@StartDate DateTime='2016/04/20',
		@EndDate DateTime='2016/04/29',
		@Zero integer=1,
		@LongShortAll integer = 0, --0 for all, 1 for long value, 2 for short value
		@PhysEff integer = 0 --0 for physical, 1 for Effective


		--check the fulldate 
		select fulldate into #TableDates 
		from AssetData.dbo.Calendar cal where cal.FullDate between @StartDate and @EndDate
		and IsWeekDay = 1 and IsHoliday = 0
	
		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.
		SET NOCOUNT ON;
	     
		declare @NextDate DateTime
		set @NextDate = @StartDate
		declare @TempValues table(FundCode varchar(200),EffectiveDate DateTime, Value Float)	
	
		declare @fundcodestable table (ID int, fundcode varchar(300))
		declare @Val float

		--Date Counter
		declare @DateCounter int = 0
		select @DateCounter = count(Fulldate) from #TableDates
		declare @Date date
				
		while @DateCounter <> 0 
	
		begin

		set @Date = (select top 1 fulldate from #TableDates order by 1)
	
		--fundcode
		insert into @fundcodestable
		select	pn, s
		from	StagedFundReportingData..FnxSplit('|', @fundcodes)

		--fundcode Counter
		Declare @counterFundId int = 0
		select @counterFundId = count(fundcode) from @fundcodestable
		declare @fundC varchar(300) 
		
		while @counterFundId <>0 and @NextDate<=@EndDate
		begin
			set @fundC = (select top 1 fundcode from @fundcodestable)
				Set @Val = 0
				if @Zero = 0
				   begin
						if @LongShortAll = 0 begin
							exec GetFundValue @fundC, @NextDate, @Val output
						end else begin
							if @LongShortAll = 1 begin
								if @PhysEff = 0 begin
									exec GetFundValueLongOnly @fundC, @NextDate, @Val output
								end else begin
									exec GetFundUnderlyingEffectiveValueLongOnly @fundC, @NextDate, @Val output
								end
							end else begin
								if @PhysEff = 0 begin
									exec GetFundValueShortOnly @fundC, @NextDate, @Val output
								end else begin
									exec GetFundUnderlyingEffectiveValueShortOnly @fundC, @NextDate, @Val output
								end
							end
						end
					end
					else
					begin
						If @LongShortAll = 0 begin
							exec GetFundValue_ZeroFutures @fundC, @NextDate, @Val output
						end else begin
							if @LongShortAll = 1 begin
								if @PhysEff = 0 begin
									exec GetFundValueLongOnly_ZeroFutures @fundC, @NextDate, @Val output
								end else begin
									exec GetFundUnderlyingEffectiveValueLongOnly_ZeroFutures @fundC, @NextDate, @Val output
								end
							end else begin
								if @PhysEff = 0 begin
									exec GetFundValueShortOnly_ZeroFutures @fundC, @NextDate, @Val output
								end else begin
									exec GetFundUnderlyingEffectiveValueShortOnly_ZeroFutures @fundC, @NextDate, @Val output
								end
							end
						end
					end
					Set @Val = isnull(@Val,0)

					if @Val <> 0
					begin
						insert into @TempValues (FundCode,EffectiveDate, Value) Values (@fundC,@NextDate, @Val)
					end

					if datepart(dw,@NextDate) = 7
					begin
						Set @NextDate = DATEADD(Day,2,@NextDate)
					end
					else
					begin
						if datepart(dw,@NextDate) = 6
						begin
							Set @NextDate = DATEADD(Day,3,@NextDate)
						end
						else
						begin
							Set @NextDate = DATEADD(Day,1,@NextDate)
						end 
					end
				  end
			    delete #TableDates where FullDate = @NextDate
		      select @DateCounter = @DateCounter-1
			 --delete @fundcodestable where fundcode = @fundC
		  --     set @counterFundId = @counterFundId - 1  	
		 end
			

		Select * from @TempValues order by FundCode
		drop table #TableDates