I don't sure if this is right, how to test

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

I don't sure if this is right, how to test

Author

Topic

jim65
Starting Member

35 Posts

Posted - 2006-02-13 : 10:24:03

there are two table in the database. one is adReportingRequirement, another one is adrequirementHistory.
there are some field which are Startdate, frequency(monthly, Quarterly, Annually, Annually) in adReportingRequirement.
what's the below T-SQL doing? get start date and frequency from the adReportingRequirement table.
if frequency is monthly. T-SQL will insert a record every month from start date into adrequirementHistory table
if frequency is Quarterly. T-SQL will insert a record every Quarter from start date into adrequirementHistory.
if frequency is Semi-Annually. T-SQL will insert a record every Semi-Annual from start date into adrequirementHistory.
if frequency is Annually. T-SQL will insert a record every Annually from start date into adrequirementHistory.

the dbo.asp_Insert_History_auto stored procedure will be called every day from DTS.
I am not sure this is right, and I don't how to test, thanks for your help

CREATE PROCEDURE dbo.asp_Insert_History_auto as
set nocount on

Declare @F_ID int
DECLARE @CNT INT
DECLARE @ID int

Declare @Startdate as smalldatetime
DECLARE @ReturnString AS smalldatetime
Declare @counter as int

select @counter = count(RptReqID) from adReportingRequirement
--print @counter

SET @CNT = 1

while @CNT <@counter
Begin

SELECT @F_ID= [FrequencyID],@ID= [RptReqID], @Startdate =[StartDate]
FROM [FACT].[dbo].[adReportingRequirement]
where RptReqID = @CNT

SET @CNT = @CNT + 1

if (@F_ID=1 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 30, @Startdate) ), 112))))
or (@F_ID=1 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 60, @Startdate) ), 112))))
or (@F_ID=1 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 90, @Startdate) ), 112))))
or (@F_ID=1 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 120, @Startdate) ), 112))))
or (@F_ID=1 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 150, @Startdate) ), 112))))
or (@F_ID=1 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 180, @Startdate) ), 112))))
or (@F_ID=1 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 210, @Startdate) ), 112))))
or (@F_ID=1 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 240, @Startdate) ), 112))))
or (@F_ID=1 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 270, @Startdate) ), 112))))
or (@F_ID=1 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 300, @Startdate) ), 112))))
or (@F_ID=1 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 330, @Startdate) ), 112))))
or (@F_ID=1 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 360, @Startdate) ), 112))))
--set @ReturnString = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 30, @Startdate) ), 112)))
exec dbo.asp_InsertReportingRequirement_History_by_frequency @ID
--print 'Monthly'

if (@F_ID=2 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 90, @Startdate) ), 112))))
or (@F_ID=2 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 180, @Startdate) ), 112))))
or (@F_ID=2 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 270, @Startdate) ), 112))))
or (@F_ID=2 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 360, @Startdate) ), 112))))
exec dbo.asp_InsertReportingRequirement_History_by_frequency @ID
--print 'Quarterly'

if @F_ID=3 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 365, @Startdate) ), 112)))
exec dbo.asp_InsertReportingRequirement_History_by_frequency @ID

if (@F_ID=5 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 180, @Startdate) ), 112))))
or(@F_ID=5 and getdate() = (select convert (datetime, convert (Char(8), ( DATEADD (dd , 1360, @Startdate) ), 112))))
exec dbo.asp_InsertReportingRequirement_History_by_frequency @ID
--print 'Semi-Annually'

end

--select cast(getdate() as Char(20))
GO

another stored procedure

CREATE PROCEDURE dbo.asp_InsertReportingRequirement_History_by_frequency (@RptReqID int) AS

INSERT INTO adReportingRequirementHistory
(RptReqID)
VALUES (@RptReqID)

declare @NewRecord as int
Set @NewRecord = @@Identity

SELECT RptReqHistoryID, RptReqID
FROM adReportingRequirementHistory
where RptReqHistoryID = @NewRecord
GO

jim65
Starting Member

35 Posts

Posted - 2006-02-13 : 16:18:56

stored procedures shall be

CREATE PROCEDURE dbo.asp_Insert_History_auto as
set nocount on

Declare @F_ID int
DECLARE @CNT INT
DECLARE @ID int

Declare @Startdate as smalldatetime
DECLARE @ReturnString AS smalldatetime
Declare @counter as int

select @counter = count(RptReqID) from adReportingRequirement
--print @counter

SET @CNT = 1

while @CNT <@counter
Begin

SELECT @F_ID= [FrequencyID],@ID= [RptReqID], @Startdate =[StartDate]
FROM [FACT].[dbo].[adReportingRequirement]
where RptReqID = @CNT

SET @CNT = @CNT + 1

if ((@F_ID=1 and getdate() = convert (datetime, convert (Char(8), ( DATEADD (dd , 30, @Startdate) ), 112)))
exec dbo.asp_InsertReportingRequirement_History_by_frequency @ID
--print 'Monthly'

if ((@F_ID=2 and getdate() = convert (datetime, convert (Char(8), ( DATEADD (dd , 90, @Startdate) ), 112)))
exec dbo.asp_InsertReportingRequirement_History_by_frequency @ID
--print 'Quarterly'

if @F_ID=3 and getdate() = convert (datetime, convert (Char(8), ( DATEADD (dd , 365, @Startdate) ), 112))
exec dbo.asp_InsertReportingRequirement_History_by_frequency @ID
--print 'Annually'

if ((@F_ID=5 and getdate() = convert (datetime, convert (Char(8), ( DATEADD (dd , 180, @Startdate) ), 112)))
or(@F_ID=5 and getdate() = convert (datetime, convert (Char(8), ( DATEADD (dd , 1360, @Startdate) ), 112))))
exec dbo.asp_InsertReportingRequirement_History_by_frequency @ID
--print 'Semi-Annually'

end

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-13 : 16:54:21

Holy crap, that is some of the worst code I've ever seen. You will be much better off to step back and write down what it SHOULD be doing (in pseudo-code) and give it a quick re-write. We can help with that.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-14 : 01:49:26

You need to know how to query on Dates
http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources