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 |
|
jayram11
Yak Posting Veteran
97 Posts |
 Posted - 2010-08-25 : 14:33:49
|
| Helloi have 11 tables A1 through A11 and all the tables have the same fields.i want to find out how many B (field) has changed for A and A is primary key from A1 through A11 tableseg:) select a.b, b.b,A.a,B.a, count(a.b) as counts from A1 as ainner join A2 as bon a.a = b.awhere a.b <> b.bgroup by a.b, b.b,A.a,B.aorder by countsthe above script gives me what i need but i have to compare each table with one another to find out the changes in field B. Is there a way to join all the 11 tables and get the result and most importantly to see when B has changed. eg:) if for 1 record, B remained same in A1 through A6 and changed in A7 and remained the same until A11. i want to flag it to say it changed in A7Thnx |
|
|
Llewellyn
Starting Member
13 Posts |
Posted - 2010-08-26 : 09:24:55
|
| Not sure what you are looking for but try puting your data into one table and working with it by join to to itself.select * into #AllDataFROM (select 1 as Tbl, A,B From A1 union all select 2 as Tbl, A,BFrom A2.....union all select 11 as Tbl, A,BFrom A11) z select a.Tbl,a.b, b.b,A.a,B.a, count(a.b) as countsFROM #AllData a join #AllData b ON a.a = b.a AND a.Tbl = b.Tbl - 1WHERE a.b <> b.bNot everything above is in place but it should give you an idea |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-08-26 : 09:40:09
|
What if A=1 and B=1 in table A1? And A=1 doesn't exist in A2, what to report then?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
jayram11
Yak Posting Veteran
97 Posts |
Posted - 2010-08-30 : 16:08:15
|
| It always exist in all tables. Thanks for asking. |
|
|
|
|
|
|
|
|
Multiple Inner joins