Joining tables with different dates - 2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Joining tables with different dates - 2

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-04-07 : 12:32:09

I thought I had this figured out yesterday, but I've run into a new variant this morning and can't figure out why I'm having trouble.

I have two tables with historical data. One table has annual records and the other has monthly records. I'm trying to join the two such that the annual records repeat for each month for a given id until the next annual record should appear (which means repeat the annual record 12 times until the next successive record should appear next to the corresponding month). To envision things, I've got:

MonthlyTable:
mo1 Item1
mo2 Item2
mo3 Item3

AnnualTable:
yr1 Item1
yr2 Item2
yr3 Item3

I am trying to join these two tables such that the AnnualTable's records appear chronologically in the correct place between the MonthlyTable's dates and are replicated however many times they must be until the weekly date record requires the next month's record (which should mean 12 times per unique id in each table). In other words, if I have:

Annual records of:
October 1, 2001
October 1, 2002
October 1, 2003

Monthly Records of :
Jul 1, 2002
Aug 1, 2002
Sep 1, 2002
Oct 1, 2002
Nov 1, 2002
Dec 1, 2002

I want to get:

Monthly Date Annual Date

Jul 1, 2002.............October 1, 2001
Aug 1, 2002.............October 1, 2001
Sep 1, 2002.............October 1, 2001
Oct 1, 2002.............October 1, 2002
Nov 1, 2002.............October 1, 2002
Dec 1, 2002.............October 1, 2002
(etc until)
Oct 1, 2003.............October 1, 2003

Again, the key is to repeat the correct annual record the appropriate number of times per monthly record.

I would be tremendously grateful to the kind soul who could help me through this.

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-07 : 12:38:00

Try this one:

SELECT A.Date, B.Date
FROM Monthly A INNER JOIN Annual B
ON A.Date BETWEEN B.Date AND DATEADD(D, -1, DATEADD(YY, 1, B.Date))

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-04-07 : 12:56:45

This worked again! As before, I am very grateful for your help. Thank You. I hope to be able to return the favor some day.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources