Joining tables with different dates

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

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-04-06 : 10:35:45

Hello,

It seems to me I've done this before, but I can't seem to remember how I did this. Also, I'm a SQL newbie, so please forgive my ignorance.

I have two tables, each with a date record and other columns in them. However, the periodicity of the dates differs between the two. By this, I mean one table has records in weeks and the other in months (these could change later to months and years, etc.). Thus:

WeeklyTable:
wk1 Item1
wk2 Item2
wk3 Item3

MonthlyTable:
mo1 Item1
mo2 Item2
mo3 Item3

I am trying to join these two tables such that the MonthlyTable records appear chronologically in the correct place between the WeeklyTable's dates and are replicated however many times they must be until the weekly date record requires the next month's record. In other words, if I have:

Monthly records of:
January 1, 2005
February 1, 2005

Weekly Records of :
January 3, 2005
January 10, 2005
January 17, 2005
January 24, 2005
January 31, 2005
February 1, 2005
February 7, 2005

I want to get:

Weekly Date Monthly Date
January 3, 2005............January 1, 2005
January 10, 2005...........January 1, 2005
January 17, 2005...........January 1, 2005
January 24, 2005...........January 1, 2005
January 31, 2005...........January 1, 2005
February 1, 2005...........February 1, 2005
February 7, 2005...........February 1, 2005

Any help would be much appreciated!

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-06 : 10:41:03

Your query may look like this:

SELECT A.Date, B.Date
FROM WeeklyTable A INNER JOIN MonthlyTable B
ON DATEPART(MM, A.Date) = DATEPART(MM, B.Date) AND
DATEPART(YYYY, A.Date) = DATEPART(YYYY, B.Date)

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-04-06 : 10:49:17

Hi rfrancisco,

Thanks for responding. I tried this and it seemed to pull in the correct weekly date records for every monthly record, but I'm wondering if I can do the reverse (as in pull the correct monthly records for every weekly record, with the correct monthly records repeating for each week). I can't imagine that reversing the order of the tables in the query would do anything (though I haven't tried this yet). Any ideas?

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-06 : 10:53:54

You can even just reverse the columns in the SELECT without touching the FROM and this should also do the trick.

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-04-06 : 11:01:53

Hi, I just tried that, but it seems to give me the same result (weekly records pulled in for every month, but not monthly records pulled in for every week) -- only with the columns reversed. For the record, I reversed the FROM clause as well (keeping the original SELECT form) and it was still the same. I wonder what might be causing this. My guess is that I'm joining based on what the two tables have in common this way which automatically means the lowest frequency is what they have in common, so I get the correct week for each month, but I'd like to get the correct month for each week, instead.

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-06 : 11:39:09

What's the result are you expecting? Can you show me some examples.

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-04-06 : 11:41:09

Got it! Neither table had a primary key. I made the date column the primary key in both and it worked - like magic! Thanks very much, I really appreciate your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources