TR_BATCH_PERIOD

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.

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

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

There is also a shared report on TASK.
Solution 237

Description:

This report will add 12 periods for a specified fiscal year into the table
tr_batch_period.