• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1459
  • Last Modified:

Incorrect syntax near the keyword with

With the help of EE I have some good code but I'm having some compile issues.
If I copy and paste the code as is the errors are...
The variable name '@from' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@to' has already been declared. Variable names must be unique within a query batch or stored procedure.
Incorrect syntax near the keyword 'with'.
'row_number' is not a recognized function name.
Line 138: Incorrect syntax near 'GO'. (.Net SqlClient Data Provider)

I thought maybe the first 2 errors are because I'm using Microsoft Visual Studio as my environment.  So if I comment out the first 2 lines that declare from and to I only get these errors.
Incorrect syntax near the keyword 'with'.
'row_number' is not a recognized function name.
Line 138: Incorrect syntax near 'GO'. (.Net SqlClient Data Provider)

I'm very new to SQL in any environment so I would appreciate help in fixing the remaining errors.
Thanks in advance.
declare @from datetime
declare @to datetime
 
set @from = '19000101'
set @to = getdate()
 
-- first query is an adaption from previous - shown by DAY
 
SELECT  TS_week, TS_day, L.EmployeeId, L.LastName + ', ' + L.FirstName AS Name, sum(TS_Seconds) as ts_seconds, Sum(TS_Manual_Seconds) as ts_manual_seconds, sum(TS_Break_Seconds) as ts_break_seconds,
convert(varchar,datediff(hh,0,(dateadd(ss,sum(TS_Seconds) + Sum([TS_Manual_Seconds]) + case when sum([TS_Break_Seconds]) < 1801 then sum([TS_Break_Seconds]) else 1800 end ,0))))+ 
substring((convert(varchar(8),(dateadd(ss,sum(TS_Seconds) + Sum([TS_Manual_Seconds]) + case when sum([TS_Break_Seconds]) < 1800 then sum([TS_Break_Seconds]) else 1800 end,0)),114)),3,3) as HrsMin
 
FROM
(
   select company,employeeid,
          datepart(week, timein) as TS_Week,
          convert(datetime,convert(varchar(8),timein,112)) as TS_Day,
          timein, [timeout], BreakFlag,
          datediff(ss,timein,[timeout]) as TS_Seconds, 
          IsNull([TimeSheetMinutes],0) * 60 as TS_Manual_Seconds,
          isnull( (select top 1 case when t1.Breakflag = 1 then datediff(ss,t1.timeout,isnull(t2.timein,t1.timeout)) else 0 end 
                   from EmployeeHours t2 
                   where t2.employeeid = t1.employeeid 
                   and t2.company=t1.company 
				   and convert(varchar(8),t2.timein) = convert(varchar(8),t1.timein)                     -- must be same day
                   and (t2.timein > t1.timein or (t2.timeout > t1.timeout and t2.timein = t1.timein))
                   order by t2.timein asc, t2.timeout asc, recordid asc),0) as TS_Break_Seconds
          from EmployeeHours t1
) times
 
INNER JOIN EmployeeCustomFieldsData C ON (times.EmployeeId = C.EmployeeId) AND (times.Company = C.Company)
INNER JOIN EmployeeList L ON (C.EmployeeId = L.EmployeeId) AND (C.Company = L.Company) 
LEFT JOIN UserList U ON L.ManagerUID = U.UID and L.Company = U.Company
 
WHERE convert(datetime,convert(char(8),times.TimeIn,112)) between @From and @to
 AND L.Suspend = 0
 AND C.CustomFieldData in ('DH')
 AND times.Company=1
 
GROUP BY  ts_week, times.employeeid, ts_day, L.EmployeeId,L.LastName + ', ' + L.FirstName
 
ORDER BY ts_week, L.LastName + ', ' + L.FirstName, L.EmployeeId, ts_day
 
 
 
-- now by week...
 
select TS_week, EmployeeID, TS_Name, sum(TS_Seconds) as ts_seconds, Sum(TS_Manual_Seconds) as ts_manual_seconds, sum(TS_Break_Seconds) as ts_break_seconds,sum(TS_approved_Break_Seconds) as ts_approved_break_seconds,
       convert(varchar,datediff(hh,0,(dateadd(ss,sum(ts_seconds + ts_manual_seconds + ts_approved_break_seconds) ,0))))+ 
       substring((convert(varchar(8),(dateadd(ss,sum(ts_seconds + ts_manual_seconds + ts_approved_break_seconds) ,0)),114)),3,3) as HrsMin
from
(
 SELECT  TS_week, TS_day, L.EmployeeId, L.LastName + ', ' + L.FirstName AS TS_Name, sum(TS_Seconds) as ts_seconds, Sum(TS_Manual_Seconds) as ts_manual_seconds, sum(TS_Break_Seconds) as ts_break_seconds,
         case when sum([TS_Break_Seconds]) < 1800 then sum([TS_Break_Seconds]) else 1800 end as ts_approved_break_seconds
 FROM
 (
   select company,employeeid,
          datepart(week, timein) as TS_Week,
          convert(datetime,convert(varchar(8),timein,112)) as TS_Day,
          timein, [timeout], BreakFlag,
          datediff(ss,timein,[timeout]) as TS_Seconds, 
          IsNull([TimeSheetMinutes],0) * 60 as TS_Manual_Seconds,
          isnull( (select top 1 case when t1.Breakflag = 1 then datediff(ss,t1.timeout,isnull(t2.timein,t1.timeout)) else 0 end 
                   from EmployeeHours t2 
                   where t2.employeeid = t1.employeeid 
                   and t2.company=t1.company 
				   and convert(varchar(8),t2.timein) = convert(varchar(8),t1.timein)                     -- must be same day
                   and (t2.timein > t1.timein or (t2.timeout > t1.timeout and t2.timein = t1.timein))
                   order by t2.timein asc, t2.timeout asc, recordid asc),0) as TS_Break_Seconds
          from EmployeeHours t1
  ) times
 
  INNER JOIN EmployeeCustomFieldsData C ON (times.EmployeeId = C.EmployeeId) AND (times.Company = C.Company)
  INNER JOIN EmployeeList L ON (C.EmployeeId = L.EmployeeId) AND (C.Company = L.Company) 
  LEFT JOIN UserList U ON L.ManagerUID = U.UID and L.Company = U.Company
 
  WHERE convert(datetime,convert(char(8),times.TimeIn,112)) between @From and @to
   AND L.Suspend = 0
   AND C.CustomFieldData in ('DH')
   AND times.Company=1
 
  GROUP BY  ts_week, times.employeeid, ts_day, L.EmployeeId,L.LastName + ', ' + L.FirstName
 ) daily
 
GROUP BY  ts_week, employeeid, ts_name
 
HAVING (sum(ts_Seconds + ts_manual_Seconds + ts_approved_Break_Seconds) / 60) < 9100 
 
ORDER BY ts_week, employeeid, ts_name
 
 
-- Or using SQL 2005 / SQL 2008 and the recursive query...
 
;
with cte_time 
as
(  select convert(int,0) as rn,E.company,E.employeeid,L.LastName + ', ' + L.FirstName AS TS_Name, datepart(week,timein) as ts_week, convert(datetime,convert(varchar(8),timein,112)) as ts_day, convert(datetime,0) as ts_timein,convert(datetime,0) as ts_timeout,convert(int,99) as breakflag, 0 as ts_seconds, 0 as ts_manual_seconds, 0 as ts_break_seconds, 0 as TS_Daily_Break_Seconds,0 as TS_Approved_Break_Seconds
   from employeeHours E
   INNER JOIN EmployeeCustomFieldsData C ON (E.EmployeeId = C.EmployeeId) AND (E.Company = C.Company)
   INNER JOIN EmployeeList L ON E.EmployeeId = L.EmployeeId AND E.Company = L.Company 
   LEFT JOIN UserList U ON L.ManagerUID = U.UID and L.Company = U.Company
   WHERE convert(datetime,convert(char(8),TimeIn,112)) between @From and @to
   AND C.CustomFieldData in ('DH')
   AND L.Suspend = 0
   group by E.company, E.employeeid, L.LastName + ', ' + L.FirstName, datepart(week,timein),convert(datetime,convert(varchar(8),timein,112))
 
   union all
 
   select this.rn, this.company, this.employeeid, previous.ts_name, this.ts_week, this.ts_day, this.timein, this.timeout, this.breakflag, this.ts_seconds, this.ts_manual_seconds,
          case when previous.employeeid = this.employeeid and previous.ts_day = convert(varchar(8),this.timein,112) and  previous.breakflag = 1 then datediff(ss,previous.ts_timeout,this.timein) else 0 end as TS_Break_Seconds,
          case when previous.employeeid = this.employeeid and previous.ts_day = convert(varchar(8),this.timein,112) and  previous.breakflag = 1 then case when previous.ts_daily_break_seconds + datediff(ss,previous.ts_timeout,this.timein) < 1800 then previous.ts_daily_break_seconds + datediff(ss,previous.ts_timeout,this.timein) else 1800 end else previous.ts_daily_break_seconds end as TS_Daily_Break_Seconds,
          case when previous.employeeid = this.employeeid and previous.ts_day = convert(varchar(8),this.timein,112) and  previous.breakflag = 1 then case when previous.ts_daily_break_seconds + datediff(ss,previous.ts_timeout,this.timein) < 1800 then datediff(ss,previous.ts_timeout,this.timein) else 1800 - previous.ts_daily_break_seconds end else 0 end as TS_Approved_Break_Seconds
 
   from ( select
               convert(int,row_number() over (partition by employeeid, convert(varchar(8),timein,112) order by timein, [timeout], recordid)) as RN,
               company,employeeid, datepart(week,timein) as ts_week,
               convert(datetime,convert(varchar(8),timein,112)) as TS_Day,
               timein, [timeout], convert(int, isnull(BreakFlag,0)) as breakflag,
               datediff(ss,timein,[timeout]) as TS_Seconds, 
               IsNull(TimeSheetMinutes,0) * 60 as TS_Manual_Seconds
          from EmployeeHours T2) this
   INNER JOIN cte_time Previous on previous.employeeid = this.employeeid and previous.ts_day = convert(varchar(8),this.timein,112) and previous.rn = this.rn - 1
   WHERE this.ts_day between @From and @to
) 
--select * from cte_time  where breakflag <> 99  order by company, ts_week, employeeid, ts_day, ts_timein, ts_timeout   -- detailed line by line
 
SELECT ts_week, EmployeeID, ts_name,
       convert(varchar,datediff(hh,0,(dateadd(ss,sum(ts_seconds + ts_manual_seconds + ts_approved_break_seconds) ,0))))+ 
       substring((convert(varchar(8),(dateadd(ss,sum(ts_seconds + ts_manual_seconds + ts_approved_break_seconds) ,0)),114)),3,3) as HrsMin
 
FROM cte_time  
 
WHERE breakflag <> 99  
 
group by ts_week, employeeid
order by ts_week, employeeid
 
GO

Open in new window

0
BobRosas
Asked:
BobRosas
  • 3
  • 3
  • 2
2 Solutions
 
BrandonGalderisiCommented:
If you are getting those errors, then you aren't using SQL Server 2005.  You are attaching to a SQL Server 2000 instance
0
 
BobRosasAuthor Commented:
Thanks for your quick reponse.  
By separating the attached code into 3 querys and running them separately I was able to get the first 2 to run but several employee totals that use to be correct are now off by minutes.  Maybe it's a rounding thing.  II've attached an excel spreadsheet with the exceptions.  Also I am not able to get the last query to run.  Since I got the first 2 to work I thought now the 3rd one should work as well but I get the following errors...
Line 1: Incorrect syntax near ';'.
'row_number' is not a recognized function name.
Line 45: Incorrect syntax near 'GO'. (.Net SqlClient Data Provider)
Thanks in advance.
OffByMins.xls
0
 
BrandonGalderisiCommented:
You don't want GO in your query and again, if you are getting errors about ;with and/or row_number, then you aren't running against a SQL 2005 database.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Mark WillsTopic AdvisorCommented:
Or, if in 2005 try changing compatability mode. In SSMS, right click on the database and go into properties, a pop-up window will appear. On the left hand side of that window you should see a lable "options" go into that and you can change compatability mode. If going up to 2005 you should recalc statistics and possible reindex - 2005 has a much "smarter" query optimiser.
 
OK lets look at row 5 and row 6 for the first employee - there is a break of 16 minutes which for the one day is OK  (according to code)

Similarly row 13 and row 14 - just the one day, and 19 minute break.

We do not really check or apportion breaks if only after four hours - your previous question said that they could be consecutive. Or, should we also check that a single break is only whole multiples of 15 ?

0
 
Mark WillsTopic AdvisorCommented:
There are three seperate queries in there. You can break them up according to the comments indicating query 1, query 2 etc. You will have t copy the @from and @to declarations, or, could run all three by simply executing the entire script (assuming the SQL2005 thingy is fixed)...

Now, if any one break con only be a max of 15 minutes, and a maximum of 30 minutes in a day, then looking at the first query only, please try :
declare @from datetime
declare @to datetime
 
set @from = '19000101'
set @to = getdate()
 
-- first query is an adaption from previous - shown by DAY
 
SELECT  TS_week, TS_day, L.EmployeeId, L.LastName + ', ' + L.FirstName AS Name, sum(TS_Seconds) as ts_seconds, Sum(TS_Manual_Seconds) as ts_manual_seconds, sum(TS_Break_Seconds) as ts_break_seconds,
convert(varchar,datediff(hh,0,(dateadd(ss,sum(TS_Seconds) + Sum([TS_Manual_Seconds]) + case when sum([TS_Break_Seconds]) < 1801 then sum([TS_Break_Seconds]) else 1800 end ,0))))+ 
substring((convert(varchar(8),(dateadd(ss,sum(TS_Seconds) + Sum([TS_Manual_Seconds]) + case when sum([TS_Break_Seconds]) < 1801 then sum([TS_Break_Seconds]) else 1800 end,0)),114)),3,3) as HrsMin
 
FROM
(
   select company,employeeid,
          datepart(week, timein) as TS_Week,
          convert(datetime,convert(varchar(8),timein,112)) as TS_Day,
          timein, [timeout], BreakFlag,
          datediff(ss,timein,[timeout]) as TS_Seconds, 
          IsNull([TimeSheetMinutes],0) * 60 as TS_Manual_Seconds,
          isnull( (select top 1 case when t1.Breakflag = 1 
                                     then case when datediff(ss,t1.timeout,isnull(t2.timein,t1.timeout)) > 900 
                                               then 900 
                                               else datediff(ss,t1.timeout,isnull(t2.timein,t1.timeout)) end 
                                     else 0 end 
                   from EmployeeHours t2 
                   where t2.employeeid = t1.employeeid 
                   and t2.company=t1.company 
				   and convert(varchar(8),t2.timein) = convert(varchar(8),t1.timein)                     -- must be same day
                   and (t2.timein > t1.timein or (t2.timeout > t1.timeout and t2.timein = t1.timein))
                   order by t2.timein asc, t2.timeout asc, recordid asc),0) as TS_Break_Seconds
          from EmployeeHours t1
) times
 
INNER JOIN EmployeeCustomFieldsData C ON (times.EmployeeId = C.EmployeeId) AND (times.Company = C.Company)
INNER JOIN EmployeeList L ON (C.EmployeeId = L.EmployeeId) AND (C.Company = L.Company) 
LEFT JOIN UserList U ON L.ManagerUID = U.UID and L.Company = U.Company
 
WHERE convert(datetime,convert(char(8),times.TimeIn,112)) between @From and @to
 AND L.Suspend = 0
 AND C.CustomFieldData in ('DH')
 AND times.Company=1
 
GROUP BY  ts_week, times.employeeid, ts_day, L.EmployeeId,L.LastName + ', ' + L.FirstName
 
ORDER BY ts_week, L.LastName + ', ' + L.FirstName, L.EmployeeId, ts_day

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Oh, and the last employee has not been allowed any breaks at all - thought they were allowed up to 15 minutes or a maximum of 30 in a day - the spreadsheet shows no breaks at all were allowed on the 1/9/2009, where as the code would have allowed up to 30 minutes (hence the difference)...



536	1	1/9/09 7:44 AM	1/9/09 11:03 AM	1			3	19
536	1	1/9/09 11:29 AM	1/9/09 12:03 PM	2			0	34
536	1	1/9/09 12:33 PM	1/9/09 4:01 PM	1			3	28
536	1	1/9/09 4:29 PM	1/9/09 4:59 PM	0			0	30

Open in new window

0
 
BobRosasAuthor Commented:
mark wills,
Your answer is really close to what I'm looking for.  Your comment....
     "Or, should we also check that a single break is only whole multiples of 15 ?"
is exactly right.  And your code fixed it.  
Your last comment about the 30 minute break is true.  But the exception to the rule is that if the break is over 25 minutes then the code assumes they meant to clock out to lunch and it doesn't pay them for any of that time.  Even if they haven't had any breaks.  
Because this code works in my Microsoft Visual Studio environment I have not made any changes in my SSMS enviroment but I will save your suggestion in case I need them later.
You've gone above and beyond so I will repost for the last issue.
Thanks again!
0
 
BobRosasAuthor Commented:
Brandon, Thanks for your input.  Technically you gave me the answer as to why my code wasn't working so I'll give you partial points.
Mark, You are getting me where I need to go so I increased points for all your much needed help.  Thanks to you both.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now