CODEWITHSUNDEEP
sqlms-access
Jaskier
Jaskier

Reputation: 1095

Insert Missing days for the last month

I have written (got help from here) a query that finds and inserts all missing dates (Holidays and weekends) within my data.

However, after running it once and realizing I may have need for this to be ran once a week or so, I am attempting to make it only insert when the date is equal to one month previous (so, I'm only wanting this to insert starting from the previous month to current).

Here's the query I have now:

INSERT INTO Archive ( [Customer Name], IDNbr, City, Balance, aNbr, BalDate )

SELECT a1.[Customer Name], a1.IDNbr, a1.City, a1.[Balance], a1.aNbr, a1.BalDate+3

FROM Archive AS a1 

INNER JOIN Archive AS a2 ON (a1.IDNbr=a2.IDNbr) AND (a1.aNbr=a2.aNbr) AND (a1.BalDate+4=a2.BalDate)

WHERE NOT EXISTS    
(
      SELECT * 
      FROM Archive a3      

   WHERE a3.IDNbr = a1.IDNbr   AND a3.BalDate = a1.BalDate + 3) AND (DatePart("w", a1.BalDate) = 6 

) AND (MONTH(a1.BalDate) >= MONTH(a1.BalDate) - 1)

I'm currently not getting any errors. However this runs for 9+ hours before completing without any work done, it seems.

QUESTION:

How can I change my query to only look back one month (or 4 weeks)?

As what I currently have looks back to the complete start of my table (which goes back every day for a couple years).

I have looked into DateAdd() and DateDiff() although I'm unsure of how to implement them. My date format is: mmddyyyy if that is of any use.

Upvotes: 1

Views: 62

Answers (1)

Andre
Andre

Reputation: 27634

WHERE (MONTH(a1.BalDate) >= MONTH(a1.BalDate) - 1)

This makes no sense - it will always be true.

You need to compare with a constant date. Date() returns the current date, DateAdd() is used to subtract one month.

WHERE (a1.BalDate >= DateAdd("m", -1, Date()))

Addendum:

If it still runs slow, adding an index on the column BalDate should help. By comparing the column value (and not a calculation from the value) with a fixed expression, Access can make use of the index.

Upvotes: 1

Related Questions