Computing Dates

I'm soliciting any suggestions from the readers of the group on how
best to tackle this task. I need to determine if trouble tickets
opened get closed within 4 hours. Yes, I already know about
datediff(hh, date1, date2, but it's not that simple. The thing I'm
struggle with is that I need to take inaccount business hours (8:00am
to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
10/28/05 10:00am. Any suggestions are much appreciated.Try,
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 8) <= 4) print 'may be in 4 hours'
else
'more than 4 hours'
AMB
"BobD" wrote:

> I'm soliciting any suggestions from the readers of the group on how
> best to tackle this task. I need to determine if trouble tickets
> opened get closed within 4 hours. Yes, I already know about
> datediff(hh, date1, date2, but it's not that simple. The thing I'm
> struggle with is that I need to take inaccount business hours (8:00am
> to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
> 10/28/05 10:00am. Any suggestions are much appreciated.
>|||Correction:
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 20) <= 4)
print 'may be in 4 hours'
else
'more than 4 hours'
go
AMB
"Alejandro Mesa" wrote:
> Try,
> if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(h
h,
> @.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112
)
> and (datediff(hh, @.sd, @.ed) - 8) <= 4) print 'may be in 4 hours'
> else
> 'more than 4 hours'
>
> AMB
> "BobD" wrote:
>|||Correction: (sorry guys)
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 16) <= 4)
print 'may be in 4 hours'
else
'more than 4 hours'
go
AMB
"Alejandro Mesa" wrote:
> Correction:
> if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(h
h,
> @.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112
)
> and (datediff(hh, @.sd, @.ed) - 20) <= 4)
> print 'may be in 4 hours'
> else
> 'more than 4 hours'
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||Bob,
Try this:
SELECT
CASE WHEN cast(convert(varchar(12),date1,114) as datetime) < '08:00'
THEN dateadd(day,datediff(day,0,date1),'12:00
')
WHEN cast(convert(varchar(12),date1,114) as datetime) < '12:00'
THEN dateadd(hour,4,date1)
ELSE dateadd(hour,20,date1) END
FROM yourTable
Steve Kass
Drew University
BobD wrote:

>I'm soliciting any suggestions from the readers of the group on how
>best to tackle this task. I need to determine if trouble tickets
>opened get closed within 4 hours. Yes, I already know about
>datediff(hh, date1, date2, but it's not that simple. The thing I'm
>struggle with is that I need to take inaccount business hours (8:00am
>to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
>10/28/05 10:00am. Any suggestions are much appreciated.
>
>|||Bod
try this
SELECT CASE WHEN DATE1 = DATE2 THEN DATEDIFF(HOUR, DATE1,DATE2) ELSE
DATEDIFF(HOUR, DATE1,(DATEADD(HOUR,-16,DATE2)))
--
Regards
R.D
--Knowledge gets doubled when shared
"BobD" wrote:

> I'm soliciting any suggestions from the readers of the group on how
> best to tackle this task. I need to determine if trouble tickets
> opened get closed within 4 hours. Yes, I already know about
> datediff(hh, date1, date2, but it's not that simple. The thing I'm
> struggle with is that I need to take inaccount business hours (8:00am
> to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
> 10/28/05 10:00am. Any suggestions are much appreciated.
>

computer name access sql server

Is there a way to determine which workstation is accessing the SQL server? I
know in SQL Profile you can see the userid but can you see what actualy
workstation is accessing the server?I found it
sp_who, tells me everything I need to know,
thanks,
"Mike" <Mike@.community.nospam.com> wrote in message
news:u$gQtjPtHHA.4952@.TK2MSFTNGP04.phx.gbl...
> Is there a way to determine which workstation is accessing the SQL server?
> I know in SQL Profile you can see the userid but can you see what actualy
> workstation is accessing the server?
>|||On Jun 22, 11:12 am, "Mike" <M...@.community.nospam.com> wrote:
> Is there a way to determine which workstation is accessing the SQL server? I
> know in SQL Profile you can see the userid but can you see what actualy
> workstation is accessing the server?
select login_time as 'Login',
last_batch as 'Batch',
cast(status as varchar(12)) as 'Status',
cast(hostname as varchar(18)) as 'Host',
cast(program_name as varchar(32)) as 'Program',
cast(cmd as varchar(24)) as 'Command',
cast(nt_username as varchar(18)) as 'User',
cast(loginame as varchar(24)) as 'Login'
from sysprocesses
WHERE hostname<>''
order by nt_username;
go
http://www.sqlhacks.com/index.php/Administration/ListConnectedUsers
for examples and explanations
New this week:
How to drop the time portion of a DateTime column in MS SQL Server
How to get both the details and the subtotals with Microsoft SQL
Server
How to summarize data with Microsoft SQL Server
How to optimize Microsoft SQL Server
How to retrieve data with Microsoft SQL Server
How to get how many records are in all the tables of a Microsoft SQL
Server database
How to calculate grand totals with SQL Server with the GROUP BY WITH
ROLLUP
How to get the 3rd highest salary with MS SQL Server
How to number rows without using cursors with MS SQL Server 2005
How to rank rows by grouping without using cursors with SQL Server
with dense_rank
How to know who is connected to your server with SQL Server?
How to manually delete duplicate rows with MS SQL Server
How to delete duplicate rows in bulk with MS SQL Server
How to move TEMPDB to another drive in Microsoft SQL Server
Improvements to SQL Server indexes
How to increase the size of TEMPDB

computer name access sql server

Is there a way to determine which workstation is accessing the SQL server? I
know in SQL Profile you can see the userid but can you see what actualy
workstation is accessing the server?
I found it
sp_who, tells me everything I need to know,
thanks,
"Mike" <Mike@.community.nospam.com> wrote in message
news:u$gQtjPtHHA.4952@.TK2MSFTNGP04.phx.gbl...
> Is there a way to determine which workstation is accessing the SQL server?
> I know in SQL Profile you can see the userid but can you see what actualy
> workstation is accessing the server?
>
|||On Jun 22, 11:12 am, "Mike" <M...@.community.nospam.com> wrote:
> Is there a way to determine which workstation is accessing the SQL server? I
> know in SQL Profile you can see the userid but can you see what actualy
> workstation is accessing the server?
select login_time as 'Login',
last_batch as 'Batch',
cast(status as varchar(12)) as 'Status',
cast(hostname as varchar(18)) as 'Host',
cast(program_name as varchar(32)) as 'Program',
cast(cmd as varchar(24)) as 'Command',
cast(nt_username as varchar(18)) as 'User',
cast(loginame as varchar(24)) as 'Login'
from sysprocesses
WHERE hostname<>''
order by nt_username;
go
http://www.sqlhacks.com/index.php/Administration/ListConnectedUsers
for examples and explanations
New this week:
How to drop the time portion of a DateTime column in MS SQL Server
How to get both the details and the subtotals with Microsoft SQL
Server
How to summarize data with Microsoft SQL Server
How to optimize Microsoft SQL Server
How to retrieve data with Microsoft SQL Server
How to get how many records are in all the tables of a Microsoft SQL
Server database
How to calculate grand totals with SQL Server with the GROUP BY WITH
ROLLUP
How to get the 3rd highest salary with MS SQL Server
How to number rows without using cursors with MS SQL Server 2005
How to rank rows by grouping without using cursors with SQL Server
with dense_rank
How to know who is connected to your server with SQL Server?
How to manually delete duplicate rows with MS SQL Server
How to delete duplicate rows in bulk with MS SQL Server
How to move TEMPDB to another drive in Microsoft SQL Server
Improvements to SQL Server indexes
How to increase the size of TEMPDB
sqlsql

computer name access sql server

Is there a way to determine which workstation is accessing the SQL server? I
know in SQL Profile you can see the userid but can you see what actualy
workstation is accessing the server?I found it
sp_who, tells me everything I need to know,
thanks,
"Mike" <Mike@.community.nospam.com> wrote in message
news:u$gQtjPtHHA.4952@.TK2MSFTNGP04.phx.gbl...
> Is there a way to determine which workstation is accessing the SQL server?
> I know in SQL Profile you can see the userid but can you see what actualy
> workstation is accessing the server?
>|||On Jun 22, 11:12 am, "Mike" <M...@.community.nospam.com> wrote:
> Is there a way to determine which workstation is accessing the SQL server?
I
> know in SQL Profile you can see the userid but can you see what actualy
> workstation is accessing the server?
select login_time as 'Login',
last_batch as 'Batch',
cast(status as varchar(12)) as 'Status',
cast(hostname as varchar(18)) as 'Host',
cast(program_name as varchar(32)) as 'Program',
cast(cmd as varchar(24)) as 'Command',
cast(nt_username as varchar(18)) as 'User',
cast(loginame as varchar(24)) as 'Login'
from sysprocesses
WHERE hostname<>''
order by nt_username;
go
http://www.sqlhacks.com/index.php/A...tConnectedUsers
for examples and explanations
New this week:
How to drop the time portion of a DateTime column in MS SQL Server
How to get both the details and the subtotals with Microsoft SQL
Server
How to summarize data with Microsoft SQL Server
How to optimize Microsoft SQL Server
How to retrieve data with Microsoft SQL Server
How to get how many records are in all the tables of a Microsoft SQL
Server database
How to calculate grand totals with SQL Server with the GROUP BY WITH
ROLLUP
How to get the 3rd highest salary with MS SQL Server
How to number rows without using cursors with MS SQL Server 2005
How to rank rows by grouping without using cursors with SQL Server
with dense_rank
How to know who is connected to your server with SQL Server?
How to manually delete duplicate rows with MS SQL Server
How to delete duplicate rows in bulk with MS SQL Server
How to move TEMPDB to another drive in Microsoft SQL Server
Improvements to SQL Server indexes
How to increase the size of TEMPDB

Composite or Primary Key

How do determine within SQL Server if key is a Primary Key
or Composite Key (Take two or more fields to uniquely
adentify a record.)? List Primary Key properties?
Thank You,
JimHi,
If a primary key is constructed with more than 1 columns then it is named as
Composite primary key.
Use the system stored procedure to identify the primary key.
sp_primary_keys_rowset @.table_name
If we have multiple entries with Ordinal (1,2,3..) then it is Composite key.
Note: you can even use sp_help <table name> to get the key information.
Thanks
Hari
MCDBA
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:04ea01c3da31$7f6983e0$a401280a@.phx.gbl...

quote:

---

> How do determine within SQL Server if key is a Primary Key
> or Composite Key (Take two or more fields to uniquely
> adentify a record.)? List Primary Key properties?
> Thank You,
> Jim

---

Composite or Primary Key

How do determine within SQL Server if key is a Primary Key
or Composite Key (Take two or more fields to uniquely
adentify a record.)? List Primary Key properties?
Thank You,
JimHi,
If a primary key is constructed with more than 1 columns then it is named as
Composite primary key.
Use the system stored procedure to identify the primary key.
sp_primary_keys_rowset @.table_name
If we have multiple entries with Ordinal (1,2,3..) then it is Composite key.
Note: you can even use sp_help <table name> to get the key information.
Thanks
Hari
MCDBA
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:04ea01c3da31$7f6983e0$a401280a@.phx.gbl...
> How do determine within SQL Server if key is a Primary Key
> or Composite Key (Take two or more fields to uniquely
> adentify a record.)? List Primary Key properties?
> Thank You,
> Jim