Getting missing date in transaction table.

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Getting missing date in transaction table.

Author

Topic

wailoon.ho
Starting Member

2 Posts

Posted - 2011-02-08 : 03:47:18

Hi,
I need to write a SP for report purpose.
In this report, I need to retrieve the TRACK that not use for more or equal to 72 hours (let call it “Inactive > 72H” continuous.
Below are the sample table data.
Transaction_ID Branch_ID Track_ID Start_Timestamp
1 B1 T1 01/01/2011
2 B1 T1 02/01/2011
3 B1 T1 03/01/2011
4 B1 T1 06/01/2011
5 B1 T1 07/01/2011
6 B1 T1 11/01/2011
7 B1 T1 12/01/2011

Using the sample above, I am manage to get 2 for “Inactive > 72H”. But actually the result should be 3. Reason is from 13/01/2011 until month end (31/01/2011) is more than 72 hours.
But I’m not able to get 3 because I don’t have the next date to compare with the last transaction date 12/01/2011.
How I did the SP is, I insert into a temp table where contain current data and the 4 dates before current date, something like below.
Transaction_ID Branch_ID Track_ID Start_Timestamp Date1 Date2 Date3 Date4 Missing
1 B1 T1 01/01/2011 31/12/2010 NULL 29/12/2010 28/12/2010 N
2 B1 T1 02/01/2011 01/01/2011 31/12/2010 NULL 20/12/2010 N
3 B1 T1 03/01/2011 02/01/2011 01/01/2011 31/12/2010 NULL N
4 B1 T1 07/01/2011 NULL NULL NULL 03/01/2011 Y
5 B1 T1 11/01/2011 NULL NULL NULL 07/01/2011 Y
6 B1 T1 12/01/2011 11/01/2011 NULL NULL NULL N
7 B1 T1 13/01/2011 12/01/2011 11/01/2011 NULL NULL N
You can see Missing = ‘Y’ for Transaction ID 4 and 5. Reason is before the date, there are 72 hours no transactions. So I can get count of 2.
But actually the result should be 3 cause 13/01/2011 to 31/01/2011 is missing also. But the problem is, I do not have the next transaction date after 13/01/2011.
This also happened if the whole month no transaction. If whole month no transaction, I should get 31 or 30 or 28 or something like that. But I got 0.
I know is my logic problem, but I really don’t know how to fix this. I hope someone can help me on this issue.

sundar_venkat
Starting Member

2 Posts

Posted - 2011-02-09 : 21:26:03

Try something like, as I am new to Sqlserver syntax

Select a.* from t1 a where exists ( select 1 from t1 b where
DATEDIFF(day, a. Start_Timestamp, b. Start_Timestamp) > 3
and b. Start_Timestamp = (select min(c.Start_Timestamp) from t1 c where c. Start_Timestamp > a. Start_Timestamp)
Union
Select a.* from t1 a where DATEDIFF(day, a. Start_Timestamp, GETDATE ( )) >= 3
And a.start_timestamp ( select max(Start_Timestamp) from t1)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-11 : 03:20:20

quote:
Originally posted by sundar_venkat

Try something like, as I am new to Sqlserver syntax

Select a.* from t1 a where exists ( select 1 from t1 b where
DATEDIFF(day, a. Start_Timestamp, b. Start_Timestamp) > 3
and b. Start_Timestamp = (select min(c.Start_Timestamp) from t1 c where c. Start_Timestamp > a. Start_Timestamp)
Union
Select a.* from t1 a where DATEDIFF(day, a. Start_Timestamp, GETDATE ( )) >= 3
And a.start_timestamp ( select max(Start_Timestamp) from t1)

You are missing = sign

Second select statement should be

Select a.* from t1 a where DATEDIFF(day, a. Start_Timestamp, GETDATE ( )) >= 3
And a.start_timestamp= ( select max(Start_Timestamp) from t1)

Madhivanan

Failing to plan is Planning to fail

wailoon.ho
Starting Member

2 Posts

Posted - 2011-02-11 : 04:07:25

I found another solution.
WITH SR1 AS
(
SELECT Transaction_ID, Track_ID, Start_Timestamp
FROM Track_Transaction
WHERE (CONVERT(VARCHAR(10), Start_Timestamp, 102) >= DATEADD(DAY, 0, @TimestampStart)
AND CONVERT(VARCHAR(10), Start_Timestamp, 102) <= DATEADD(HOUR, -1, DATEADD(DAY, 1, @TimestampEnd)))
)
SELECT SR1.Transaction_ID AS Previous_Transaction_ID, SR1.Track_ID AS Previous_Track_ID, SR1.Start_Timestamp AS Previous_Start_Timestamp,
SR2.Transaction_ID AS Next_Transaction_ID, SR2.Track_ID AS Next_Track_ID, SR2.Start_Timestamp AS Next_Start_Timestamp,
DATEDIFF(Hour, SR1.Start_Timestamp, SR2.Start_Timestamp) AS MissingHours
FROM SR1
CROSS APPLY
(
SELECT TOP 1 Transaction_ID, Branch_ID, Track_ID, Start_Timestamp
FROM Track_Transaction
WHERE Track_Transaction.Track_ID = SR1.Track_ID
AND Track_Transaction.Start_Timestamp > SR1.Start_Timestamp
AND (CONVERT(VARCHAR(10), Start_Timestamp, 102) >= DATEADD(DAY, 0, @TimestampStart)
AND CONVERT(VARCHAR(10), Start_Timestamp, 102) <= DATEADD(HOUR, -1, DATEADD(DAY, 1, @TimestampEnd)))
ORDER BY Track_Transaction.Stock_Timestamp ASC
) SR2

The result will be something like this
Previous_Transaction_ID Previous_Track_ID Previous_Start_Timestamp Next_Transaction_ID Next_Track_ID Next_Start_Timestamp MissingHours
1 T1 01/01/2011 05:00 2 T1 02/01/2011 05:00 24
2 T1 02/01/2011 05:00 3 T1 03/01/2011 05:00 24
3 T1 03/01/2011 05:00 4 T1 06/01/2011 05:00 72
4 T1 06/01/2011 05:00 5 T1 11/01/2011 05:00 120
5 T1 11/01/2011 05:00 6 T1 13/01/2011 05:00 48

By using these result, I can check whether the Track is missing in action or not.

But anyway, thanks for the reply.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources