Last 7 days vs last 7 days previous year

[maw230]

I'm not sure why I'm having such trouble here. Last 7 days this year is no problem, but now I need those same days for last year; the caveat being that they must be the same Days of the Week, so they are comparable.

If Yesterday = Wednesday of Week 38, then for Last Year I will need Wednesday of Week 38 minus 8 days.

I'm able to get Yesterday of Last Year fine:

SELECT DATE_ID
FROM CALDATE
WHERE (((DatePart("w",[DATE_ID]))=DatePart("w",Date()-1)) AND ((DatePart("ww",[DATE_ID]))=DatePart("ww",Date()-1)) AND ((Year([DATE_ID]))=Year(Date())-1))
GROUP BY DATE_ID;

Now I need that minus 8.

 

[MarkK]

This date comparison scheme doesn't make sense to me. In 2013 the first day of the year is Tuesday. In 2014 the first day of the year is Wednesday. So in 2014, how does your Week 1, Tuesday work? Is it 6 days into the year, or does it not exist? In either case it's not comparable to 'Week1 Tuesday 2013' in any meaningful way. As a result, it's going to be really hard to write a procedure to make this scheme make sense for all possible dates.

In addition, it doesn't make sense to me to have a table of dates so that you can link to a date. A date is a discrete measure of time, like dollars is a discrete measure of currency. You would never have a tblDollarAmounts and store, say, $12.04, and then link to that number. $12.04 is a dimension of some other, more complex object, so maybe it's an amount in a transaction, or it's the cost of a movie, or the value of a loan. Time, in data, is a number like money which is always a dimension of something else. A transaction has a date. A person is born on a date. You start school on a date, but you don't just store dates. They don't mean anything as measures when they don't measure something specific.

I think that's why you're having trouble.

hope this helps,

 

[namliam]

Could it be as simple as:

SELECT DATE_ID
FROM CALDATE
WHERE (((DatePart("w",[DATE_ID]))=DatePart("w",Date()-8)) AND ((DatePart("ww",[DATE_ID]))=DatePart("ww",Date()-8)) AND ((Year([DATE_ID]))=Year(Date())-8))
GROUP BY DATE_ID;

??

I agree with lagbolt though, it doesnt seem smart to store dates in a table.

 

[namliam]

I dug thru some old code of mine and found this function to get the first monday of the first week of this year...

Public Function FindFirstMonday(ThisDate As Date) As Date
    Dim Jan1st As Date
    Jan1st = DateSerial(year(Date), 1, 1)
    FindFirstMonday = Jan1st - Weekday(Jan1st, vbMonday) + 1
    If Weekday(Jan1st, vbMonday) > 4 Then
        FindFirstMonday = FindFirstMonday + 7
    End If
End Function

From that this function will find the same day of the week for last year, i.e. Tuesday of week 38 2012 (today) is Sept 18, 2012

Public Function SameWeekDayLastYear(ThisDate As Date) As Date
    SameWeekDayLastYear = FindFirstMonday(DateAdd("YYYY", -1, ThisDate)) + (DatePart("WW", ThisDate, vbMonday, vbFirstFourDays) - 1) * 7 + DatePart("W", ThisDate, vbMonday, vbFirstFourDays)
End Function

 

[maw230]

This date comparison scheme doesn't make sense to me. In 2013 the first day of the year is Tuesday. In 2014 the first day of the year is Wednesday. So in 2014, how does your Week 1, Tuesday work? Is it 6 days into the year, or does it not exist? In either case it's not comparable to 'Week1 Tuesday 2013' in any meaningful way. As a result, it's going to be really hard to write a procedure to make this scheme make sense for all possible dates.

In addition, it doesn't make sense to me to have a table of dates so that you can link to a date. A date is a discrete measure of time, like dollars is a discrete measure of currency. You would never have a tblDollarAmounts and store, say, $12.04, and then link to that number. $12.04 is a dimension of some other, more complex object, so maybe it's an amount in a transaction, or it's the cost of a movie, or the value of a loan. Time, in data, is a number like money which is always a dimension of something else. A transaction has a date. A person is born on a date. You start school on a date, but you don't just store dates. They don't mean anything as measures when they don't measure something specific.

I think that's why you're having trouble.

hope this helps,

This would explain why I was having trouble grasping what they were asking for..

The date file is one our datawhse team has put in place. It stores Date, Week, Month, Quarter, Year, and Day of Week (e.g. Sunday). It's quite helpful in a lot of cases where writing Date functions causes time constraints, but for all I know you're right in calling it impractical.

 

[maw230]

I dug thru some old code of mine and found this function to get the first monday of the first week of this year...

Public Function FindFirstMonday(ThisDate As Date) As Date
    Dim Jan1st As Date
    Jan1st = DateSerial(year(Date), 1, 1)
    FindFirstMonday = Jan1st - Weekday(Jan1st, vbMonday) + 1
    If Weekday(Jan1st, vbMonday) > 4 Then
        FindFirstMonday = FindFirstMonday + 7
    End If
End Function

From that this function will find the same day of the week for last year, i.e. Tuesday of week 38 2012 (today) is Sept 18, 2012

Public Function SameWeekDayLastYear(ThisDate As Date) As Date
    SameWeekDayLastYear = FindFirstMonday(DateAdd("YYYY", -1, ThisDate)) + (DatePart("WW", ThisDate, vbMonday, vbFirstFourDays) - 1) * 7 + DatePart("W", ThisDate, vbMonday, vbFirstFourDays)
End Function

Will this run into the problem lagbolt described for the beginning of the year?

 

[maw230]

Could it be as simple as:

SELECT DATE_ID
FROM CALDATE
WHERE (((DatePart("w",[DATE_ID]))=DatePart("w",Date()-8)) AND ((DatePart("ww",[DATE_ID]))=DatePart("ww",Date()-8)) AND ((Year([DATE_ID]))=Year(Date())-8))
GROUP BY DATE_ID;

??

I agree with lagbolt though, it doesnt seem smart to store dates in a table.

This will get the first day in the series, but I'm having trouble combining that with the last day in a between statement, so that I have a total of one week going back 7 days from the same weekday as yesterday, last year.

My users apparently aren't worried about the query not working for the first week of the year, so with that new info I'm still having trouble getting minus 7 days from the same day as yesterday this year, last year.

I've gotten it to work albeit in a silly way. The first query remains the same as my OP:

SELECT DATE_ID
FROM CALDATE
WHERE (((DatePart("w",[DATE_ID]))=DatePart("w",Date()-1)) AND ((DatePart("ww",[DATE_ID]))=DatePart("ww",Date()-1)) AND ((Year([DATE_ID]))=Year(Date())-1))
GROUP BY DATE_ID;

Then I Dlookup DATE_ID from that query into my Sales query and do a between that DATE_ID and 7 days prior.

 

[namliam]

My function just finds the SameWeekDayLastYear, if that is tuesday of week one regardless of year it will find tuesday of week one last year...

Some quick tests showed a bug.... or two
But these two functions seem to work just fine

Public Function FindFirstMonday(ThisDate As Date) As Date
    Dim Jan1st As Date
    Jan1st = DateSerial(year(ThisDate), 1, 1)
    FindFirstMonday = Jan1st - Weekday(Jan1st, vbMonday) + 1
    If Weekday(Jan1st, vbMonday) > 4 Then
        FindFirstMonday = FindFirstMonday + 7
    End If
End Function

Public Function SameWeekDayLastYear(ThisDate As Date) As Date
    SameWeekDayLastYear = FindFirstMonday(DateAdd("YYYY", -1, ThisDate)) + (DatePart("WW", ThisDate, vbMonday, vbFirstFourDays) - 1) * 7 + (DatePart("W", ThisDate, vbMonday, vbFirstFourDays) - 1)
End Function

Returning:
?sameweekdaylastyear(#01/01/2013#)
1/3/2012
?sameweekdaylastyear(#1/3/2012#)
1/4/2011
?sameweekdaylastyear(#1/4/2011#)
1/5/2010
?sameweekdaylastyear(#1/5/2010#)
12/30/2008
?sameweekdaylastyear(#12/30/2008#)
1/2/2007
?sameweekdaylastyear(#1/2/2007#)
1/3/2006

As for weeks, only thing you have to be mindfull of, which isnt accounted for here is...
There is always a week 1 to a year, but the (in above example) Tuesday of that week may not always lie in the year you expect (ref: 12/30/2008, which is the Tuesday of Week 1 of 2009)...
Another thing that is going to be an issue is the actual weeknumbers, while most years have 52 weeks, every so many years there are 53 weeks... How will you handle this?

 

[plog]

The below code should do what you asked for.

Function getLastYearsDate(d)
    ' gets corresponding date for d, from last year
    ' corresponding date occurs on same day of week as d, based on same week of year d occurs on,
 
Dim ret As Date
Dim FirstDay As Date
 
FirstDay = CDate("1/1/" & Year(d) - 1)
If Weekday(FirstDay) > 1 Then FirstDay = DateAdd("d", (Weekday(FirstDay) - 1) * -1, FirstDay)
 ' gets date of sunday in first week of prior year
 
ret = DateAdd("ww", DatePart("ww", d) - 1, FirstDay)
' sets return value to first sunday of corresponding week d is in
 
ret = DateAdd("d", Weekday(d) - Weekday(ret), ret)
' moves return value to correct day of the week
 
If (DateDiff("d", ret, d) < 365) Then ret = DateAdd("d", -7, ret)
 ' moves return value back a week, if return date occurs later in year than d
 
getLastYearsDate = ret
End Function

 

[namliam]

SELECT DATE_ID 
FROM CALDATE
WHERE (((DatePart("w",[DATE_ID]))=DatePart("w",Date()-8)) AND ((DatePart("ww",[DATE_ID]))=DatePart("ww",Date()-8)) AND ((Year([DATE_ID]))=Year(Date())-8))
   OR (((DatePart("w",[DATE_ID]))=DatePart("w",Date()-7)) AND ((DatePart("ww",[DATE_ID]))=DatePart("ww",Date()-7)) AND ((Year([DATE_ID]))=Year(Date())-7))
   OR (((DatePart("w",[DATE_ID]))=DatePart("w",Date()-6)) AND ((DatePart("ww",[DATE_ID]))=DatePart("ww",Date()-6)) AND ((Year([DATE_ID]))=Year(Date())-6))
   OR (((DatePart("w",[DATE_ID]))=DatePart("w",Date()-5)) AND ((DatePart("ww",[DATE_ID]))=DatePart("ww",Date()-5)) AND ((Year([DATE_ID]))=Year(Date())-5))
   OR (((DatePart("w",[DATE_ID]))=DatePart("w",Date()-4)) AND ((DatePart("ww",[DATE_ID]))=DatePart("ww",Date()-4)) AND ((Year([DATE_ID]))=Year(Date())-4))
   OR (((DatePart("w",[DATE_ID]))=DatePart("w",Date()-3)) AND ((DatePart("ww",[DATE_ID]))=DatePart("ww",Date()-3)) AND ((Year([DATE_ID]))=Year(Date())-3))
   OR (((DatePart("w",[DATE_ID]))=DatePart("w",Date()-2)) AND ((DatePart("ww",[DATE_ID]))=DatePart("ww",Date()-2)) AND ((Year([DATE_ID]))=Year(Date())-2))
   OR (((DatePart("w",[DATE_ID]))=DatePart("w",Date()-1)) AND ((DatePart("ww",[DATE_ID]))=DatePart("ww",Date()-1)) AND ((Year([DATE_ID]))=Year(Date())-1))
GROUP BY DATE_ID;

Perhaps something like above?

Having these tables in Datawarehowses/datamarts is common enough as these are meant to be used by * dummies * on the user end of things...
Still a "proper" developer dont regularly use such "crap" imho.

 

[plog]

while most years have 52 weeks, every so many years there are 53 weeks

Actually, every year has days in 53 weeks, so what he wants is possible. A good example of this is to look at 2012 calendar and a 2011 calendar. One starts on a Saturday (last day of the week) and one starts on a Sunday (first day of the week). Count the number of weeks that have days and its 53 in both.

 

[maw230]

My edit from above:

I've gotten it to work albeit in a silly way. The first query remains the same as my OP:

SELECT DATE_ID
FROM CALDATE
WHERE (((DatePart("w",[DATE_ID]))=DatePart("w",Date()-1)) AND ((DatePart("ww",[DATE_ID]))=DatePart("ww",Date()-1)) AND ((Year([DATE_ID]))=Year(Date())-1))
GROUP BY DATE_ID;

Then I Dlookup DATE_ID from that query into my Sales query and do a between that DATE_ID and 7 days prior.

Getting yesterday last year is easy enough, but without using all the date functions like you did above, namliam, getting 7 days before yesterday was the tricky part. I hate to use a dlookup, but it's working.

If my users don't care about leap years and beginning and ending weeks then this time I won't either.

 

[namliam]

No, Plog, if you look at the official calander a week (depending on your point of view) will ALWAYS start on either a Sunday or a Monday, regardless of what day is the first of January...
Weeknumber can also differ per region logic, which can be VERY annoying

Week 1 2013, 31-dec-2013 - 6 Jan 2013
Week 1 2012, 2 - Jan-2012 - 8 Jan 2012
Week 1 2009, 29 Dec 2008 - 4 Jan 2009

As said some start the week on Sunday, some on Monday.
Then some regions start on week 1 if....
- Jan 1st is in the week
- atleast 4 days are in the week
- a full week of the new year

You can see depending on Sunday or monday and above 3 seperate rules... weeks can differ in different spots accross the world.
Fortunately a lot of times the different systems overlap but sometimes they do not and miscommunications can arrise

 

[plog]

So you are saying 12/31/2013 is not in Week 53 of 2013?

In the realm of Access (the context we are using) its week 53.

 

[namliam]

No it is NOT per definition week 53, it CAN be but it depends on your region and your way of looking at years/weeks.
i.e.
Week starts on monday and the first four days rule applies:
?format(#12/31/2013#, "WW", vbMonday, vbFirstFourDays)
31 dec 2013 is part of week 1 of 2014, NOT of week 52 (or if you want 53) of 2012.
2012 in this case only has 52 weeks starting from 31 Dec 2011 up to and including 29 Dec 2012
Note how this is NOT a full year and you are missing 2 days in that "week-year"

With the week starting on monday but with the first full week
?format(#12/31/2013#, "WW", vbMonday, vbFirstFullWeek)
Will put this date in week 52 of 2013, which is actually the same as week 1 is the previous example.

Just an illustration of how important regional influences can be.

Edit:
The "realm of access" doesnt exist in this context, it is the realm of our reality, locality, region, possibly even religion that dictates our view of the weeks inside the year. Just think of things like our western year vs that of the chinese year and there are more examples like that.

 

[plog]

How very metaphysical of you. I have no doubt you are correct about your discussion about cultures and regions and the loss of days when we switched from Julian to Gregorian, but you've moved the discussion. My initial statement still stands:

Actually, every year has days in 53 weeks, so what he wants is possible.

Its math. 365 / 7 = 52 with a remainder of 1. That remainder ensures that every year will have days in 53 weeks. Also, and I will contradict this point in a second, because its 1 it also ensures that every year will have days in exactly 53 weeks--if it was a remainder of 2 then there's a possibility it could be divided over 54 weeks.

Now to crap on that point I just made--2028 is such a year ( a leap year that begins on a Saturday--it has days in 54 weeks).

 

[namliam]

that is like saying that each year has 366 days because there is one year that is a leap year, therefor every year is (365 + 365 + 365 + 366 ) / 4 = 365.25 days thus has 0.25 of a 366th day therefor has a 366th day....
It is like saying every month has 5 weeks and 3 months have 13, it doesnt add up
Even 3 months having 13 weeks isnt true, yet both are considered to be exactly one quarter of a year... Yet 1st quarter is 90 days, second is spot on 91 and the 3rd and 4th are 92, unless there is a leap year :banghead:
How does that make sence in your world? It doesnt, yet it is the way the entire world works...

A week is 7 days from monday to sunday or sunday to saturday and it doesnt fit into a year, quarter or a month all that nicely unfortunatly


I am sorry my friend it just doesnt work, straight up math does NOT work on a calander....

The thing is your being pedantic about the subject, 365.25 days in a year (and some after that to be even more precise) means there are 52.1785 weeks in a year. which means every year has 1.25 "bonus" days that do not fit into a "week year"
we count a year as 52 weeks just like we do count a year to be 365 days and "fix it" by adding a "bonus" or correction week or day to the "calander".
Thems the facts, it is simple some years have 52 weeks and some years have 53. If you are going to be looking for an "Official" week 53 you will find,
- It doesnt exist in every year
- People across the world will disagree about the year that has 53 weeks in it

 

[plog]

You drove us down the pedant highway with talk of regions and cultures and moving us away from Access and how it determines the week of a year.

My initial thought was similar to yours--corresponding weeks in priors years could become nonsensical at some dates. But with his definition and the way DatePart works, it is in fact possible. In one sentence here is my main argument: In Access, a year will have at least one date that resolves to a 53rd week.

To show me wrong, just show me one date value of [TestDate] such that


DatePart("ww", [TestDate])

equals 52 and

DatePart("ww", DateAdd("d", 1, [TestDate]))

equals 1.


I know your ego is now involved, so let me assuage it: this in no way assails your arguments about weeks/years and days. You are correct about all that. It's not germane to his issue, but it is factually correct.

 

[namliam]

It in fact IS involved in this issue try finding, in real life, a week 53 in a year that only has 52 weeks, you DONT .... That is the problem

Yes using the (broken/ill used) function in access will always make 01-Jan of any year always be the first day of the first week... Fact of the matter is that in reality it does not work that way.
Fact of the matter is that you NEED to fill in the third and fourth parameters of the function to make it work properly.
Proper way to use it: Datepart("ww", #12/31/2013#, vbMonday, vbFirstFourDays)

And its not my ego that is in the way, code not reflecting reality is nonsence.