Tuesday, March 27, 2012

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!To get the latest row, use:SELECT TOP 1 *
FROM [Table A]
ORDER BY lastserviceyear, lastservicemonth, lastserviceday-PatP|||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.|||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..."|||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|||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|||I'd use something like: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|||I was going to suggest the use of DateAdd(), but I think that will be far more efficient.|||last time it was me dropping a zero, pat, this time it's you ;)

Cast(10000 * ...|||Excellent. That will do the trick.
Thanks to all for your help and patience!

No comments:

Post a Comment