I've got a complex query going on here that figures out what the average response time between two events is, and takes into account working hours and weekends. It works fine when I run it in Microsoft SQL Server Management Studio, but I get the error "Incorrect Syntax near 'DATEPART'" when I run it from my asp.net c# website. Anybody have any ideas?
select
AVG(weekdiff) as 'Response Time'
from
(select
TicketID,
modifiedCreateDate,
AssignedDateTime,
DATEDIFF(hour,
modifiedcreatedate,
AssignedDateTime) as actualhours,
DATEDIFF(day,
modifiedcreatedate,
AssignedDateTime)actualdays,
DATEDIFF(hour,
modifiedcreatedate,
AssignedDateTime)-( DATEDIFF(day,
modifiedcreatedate,
AssignedDateTime)*13)- ((datediff(wk,
modifiedCreateDate,
AssignedDateTime) * 2) - case
when datepart(dw,
modifiedCreateDate) = 1 then 1
else 0
end + case
when datepart(dw,
AssignedDateTime) = 1 then 1
else 0
end)*11 as Weekdiff
FROM
(SELECT
[TicketID],
(CASE
WHEN DATEPART(hour,
OriginDateTime) > '17' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
(DATEADD (day,
1,
OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(month,
(DATEADD (day,
1,
OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(day,
(DATEADD (day,
1,
OriginDateTime))) AS varchar(4)) AS datetime))
WHEN DATEPART(hour,
OriginDateTime) < '6' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
OriginDateTime) AS varchar(4)) + '-' + CAST(DATEPART(month,
OriginDateTime) AS varchar(4)) + '-' + CAST(DATEPART(day,
OriginDateTime) AS varchar(4)) AS datetime))
WHEN DATEPART(dw,
OriginDateTime) = '7' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
(DATEADD (day,
2,
OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(month,
(DATEADD (day,
2,
OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(day,
(DATEADD (day,
2,
OriginDateTime))) AS varchar(4)) AS datetime))
WHEN DATEPART(dw,
OriginDateTime) = '1' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
(DATEADD (day,
1,
OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(month,
(DATEADD (day,
1,
OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(day,
(DATEADD (day,
1,
OriginDateTime))) AS varchar(4)) AS datetime))
ELSE OriginDateTime
end) AS modifiedCreateDate,
[AssignedDateTime]
FROM
[LFM_Archive].[dbo].[IT_Track]
WHERE
DATEDIFF(HOUR,[OriginDateTime],[AssignedDateTime]) IS NOT NULL)x
where
DATEPART(WEEK, [modifiedCreateDate]) = DATEPART(WEEK, getdate())
)y
EDIT:
So, we tried doing a view instead, but running the query SELECT AVG(workdaydiff) from LFM_Archive.dbo.Statistics still throws the incorrect syntax near DATEPART error from the webpage. Here's the view:
SELECT ticketid,
modifiedcreatedate,
assigneddatetime,
Datediff(hour, modifiedcreatedate, assigneddatetime)
AS actualhours,
Datediff(day, modifiedcreatedate, assigneddatetime)
AS actualdays,
( Datediff(hour, modifiedcreatedate, assigneddatetime) -
Datediff(day, modifiedcreatedate, assigneddatetime) * 13 ) - (
Datediff(wk, modifiedcreatedate, assigneddatetime) * 2 - CASE
WHEN
Datepart(dw, modifiedcreatedate) = 1 THEN 1
ELSE 0
END + CASE
WHEN Datepart(dw, assigneddatetime) = 1 THEN 1
ELSE 0
END ) * 11
AS
WorkHourDiff,
Datediff(day, modifiedcreatedate, assigneddatetime) - (
Datediff(wk, modifiedcreatedate, assigneddatetime) * 2 - CASE
WHEN Datepart(dw,
modifiedcreatedate) = 1 THEN 1
ELSE 0
END + CASE
WHEN
Datepart(
dw,
assigneddatetime)
= 1 THEN 1
ELSE 0
END )
AS
workdaydiff
FROM (SELECT ticketid,
( CASE
WHEN Datepart(hour, origindatetime) > '17' THEN
Dateadd(hour, 6,
Cast(
Cast(Datepart(year, (Dateadd(day, 1, origindatetime))) AS
VARCHAR(4)
)
+ '-'
+ Cast(Datepart(month, (Dateadd(day, 1,
origindatetime))
)
AS VARCHAR
(4))
+ '-'
+ Cast(Datepart(day, (Dateadd(day, 1,
origindatetime)))
AS
VARCHAR(4)) AS
DATETIME))
WHEN Datepart(hour, origindatetime) < '6' THEN
Dateadd(hour, 6,
Cast(
Cast(Datepart(year, origindatetime) AS VARCHAR(4))
+ '-'
+ Cast(Datepart(month, origindatetime) AS
VARCHAR(4))
+ '-'
+ Cast(Datepart(day, origindatetime) AS
VARCHAR(4))
AS
DATETIME))
WHEN Datepart(dw, origindatetime) = '7' THEN
Dateadd(hour, 6, Cast(
Cast(Datepart(year, (Dateadd(day, 2, origindatetime))) AS
VARCHAR(4))
+ '-'
+ Cast(Datepart(month, (Dateadd(day, 2, origindatetime)))
AS VARCHAR(4))
+ '-'
+ Cast(Datepart(day, (Dateadd(day, 2, origindatetime))) AS
VARCHAR(4)) AS
DATETIME))
WHEN Datepart(dw, origindatetime) = '1' THEN
Dateadd(hour, 6, Cast(
Cast(Datepart(year, (Dateadd(day, 1, origindatetime))) AS
VARCHAR(4))
+ '-'
+ Cast(Datepart(month, (Dateadd(day, 1, origindatetime)))
AS VARCHAR(4))
+ '-'
+ Cast(Datepart(day, (Dateadd(day, 1, origindatetime))) AS
VARCHAR(4)) AS
DATETIME))
ELSE origindatetime
END ) AS modifiedCreateDate,
assigneddatetime
FROM dbo.it_track
WHERE ( Datediff(hour, origindatetime, assigneddatetime) IS NOT NULL ))
AS x