Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2010
    Posts
    4

    Unanswered: Combining date fields from two tables for complete range of dates

    Hi I am using TSQL 2005 and have hit a sticky wicket. If anybody can offer some options it would be greatly appreciated.

    The problem I have two tables one table holds a complete range of dates that the college is open ,

    Table 1
    e.g acad_year, week_no, date, week_day,holiday
    09/10 , 1 , 31/08/2009, 1 , False
    . , . , . , . , .
    . , . , . , . , .
    09/10 , 52 , 29/08/2010 , 7 , True,

    Table two contains details of a student record and if the where present that day

    Table 2

    RegisterID ,Week_no, day, present

    XXXXXX , 1 , 1 , /

    I wish to combine the two tables so that all dates from table 1 are included in table 2

    When I use the query below the query omits weeks from table 1 when a register is not marked where as I want to keep these weeks in as well

    SELECT DISTINCT b.acad_period, b.register_id, b.register_group, b.day_of_week, b.student_id, b.abs_code ,a.week_no,a.date

    FROM @NRCRegisterWeekDates_tb a
    left outer join @NRCStudentRegisterMarks_tb b ON
    a.acad_period = b.acad_period
    and a.week_day = b.day_of_week
    and a.week_no = b.week_no
    WHERE b.day_of_week is not null

    The reason being when I import this into crystal as a stored procedure to use in a cross tab the weeks are sporadic in that the week pattern looks like 1,2,3,4,6,7 ,11(for weeks studnts have a mark against then )

    Whereas I want to display in the crosstab weeks when the student has not been marked as well e.g. 1,2,3,4,5,6,7,9,10,11.

    Hope this makes sense . many thanks Conor.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Remove the WHERE clause.
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Did that work for you?
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I noticed a DISTINCT in the SELECT.

    Each time when you have to rely on that, try to find out if you really need it (it slows things down when you use it) and if you do if your JOIN-criteria are correct.

    Sometimes it is necessary to use a DISTINCT, but in many cases it indicates a badly formed JOIN.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Aug 2010
    Posts
    4

    Sorted Nearly

    Hi Think I got it sorted (nearly)
    used a cross join to combine all tables in table 1 with table 2 (as table1 contains all dates for that year

    SELECT DISTINCT a.acad_period, a.register_id , a.register_group AS RegisterID, b.week_no,
    isnull(c.student_id,'') AS studentID
    FROM @NRCStudentRegisterMarks_tb a
    cross join @NRCRegisterWeekDates_tb b
    left outer join
    (SELECT acad_period, register_id, register_group, student_id
    FROM @NRCStudentRegisterMarks_tb
    GROUP BY acad_period, register_id, register_group, Student_id) C
    ON a.acad_period = c.acad_period
    and a.register_id = c.register_id
    and a.register_group = c.register_group

    Many thanks Conor.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •