Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2007
    Posts
    10

    Question Unanswered: Combining or Concating seperate fields for date?

    I have two tables in a SQL db.
    Each has 3 separate fields used to store a date info:

    lastservicemonth tinyint1
    lastserviceday tinyint1
    lastserviceyear smallint2
    (and these fields can be nulls)

    I want to compare the date info in Table A vs. Table B and find the latest date between the two.

    I know I somehow need to combine the 3 separate fields in each table to form one date field. Then I can compare the dates.
    But ths far I have been unsuccessful.

    Any help would be greatly appreciated!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    To get the latest row, use:
    Code:
    SELECT TOP 1 *
       FROM [Table A]
       ORDER BY lastserviceyear, lastservicemonth, lastserviceday
    -PatP

  3. #3
    Join Date
    Apr 2007
    Posts
    10
    Thanks Pat - I can can see how my question was unclear. Hope this clarifies.

    Say Table A and Table B both contain the same person records for when they last came in the hospital. But the tables contain different dates.

    Example:
    Table A - ID #123, John Doe , 11-1-2007
    Table B - ID#123, John Doe, 12-3-2007

    I want to update the date fields in Table A with the data in Table B, but only if the date in Table B is more recent than the date in Table A.

    So, I need to compare the dates for each person in Table A to the same person in Table and determine which visit date is more recent.

    Hope this makes more sense.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why are two tables storing such similar information?

    If you give us the real problem, it might also be easier to decipher than "table A and table B..."
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    While I understand the desire to "simplify" a problem for posting purposes, the process usually infuriates me... All too often critical pieces of information get "simplified" out of the example that gets posted!

    Can you post at least the DDL for the tables (in other words the CREATE TABLE statements needed to recreate them), and whatever attempt you've made so far to do what you want? This would help us a lot in determining what you need.

    -PatP

  6. #6
    Join Date
    Apr 2007
    Posts
    10
    I appreciate your feedback. I can see I need to clarify.
    Table A and B are in different dbs connected to different apps.
    The apps communicate with each other imperfectly, so the dates get out of synch.

    I imported the id# and the day, month and year column from System B, Table B to system A.
    Now I want to update Table A with that data.

    Here's a select statement where I attempt to identify discrepancies in the two data sets. If I can correct this , I can do an update statement. The statement below adds the 3 date fields and arrives a a number rather than a date. Do the fields need to be converted from smallint and tinyint ?

    SELECT
    (p.lastserviceYEAR +'-'+ p.lastserviceMONTH +'-'+ p.lastserviceday) as pdate,
    (e.lastserviceYEAR +'-'+ e.lastserviceMONTH +'-'+ e.lastserviceday) as edate
    From patient p Inner Join empi e On (p.ID = e.ID)
    where edate > pdate

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use something like:
    Code:
    Cast(1000 * lastserviceyear + 100 * lastservicemonth + lastserviceday AS INT)
    to get integer values that you can safely compare and sort... They aren't pretty to print, but they work well for comparisons and sorting.

    -PatP

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I was going to suggest the use of DateAdd(), but I think that will be far more efficient.
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    last time it was me dropping a zero, pat, this time it's you

    Code:
    Cast(10000 * ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2007
    Posts
    10
    Excellent. That will do the trick.
    Thanks to all for your help and patience!

Posting Permissions

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