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 |
AhmetEmre90
Starting Member
5 Posts |
Posted - 2013-02-20 : 06:11:39
|
Hi;I have two SQL tables like below;[CODE]T1: relationID, meterID, parentID, childIDT2: dataID, meterID, date, amount[/CODE]Sample data of tables;[CODE] T1 T2----------------- -------------------------1 | 1 | null | 2 * 1 | 1 | 01,01,2013 | 100 *2 | 1 | null | 3 * 2 | 2 | 01,01,2013 | 60 *3 | 2 | 1 | 4 3 | 3 | 01,01,2013 | 40 *4 | 2 | 1 | 5 4 | 4 | 01,01,2013 | 355 | 3 | 1 | 6 5 | 5 | 01,01,2013 | 256 | 3 | 1 | 7 6 | 6 | 01,01,2013 | 157 | 4 | 2 | null 7 | 7 | 01,01,2013 | 258 | 5 | 2 | null9 | 6 | 3 | null 10 | 7 | 3 | null [/CODE]I want to compare if the sum of the children's amount is equal to parent's amount.For example; meter1 is parent of meter2 and meter3 (lines with *). I want to check if 100 = 60 + 40.How can i do this with SQL query.You can see the relation between the meters at the image below. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-20 : 08:06:35
|
This can be done using recursive CTE's. But, looking at your data, I was unable to relate the picture with the data in Table1. In the picture it looks like relation = 1 is the root node. Yet in T1, it looks like that is not the case. Which column in T1 identifies a row as belonging to a node in the picture? Or may be I am interpreting that table incorrectly? |
|
|
AhmetEmre90
Starting Member
5 Posts |
Posted - 2013-02-20 : 08:22:35
|
Numbers at the picture are the meterID's.http://ahmetemremermer.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-20 : 08:34:19
|
see example herehttp://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|