×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Insert Missing GL Periods From One Table To Another

Insert Missing GL Periods From One Table To Another

Insert Missing GL Periods From One Table To Another

(OP)
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

RE: Insert Missing GL Periods From One Table To Another

(OP)
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?

CODE

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

RE: Insert Missing GL Periods From One Table To Another

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close