August 23, 2010 at 10:12 am
getting "incorrect syntax near '3'.
can anyone figure out WHY??
CREATE procedure [dbo].[TRACKIT_SUMMARY]
AS
BEGIN
declare @DOW int
select @DOW = DATEPART(weekday,getdate())
if @DOW = 2 -- Monday
select
'OPEN',
[Priority] = substring(PRIORITY,1,2),
[WorkOrder#] = WO_NUM,
[Summary] = TASK,
[Status] = isnull(STATUS,'Open'),
[Type] = TYPE,
[DateEntered] = convert(char(10),OPENDATE,110),
[AssignedTech] = RESPONS,
[Requestor] = REQUEST
from TASKS
where TYPE in ('Incident','Problem','Service Request')
and OPENDATE between getdate()-3 and getdate()+1
and CLSDDATE is null -- Not Closed
union
-- CLOSED
select
'CLOSED',
[Priority] = substring(PRIORITY,1,2),
[WorkOrder#] = WO_NUM,
[Summary] = TASK,
[Status] = isnull(STATUS,'Closed'),
[Type] = TYPE,
[DateEntered] = convert(char(10),OPENDATE,110),
[AssignedTech] = RESPONS,
[Requestor] = REQUEST
from TASKS
where TYPE in ('Incident','Problem','Service Request')
and CLSDDATE between getdate()-3 and getdate()+1
union
-- PAST DUE
select
'PAST DUE',
[Priority] = substring(PRIORITY,1,2),
[WorkOrder#] = WO_NUM,
[Summary] = TASK,
[Status] = isnull(STATUS,'Open'),
[Type] = TYPE,
[DateEntered] = convert(char(10),OPENDATE,110),
[AssignedTech] = RESPONS,
[Requestor] = REQUEST
from TASKS
where TYPE in ('Incident','Problem','Service Request')
and OPENDATE < getdate()-21
and CLSDDATE is null
order by 1,2,6,3
if @DOW <> 2
-- OPEN
select
'OPEN',
[Priority] = substring(PRIORITY,1,2),
[WorkOrder#] = WO_NUM,
[Summary] = TASK,
[Status] = isnull(STATUS,'Open'),
[Type] = TYPE,
[DateEntered] = convert(char(10),OPENDATE,110),
[AssignedTech] = RESPONS,
[Requestor] = REQUEST
from TASKS
where TYPE in ('Incident','Problem','Service Request')
and OPENDATE between getdate()-1 and getdate()+1
and CLSDDATE is null -- Not Closed
union
-- CLOSED
select
'CLOSED',
[Priority] = substring(PRIORITY,1,2),
[WorkOrder#] = WO_NUM,
[Summary] = TASK,
[Status] = isnull(STATUS,'Closed'),
[Type] = TYPE,
[DateEntered] = convert(char(10),OPENDATE,110),
[AssignedTech] = RESPONS,
[Requestor] = REQUEST
from TASKS
where TYPE in ('Incident','Problem','Service Request')
and CLSDDATE between getdate()-1 and getdate()+1
union
-- PAST DUE
select
'PAST DUE',
[Priority] = substring(PRIORITY,1,2),
[WorkOrder#] = WO_NUM,
[Summary] = TASK,
[Status] = isnull(STATUS,'Open'),
[Type] = TYPE,
[DateEntered] = convert(char(10),OPENDATE,110),
[AssignedTech] = RESPONS,
[Requestor] = REQUEST
from TASKS
where TYPE in ('Incident','Problem','Service Request')
and OPENDATE < getdate()-21
and CLSDDATE is null
order by 1,2,6,3
August 23, 2010 at 10:55 am
jrbass81 (8/23/2010)
getting "incorrect syntax near '3'.can anyone figure out WHY??
CREATE procedure [dbo].[TRACKIT_SUMMARY]
AS
BEGIN
declare @DOW int
select @DOW = DATEPART(weekday,getdate())
if @DOW = 2 -- Monday
select
'OPEN',
[Priority] = substring(PRIORITY,1,2),
[WorkOrder#] = WO_NUM,
[Summary] = TASK,
[Status] = isnull(STATUS,'Open'),
[Type] = TYPE,
[DateEntered] = convert(char(10),OPENDATE,110),
[AssignedTech] = RESPONS,
[Requestor] = REQUEST
from TASKS
where TYPE in ('Incident','Problem','Service Request')
and OPENDATE between getdate()-3 and getdate()+1
and CLSDDATE is null -- Not Closed
union
-- CLOSED
select
'CLOSED',
[Priority] = substring(PRIORITY,1,2),
[WorkOrder#] = WO_NUM,
[Summary] = TASK,
[Status] = isnull(STATUS,'Closed'),
[Type] = TYPE,
[DateEntered] = convert(char(10),OPENDATE,110),
[AssignedTech] = RESPONS,
[Requestor] = REQUEST
from TASKS
where TYPE in ('Incident','Problem','Service Request')
and CLSDDATE between getdate()-3 and getdate()+1
union
-- PAST DUE
select
'PAST DUE',
[Priority] = substring(PRIORITY,1,2),
[WorkOrder#] = WO_NUM,
[Summary] = TASK,
[Status] = isnull(STATUS,'Open'),
[Type] = TYPE,
[DateEntered] = convert(char(10),OPENDATE,110),
[AssignedTech] = RESPONS,
[Requestor] = REQUEST
from TASKS
where TYPE in ('Incident','Problem','Service Request')
and OPENDATE < getdate()-21
and CLSDDATE is null
order by 1,2,6,3
if @DOW <> 2
-- OPEN
select
'OPEN',
[Priority] = substring(PRIORITY,1,2),
[WorkOrder#] = WO_NUM,
[Summary] = TASK,
[Status] = isnull(STATUS,'Open'),
[Type] = TYPE,
[DateEntered] = convert(char(10),OPENDATE,110),
[AssignedTech] = RESPONS,
[Requestor] = REQUEST
from TASKS
where TYPE in ('Incident','Problem','Service Request')
and OPENDATE between getdate()-1 and getdate()+1
and CLSDDATE is null -- Not Closed
union
-- CLOSED
select
'CLOSED',
[Priority] = substring(PRIORITY,1,2),
[WorkOrder#] = WO_NUM,
[Summary] = TASK,
[Status] = isnull(STATUS,'Closed'),
[Type] = TYPE,
[DateEntered] = convert(char(10),OPENDATE,110),
[AssignedTech] = RESPONS,
[Requestor] = REQUEST
from TASKS
where TYPE in ('Incident','Problem','Service Request')
and CLSDDATE between getdate()-1 and getdate()+1
union
-- PAST DUE
select
'PAST DUE',
[Priority] = substring(PRIORITY,1,2),
[WorkOrder#] = WO_NUM,
[Summary] = TASK,
[Status] = isnull(STATUS,'Open'),
[Type] = TYPE,
[DateEntered] = convert(char(10),OPENDATE,110),
[AssignedTech] = RESPONS,
[Requestor] = REQUEST
from TASKS
where TYPE in ('Incident','Problem','Service Request')
and OPENDATE < getdate()-21
and CLSDDATE is null
order by 1,2,6,3
Get rid of the BEGIN or put and END at the botton
August 23, 2010 at 11:48 am
Tyson
Thanks so much...that fixed it...I had a brain fart. Monday 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply