December 20, 2011 at 10:02 am
I have trolled through the date, business days etc searches and haven't really fond the solution for my dilemma.
I have a table with workflow records in, (workflow) this has uniqueid, user, startdate, enddate (which could be null as not ended yet)
I also have a Calendar table holding dates and if they are business days or not (True or False). The table has CalendarDate, BusinessDay, NonBusinessDay amongst other fields.
The need - To create a report of the days it took to complete the workflow.
I have attempted to create a view to show workflow.uniqeid, workflow.user, workflow.startdate, workflow.enddate, duration. Where duration is the count of days between workflow.startdate and workflow.enddates from Calendar.CalendarDate where Calendar.BusinessDay is True.
I have not managed to do this.
The view is to be used by Visual Studio to create a report.
Is there any other way to get this done? I am not allowed to touch the workflow table but can pretty much do anything else. I am no expert on stored proc's and the like.
Any help would be very much appreciated and apologies for raising the subject once again when so much already exists on similar subject.
Thanks
Steve
December 20, 2011 at 10:08 am
December 20, 2011 at 1:23 pm
Apologies for the non rtfm post previously, hopefully this helps. Please feel free to guide me to the error of my ways if anything is wrong below. Hopefully the first part of my post describes the preferred output.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Workflows','U') IS NOT NULL
DROP TABLE #Workflows
--===== To create the workflow table
CREATE TABLE [dbo].[Workflows](
[workflowId] [uniqueidentifier] NOT NULL,
[workflowTypeId] [uniqueidentifier] NOT NULL,
[started] [datetime] NOT NULL CONSTRAINT [DF_Workflows_started] DEFAULT (getdate()),
[ended] [datetime] NULL,
[outcome] [nvarchar](50) NULL,
[agentName] [nvarchar](70) NULL,
CONSTRAINT [PK_Workflows] PRIMARY KEY CLUSTERED )
--===== If the calendar table already exists, drop it
IF OBJECT_ID('TempDB..#Calendar','U') IS NOT NULL
DROP TABLE #Calendar
--===== To create the Calendar table
CREATE TABLE [dbo].[Calendar](
[CalendarDate] [datetime] NOT NULL,
[CalendarYear] [int] NOT NULL,
[CalendarMonth] [int] NOT NULL,
[CalendarDay] [int] NOT NULL,
[DayOfWeekName] [varchar](10) NOT NULL,
[FirstDateOfWeek] [datetime] NOT NULL,
[LastDateOfWeek] [datetime] NOT NULL,
[FirstDateOfMonth] [datetime] NOT NULL,
[LastDateOfMonth] [datetime] NOT NULL,
[FirstDateOfQuarter] [datetime] NOT NULL,
[LastDateOfQuarter] [datetime] NOT NULL,
[FirstDateOfYear] [datetime] NOT NULL,
[LastDateOfYear] [datetime] NOT NULL,
[BusinessDay] [bit] NOT NULL,
[NonBusinessDay] [bit] NOT NULL,
[Weekend] [bit] NOT NULL,
[Holiday] [bit] NOT NULL,
[Weekday] [bit] NOT NULL,
[CalendarDateDescription] [varchar](50) NULL,
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED )
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Workflow ON
--===== Insert the test data into the Workflow table
INSERT INTO #Workflow
(workflowId, workflowTypeId, started, ended, outcome, agentName)
SELECT '9582FDD9-9C7E-4AEA-A2A9-0105736ABD45','2A77C165-55E8-43EE-AC55-338523BF3B03','Sep 16 2011 10:06AM','Sep 16 2011 10:06AM','Completed','System Administrator Account' UNION ALL
SELECT 'E9ACE36E-8DB3-44D8-B55B-011F7299920D','E166C4C0-4464-40DA-940E-7C7D15027338','Sep 16 2011 12:04PM','Sep 16 2011 12:59PM','Completed','System Administrator Account' UNION ALL
SELECT '4D9E4407-A178-4302-AFD9-02BEA6FE26DB','E166C4C0-4464-40DA-940E-7C7D15027338','Sep 19 2011 1:19PM','Sep 19 2011 1:21PM','Completed','System Administrator Account' UNION ALL
SELECT '9F32C4CC-DCC2-4824-8D7D-048715FABC24','3BF547D9-629F-4CCA-8563-F6E95F75F56F','Sep 15 2011 12:39PM','Sep 15 2011 12:41PM','Completed','System Administrator Account' UNION ALL
SELECT 'F74F24E6-D3A4-4E71-AAC3-04D0E5470A48','0465DFEA-0A74-4D3A-8EC1-DC7203122BF2','Sep 15 2011 12:12PM','Sep 15 2011 12:13PM','Completed','System Administrator Account' UNION ALL
SELECT '523F0AE4-9A4E-4EB7-83FE-09E7CEBFF8AF','07BBDB57-5021-4A88-A02D-844413922762','Sep 15 2011 2:47PM','Sep 15 2011 2:48PM','Completed','System Administrator Account' UNION ALL
SELECT '7EDE9FF0-84D4-4C52-8B6F-0F058F4A35F5','E007484B-59E2-4556-AFFA-6ADEA4B2581B','Sep 15 2011 12:47PM','Sep 15 2011 12:48PM','Completed','System Administrator Account' UNION ALL
SELECT 'B203CC15-3FF0-461F-A7EE-20BA9DD6DD37','97592063-1911-4941-852B-8830DC39B6D3','Dec 14 2011 12:17PM','Dec 14 2011 12:41PM','Completed','System Administrator Account' UNION ALL
SELECT '99C44839-6674-46F4-BFFB-2127EB413F11','AA20CEB1-8003-4BD3-BE44-AE1E62A7AB9B','Sep 20 2011 11:56AM','Sep 20 2011 11:56AM','Completed','System Administrator Account' UNION ALL
SELECT '72EC502D-E2DF-41E1-A750-2163A49C2199','E166C4C0-4464-40DA-940E-7C7D15027338','Sep 23 2011 1:28PM','Sep 23 2011 1:33PM','Completed','System Administrator Account'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Workflow ON
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Calendar ON
--===== Insert the test data into the Calendar table
INSERT INTO #Calendar
(CalendarDate,CalendarYear,CalendarMonth,CalendarDay,DayOfWeekName,FirstDateOfWeek,
LastDateOfWeek,FirstDateOfMonth,LastDateOfMonth,FirstDateOfQuarter,LastDateOfQuarter,
FirstDateOfYear,LastDateOfYear,BusinessDay,NonBusinessDay,Weekend,Holiday,Weekday,
CalendarDateDescription)
SELECT 'Sep 1 2011 12:00AM','2011','9','1','Thursday','Aug 28 2011 12:00AM','Sep 3 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 2 2011 12:00AM','2011','9','2','Friday','Aug 28 2011 12:00AM','Sep 3 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 3 2011 12:00AM','2011','9','3','Saturday','Aug 28 2011 12:00AM','Sep 3 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','0','1','1','0','0' UNION ALL
SELECT 'Sep 4 2011 12:00AM','2011','9','4','Sunday','Sep 4 2011 12:00AM','Sep 10 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','0','1','1','0','0' UNION ALL
SELECT 'Sep 5 2011 12:00AM','2011','9','5','Monday','Sep 4 2011 12:00AM','Sep 10 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 6 2011 12:00AM','2011','9','6','Tuesday','Sep 4 2011 12:00AM','Sep 10 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 7 2011 12:00AM','2011','9','7','Wednesday','Sep 4 2011 12:00AM','Sep 10 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 8 2011 12:00AM','2011','9','8','Thursday','Sep 4 2011 12:00AM','Sep 10 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 9 2011 12:00AM','2011','9','9','Friday','Sep 4 2011 12:00AM','Sep 10 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 10 2011 12:00AM','2011','9','10','Saturday','Sep 4 2011 12:00AM','Sep 10 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','0','1','1','0','0' UNION ALL
SELECT 'Sep 11 2011 12:00AM','2011','9','11','Sunday','Sep 11 2011 12:00AM','Sep 17 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','0','1','1','0','0' UNION ALL
SELECT 'Sep 12 2011 12:00AM','2011','9','12','Monday','Sep 11 2011 12:00AM','Sep 17 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 13 2011 12:00AM','2011','9','13','Tuesday','Sep 11 2011 12:00AM','Sep 17 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 14 2011 12:00AM','2011','9','14','Wednesday','Sep 11 2011 12:00AM','Sep 17 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 15 2011 12:00AM','2011','9','15','Thursday','Sep 11 2011 12:00AM','Sep 17 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 16 2011 12:00AM','2011','9','16','Friday','Sep 11 2011 12:00AM','Sep 17 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 17 2011 12:00AM','2011','9','17','Saturday','Sep 11 2011 12:00AM','Sep 17 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','0','1','1','0','0' UNION ALL
SELECT 'Sep 18 2011 12:00AM','2011','9','18','Sunday','Sep 18 2011 12:00AM','Sep 24 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','0','1','1','0','0' UNION ALL
SELECT 'Sep 19 2011 12:00AM','2011','9','19','Monday','Sep 18 2011 12:00AM','Sep 24 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 20 2011 12:00AM','2011','9','20','Tuesday','Sep 18 2011 12:00AM','Sep 24 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 21 2011 12:00AM','2011','9','21','Wednesday','Sep 18 2011 12:00AM','Sep 24 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 22 2011 12:00AM','2011','9','22','Thursday','Sep 18 2011 12:00AM','Sep 24 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 23 2011 12:00AM','2011','9','23','Friday','Sep 18 2011 12:00AM','Sep 24 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 24 2011 12:00AM','2011','9','24','Saturday','Sep 18 2011 12:00AM','Sep 24 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','0','1','1','0','0' UNION ALL
SELECT 'Sep 25 2011 12:00AM','2011','9','25','Sunday','Sep 25 2011 12:00AM','Oct 1 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','0','1','1','0','0' UNION ALL
SELECT 'Sep 26 2011 12:00AM','2011','9','26','Monday','Sep 25 2011 12:00AM','Oct 1 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 27 2011 12:00AM','2011','9','27','Tuesday','Sep 25 2011 12:00AM','Oct 1 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 28 2011 12:00AM','2011','9','28','Wednesday','Sep 25 2011 12:00AM','Oct 1 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 29 2011 12:00AM','2011','9','29','Thursday','Sep 25 2011 12:00AM','Oct 1 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1' UNION ALL
SELECT 'Sep 30 2011 12:00AM','2011','9','30','Friday','Sep 25 2011 12:00AM','Oct 1 2011 12:00AM','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','Jul 1 2011 12:00AM','Sep 30 2011 12:00AM','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','0','0','0','1'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Calendar ON
December 20, 2011 at 1:26 pm
Do you just need the count of workdays, or do you need a list of them, or something else?
If it's a count of them, then the query would look something like this:
select *,
(select count(*)
from dbo.MyCalendarTable
where Workday = 1
and CalendarDate between MyWorkflowTable.StartDate and MyWorkflowTable.EndDate) as WorkDays
from dbo.MyWorkflowTable ;
Note that this doesn't take into account partial days, et al. Just the raw number of days.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 21, 2011 at 3:21 am
That is exactly what i wanted. Brilliant! Thanks for all the help.
December 21, 2011 at 6:33 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2012 at 6:15 am
Sorry to re-start this one as all I requested is now working but I have just had an added twist thrust upon me.
The number of business days returned relates to an SLA target date and I don't know how to figure out the target date based on the SLA days in business days.
What I basically need is workflow.started + SLA.sladays but counting only business days
Hopefully you can once again help
Below are the scripts to get the SLA data in. (Hope they are correct)
--===== To create the workflow SLA table
CREATE TABLE [dbo].[SLA](
[workflowTypeId] [uniqueidentifier] NOT NULL,
[sladays] [nchar](10) NULL,
CONSTRAINT [PK_SLA] PRIMARY KEY CLUSTERED )
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #SLA ON
--===== Insert the test data into the Workflow SLA table
INSERT INTO #SLA
(workflowTypeId, sladays)
SELECT '2A77C165-55E8-43EE-AC55-338523BF3B03','10' UNION ALL
SELECT 'E166C4C0-4464-40DA-940E-7C7D15027338','12' UNION ALL
SELECT 'E166C4C0-4464-40DA-940E-7C7D15027338','20' UNION ALL
SELECT '3BF547D9-629F-4CCA-8563-F6E95F75F56F','15' UNION ALL
SELECT '0465DFEA-0A74-4D3A-8EC1-DC7203122BF2','5' UNION ALL
SELECT '07BBDB57-5021-4A88-A02D-844413922762','10' UNION ALL
SELECT 'E007484B-59E2-4556-AFFA-6ADEA4B2581B','10' UNION ALL
SELECT '97592063-1911-4941-852B-8830DC39B6D3','5' UNION ALL
SELECT 'AA20CEB1-8003-4BD3-BE44-AE1E62A7AB9B','15' UNION ALL
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #SLA ON
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply