Need help to create a column, add values and then compare with another table
-
Hi all
I need some basic help in adding values to a column in a table(like making a key in EXCEL) and making a similar key in a different table, and then compare which rows are present in one and not in another table and vice versa. For example, one table contains values:
ANUM,BNUM,STARTDATE,DURATION
260975649803,0965000066,2013-04-20 00:02:55,49
260976522522,0977144491,2013-04-20 10:23:35,161
260977313710,0977809127,2013-04-20 07:21:48,272
260977206661,0976320516,2013-04-20 20:39:36,51
260977948010,0966952330,2013-04-20 00:34:04,55
260979121229,0977261566,2013-04-20 00:33:25,13
Note that all field types are varchar. I want to make a key which will give me
substring(ANUM,4,10) &"_"& substring(BNUM,2,10) &"_"& only the HHMM of the STARTDATE &"_"& DURATION.
i have altered the same table and created a column "KEY_IN" with varchar(50) but need help in populating the values from the relevant column fields into it. My expected result for the above sample rows should look like:
ANUM,BNUM,STARTDATE,DURATION,KEY_IN
260975649803,0965000066,2013-04-20 00:02:55,49,975649803_965000066_00:02_49
260976522522,0977144491,2013-04-20 10:23:35,161,976522522_977144491_10:23_161
260977313710,0977809127,2013-04-20 07:21:48,272,977313710_977809127_07:21_272
260977206661,0976320516,2013-04-20 20:39:36,51,977206661_976320516_20:39_51
Thanks in anticipation!
-
You don't need to add an extra column, you can do it all by code
--If you have STARTDATE as varcharSELECT substring(ANUM,4,10) + '_' + substring(BNUM,2,10) + '_' + REPLACE( SUBSTRING( STARTDATE,12,5 ), ':', '') + '_' + DURATION
FROM Mytable
--If you have STARTDATE as datetime
SELECT substring(ANUM,4,10) + '_' + substring(BNUM,2,10) + '_' + REPLACE( CONVERT( char(5), STARTDATE, 8), ':', '') + '_' + DURATION
FROM Mytable
You prefer this for a direct comparison:
SELECT *FROM OneTable a
FULL
JOIN OtherTable b ON substring(a.ANUM,4,10) = substring(b.ANUM,4,10)
AND substring(a.BNUM,2,10) = substring(b.BNUM,2,10)
AND substring(a.BNUM,2,10) = substring(b.BNUM,2,10)
AND REPLACE( SUBSTRING( a.STARTDATE,12,5 ), ':', '') = REPLACE( SUBSTRING( b.STARTDATE,12,5 ), ':', '')
AND a.DURATION = b.DURATION
WHERE a.ANUM IS NULL --Checks for rows existing on table b and not on table a
OR b.ANUM IS NULL --Checks for rows existing on table a and not on table b
-
Thanks a ton Luis!!!
I have a doubt, in the last 2 sentences of your code, we are checking if a.ANUM is null, will this work?
Because, if there are no null values in the ANUM col, then the query will not return any missing records!
-
The condition is to get the rows that don't exist on either table using the FULL JOIN. I took a shot at guessing ANUM wouldn't be nullable or at least won't have null values.
Take a look at this cheatsheet on JOINS and the full article http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
-
Pictorial representation is of Great help...thanks a ton!!!
Now, in the same tables , I need to add certain conditions:
1. Table A- a column(say TYPE) contains 10 distinct values, and for each record a particular value is captured(say a0,a1...a10)
2. Similarly, Table B- a column(say CASE) contains 4 distinct values, one for each record(say 1,2,3,4)
Now if I want to do the reconciliation between these two tables, but only for a.TYPE in ('a0','a1','a5') which will be covered in b.CASE in ('1','3').
Expecting result should contain all records(with TYPE a0 or a1 or a5) in table A which are not present in table B(with b.CASE in 1 or 3).
Hope you got the query?
Many thanks!
-
Something like this? or am I missing something?
SELECT *
FROM OneTable a
FULL
JOIN OtherTable b ON substring(a.ANUM,4,10) = substring(b.ANUM,4,10)
AND substring(a.BNUM,2,10) = substring(b.BNUM,2,10)
AND substring(a.BNUM,2,10) = substring(b.BNUM,2,10)
AND REPLACE( SUBSTRING( a.STARTDATE,12,5 ), ':', '') = REPLACE( SUBSTRING( b.STARTDATE,12,5 ), ':', '')
AND a.DURATION = b.DURATION
WHERE (a.ANUM IS NULL --Checks for rows existing on table b and not on table a
OR b.ANUM IS NULL) --Checks for rows existing on table a and not on table b
--Check that the OR condition is between parenthesis to avoid affecting the logic
AND a.TYPE in ('a0','a1','a5')
AND b.CASE in ('1','3')
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply