Need help with a complex query

[Scott C]

I have a "audit log" file called tblTransferLog that I am trying to
parse. Each line represents a "transfer" of goods from one site to
another. The majority of rows are self-contained meaning that there is
just one transfer but there can be others where multiple rows show the
sequence of transfers. I am trying to traverse the table with WBTNo,
RecdWBTNo, ShipSite, and RecdSite. The combination of RecdWBTNo and
RecdSite on one row can match the WBTNo and ShipSite of another row
farther down. The stopping point is when the RecdWBTNo and RecdSite no
longer matches any WBTNos and sites farther down. The tricky thing is
that the RecdWBTNo can change from the WBTNo on the same row.

What I am trying to do is group the related rows with some identifier
(possibly the min ID value from the starting row). Most rows will be
just one group as I said but others could have the same group number.
Does anyone know of an elegant way to handle this?


The DDL is as follows along with sample data for 25 rows:

create table dbo.tblTransferLog (
ID int identity(1, 1)
not null,
WBTNo varchar(31) not null,
ShipDate datetime not null,
ShipSite varchar(10) not null,
ShipEntryNo int null,
ShipToSite varchar(10) not null,
RecdWBTNo varchar(31) null,
RecdDate datetime null,
RecdSite varchar(20) null,
RecdEntryNo int null,
constraint PK_tblTransferLog_1 primary key clustered (ID asc)
)


set identity_insert [dbo].[tblTransferLog] on
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (1,
N'2050006505-01',
cast(0x00009F0200698C0A as datetime),
N'205',
183176,
N'206',
N'2050006505-01',
cast(0x00009F0300930AF8 as datetime),
N'206',
184758)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (2,
N'2050006574-01',
cast(0x00009F0200699BE9 as datetime),
N'205',
183177,
N'206',
null,
null,
null,
null)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (3,
N'2050006577-01',
cast(0x00009F020069C897 as datetime),
N'205',
183178,
N'206',
N'2050006577-01',
cast(0x00009F0300930E7C as datetime),
N'206',
184759)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (4,
N'2060002431-01',
cast(0x00009F0400AC9D3B as datetime),
N'206',
186173,
N'205',
N'2060002431-01',
cast(0x00009F0400CD0AA0 as datetime),
N'205',
186385)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (5,
N'2060002425-01',
cast(0x00009F0400ACA584 as datetime),
N'206',
186174,
N'205',
N'2060002425-01',
cast(0x00009F0400CCB3E8 as datetime),
N'205',
186384)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (6,
N'2050006575-01',
cast(0x00009F0400AD2039 as datetime),
N'206',
186175,
N'205',
N'2060002432-01',
cast(0x00009F0400CC7BA8 as datetime),
N'205',
186383)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (7,
N'2060002424-01',
cast(0x00009F0400AD70AD as datetime),
N'206',
186176,
N'205',
N'2060002424-01',
cast(0x00009F0400CD9F74 as datetime),
N'205',
186388)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (8,
N'2060002428-01',
cast(0x00009F0400AE0528 as datetime),
N'206',
186177,
N'205',
N'2060002428-01',
cast(0x00009F0400CD3980 as datetime),
N'205',
186387)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (9,
N'2060002432-01',
cast(0x00009F0400AE4788 as datetime),
N'205',
186178,
N'205',
N'2060002433-01',
cast(0x00009F0400CD1B08 as datetime),
N'205',
186386)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (10,
N'2060002422-01',
cast(0x00009F0400AF2C8A as datetime),
N'206',
186179,
N'205',
N'2060002422-01',
cast(0x00009F0400CAF8F0 as datetime),
N'205',
186382)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (11,
N'2060002430-01',
cast(0x00009F0400AF9598 as datetime),
N'206',
186180,
N'205',
N'2060002430-01',
cast(0x00009F0400CDA8D4 as datetime),
N'205',
186389)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (12,
N'2060002421-01',
cast(0x00009F0400AFF793 as datetime),
N'206',
186181,
N'205',
N'2060002421-01',
cast(0x00009F0400CABF84 as datetime),
N'205',
186381)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (13,
N'2060002420-01',
cast(0x00009F0400B048E5 as datetime),
N'206',
186182,
N'205',
N'2060002420-01',
cast(0x00009F0400CA76DC as datetime),
N'205',
186380)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (14,
N'2060002416-01',
cast(0x00009F0400B0BF2E as datetime),
N'206',
186183,
N'205',
N'2060002416-01',
cast(0x00009F0400C99834 as datetime),
N'205',
186377)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (15,
N'2060002421-01',
cast(0x00009F0400B0C727 as datetime),
N'205',
186184,
N'205',
N'2060002414-01',
cast(0x00009F0400CA1214 as datetime),
N'205',
186379)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (16,
N'2060002417-01',
cast(0x00009F0400B13213 as datetime),
N'206',
186185,
N'205',
N'2060002417-01',
cast(0x00009F0400C8ED1C as datetime),
N'205',
186376)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (17,
N'2060002415-01',
cast(0x00009F0400B13DB8 as datetime),
N'206',
186186,
N'205',
N'2060002415-01',
cast(0x00009F0400C9DD58 as datetime),
N'205',
186378)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (18,
N'2060002414-01',
cast(0x00009F0400B1A5DB as datetime),
N'205',
186187,
N'205',
N'2060002412-01',
cast(0x00009F0400C80190 as datetime),
N'205',
186373)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (19,
N'2060002411-01',
cast(0x00009F0400B1AFAD as datetime),
N'206',
186188,
N'205',
N'2060002411-01',
cast(0x00009F0400C87468 as datetime),
N'205',
186375)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (20,
N'2060002412-01',
cast(0x00009F0400B22753 as datetime),
N'205',
186189,
N'205',
N'2060002410-01',
cast(0x00009F0400C8445C as datetime),
N'205',
186374)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (21,
N'2060002409-01',
cast(0x00009F0400B27314 as datetime),
N'206',
186190,
N'205',
N'2060002409-01',
cast(0x00009F0400C779A0 as datetime),
N'205',
186371)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (22,
N'2060002418-01',
cast(0x00009F0400B2CDDF as datetime),
N'206',
186191,
N'205',
null,
null,
null,
null)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (23,
N'2060002407-01',
cast(0x00009F0400B325D2 as datetime),
N'206',
186192,
N'205',
N'2060002407-01',
cast(0x00009F0400C68838 as datetime),
N'205',
186368)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (24,
N'2060002405-01',
cast(0x00009F0400B32E15 as datetime),
N'206',
186193,
N'205',
N'2060002405-01',
cast(0x00009F0400C706C8 as datetime),
N'205',
186369)
insert [dbo].[tblTransferLog]
([ID],
[WBTNo],
[ShipDate],
[ShipSite],
[ShipEntryNo],
[ShipToSite],
[RecdWBTNo],
[RecdDate],
[RecdSite],
[RecdEntryNo])
values (25,
N'2060002408-01',
cast(0x00009F0400B38D9B as datetime),
N'206',
186194,
N'205',
N'2060002408-01',
cast(0x00009F0400C74BEC as datetime),
N'205',
186370)
set identity_insert [dbo].[tblTransferLog] off

[Erland Sommarskog]

Post by Scott C
I have a "audit log" file called tblTransferLog that I am trying to
parse. Each line represents a "transfer" of goods from one site to
another. The majority of rows are self-contained meaning that there is
just one transfer but there can be others where multiple rows show the
sequence of transfers. I am trying to traverse the table with WBTNo,
RecdWBTNo, ShipSite, and RecdSite. The combination of RecdWBTNo and
RecdSite on one row can match the WBTNo and ShipSite of another row
farther down. The stopping point is when the RecdWBTNo and RecdSite no
longer matches any WBTNos and sites farther down. The tricky thing is
that the RecdWBTNo can change from the WBTNo on the same row.

It's great that you posted table and sample data. However, one piece is
missing: the expected result from this sample.

It could also help if you could clarify what "further down" means. Tables
are unordered themselves unordered, do "down" by what?

Finally, which version of SQL Server are you using?

[Scott C]

Post by Erland Sommarskog

Post by Scott C
I have a "audit log" file called tblTransferLog that I am trying to
parse. Each line represents a "transfer" of goods from one site to
another. The majority of rows are self-contained meaning that there is
just one transfer but there can be others where multiple rows show the
sequence of transfers.  I am trying to traverse the table with WBTNo,
RecdWBTNo, ShipSite, and RecdSite. The combination of RecdWBTNo and
RecdSite on one row can match the WBTNo and ShipSite of another row
farther down. The stopping point is when the RecdWBTNo and RecdSite no
longer matches any WBTNos and sites farther down. The tricky thing is
that the RecdWBTNo can change from the WBTNo on the same row.

It's great that you posted table and sample data. However, one piece is
missing: the expected result from this sample.
It could also help if you could clarify what "further down" means. Tables
are unordered themselves unordered, do "down" by what?
Finally, which version of SQL Server are you using?
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Sorry for the omission Erland. I'm using SQL 2008. The rows in the
table are written in chronological order. So if we assume there are
row numbers present we could say for example that row 6 in the sample
data I provided has '2050006575-01' for WBTNo, 206 for ShipSite,
'2060002432-01' as RecdWBTNo, and 205 as RecdSite. Farther down in
the table at row 9 we have '2060002432-01' for WBTNo and 205 for
ShipSite (note that these match the received values from row 6 so
these rows are associated). The Recd values from this row could then
be associated with another row farther down (say row 112 - I have only
given 25 sample rows but this table will have around 50K rows and will
grow). All I want is an additional field called GrpNum that would have
a common value for these associated rows. Most rows would have only
one distinct GrpNum but others will have common GrpNums that indicate
that they are related. Hopefully this clarifies it.

Scott

[Bob Barrows]

Post by Scott C
Sorry for the omission Erland. I'm using SQL 2008. The rows in the
table are written in chronological order.

The order in which the rows are inserted is irrelevant. Unless you have a
DateAdded column, or an identity column, to order the results by, you can
not reliably achieve chronological order. There is no such thing as "further
down" in a relational database. The RDBMS is free to store the data in any
order it sees fit, even if a table has a clustered index.

[Scott C]

Sorry I should have been more clear. I wasn't referring to physical
ordering of the rows when they get inserted. The date fields such as
ShipDate are a way of ordering the rows. One row's ship date should be
a later datetime than the previous row's ship date. So this is a field
to ORDER BY to get a proper sequence. The ID field is an identity
field and also represents a numeric sequence.

I was originally trying a self-join such as this

select *
from tblTransferLog t1
join tblTransferLog t2 on t1.RecdWBTNo = t2.WBTNo and t1.RecdSite =
t2.ShipSite and t2.ID > t1.ID

This gives me rows back that have more than one sequence but I am not
sure what association the rows within this resultset have. The fact
that a WBTNo (ticket no) can sometimes change when it is received
(RecdWBTNo) makes it hard to do any grouping.

Of course I can always do a brute force method starting with the first
row and traversing the table multiple times but I am looking for a
more elegant way. The ultimate result would be a resultset with an
additional column called GrpNum that would contain a unique value
(most likely an int) that represents the combination of associated
rows.

Post by Bob Barrows

Post by Scott C
Sorry for the omission Erland. I'm using SQL 2008. The rows in the
table are written in chronological order.

The order in which the rows are inserted is irrelevant. Unless you have a
DateAdded column, or an identity column, to order the results by, you can
not reliably achieve chronological order. There is no such thing as "further
down" in a relational database. The RDBMS is free to store the data in any
order it sees fit, even if a table has a clustered index.

[Bob Barrows]

Use a CTE with ROW_NUMBER() to ... wait ... the ticket number can change?
That stumps me. Won't a human have to be involved in grouping these?

Post by Scott C
Sorry I should have been more clear. I wasn't referring to physical
ordering of the rows when they get inserted. The date fields such as
ShipDate are a way of ordering the rows. One row's ship date should be
a later datetime than the previous row's ship date. So this is a field
to ORDER BY to get a proper sequence. The ID field is an identity
field and also represents a numeric sequence.
I was originally trying a self-join such as this
select *
from tblTransferLog t1
join tblTransferLog t2 on t1.RecdWBTNo = t2.WBTNo and t1.RecdSite =
t2.ShipSite and t2.ID > t1.ID
This gives me rows back that have more than one sequence but I am not
sure what association the rows within this resultset have. The fact
that a WBTNo (ticket no) can sometimes change when it is received
(RecdWBTNo) makes it hard to do any grouping.
Of course I can always do a brute force method starting with the first
row and traversing the table multiple times but I am looking for a
more elegant way. The ultimate result would be a resultset with an
additional column called GrpNum that would contain a unique value
(most likely an int) that represents the combination of associated
rows.

Post by Bob Barrows

Post by Scott C
Sorry for the omission Erland. I'm using SQL 2008. The rows in the
table are written in chronological order.

The order in which the rows are inserted is irrelevant. Unless you
have a DateAdded column, or an identity column, to order the results
by, you can not reliably achieve chronological order. There is no
such thing as "further down" in a relational database. The RDBMS is
free to store the data in any order it sees fit, even if a table has
a clustered index.

[Scott C]

Yes the Ticket Number can change - it is rare but I need to be able to
account for it. The logic is fairly simple...match the RecdWBTNo and
RecdSite of one row with the WBTNo and ShipSite of another row with a
later shipdate (or ID) than the initial row. If we find a match get
that row's RecdWBTNo and RecdSite and repeat until no more rows will
match. Then we can assume that we have found the final "transfer". I'm
thinking brute force is the only way to go with this. If anyone can
suggest another way please let me know.

[rpresser]

This sounds like it might be a different application of the adjacency model of hierarchies that Celko is always promoting.

[Erland Sommarskog]

Post by Scott C
Sorry for the omission Erland. I'm using SQL 2008. The rows in the
table are written in chronological order. So if we assume there are
row numbers present we could say for example that row 6 in the sample
data I provided has '2050006575-01' for WBTNo, 206 for ShipSite,
'2060002432-01' as RecdWBTNo, and 205 as RecdSite. Farther down in
the table at row 9 we have '2060002432-01' for WBTNo and 205 for
ShipSite (note that these match the received values from row 6 so
these rows are associated). The Recd values from this row could then
be associated with another row farther down (say row 112 - I have only
given 25 sample rows but this table will have around 50K rows and will
grow). All I want is an additional field called GrpNum that would have
a common value for these associated rows. Most rows would have only
one distinct GrpNum but others will have common GrpNums that indicate
that they are related. Hopefully this clarifies it.

This is my interpretation of the above.

; WITH CTE AS (
SELECT a.ID as GrpNum, a.*
FROM tblTransferLog a
WHERE NOT EXISTS (SELECT *
FROM tblTransferLog b
WHERE a.WBTNo = b.RecdWBTNo
AND a.ShipSite = b.RecdSite)
UNION ALL
SELECT CTE.GrpNum, new.*
FROM tblTransferLog new
JOIN CTE ON CTE.RecdWBTNo = new.WBTNo
AND CTE.RecdSite = new.ShipSite
)
SELECT *
FROM CTE
ORDER BY GrpNum, ID


Well, I did not follow it to the letter, but I ignored "further down",
because tables does not have order. Using the ID value would be crazy.
Who says the rows were inserted in the very order that the events
occurred.

What could make sense is to add conditions based on the dates. But
here is a mystery. For the rows that constitute a chain, for instance
6 and 9, RecdDate on row 6 is later than ShipDate on row 9.

[Scott C]

Post by Erland Sommarskog

Post by Scott C
Sorry for the omission Erland. I'm using SQL 2008. The rows in the
table are written in chronological order. So if we assume there are
row numbers present we could say for example that row 6 in the sample
data I provided has '2050006575-01' for WBTNo, 206 for ShipSite,
'2060002432-01' as RecdWBTNo, and 205 as RecdSite.  Farther down in
the table at row 9 we have '2060002432-01' for WBTNo and 205 for
ShipSite (note that these match the received values from row 6 so
these rows are associated). The Recd values from this row could then
be associated with another row farther down (say row 112 - I have only
given 25 sample rows but this table will have around 50K rows and will
grow). All I want is an additional field called GrpNum that would have
a common value for these associated rows. Most rows would have only
one distinct GrpNum but others will have common GrpNums that indicate
that they are related. Hopefully this clarifies it.

This is my interpretation of the above.
; WITH CTE AS (
   SELECT a.ID as GrpNum, a.*
   FROM   tblTransferLog a
   WHERE  NOT EXISTS (SELECT *
                      FROM   tblTransferLog b
                      WHERE  a.WBTNo    = b.RecdWBTNo
                        AND  a.ShipSite = b.RecdSite)
   UNION  ALL
   SELECT CTE.GrpNum, new.*
   FROM   tblTransferLog new
   JOIN   CTE ON CTE.RecdWBTNo = new.WBTNo
             AND CTE.RecdSite  = new.ShipSite
)
SELECT *
FROM   CTE
ORDER  BY GrpNum, ID
Well, I did not follow it to the letter, but I ignored "further down",
because tables does not have order. Using the ID value would be crazy.
Who says the rows were inserted in the very order that the events
occurred.
What could make sense is to add conditions based on the dates. But
here is a mystery. For the rows that constitute a chain, for instance
6 and 9, RecdDate on row 6 is later than ShipDate on row 9.
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

That could have been a mistake on my part. I was trying to mock up
sample data for this example. RecdDate on row 6 should be earlier than
Row 9's ship date. Thanks for working this out - I will try and test it

[Bob Barrows]

Post by Scott C
That could have been a mistake on my part. I was trying to mock up
sample data for this example. RecdDate on row 6 should be earlier than
Row 9's ship date. Thanks for working this out - I will try and test it

Darn, I didn't realize you provided sample data. Your initial post was not
in my newsreader when I cam across this thread, so all I was going by was
Erland's reply, in which he appropriately snipped it. Looks like you're good
to go.