C
C TO
CREATE TABLE [dbo].[ScheduledTask] (
[WeekNum] [int] NULL ,
[DayNum] [int] NULL ,
[DayName] [char] (30) NULL ,
[PrevCompletedTime] [char] (8) NULL ,
[StartedTime] [char] (8) NULL ,
[PrevCompleted] [char] (30) NULL ,
[Started] [char] (30) NULL ,
[Busy] [char] (30) NULL ,
[Idle] [char] (30) NULL
) ON [PRIMARY]
GO
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,1,'Monday','04:58:40','07:00:15','2004-12-20 04:58:40','2004-12-20 07:00:15','00:00:00','122')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,2,'Tuesday','03:19:56','07:01:10','2004-12-21 03:19:56','2004-12-21 07:01:10','00:00:00','222')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,2,'Tuesday','07:01:10','23:58:55','2004-12-21 07:01:10','2004-12-21 23:58:55','00:00:00','1017')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','02:02:27','06:41:57','2004-12-22 02:02:27','2004-12-22 06:41:57','00:00:00','279')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,4,'Thursday','06:41:57','06:42:40','2004-12-22 06:41:57','2004-12-23 06:42:40','00:00:00','1441')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,5,'Friday','02:51:18','06:45:09','2004-12-24 02:51:18','2004-12-24 06:45:09','00:00:00','234')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','06:45:09','00:01:27','2004-12-24 06:45:09','2004-12-25 00:01:27','00:00:00','1036')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','21:19:52','23:07:19','2004-12-25 21:19:52','2004-12-25 23:07:19','00:00:00','108')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,7,'Sunday','21:19:43','23:07:20','2004-12-26 21:19:43','2004-12-26 23:07:20','00:00:00','108')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,1,'Monday','08:14:50','09:52:05','2004-12-27 08:14:50','2004-12-27 09:52:05','00:00:00','98')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,1,'Monday','21:19:43','22:32:26','2004-12-27 21:19:43','2004-12-27 22:32:26','00:00:00','73')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,2,'Tuesday','08:12:05','09:25:17','2004-12-28 08:12:05','2004-12-28 09:25:17','00:00:00','73')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,2,'Tuesday','21:30:05','23:07:20','2004-12-28 21:30:05','2004-12-28 23:07:20','00:00:00','97')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','07:26:00','08:53:22','2004-12-29 07:26:00','2004-12-29 08:53:22','00:00:00','87')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','16:59:43','17:36:07','2004-12-29 16:59:43','2004-12-29 17:36:07','00:23:51','37')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','16:59:43','17:36:07','2004-12-29 16:59:43','2004-12-29 17:36:07','00:41:43','37')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','21:29:01','23:07:20','2004-12-29 21:29:01','2004-12-29 23:07:20','00:00:00','98')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,4,'Thursday','11:49:32','13:04:25','2004-12-30 11:49:32','2004-12-30 13:04:25','00:00:00','75')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,4,'Thursday','15:04:07','17:10:50','2004-12-30 15:04:07','2004-12-30 17:10:50','11:18:07','126')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,4,'Thursday','21:20:46','22:33:01','2004-12-30 21:20:46','2004-12-30 22:33:01','00:00:00','73')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,5,'Friday','07:54:00','11:07:03','2004-12-31 07:54:00','2004-12-31 11:07:03','00:00:00','193')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,5,'Friday','15:21:49','18:05:32','2004-12-31 15:21:49','2004-12-31 18:05:32','00:00:00','164')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,5,'Friday','21:19:43','23:05:35','2004-12-31 21:19:43','2004-12-31 23:05:35','00:00:00','106')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','07:30:00','10:38:32','2005-01-01 07:30:00','2005-01-01 10:38:32','00:00:00','188')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','15:21:49','18:05:37','2005-01-01 15:21:49','2005-01-01 18:05:37','00:00:00','164')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','21:19:43','22:31:54','2005-01-01 21:19:43','2005-01-01 22:31:54','00:00:00','72')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,7,'Sunday','10:42:21','11:54:23','2005-01-02 10:42:21','2005-01-02 11:54:23','00:00:00','72')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,7,'Sunday','15:21:43','18:05:41','2005-01-02 15:21:43','2005-01-02 18:05:41','00:00:00','164')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,7,'Sunday','21:29:03','23:07:19','2005-01-02 21:29:03','2005-01-02 23:07:19','00:00:00','98')
Given the DDL above and import it into a csv file, I need to generate a
chart using EXCEL that display the idle window based on the following
specs....
Let's say I have a fiscal year that starts on 12/20 and ends on 12/19 the
next year.
My ultimate goal is trying to display the Idle window of a series of
scheduled tasks on each day of the week, on a Excel Spreadsheet. It may have
the look of the MS Project Schedule Task.
My Challenges are:
1. My x-axis may be the DayName (7 values) with only and with minor scale
that contains hour. My y-axis may be grouped by the actual date (14 days),
with the values of the actual prevCompleted and Started (29 series in this
sample). If this is too complicated to accomplish, then my Y-axis may be
group by the 29 series instead of the 14 days. If I must write a query to
iterate my data after 7 days so that the Excel Chart can understand it, how
the query should be written? If that is not neccessary, how?
2. How can I generate weeknum based on the given data?
My ultimate goal of this chart is to identify the idle windows every week.
Many thanks.
[WeekNum] [int] NULL ,
[DayNum] [int] NULL ,
[DayName] [char] (30) NULL ,
[PrevCompletedTime] [char] (8) NULL ,
[StartedTime] [char] (8) NULL ,
[PrevCompleted] [char] (30) NULL ,
[Started] [char] (30) NULL ,
[Busy] [char] (30) NULL ,
[Idle] [char] (30) NULL
) ON [PRIMARY]
GO
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,1,'Monday','04:58:40','07:00:15','2004-12-20 04:58:40','2004-12-20 07:00:15','00:00:00','122')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,2,'Tuesday','03:19:56','07:01:10','2004-12-21 03:19:56','2004-12-21 07:01:10','00:00:00','222')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,2,'Tuesday','07:01:10','23:58:55','2004-12-21 07:01:10','2004-12-21 23:58:55','00:00:00','1017')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','02:02:27','06:41:57','2004-12-22 02:02:27','2004-12-22 06:41:57','00:00:00','279')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,4,'Thursday','06:41:57','06:42:40','2004-12-22 06:41:57','2004-12-23 06:42:40','00:00:00','1441')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,5,'Friday','02:51:18','06:45:09','2004-12-24 02:51:18','2004-12-24 06:45:09','00:00:00','234')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','06:45:09','00:01:27','2004-12-24 06:45:09','2004-12-25 00:01:27','00:00:00','1036')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','21:19:52','23:07:19','2004-12-25 21:19:52','2004-12-25 23:07:19','00:00:00','108')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,7,'Sunday','21:19:43','23:07:20','2004-12-26 21:19:43','2004-12-26 23:07:20','00:00:00','108')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,1,'Monday','08:14:50','09:52:05','2004-12-27 08:14:50','2004-12-27 09:52:05','00:00:00','98')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,1,'Monday','21:19:43','22:32:26','2004-12-27 21:19:43','2004-12-27 22:32:26','00:00:00','73')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,2,'Tuesday','08:12:05','09:25:17','2004-12-28 08:12:05','2004-12-28 09:25:17','00:00:00','73')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,2,'Tuesday','21:30:05','23:07:20','2004-12-28 21:30:05','2004-12-28 23:07:20','00:00:00','97')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','07:26:00','08:53:22','2004-12-29 07:26:00','2004-12-29 08:53:22','00:00:00','87')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','16:59:43','17:36:07','2004-12-29 16:59:43','2004-12-29 17:36:07','00:23:51','37')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','16:59:43','17:36:07','2004-12-29 16:59:43','2004-12-29 17:36:07','00:41:43','37')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','21:29:01','23:07:20','2004-12-29 21:29:01','2004-12-29 23:07:20','00:00:00','98')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,4,'Thursday','11:49:32','13:04:25','2004-12-30 11:49:32','2004-12-30 13:04:25','00:00:00','75')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,4,'Thursday','15:04:07','17:10:50','2004-12-30 15:04:07','2004-12-30 17:10:50','11:18:07','126')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,4,'Thursday','21:20:46','22:33:01','2004-12-30 21:20:46','2004-12-30 22:33:01','00:00:00','73')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,5,'Friday','07:54:00','11:07:03','2004-12-31 07:54:00','2004-12-31 11:07:03','00:00:00','193')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,5,'Friday','15:21:49','18:05:32','2004-12-31 15:21:49','2004-12-31 18:05:32','00:00:00','164')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,5,'Friday','21:19:43','23:05:35','2004-12-31 21:19:43','2004-12-31 23:05:35','00:00:00','106')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','07:30:00','10:38:32','2005-01-01 07:30:00','2005-01-01 10:38:32','00:00:00','188')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','15:21:49','18:05:37','2005-01-01 15:21:49','2005-01-01 18:05:37','00:00:00','164')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','21:19:43','22:31:54','2005-01-01 21:19:43','2005-01-01 22:31:54','00:00:00','72')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,7,'Sunday','10:42:21','11:54:23','2005-01-02 10:42:21','2005-01-02 11:54:23','00:00:00','72')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,7,'Sunday','15:21:43','18:05:41','2005-01-02 15:21:43','2005-01-02 18:05:41','00:00:00','164')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,7,'Sunday','21:29:03','23:07:19','2005-01-02 21:29:03','2005-01-02 23:07:19','00:00:00','98')
Given the DDL above and import it into a csv file, I need to generate a
chart using EXCEL that display the idle window based on the following
specs....
Let's say I have a fiscal year that starts on 12/20 and ends on 12/19 the
next year.
My ultimate goal is trying to display the Idle window of a series of
scheduled tasks on each day of the week, on a Excel Spreadsheet. It may have
the look of the MS Project Schedule Task.
My Challenges are:
1. My x-axis may be the DayName (7 values) with only and with minor scale
that contains hour. My y-axis may be grouped by the actual date (14 days),
with the values of the actual prevCompleted and Started (29 series in this
sample). If this is too complicated to accomplish, then my Y-axis may be
group by the 29 series instead of the 14 days. If I must write a query to
iterate my data after 7 days so that the Excel Chart can understand it, how
the query should be written? If that is not neccessary, how?
2. How can I generate weeknum based on the given data?
My ultimate goal of this chart is to identify the idle windows every week.
Many thanks.