Updating one table from another without a one to one relationship.

Question

I am trying to populate a date table which contains every date for the next 35 years with information regarding each day.

My ERP system has an accounting years table (GLRULE) which specifies the accounting periods for each specific year since many companies do not work on Calendar Months.

The GLRule table contains one record for each period and looks like this:

fcbasis  fcname    fcstatus  fdend              fdstart            flisadjust  flisaudit  fnnumber  freval  identity_column
A        FY 2000   C         1/28/2000 0:00:00  1/1/2000 0:00:00   FALSE       FALSE      1         FALSE   37
A        FY 2000   C         2/25/2000 0:00:00  1/29/2000 0:00:00  FALSE       FALSE      2         FALSE   38
A        FY 2000   C         3/31/2000 0:00:00  2/26/2000 0:00:00  FALSE       FALSE      3         FALSE   39
A        FY 2000   C         4/28/2000 0:00:00  4/1/2000 0:00:00   FALSE       FALSE      4         FALSE   40
A        FY 2000   C         5/26/2000 0:00:00  4/29/2000 0:00:00  FALSE       FALSE      5         FALSE   41
A        FY 2000   C         6/30/2000 0:00:00  5/27/2000 0:00:00  FALSE       FALSE      6         FALSE   42

Anyway, I can update my date table one field at a time with a query similar to this:

UPDATE redfridaydates.dbo.testdates 
   SET [FISCAL_PERIOD]   = 
                (SELECT fnnumber
                  FROM m2mdata01..glrule GLR
                  where DATE >= GLR.FDSTART and DATE <= GLR.FDEND)

Is there a better way to update multiple fields at a time? I'm not sure how I can do so since I do not have a join.

Answer 1

you can always set more fields in your update query:

UPDATE TableName Set Field1 = (Select fnnumber From ....), 
Field2 = (some other query or value), etc.

Answer 2

It sounds like the table you're trying to update has one record for every date. The answer assumes that. If you can ensure that your GL datasource has no overlapping dates:

UPDATE T
SET T.[FISCAL_PERIOD] = GLR.fnnumber,
     T.Foo = GLR.Bar,
     T.Bat = GLR.Baz  --etc.

FROM redfridaydates.dbo.testdates  AS T
INNER JOIN m2mdata01..glrule AS GLR
ON T.[Date] BETWEEN GLR.FDSTART  AND GLR.FDEND

If you're interested, here's a proof of concept on some test data.

Answer 3

Maybe looping through your date table would be a solution?

declare @myDate SmallDateTime
set @myDate = (Select MIN(DATE) FROM dbo.testdates)

declare @myFiscal int

WHILE @myDate is not null
BEGIN

--determine the fiscal period
--output a zero for missing fiscal periods
SET @myFiscal = isnull(
   (
   SELECT fnnumber 
   FROM m2mdata01..glrule GLR 
   WHERE @myDate >= GLR.FDSTART AND @myDate <= GLR.FDEND
   ),0)

--update the date table
UPDATE redfridaydates.dbo.testdates 
SET fiscal_period = @fiscal
WHERE date = @myDate

--get the next date
--will be null once the loop reaches the end of the table
SET @myDate = (Select MIN(DATE) FROM dbo.testdates WHERE DATE > @myDAte)

END