Lag 1 day relationship
in [Queries]
|
Prev: First function returning min
Next: Friday's Due Date
From: Michelle on 14 May 2010 08:49 I was hoping there was some subquery I could run to look for the next highest day and populate date2 with that. I'm not looking for software to be telepathic. That would be ridiculous. When I need a 1 day lag when I pull data just for one day I simply use the same end date as before and then query that date -5 days (in case there is a weekend and holiday in between dates). Then I use another query that contains the following subquery to calculate the 2nd highest date... SELECT Max(AS_OF_DATE) AS 2ndMax INTO 2ndDate_Tbl FROM 2ndMaxRaw WHERE (((2ndMaxRaw.AS_OF_DATE)<(SELECT Max(AS_OF_DATE) FROM InfoTbl))); Then I use the resulting date to query in the table where I need a 1 day lag. When it's just 1 day I can ignore the actual date since I know everything queried is for the same date. The problem with using this method on a date range is I need to relate the 2 dates properly. I was hoping to use the date2 to join the tables. This way you can use Access to keep track of your weekends and holidays indirectly. Make sense? -- Cheers, Michelle "Have no fear of perfection - you'll never reach it." - Salvador Dali "John W. Vinson" wrote: > On Thu, 13 May 2010 13:30:01 -0700, Michelle > <Michelle(a)discussions.microsoft.com> wrote: > > >I also don't want to have > >to create a holiday table since it is one more thing that would have to be > >maintained. (Meaning it would get forgotten.) > > Sorry, Michell, if you want Access to keep track of the holidays that *your > business* observes, you will have to somehow tell Access what those holidays > are. > > The telepathic interface won't be out until Access 2021 (same release as the > time machine interface,which is how I knew...) <bg> > > -- > > John W. Vinson [MVP] > . >
From: Bob Barrows on 14 May 2010 09:01 It seems that T2 is essentially your holiday table. It's not going to perform well, but a correlated subquery will do the trick. Create this saved query (call it Q1): Select T1.ID,T1.date, (select max(T2.date) from T2 WHERE T2.date < T1.date) As T2date FROM T1 Now you can join Q1 to T2: SELECT Q1.ID, Q2.date,T2.whatever FROM Q1 JOIN T2 ON Q1.ID=T2.ID AND Q1.T2date=T2.date Michelle wrote: > I was hoping there was some subquery I could run to look for the next > highest day and populate date2 with that. I'm not looking for > software to be telepathic. That would be ridiculous. > > When I need a 1 day lag when I pull data just for one day I simply > use the same end date as before and then query that date -5 days (in > case there is a weekend and holiday in between dates). Then I use > another query that contains the following subquery to calculate the > 2nd highest date... > > SELECT Max(AS_OF_DATE) AS 2ndMax INTO 2ndDate_Tbl > FROM 2ndMaxRaw > WHERE (((2ndMaxRaw.AS_OF_DATE)<(SELECT Max(AS_OF_DATE) > FROM InfoTbl))); > > Then I use the resulting date to query in the table where I need a 1 > day lag. When it's just 1 day I can ignore the actual date since I > know everything queried is for the same date. The problem with using > this method on a date range is I need to relate the 2 dates properly. > I was hoping to use the date2 to join the tables. This way you can > use Access to keep track of your weekends and holidays indirectly. > Make sense? > > > >> On Thu, 13 May 2010 13:30:01 -0700, Michelle >> <Michelle(a)discussions.microsoft.com> wrote: >> >>> I also don't want to have >>> to create a holiday table since it is one more thing that would >>> have to be maintained. (Meaning it would get forgotten.) >> >> Sorry, Michell, if you want Access to keep track of the holidays >> that *your business* observes, you will have to somehow tell Access >> what those holidays are. >> >> The telepathic interface won't be out until Access 2021 (same >> release as the time machine interface,which is how I knew...) <bg> >> -- HTH, Bob Barrows
From: Bob Barrows on 14 May 2010 09:19 Oops, I forgot the ID relationship in that saved query (although it might not make any difference ...) Select T1.ID,T1.date, (select max(T2.date) from T2 WHERE T1.ID=T2.ID AND T2.date < T1.date) As T2date FROM T1 Bob Barrows wrote: > It seems that T2 is essentially your holiday table. It's not going to > perform well, but a correlated subquery will do the trick. Create this > saved query (call it Q1): > > Select T1.ID,T1.date, > (select max(T2.date) from T2 WHERE T2.date < T1.date) > As T2date > FROM T1 > > > Now you can join Q1 to T2: > > SELECT Q1.ID, Q2.date,T2.whatever > FROM Q1 JOIN T2 ON Q1.ID=T2.ID AND Q1.T2date=T2.date > > > Michelle wrote: >> I was hoping there was some subquery I could run to look for the next >> highest day and populate date2 with that. I'm not looking for >> software to be telepathic. That would be ridiculous. >> >> When I need a 1 day lag when I pull data just for one day I simply >> use the same end date as before and then query that date -5 days (in >> case there is a weekend and holiday in between dates). Then I use >> another query that contains the following subquery to calculate the >> 2nd highest date... >> >> SELECT Max(AS_OF_DATE) AS 2ndMax INTO 2ndDate_Tbl >> FROM 2ndMaxRaw >> WHERE (((2ndMaxRaw.AS_OF_DATE)<(SELECT Max(AS_OF_DATE) >> FROM InfoTbl))); >> >> Then I use the resulting date to query in the table where I need a 1 >> day lag. When it's just 1 day I can ignore the actual date since I >> know everything queried is for the same date. The problem with using >> this method on a date range is I need to relate the 2 dates properly. >> I was hoping to use the date2 to join the tables. This way you can >> use Access to keep track of your weekends and holidays indirectly. >> Make sense? >> >> >> >>> On Thu, 13 May 2010 13:30:01 -0700, Michelle >>> <Michelle(a)discussions.microsoft.com> wrote: >>> >>>> I also don't want to have >>>> to create a holiday table since it is one more thing that would >>>> have to be maintained. (Meaning it would get forgotten.) >>> >>> Sorry, Michell, if you want Access to keep track of the holidays >>> that *your business* observes, you will have to somehow tell Access >>> what those holidays are. >>> >>> The telepathic interface won't be out until Access 2021 (same >>> release as the time machine interface,which is how I knew...) <bg> -- HTH, Bob Barrows
From: Michelle on 14 May 2010 10:28 Bob, This is exactly what I was looking for. Yes, T2 is essentially my holiday table. I wasn't thinking of it that way before. Thank you kindly for your help. John, I really didn't appreciate your prior sarcastic comments. It wasn't at all necessary. -- Cheers, Michelle "Have no fear of perfection - you'll never reach it." - Salvador Dali "Bob Barrows" wrote: > Oops, I forgot the ID relationship in that saved query (although it > might not make any difference ...) > > Select T1.ID,T1.date, > (select max(T2.date) from T2 WHERE T1.ID=T2.ID > AND T2.date < T1.date) > As T2date > FROM T1 > > > Bob Barrows wrote: > > It seems that T2 is essentially your holiday table. It's not going to > > perform well, but a correlated subquery will do the trick. Create this > > saved query (call it Q1): > > > > Select T1.ID,T1.date, > > (select max(T2.date) from T2 WHERE T2.date < T1.date) > > As T2date > > FROM T1 > > > > > > Now you can join Q1 to T2: > > > > SELECT Q1.ID, Q2.date,T2.whatever > > FROM Q1 JOIN T2 ON Q1.ID=T2.ID AND Q1.T2date=T2.date
From: Bob Barrows on 14 May 2010 11:01
If you stick around and read more of the posts, I think you'll realize that John was not trying to be nasty or sarcastic. "Gently chiding" at the worst, but the intent was to make you smile and prompt you for further information. Intentions are very difficult to convey in plain-text messages like this so please try not to jump to conclusions so quickly. :-) And his post did serve the purpose of getting you to further clarify things which allowed me to realize that T2 was your holiday table, and allowed you to get your solution. Michelle wrote: > Bob, > > This is exactly what I was looking for. Yes, T2 is essentially my > holiday table. I wasn't thinking of it that way before. Thank you > kindly for your help. > > > John, I really didn't appreciate your prior sarcastic comments. It > wasn't at all necessary. > > >> Oops, I forgot the ID relationship in that saved query (although it >> might not make any difference ...) >> >> Select T1.ID,T1.date, >> (select max(T2.date) from T2 WHERE T1.ID=T2.ID >> AND T2.date < T1.date) >> As T2date >> FROM T1 >> >> >> Bob Barrows wrote: >>> It seems that T2 is essentially your holiday table. It's not going >>> to perform well, but a correlated subquery will do the trick. >>> Create this saved query (call it Q1): >>> >>> Select T1.ID,T1.date, >>> (select max(T2.date) from T2 WHERE T2.date < T1.date) >>> As T2date >>> FROM T1 >>> >>> >>> Now you can join Q1 to T2: >>> >>> SELECT Q1.ID, Q2.date,T2.whatever >>> FROM Q1 JOIN T2 ON Q1.ID=T2.ID AND Q1.T2date=T2.date -- HTH, Bob Barrows |