ARCCode/Account Exclusions: Add an "Import data from prior reporting period" button.
Closed this issue · 5 comments
Please add an "Import data from prior reporting period" button to the maintenance page that copies all data from the prior reporting period to the current
reporting period. Only copy those records that are not already present in the data for the current reporting period's data.
Which records should I NOT copy? Which fields do I compare? Chart and Account only?
Do I copy the entire record? Including is204, comments, project number and award number?
I hear you. Yes, you copy the entire record less the year, which you replace with the current year.
Here’s the SQL I would use:
Declare @FiscalYear int = 2015
SELECT @FiscalYear Year, t1.Chart, t1.Account, t1.AnnualReportCode, t1.Is204, t1.AwardNumber, t1.ProjectNumber
FROM ARCCodeAccountExclusions t1
INNER JOIN (
SELECT Chart, Account, AnnualReportCode
FROM ARCCodeAccountExclusions
WHERE Year = @FiscalYear-1
EXCEPT
SELECT Chart, Account, AnnualReportCode
FROM ARCCodeAccountExclusions
WHERE Year = @FiscalYear
) t2 ON t1.Chart = t2.chart AND t1.Account = t2.Account AND t1.AnnualReportCode = t2.AnnualReportCode
WHERE Year = @FiscalYear
From: John Knoll [mailto:notifications@github.com]
Sent: Thursday, August 11, 2016 11:10 AM
To: ucdavis/AD419DataHelper
Cc: Kenneth J Taylor; Author
Subject: Re: [ucdavis/AD419DataHelper] ARCCode/Account Exclusions: Add an "Import data from prior reporting period" button. (#45)
Do I copy the entire record? Including is204, comments, project number and award number?
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHubhttps://github.com//issues/45#issuecomment-239243167, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AAjl4UfuMWQIxSWtdsWS_dftn-0ttb6tks5qe2V0gaJpZM4JiPgq.
Sorry, the bottom-most fiscal year should have been @FiscalYear – 1:
Declare @FiscalYear int = 2015
SELECT @FiscalYear Year, t1.Chart, t1.Account, t1.AnnualReportCode, t1.Is204, t1.AwardNumber, t1.ProjectNumber
FROM ARCCodeAccountExclusions t1
INNER JOIN (
SELECT Chart, Account, AnnualReportCode
FROM ARCCodeAccountExclusions
WHERE Year = @FiscalYear-1
EXCEPT
SELECT Chart, Account, AnnualReportCode
FROM ARCCodeAccountExclusions
WHERE Year = @FiscalYear
) t2 ON t1.Chart = t2.chart AND t1.Account = t2.Account AND t1.AnnualReportCode = t2.AnnualReportCode
WHERE Year = @FiscalYear -1
From: John Knoll [mailto:notifications@github.com]
Sent: Thursday, August 11, 2016 11:10 AM
To: ucdavis/AD419DataHelper
Cc: Kenneth J Taylor; Author
Subject: Re: [ucdavis/AD419DataHelper] ARCCode/Account Exclusions: Add an "Import data from prior reporting period" button. (#45)
Do I copy the entire record? Including is204, comments, project number and award number?
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHubhttps://github.com//issues/45#issuecomment-239243167, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AAjl4UfuMWQIxSWtdsWS_dftn-0ttb6tks5qe2V0gaJpZM4JiPgq.
Obviously you’d need to add the INSERT INTO ARCCodeAccountExclusions statement just above the SELECT statements I provided, i.e.:
Declare @FiscalYear int = 2015
INSERT INTO ARCCodeAccountExclusions
SELECT @FiscalYear Year, t1.Chart, t1.Account, t1.AnnualReportCode, t1.Is204, t1.AwardNumber, t1.ProjectNumber
FROM ARCCodeAccountExclusions t1
INNER JOIN (
SELECT Chart, Account, AnnualReportCode
FROM ARCCodeAccountExclusions
WHERE Year = @FiscalYear-1
EXCEPT
SELECT Chart, Account, AnnualReportCode
FROM ARCCodeAccountExclusions
WHERE Year = @FiscalYear
) t2 ON t1.Chart = t2.chart AND t1.Account = t2.Account AND t1.AnnualReportCode = t2.AnnualReportCode
WHERE Year = @FiscalYear -1
From: John Knoll [mailto:notifications@github.com]
Sent: Thursday, August 11, 2016 11:10 AM
To: ucdavis/AD419DataHelper
Cc: Kenneth J Taylor; Author
Subject: Re: [ucdavis/AD419DataHelper] ARCCode/Account Exclusions: Add an "Import data from prior reporting period" button. (#45)
Do I copy the entire record? Including is204, comments, project number and award number?
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHubhttps://github.com//issues/45#issuecomment-239243167, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AAjl4UfuMWQIxSWtdsWS_dftn-0ttb6tks5qe2V0gaJpZM4JiPgq.