Not in statement not working - SQL Server Forums

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)

Not in statement not working

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-09-07 : 06:14:28

Hello,

Can anyone help with this one?

The incident table and the task table both have a OwnerTeam field, I want to make sure the OwnerTeam from the Task table does not return any records based on the Not In clause in the query below...I thought it was working but it is using the OwnertTeam from the incident table not Task. I've changed the OwnerTeam = TaskOwnerTeam but I just need to alter the query to make sure I can for one see TaskOwnerTeam in the results set and to make sure it does not contain the teams in the Not In statement.

Can you help?

Thanks

ALTER PROCEDURE [dbo].[GetCallDuration]

--@Start datetime,
--@End datetime

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @workDays table (
dw int,
workBegin datetime,
workEnd datetime
)

Declare @holidays table (
holidate datetime,
holidayName varchar(100)
)

--exclude all times outside 8am - 8pm
Insert Into @workDays Select 2, '4/11/2011 08:00:00.000', '4/11/2011 18:00:00.000' --Monday 8am-8pm
Insert Into @workDays Select 3, '4/12/2011 08:00:00.000', '4/12/2011 18:00:00.000' --Tuesday 8am-8pm
Insert Into @workDays Select 4, '4/13/2011 08:00:00.000', '4/13/2011 18:00:00.000' --Wednesday 8am-8pm
Insert Into @workDays Select 5, '4/14/2011 08:00:00.000', '4/14/2011 18:00:00.000' --Thurdays 8am-8pm
Insert Into @workDays Select 6, '4/15/2011 08:00:00.000', '4/15/2011 18:00:00.000' --Friday 8am-8pm
---weekends will be excluded here also...only the times set will be calculated

--exclude bank holidays days...
Insert Into @holidays Select '1/1/2010', 'New Years' ---2010 holidays
Insert Into @holidays Select '4/2/2010', 'Bank Holiday'
Insert Into @holidays Select '4/5/2010', 'Bank Holiday'
Insert Into @holidays Select '5/3/2010', 'Bank Holiday'
Insert Into @holidays Select '5/31/2010', 'Bank Holiday'
Insert Into @holidays Select '8/30/2010', 'Bank Holiday'
Insert Into @holidays Select '12/27/2010', 'Bank Holiday'
Insert Into @holidays Select '12/28/2010', 'Bank Holiday'
Insert Into @holidays Select '1/3/2011', 'Bank Holiday' ---2011 holidays
Insert Into @holidays Select '4/22/2011', 'Bank Holiday'
Insert Into @holidays Select '4/25/2011', 'Bank Holiday'
Insert Into @holidays Select '4/29/2011', 'Bank Holiday'
Insert Into @holidays Select '5/2/2011', 'Bank Holiday'
Insert Into @holidays Select '5/30/2011', 'Bank Holiday'
Insert Into @holidays Select '8/29/2011', 'Bank Holiday'
Insert Into @holidays Select '12/26/2011', 'Bank Holiday'
Insert Into @holidays Select '12/27/2011', 'Bank Holiday'

Select [RecID],
[CreatedDateTime],
[ResolutionDateAndTime],
[ResolvedBy],
[IncidentNumber],
[Owner],
[OwnerTeam],
[ClientName],
[Priority],
[Subject],
[TypeofIncident],
[FirstCallResolution],
[seconds],-- Total seconds based on the original date/time range (regardless of work hours)
--B.[ParentNumber], --to add the ParentNumber field from Task
--Max B.[CreatedDateTime] as LastAssignment--to only show the last assignment

--display of actual seconds (regardless of work hours)
[display] = convert(varchar,seconds/3600)+':'+
right('00'+convert(varchar,seconds%3600/60),2)+':'+
right('00'+convert(varchar,seconds%3600%60),2),

-- Sum up adjusted seconds (during work hours only)
[actualseconds] = sum(actualSeconds),
-- average of actualseconds
--AVG(actualSeconds) AS Average,
-- Sum up adjusted display (during work hours only)
[actualDisplay] = convert(varchar,sum(actualSeconds)/3600)+':'+
right('00'+convert(varchar,sum(actualSeconds)%3600/60),2)+':'+
right('00'+convert(varchar,sum(actualSeconds)%3600%60),2)

From
(
Select
[RecID],
[CreatedDateTime],
[ResolutionDateAndTime],
[ResolvedBy],
[IncidentNumber],
[Owner],
[OwnerTeam],
[ClientName],
[Priority],
[Subject],
[TypeofIncident],
[FirstCallResolution],
--B.[ParentNumber],
--Max B.[CreatedDateTime] as LastAssignment--to only show the last assignment

-- Calculate seconds based only on the Original Dates
[seconds] = DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime),
-- Determine actual seconds worked during WorkDay Range based on 8am - 8pm, no weekends
[actualSeconds] = DATEDIFF(ss,ActualBegin,ActualEnd)

From
(
Select
A.[RecID],
A.[CreatedDateTime],
A.[ResolutionDateAndTime],
A.[ResolvedBy],
A.[IncidentNumber],
A.[Owner],
A.[OwnerTeam],
A.[ClientName],
A.[Priority],
A.[Subject],
A.[TypeofIncident],
A.[FirstCallResolution],
B.[TaskOwnerTeam],
--B.[ParentNumber],
--Max B.[CreatedDateTime] as LastAssignment--to only show the last assignment

[WorkDay] = (dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + C.n),
[WorkBegin],
[WorkEnd],
-- Determine the actual begin date and time. The earliest of CreatedDateTime and 'WorkDay Begin'
[ActualBegin] = case
when A.CreatedDateTime < dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + C.n),WorkBegin),WorkBegin)
then dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + C.n),WorkBegin),WorkBegin)
when A.CreatedDateTime > dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + C.n),WorkEnd),WorkEnd)
then dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + C.n),WorkEnd),WorkEnd)
else A.CreatedDateTime end,
-- Determine the actual end date and time. The earliest of ResolutionDateTime and 'WorkDay End'
[ActualEnd] = case
when A.ResolutionDateAndTime < dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + C.n),WorkBegin),WorkBegin)
then dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + C.n),WorkBegin),WorkBegin)
when A.ResolutionDateAndTime > dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + C.n),WorkEnd),WorkEnd)
then dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + C.n),WorkEnd),WorkEnd)
else A.ResolutionDateAndTime end

From dbo.Incident A

Inner Join
(
Select Distinct ParentLink_RecID, --Distinct enforces a collection of unique results, thus 1-1 link...
TaskOwnerTeam = B.OwnerTeam
From dbo.Task B
Where B.OwnerTeam Not In ('Core Systems ','SAP Team','Sales Team','Finance Team',
'Enterprise Team', 'Clients','Systems Training Team','Support Unit')--Exclude other teams
) B
On A.RecID = B.ParentLink_RecID

--Inner Join dbo.Task B
--On A.RecID = B.Parentlink_RecID
--And B.OwnerTeam In('Team1','Team2','Team3')--include 2nd line teams only.

Inner Join
(
-- Get a number array (spt_values: type 'P' provides 0-2048)
Select
n = Number
From master..spt_values
where type = 'P'
) C
On (dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + C.n) between dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) and dateadd(dy,datediff(dy,0,A.ResolutionDateAndTime),0)
Inner Join @workDays D
On datepart(dw,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + C.n)) = D.dw
Left Join @Holidays E
On (dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + C.n) = E.Holidate
Where E.Holidate is null
) Z
) A

Group By
RecID,
CreatedDateTime,
ResolutionDateAndTime,
ResolvedBy,
IncidentNumber,
Owner,
OwnerTeam,
ClientName,
Priority,
Subject,
TypeofIncident,
FirstCallResolution,
seconds

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 07:12:26

it might be much easier for some one to help if you can post some sample data from tables and then explain what you want out of them rather than posting the whole query here.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources