|
Try this
Select replace(fullname,' ','')
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
I have a programming problem to split the total time spent on jobs into business hour and after hours
business hour 8:30 - 17:00
after hour 17:01 - 8:29
database is on sql server 2005, there are job_start_datetime and job_end_datetime columns hold the jobs start and end time, also a total_time_spent column hold the total of time spent on the job.
I have an order to split the time spent into business hour and after hours. I am creating 2 filed in the table to sperate these hours. but I am having problem to using Datediff to calculate amount of hour into 2 time frames.
Can someone give me any advise how can I do that?
Thanks
Candy
|
|
|
|
|
Here is idea how to implement in your real data:
select convert(varchar(15), cast(getdate()+ ' 17:00' as datetime) - cast(getdate()+' 08:00' as datetime),108)<br />
select convert(varchar(15),cast(getdate()+' 17:01' as datetime) - cast(getdate()+1+'08:29' as datetime),108)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi,
Thank you for your response.
I think I didn't explan it very well.
shall i explan the problem more in detail? for example, the job_time table contains following data
Job_id, Start_Datetime, End_Datetime, [Time_spent__hrs_]
1, 2001-08-20 00:00:00.000, 2001-08-21 00:00:00.000, 24.000000
2, 2001-08-21 00:00:00.000, 2001-08-21 12:00:00.000, 12.000000
3, 2001-08-20 08:00:00.000, 2001-08-21 10:00:00.000, 26.000000
4, 2001-08-13 13:00:00.000, 2001-08-17 15:00:00.000, 98.000000
5, 2001-07-18 12:00:00.000, 2001-07-18 17:00:00.000, 5.000000
6, 2001-08-24 08:30:00.000, 2001-08-24 17:30:00.000, 9.000000
I want to split the time spent hours into 2 fames, how many hours spent with in 8:30 - 17:00 & 17:01 - 8:29
Thank you
modified on Monday, September 15, 2008 7:34 PM
|
|
|
|
|
select *,<br />
datediff(hh,cast(start_datetime as datetime),cast(end_datetime as datetime)) as [Time_spent__hrs_]<br />
from MyTable
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
You may want to consider storing your data differently so that it is easier and more flexible to manipulate.
For example, consider the following:
Start_Time End_Time Type Pay_Code
06:00 8:00 WORK OT1
8:00 10:00 WORK REG
10:00 10:05 BREAK UNPAID
10:05 12:00 WORK REG
12:00 13:00 BREAK UNPAID
13:00 17:00 WORK REG
17:00 19:00 WORK OT1
Start_Time End_Time Type Pay_Code
08:00 16:00 HOLIDAY REG
This type of structure allows you to identify parts of a day and classify them appropriately. You can say that time worked before the scheduled work day is paid at overtime. You could identify a break in the middle of the day as unpaid. (Same is true for lunch) You could also then show the time after the normal work day to be at overtime. This example assumes that you are using a pay schedule where time over 8 hours in a day is paid at overtime. In the USA, most overtime is paid only after 40 hours of work.
The second example shows how you represent Holiday pay. This schema also allows for sick time (paid or unpaid).
I'll leave it up to the class to show how you can expand this data structure to include charge codes where you could identify which cost centers the labor should be applied to. 
|
|
|
|
|
I tried re-posting the example data with HTML table formatting.
(You may need to scroll down to see it ... I'm not sure why there is so much white space)
| Start_Time | End_Time | Type | Pay_Code | | 06:00 | 08:00 | WORK | OT1 | | 08:00 | 10:00 | WORK | REG | | 10:00 | 10:15 | BREAK | UNPAID | | 10:15 | 12:00 | WORK | REG | | 12:00 | 13:00 | BREAK | UNPAID | | 13:00 | 17:00 | WORK | REG | | 17:00 | 19:00 | WORK | OT1 |
| Start_Time | End_Time | Type | Pay_Code | | 08:00 | 17:00 | HOLIDAY | REG |
|
|
|
|
|
Hi Guys
If anyone can help me with this it would be greatly appreciated.
K here is what's happening.I am supposed to check if a student exists in the STUDENT table and then check if a module exists in the MODULE table.if they exist in both tables I must add the student(StudentNumber) and the module(ModuleCode) to the STUDENTMODULE table(register the student).
How do i do this plz help
|
|
|
|
|
To find if a record exists, you would use a select statement ...
<br />
select count(*) from student where studentNumber = [myStud]<br />
<br />
if count = 0 then <br />
insert StudentNumber,ModuleCode into STUDENTModule values (myStud,myMoodule)<br />
This is not actual code, but pseudocode meant to point you in the right direction. I won't do your homework for you. 
|
|
|
|
|
if(select count(*) from students where studentnamecolumn = 'studentname') = 0<br />
begin<br />
insert into students values (value1,value1......)<br />
end
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
You can do this with an insert into table, e.g.
insert into mytable (value1, value2, value3)
select @value1, @value2, @value3
where @myitem not in (select myitem from table2)
|
|
|
|
|
anyone.. plss... nid it badly..
|
|
|
|
|
<a href="http://www.google.com/search?source=ig&hl=en&rlz=&=&q=Update+-+TSQL&btnG=Google+Search">Update</a>[<a href="http://www.google.com/search?source=ig&hl=en&rlz=&=&q=Update+-+TSQL&btnG=Google+Search" target="_blank" title="New Window">^</a>] tablename set columnmae='newvalue' where columname='oldvalue'
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hello All
i am wondering how to call a batch of SQL server scripts from inside a C# code
Thanx for your help
|
|
|
|
|
ADO.NET
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I have a requirement to allow a user to search using Staff Number is like, i.e. starts with or ends with, as well as Staff Number is in, i.e. a list of Staff Numbers. Is there some way I could handle these two filter criterion together, or should I simply create an IN query when I detect a list of numbers, and a like query otherwise?
|
|
|
|
|
I think query should be something like this:
<br />
select * from tablename <br />
where ([Staff Number] like '%value%')<br />
or ([Staff Number] in ('value'))
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I have a table and this table like the following
id_______ name_________ date
1________mm___________28/3/1999
2________mm___________21/3/1999
3________mm___________25/4/1999
4________nn____________10/8/1999
5________nn____________15/9/1999
6________nn____________2/10/1999
i need the following result
id_______ name_________ date
1________mm___________25/4/1999
2________nn____________2/10/1999
thank you
|
|
|
|
|
select [name],<br />
(select top 1 t1.[date] from MyTable as t1 where t1.[name] = MyTable.[name] order by t1.[id] desc)<br />
from MyTable <br />
group by [name]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
try this
select name, max(date)
from table
group by name
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I have an inline table function and it takes tow parameters of type VARCHAR.
I functions when I hard code to string within of call like this:
SELECT * FROM dbo.TestTable('a', 'b')
Here is definition of inline table function:
FUNCTION GetIPTimeRangeTable (@StartDate VARCHAR(10) = '', @EndDate VARCHAR(10) = '')
RETURNS TABLE
AS
RETURN
(
SELECT
* FROM UserDataSetsAccess
WHERE 1 = 1
AND CreatedAT between @StartDate AND @EndDate
)
If call function like this:
SELECT * FROM GetIPTimeRangeViewTable('2008-7-1', '2008-7-11')
Every ting is OK but if I call function like this
SELECT * FROM GetIPTimeRangeViewTable('2008-7-1', CONVERT(CHAR(10), GETDATE(),121))
I get error:
Incorrect syntax near the keyword 'CONVERT'.
Do you have any explanation for that!
Tanks
|
|
|
|
|
try using varchar(10), you there have 8 or 9 char not 10!!!
you can get 8 (1-1-2004), 9 (10-9-2005) or 10 (10-10-2004) chars.
you used getdate(), if you ran it october 10th it will work 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hello all, i have a three tables user,aspnet_user,and aspnetmembership table and i made my table name myapplicationuser which contins colum similer to that of the three tables. Now from Store procedure i want to check those data in tables user,aspnet_user,and aspnetmembership and not in myapplicationuser and insert into myapplicationuser , i.e check if exist if not insert it (from stored procedure)
|
|
|
|
|
Try using IF EXISTS statement.
IF Not exists (select 1 from tablex where columny = 'z')
begin
insert ....
end
else
begin
update ...
end
Bob
Ashfield Consultants Ltd
|
|
|
|
|
whats wrong with this one why is it opening a new sqlqury ide
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[copyuser]
AS
SET NOCOUNT ON;
DECLARE @iRowId int, @userName NVARCHAR(100)
DECLARE usercursor1 CURSOR FOR
SELECT UserID,Username FROM users
print 'reached here'
OPEN usercursor1
FETCH usercursor1 into @iRowId,@userName
WHILE @@Fetch_Status = 0
BEGIN --loop started
PRINT @userName
IF not EXISTS (SELECT 1 FROM Esso_UserInfo WHERE UserName=@userName)
BEGIN --if started
PRINT 'not exist'
END --if closed
ELSE
BEGIN --else started
PRINT 'exist'
END --else closed
FETCH usercursor1 into @iRowId,@userName
End --loop closed
CLOSE usercursor1
DEALLOCATE usercursor1
RETURN
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
