Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-03-08 : 12:43:14
|
Trying to reconcile 2 different data sources. I was told that if I sum up an IDENTITY Column, Count the Rows, and take the MAX and MIN dates, and if these results from 2 different systems are the same, that it would prove the data is the same.And I said: "NO WAY"They said: "I have a background in mathematics, and I can ASSURE you that is indeed true, AND I can Prove it"There may be a high likelyhood, but not absolute.They never said degree btw....in any case..any ideas that would really prove to sets of data in 2 tables in 2 different environments are the same?CREATE TABLE #myActions_myMessage ( [Group_Trans_Id] [int], [Term_DT] [date] NULL, [Add_TS] [datetime] NOT NULL, [Upd_TS] [datetime] NOT NULL)GOCREATE TABLE #GETS_myMessage ( [Group_Trans_Id] [int], [Term_DT] [date] NULL, [Add_TS] [datetime] NOT NULL, [Upd_TS] [datetime] NOT NULL)GOINSERT INTO #myActions_myMessage ([Group_Trans_Id], [Term_DT], [Add_TS], [Upd_TS])SELECT 10, '09/11/2001', '1/1/2001', '2/1/2001' UNION ALLSELECT 20, null, '3/1/2012', '3/2/2011' UNION ALLSELECT 30, '09/11/2012', '3/2/2012', '3/3/2011' UNION ALLSELECT 40, '03/17/2012', '3/3/2011', '3/4/2011' UNION ALLSELECT 50, '12/31/9999', '3/4/2011', '3/5/2011' SELECT COUNT(*) AS COUNT_myMessage_Active , SUM(Group_Trans_ID) AS SUM_Group_TRANS_ID_Active , MAX(Term_Dt) AS MAX_Term_Dt , MIN(Term_Dt) AS MIN_Term_Dt , MAX(Add_TS) AS MAX_Add_TS_Active , MAX(Upd_TS) AS MAX_Upd_TS_Active FROM #myActions_myMessage WHERE COALESCE(Term_Dt,'12/31/9999') > CONVERT(varchar(10),GETDATE(),1)INSERT INTO #GETS_myMessage ([Group_Trans_Id], [Term_DT], [Add_TS], [Upd_TS])SELECT 10, '09/11/2001', '1/1/2001', '2/1/2001' UNION ALLSELECT 15, '03/17/2012', '3/1/2012', '3/2/2011' UNION ALLSELECT 30, '09/11/2012', '3/2/2012', '3/3/2011' UNION ALLSELECT 45, '05/17/2012', '3/3/2011', '3/5/2011' UNION ALLSELECT 50, '12/31/9999', '3/4/2011', '3/5/2011' SELECT COUNT(*) AS COUNT_myMessage_Active , SUM(Group_Trans_ID) AS SUM_Group_TRANS_ID_Active , MAX(Term_Dt) AS MAX_Term_Dt , MIN(Term_Dt) AS MIN_Term_Dt , MAX(Add_TS) AS MAX_Add_TS_Active , MAX(Upd_TS) AS MAX_Upd_TS_Active FROM #GETS_myMessage WHERE COALESCE(Term_Dt,'12/31/9999') > CONVERT(varchar(10),GETDATE(),1) GODROP TABLE #myActions_myMessage, #GETS_myMessageGO/*COUNT_myMessage_Active SUM_Group_TRANS_ID_Active MAX_Term_Dt MIN_Term_Dt MAX_Add_TS_Active MAX_Upd_TS_Active---------------------- ------------------------- ----------- ----------- ----------------------- -----------------------4 140 9999-12-31 2012-03-17 2012-03-02 00:00:00.000 2011-03-05 00:00:00.000COUNT_myMessage_Active SUM_Group_TRANS_ID_Active MAX_Term_Dt MIN_Term_Dt MAX_Add_TS_Active MAX_Upd_TS_Active---------------------- ------------------------- ----------- ----------- ----------------------- -----------------------4 140 9999-12-31 2012-03-17 2012-03-02 00:00:00.000 2011-03-05 00:00:00.000*/ Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-03-08 : 13:27:37
|
quote: Originally posted by X002548 Trying to reconcile 2 different data sources. I was told that if I sum up an IDENTITY Column, Count the Rows, and take the MAX and MIN dates, and if these results from 2 different systems are the same, that it would prove the data is the same.And I said: "NO WAY"They said: "I have a background in mathematics, and I can ASSURE you that is indeed true, AND I can Prove it"There may be a high likelyhood, but not absolute.
Please:create table #a(i int not null identity(0,4), dt datetime)create table #b(i int not null identity(1,2), dt datetime)insert #a(dt) values(GETDATE())insert #a(dt) values(GETDATE()-1)insert #b(dt) values(GETDATE()-1)insert #b(dt) values(GETDATE())-- "identical" data according to moron:select COUNT(*) row, SUM(i) sum_identity, MAX(dt) max_dt, MIN(dt) min_dt from #aselect COUNT(*) row, SUM(i) sum_identity, MAX(dt) max_dt, MIN(dt) min_dt from #b-- proof it's not identicalselect 'intersect 2', * from #a intersect select 'intersect', * from #b -- 2 rows if identicalselect 'union 2', * from #a union select 'union 2', * from #b -- ONLY 2 rows if identicalselect 'except 0', * from #a except select 'except 0', * from #b -- no rows if identicalselect 'except 0', * from #b except select 'except 0', * from #a -- no rows if identicaldrop table #a,#b 2 columns + 2 rows = 100% disproved. I'm not sure you can come up with a smaller example, and it's easy to add a third column and/or row that really kills it. I didn't even cheat like you did, I used actual identity columns. |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-03-08 : 13:29:50
|
| Well you can prove that the count is the same, but that doesn't mean the other columns have the same data. A simple example can show that. Additionally, you could SUM the IDs and get the same VALUE, but that doesn't mean all the IDs are the same either. Doesn't seem like much of a discussion to me. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-03-08 : 14:38:05
|
quote: why do you think I was floored...and when they pull out...I have a background...I'm like...get your waders on
I'm pretty sure their "background in mathematics" consists of a picture of "E=mc2" or a square root sign or a college math blackboard on their computer desktop. I was pretty sure the misspelling of "Mathematics" in the title was theirs, not yours. So how did you keep from strangling them? (If you did, that is) |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-03-08 : 15:01:13
|
"thanks for the clarification on this. I was only anticipating errors on the client side and should have considered both.And"Thank you Brett....Brett is right. Approach that "mostly" works is not sufficient."And then...maybe this can be used to "Hash" (cough, cough) the DataDo we have a WTF Forum?with temp_hash ( hash_num )AS(SELECT CASE WHEN ( SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 1, 1 ) = ht.char_value ) < 128 THEN CAST(((((SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 1, 1 ) = ht.char_value ) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 2, 1 ) = ht.char_value )) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 3, 1 ) = ht.char_value )) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 4, 1 ) = ht.char_value )) AS BIGINT) ELSE CAST(((((SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 1, 1 ) = ht.char_value ) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 2, 1 ) = ht.char_value )) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 3, 1 ) = ht.char_value )) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 4, 1 ) = ht.char_value )) + 1 AS BIGINT) END + CASE WHEN ( SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 5, 1 ) = ht.char_value ) < 128 THEN CAST(((((SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 5, 1 ) = ht.char_value ) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 6, 1 ) = ht.char_value )) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 7, 1 ) = ht.char_value )) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 8, 1 ) = ht.char_value )) AS BIGINT) ELSE CAST(((((SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 5, 1 ) = ht.char_value ) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 6, 1 ) = ht.char_value )) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 7, 1 ) = ht.char_value )) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 8, 1 ) = ht.char_value )) + 1 AS BIGINT) END + CASE WHEN ( SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 9, 1 ) = ht.char_value ) < 128 THEN CAST(((((SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 9, 1 ) = ht.char_value ) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 10, 1 ) = ht.char_value )) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 11, 1 ) = ht.char_value )) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 12, 1 ) = ht.char_value )) AS BIGINT) ELSE CAST(((((SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 9, 1 ) = ht.char_value ) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 10, 1 ) = ht.char_value )) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 11, 1 ) = ht.char_value )) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 12, 1 ) = ht.char_value )) + 1 AS BIGINT) END + CASE WHEN ( SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 13, 1 ) = ht.char_value ) < 128 THEN CAST(((((SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 13, 1 ) = ht.char_value ) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 14, 1 ) = ht.char_value )) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 15, 1 ) = ht.char_value )) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 16, 1 ) = ht.char_value )) AS BIGINT) ELSE CAST(((((SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 13, 1 ) = ht.char_value ) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 14, 1 ) = ht.char_value )) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 15, 1 ) = ht.char_value )) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 16, 1 ) = ht.char_value )) + 1 AS BIGINT) END + CASE WHEN ( SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 17, 1 ) = ht.char_value ) < 128 THEN CAST(((((SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 17, 1 ) = ht.char_value ) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 18, 1 ) = ht.char_value )) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 19, 1 ) = ht.char_value )) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 20, 1 ) = ht.char_value )) AS BIGINT) ELSE CAST(((((SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 17, 1 ) = ht.char_value ) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 18, 1 ) = ht.char_value )) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 19, 1 ) = ht.char_value )) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 20, 1 ) = ht.char_value )) + 1 AS BIGINT) END + CASE WHEN ( SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 21, 1 ) = ht.char_value ) < 128 THEN CAST(((((SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 21, 1 ) = ht.char_value ) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 22, 1 ) = ht.char_value )) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE ' ' = ht.char_value )) * 256 + (SELECT tran_val1 FROM DB2SCHEMA.npt_hash_table_r ht WHERE ' ' = ht.char_value )) AS BIGINT) ELSE CAST(((((SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 21, 1 ) = ht.char_value ) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE SUBSTR(co.contr_id, 22, 1 ) = ht.char_value )) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE ' ' = ht.char_value )) * 256 + (SELECT tran_val2 FROM DB2SCHEMA.npt_hash_table_r ht WHERE ' ' = ht.char_value )) + 1 AS BIGINT) END FROM DB2SCHEMA.npt_contract co )SELECT 'KT~TK '|| cast (sum(hash_num ) as char(20))from temp_hash; Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-03-08 : 15:03:12
|
based on the following..I wonder how often it blows up\par \plain\f4\fs32\b TABLE : npt_hash_table_r \plain\f3\fs30\b \par \par \par \plain\f3\fs32\b STRUCTURE\plain\f3\fs30\b \par \plain\f3\fs20 \par Column Type Type\par name schema name Length Scale Nulls\par ------------------ ----------- ------------------ -------- -------- --------\par CHAR_VALUE SYSIBM CHARACTER 1 0 Yes \par ASCII_VAL SYSIBM INTEGER 4 0 No \par TRAN_VAL1 SYSIBM INTEGER 4 0 Yes \par TRAN_VAL2 SYSIBM INTEGER 4 0 Yes \par \par \pard\plain\f2\fs20 \par \par \plain\f2\fs32\b VALUES \plain\f2\fs20 \par \par \par \par \par CHAR_VALUE ASCII_VAL TRAN_VAL1 TRAN_VAL2 \par ---------- ----------- ----------- -----------\par 32 64 191\par ! 33 90 165\par - 34 127 128\par # 35 123 132\par $ 36 91 164\par % 37 108 147\par & 38 80 175\par ' 39 125 130\par ( 40 77 178 Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-03-08 : 15:27:09
|
quote: And then...maybe this can be used to "Hash" (cough, cough) the DataDo we have a WTF Forum?...snipped ungodly fucking wrong query from quoted post...
This...person...who suggested this, do they write code generators for a living? They should. They absolutely SHOULD NOT be allowed to write SQL. I've barfed on my keyboard and "written" better SQL while mopping it up. Do they know you can select more than one column from the same table in the same SELECT statement? |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-03-09 : 07:06:20
|
so, what.. are you showing us how you took care of this "problem"? http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|