Results 1 to 1 of 1
  1. #1
    Join Date
    Apr 2012
    Posts
    1

    Question Unanswered: To find Missing month(s) by matching against a range of dates across different yeasr

    Hi there,
    Iam trying to create a SP in Sql Server 2005. here's my criteria.
    My first part of SP retrieves the following data - Table A.

    ID Name StartDate IsMissing
    ------- -------- ----------- ---------
    123456 Mike 2011-06-01 NULL
    789123 Dave 2011-08-01 NULL

    Now ID listed above, has data in table b as follows

    ID Name IncidentDate Game
    ------- -------- ----------- ---------
    123456 Mike 2011-06-01 Baseball
    123456 Mike 2011-06-21 FootBall
    123456 Mike 2011-07-11 Soccer
    123456 Mike 2011-07-16 Baseball
    123456 Mike 2011-09-01 Baseball
    123456 Mike 2011-10-11 Soccer
    123456 Mike 2011-12-01 Baseball
    123456 Mike 2011-12-22 Baseball
    123456 Mike 2011-12-23 Soccer
    123456 Mike 2012-01-10 Baseball
    123456 Mike 2012-01-12 Baseball
    123456 Mike 2012-03-01 Baseball
    123456 Mike 2012-03-15 FootBall
    789123 Dave 2011-08-01 Baseball
    789123 Dave 2011-09-11 Baseball
    789123 Dave 2011-10-21 Soccer
    789123 Dave 2011-11-01 Baseball
    789123 Dave 2011-12-21 Baseball
    789123 Dave 2012-01-31 FootBall
    789123 Dave 2012-02-21 Baseball
    789123 Dave 2012-03-14 Baseball
    789123 Dave 2012-04-03 Basketball

    Based on the table b's data and the criteria - tableB.Incidentdate between tableA.Startdate and getdate()
    getdate() = current date i.e, today 04/04/2012

    1. the table A should be updated as follows

    ID Name StartDate IsMissing
    ------- -------- ----------- ---------
    123456 Mike 2011-06-01 Yes
    789123 Dave 2011-08-01 No



    2. List Missing months in Table C

    ID Name Missing Month Missing Year
    ------- -------- ----------- ---------
    123456 Mike 08 2011
    123456 Mike 11 2011
    123456 Mike 02 2012
    123456 Mike 04 2012

    Note: None missing for ID 789123

    I would appreciate if any of the sql guru's can help me with this.

    Kindly help pls.

    Thanks,
    sqlbeginner
    Last edited by ursql; 04-04-12 at 23:54.

Posting Permissions

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