November 19, 2014 at 8:39 pm
We extract 10k tables every night and I have a table that keeps track of ETL tables that fail or succeed.
I would like to know if a table fails during the night and nobody kicks off another job to fix it during the day.
The table structure looks like this:
| Table_Name | Time_Start | Status | Duration | Time_End |
Table_Name = varchar(20)
Time_Start = DateTime
Status varchar(7) = Success or Error
Duration = Number
Time_End = DateTime
Select Table_Name into #MyTempTable
From ETL.STATS_Table
Where Status = 'Error'
AND Cast(Time_Start as Date) = GetDate()
Here's where I need your help. How do I take the table names from #MyTempTable
and find out if they where successful for the same date? Duration time and Time_End fields aren't needed.
November 20, 2014 at 7:05 am
So that means my question is really complicated or really stupid....
November 20, 2014 at 7:26 am
The name of getdate() is somewhat misleading as it returns datetime. For your WHERE clause to work you will have to replace getdate() by cast(getdate() as date).
November 20, 2014 at 8:22 am
For datetime columns, you should use a range to prevent the use of functions in your column. You might also want to have a computed column either for duration or time_end.
Here's an example of these recommendations.
CREATE TABLE STATS_Table(
Table_Name varchar(20),
Time_Start DateTime,
Status varchar(7),-- = Success or Error
Duration int,
Time_End AS DATEADD(minute, Duration, Time_Start));
INSERT STATS_Table VALUES
('Table 1', '20141120 03:12:15', 'Success', 12),
('Table 2', '20141120 04:12:15', 'Error', 15),
('Table 3', '20141120 05:12:15', 'Success', 32),
('Table 4', '20141120 06:12:15', 'Error', 27),
('Table 5', '20141120 07:12:15', 'Success', 5);
Select *
--into #MyTempTable
From STATS_Table
Where Status = 'Error'
AND Time_Start >= DATEADD( dd, DATEDIFF( dd, 0, GETDATE()), 0)
AND Time_Start <= GETDATE()
DROP TABLE STATS_Table
November 22, 2014 at 4:07 am
I apologize that my statement confused the result I was looking for. How can I find the tables
below that failed and were never successful for the same day? Some tables may succeed or fail
more than once on the same day.
/********* This is the table ***********/
|TABLE_NAME | STATUS | START_TIME |
Table1 fail 2014-11-22 02:05:00.000
Table1 success2014-11-22 16:51:00.000
Table2 success2014-11-22 05:59:00.000
Table2 success2014-11-23 05:51:00.000
Table3 fail 2014-11-22 05:30:00.000
Table3 success 2014-11-23 06:50:00.000
Table1 success2014-11-22 18:51:00.000
/*********** End table ***************/
Now how do I find out if those two entries Table1 and Table3 were later successful for 11/22/2014?
November 22, 2014 at 8:00 am
smitty-1088185 (11/22/2014)
How can I find the tablesbelow that failed and were never successful for the same day? Some tables may succeed or fail
more than once on the same day.
Now how do I find out if those two entries Table1 and Table3 were later successful for 11/22/2014?
This is A way of doing this.
** NOTES **
This is not the most efficient way to do this but should be enough to get you pointed in the right direction.
This works for the dataset provided.
See how the DDL is formated for easy consumption for future posts!!
declare @t1 table
(
Table_Name varchar(20)
,Import_Status varchar(7)
,Start_Time datetime
)
Insert @t1 (Table_Name, Import_Status, Start_Time) VALUES
('Table1', 'fail', '2014-11-22 02:05:00.000')
,('Table1', 'success', '2014-11-22 16:51:00.000')
,('Table2', 'success', '2014-11-22 05:59:00.000')
,('Table2', 'success', '2014-11-23 05:51:00.000')
,('Table3', 'fail', '2014-11-22 05:30:00.000')
,('Table3', 'success', '2014-11-23 06:50:00.000')
,('Table1', 'success', '2014-11-22 18:51:00.000');
select Table_Name, CAST(start_time as DATE) RunDate
,SUM(case when import_status = 'success' then 1 else 0 end) successRuns
,SUM(case when import_status = 'fail' then 1 else 0 end) failedRuns
from @t1
group by Table_Name, cast(Start_Time as date)
order by Table_Name
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply