-4

Cant seem to wrap my head round this problem.

I have two tables one which has the following sample values: Module

Second table had the following values: date lookup

What i am trying to achieve is like the following:

enter image description here

So you can see the first table has the modules, what year and what term. Based on these there is a start week and and end week.

The lookup table for the start and the finish unfortunatley is in a week basis and i need the begin week to match the second tables weekNo based on the season i guess and taking the start date being Sdate from that table to match what i am looking for and then the same applies to the end date. Match the season and the endweek with the second tables WeekNo and Edate and only bring that date in.

Hope i made a bit of sense but i am hoping the third image shows what i am look for.

I've tried CTE, Group by, Partition by, order by, min, max and got nowhere :( Dont really want to hard code anything, so was hoping you wonderful peps can help me out !!

Many thanks in advance :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user6667537
  • 13
  • 2
  • Can you post the query you've already tried and what's not working with it? Reason being this "week to match the second tables weekNo based on the season i guess and taking the start date being Sdate from that table to match" seems like the exact thing you need to do. – Error_2646 Aug 10 '22 at 20:31
  • 3
    [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) Certainly you should *not* be expecting us to transcribe all that data. – Thom A Aug 10 '22 at 20:34

1 Answers1

0

I suspect you are trying to achieve this by using one a single join between the tables - whereas what you actually need is two separate joins:

SELECT table1.module as mod_code, 
       table1.season as psl_code, 
       table2.Sdate as ypd_sdate,
       table3.Edate as ypd_edate
FROM t1 as table1
     JOIN t2 as table2 ON table2.yr = table1.year AND table2.season = table1.season AND table2.weekNo = table1.BeginWeek
     JOIN t2 as table3 ON table3.yr = table1.year AND table3.season = table1.season AND table3.weekNo = table1.EndWeek
Martin Cairney
  • 1,714
  • 1
  • 6
  • 18
  • I feel so Dumb :) You are a Star Martin Cairney. Your suspicion was totally correct i had only one Join and i was trying all sorts of methods after the join such as unions, partitions, groups and all it was... was another join.. THANK YOU Martin Cairney!!!!!! Sorry about the pictures – user6667537 Aug 11 '22 at 09:16