Combining SQL Rows
- by lumberjack4
I've got SQL Compact Database that contains a table of IP Packet Headers. The Table looks like this:
Table: PacketHeaders
ID SrcAddress SrcPort DestAddress DestPort Bytes
1 10.0.25.1 255 10.0.25.50 500 64
2 10.0.25.50 500 10.0.25.1 255 80
3 10.0.25.50 500 10.0.25.1 255 16
4 75.48.0.25 387 74.26.9.40 198 72
5 74.26.9.40 198 75.48.0.25 387 64
6 10.0.25.1 255 10.0.25.50 500 48
I need to perform a query to show 'conversations' going on across a local network. Packets going from A - B is part of the same conversations as packets going from B - A. I need to perform a query to show the on going conversations. Basically what I need is something that looks like this:
Returned Query:
SrcAddress SrcPort DestAddress DestPort TotalBytes BytesA->B BytesB->A
10.0.25.1 255 10.0.25.50 500 208 112 96
75.48.0.25 387 74.26.9.40 198 136 72 64
As you can see I need the query (or series of queries) to recognize that A-B is the same as B-A and break up the byte counts accordingly. I'm not a SQL guru by any means but any help on this would be greatly appreciated.
