TR_BATCH_PERIOD

Matt Gonzales $organization over 16 years ago

I've been told that my TR_BATCH_PERIOD table doesn't have any entries for the current 2010 season and there should be. Any idea what this table is and what stored procedure (which I assume I've neglected to update) updates it?

Thanks, Tessiturians.

BONUS TRIVIA: John Wayne once won the dog Lassie from its owner in a poker game.

Parents

John Chabot $organization over 16 years ago

Hi Matt,

I asked Darrin, our Programmer/Analyst to write a script to populate this automatically. We've only used it for one batch period, but it seems to work well so far.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[lp_update_tr_batch_period]
    -- Add the parameters for the stored procedure here
    @year int = null
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

DECLARE @dtDate DATETIME

SET @dtDate = '1/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 1, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '2/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 2, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '3/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 3, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '4/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 4, 2, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '5/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 5, 2, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '6/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 6, 2, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '7/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 7, 3, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '8/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 8, 3, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '9/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 9, 3, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '10/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 10, 4, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '11/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 11, 4, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '12/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 12, 4, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

END
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel

Reply

John Chabot $organization over 16 years ago

Hi Matt,

I asked Darrin, our Programmer/Analyst to write a script to populate this automatically. We've only used it for one batch period, but it seems to work well so far.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[lp_update_tr_batch_period]
    -- Add the parameters for the stored procedure here
    @year int = null
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

DECLARE @dtDate DATETIME

SET @dtDate = '1/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 1, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '2/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 2, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '3/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 3, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '4/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 4, 2, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '5/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 5, 2, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '6/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 6, 2, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '7/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 7, 3, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '8/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 8, 3, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '9/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 9, 3, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '10/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 10, 4, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '11/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 11, 4, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

SET @dtDate = '12/1/' + CAST(@year as VARCHAR(4))
INSERT INTO tr_batch_period(fyear, period, quarter, start_dt, end_dt)
SELECT @year, 12, 4, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtDate), 0), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

END
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel

Children

No Data