Hello, I have an interesting problem. I have to compare 2 consecutive
records. I'm using Crystal Reports v.10 if that helps. I have a nchar Col4
and a date/time in Col3. I need to compare the date/time of every 2nd of 2
records. The time in the second record of each pair is always a few seconds
,
or a few minutes, greater than in the first record. Col1 and Col2 have
values that are always the same; Col4 varies slightly by the text message.
Any help would be appreciated. Thanks.This would be a self join. For example:
SELECT T1.col1, T1.col2, T1.col3, T1.col4,
MIN(T2.col3)
FROM YourTable AS T1
JOIN YourTable AS T2
ON T1.col3 < T2.col3
GROUP BY T1.col1, T1.col2, T1.col3, T1.col4
David Portas
SQL Server MVP
--|||David,
This query is helpful but I suppose I should have added that I need to CALC
a subtraction of the 1st row's date/timestamp from the 2nd row's
date/timestamp to produce an interval value such as :03 (seconds) etc. Is
this possible? Thanks.
"David Portas" wrote:
> This would be a self join. For example:
> SELECT T1.col1, T1.col2, T1.col3, T1.col4,
> MIN(T2.col3)
> FROM YourTable AS T1
> JOIN YourTable AS T2
> ON T1.col3 < T2.col3
> GROUP BY T1.col1, T1.col2, T1.col3, T1.col4
> --
> David Portas
> SQL Server MVP
> --
>
>|||SELECT T1.col1, T1.col2, T1.col3, T1.col4,
MIN(T2.col3), DATEDIFF(S,T1.col3,MIN(T2.col3))
FROM YourTable AS T1
JOIN YourTable AS T2
ON T1.col3 < T2.col3
GROUP BY T1.col1, T1.col2, T1.col3, T1.col4
David Portas
SQL Server MVP
--|||This was what I needed and it worked great. I marked it a Helpful Post.
Thanks!
"David Portas" wrote:
> SELECT T1.col1, T1.col2, T1.col3, T1.col4,
> MIN(T2.col3), DATEDIFF(S,T1.col3,MIN(T2.col3))
> FROM YourTable AS T1
> JOIN YourTable AS T2
> ON T1.col3 < T2.col3
> GROUP BY T1.col1, T1.col2, T1.col3, T1.col4
> --
> David Portas
> SQL Server MVP
> --
>
>
No comments:
Post a Comment