Insert Missing GL Periods From One Table To Another

[Auguy]

I have two tables. GL period table contains entries like 2018-01, 2018-02, etc. GL Account table has a column GlPeriod. What I am trying to do in one SQl statement is to insert any missing GL Periods into the GL Account table. i.e. if account 1234 only has records thru 2012-11 then I want to add 2012-12 thru 2018-02 for account 1234. But I want to do this for all accounts and I don't want to insert any duplicate GL periods for any one account. I seems as though a left outer join should do the trick to get he required records, but I can't seem to get it to work. Can somebody point me in the right direction?

Auguy
Sylvania/Toledo Ohio

 

[Auguy]

OK, made some progress. Slight revision. There are actually three tables. The GL period table and two GL Account tables, a Master and a Detail. The master has the definition of the account and the detail has the postings with a record for each GL period. This code gives me the missing records. Now just have to add it to an Insert Into procedure and make the GL periods parameters. Note, the GL period table has periods thru the year 2030. Any problems with this so far?

SELECT Table1.*
FROM
	(SELECT glaccountmaster.glmasterpk, glaccountmaster.accountnbr, glperiod.glperiod
		FROM glperiod
		CROSS JOIN glaccountmaster
		WHERE glperiod.glperiod >= '2017-02' AND glperiod.glperiod <= '2018-03') AS Table1

WHERE NOT EXISTS(SELECT glmasterfk, Glperiod FROM glaccountdetail
	WHERE Table1.glmasterpk = glaccountdetail.glmasterfk AND glaccountdetail.Glperiod = Table1.Glperiod)

ORDER BY Table1.glmasterpk, Table1.GLPeriod

Auguy
Sylvania/Toledo Ohio

 

[Auguy]

I think I have it. This seems to work. Will do some testing.

INSERT INTO GLaccountDetail
	(GLmasterFK
	,GLperiod)

	SELECT Table1.GLmasterPK, Table1.GLperiod
	FROM
		(SELECT GLM.GLmasterPK, GLP.GLperiod
			FROM GLperiod GLP
			CROSS JOIN GLaccountMaster GLM
			WHERE GLP.GLperiod >= '2017-02' AND GLP.GLperiod <= '2018-02') AS Table1

	WHERE NOT EXISTS (SELECT GLmasterFK, Glperiod FROM GLaccountDetail GLD
		WHERE Table1.GLmasterPK = GLD.GLmasterFK AND Table1.Glperiod = GLD.Glperiod)

ORDER BY Table1.GLmasterPK, Table1.GLPeriod

Auguy
Sylvania/Toledo Ohio