Determine the business day

If I have a calendar table, that has the business days of the entire year ie 2007.--How do I assign it a business day number for eg JUNE 2007 has 21 business days.. Hence on my calendar tbl, I have 21 records for June.. My question is:

How do i assign a business day number eg 6-1-07 is BUSDAY1, 6-4-07 Is Busday2, and so on..

Basicallly, I need to run an update on my calendar table, to indicate, what BUSDAY number it is ie 6-4-07 is BUSDAY 2, 6-5-07 IS Busday 3 and so on, for the entire year 2007.

Pl advise.

if you use sql server 2005 then you can use the Row_Number() ..

Code Snippet

Update MyCalendar

Set

BusinessDayNumber = 'BUSDAY' + Cast(data.Number as varchar)

From

(Select Date,Row_Number() OVER(Partition By Year,Month Order By Date) From MyCalendar) as Data

Where

Data.Date = MyCalendar.Date

if you use SQL Server 2000,

Code Snippet

Update MyCalendar

Set

BusinessDayNumber = (

Select 'BUSDAY' + Cast(Count(*) as Varchar) From MyCalendar Sub

Where

MyCalendar.Year = Sub.Year

and MyCalendar.Month = Sub.Month

and Sub.Date <= MyCalendar.Date

)

|||

Hi Tarana,

Which version of SS are you using?

-- 2005

;with cte

as

(

select

[date], BSnumber,

row_number() over(partition by year([date]), month([date]) order by [date]) as rn

from

dbo.calendar

where

IsBusinessDay = 1

)

update cte

set BDnumber = rn

-- 2000 / 2005

update dbo.calendar

set BDnumber = (

select

count(*)

from

dbo.calendar as c

where

year(c.[date]) = year(dbo.calendar.[date])

and month(c.[date]) = month(dbo.calendar.[date])

and c.[date] <= dbo.calendar.[date]

and c.IsBusinessDay = 1

)

where IsBusinessDay = 1

go

AMB

|||

You can determine the Day of the Week very easily:

selectdatepart(weekday,) from

Depending on the settings this will return 1 for sunday, 2 for Monday, 3 for Wednesday etc.

So:

Code Snippet

selectdatepart(weekday,<DateField>)-1 as [WorkDay]

from<TableName>

wheredatepart(weekday,<DateField>)between 2 and 6

|||THis code worked beautiful.sql

determine if value in field is number

I have a varchar field in a table that could contain values such as 123,
345, ABC, or CDF
I want to do a select statement on the table to return records where this
field could be number (123, 345) and exclude those that are not (ABC, CDF).
Is there an IsNum function or something like that?
hi matt,
make use of isnumeric function, which returns 1 for valid numeric value.
ex:
select *
from
<table>
where isnumeric(col) = 1
Vishal Parkar
vgparkar@.yahoo.co.in

Determine database no longer in use - Redux

I've attempted a number of methods to capture whether
specific databases are in use. I've configured an alert
for each one to trigger if the number of Active
Transactions exceeds 0. If this occurs, a job is run
which sends me an email via SMTP.
The question I have right now is what qualifies as an
Active Transaction? I can't find any reference to the
specific performance conditions in the BOL, just how to
set them up. I make changes to a test database and
sometimes the alert fires and sometimes it doesn't.
Thanks for any insight.
AllenAllen
One way you could do it is to detach the databases you
think are not being used and wait to see if anyone
complains. If you do make a mistake you can attach again
very quickly.
Depending on your company and what the databases are used
for you may or may not be able to do this. I know I would
not be able to, but it would work.
You could also use profiler to see if there is any
activity on a database. You might have issues with that if
you need to run it for several days and you have other
active databases on the same server.
Hope this helps
John
John

Determin whether port number assigned dynamically or hardcoded

I undstand that if you choose to dynamically assign a port number to an
instance this is a once only operation performed the first time that instance
is started and from then on it always attepts to use that port number.
Having just started with a new company I'm trying to work out if instance
port numbers were assigned dynamically or hardcoded during install as this
affect the client connection properties for connections (if hardcoded each
client needs to be configured with the port number rather than allowing an
instance to be resolved to a port).
Is there a regkey or anything that says how ports were assigned?
Steve
Steve Morgan
MCDBA
Snr Production DBA
If you are looking for a registry key to determine if SQL
Server is listening on the default port, it's located at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\SuperSocketNetLib\Tcp
That will give you the listening port.
You can also get the information from the SQL Server log.
You can also get the information using various network
commands, tools.
-Sue
On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:

>I undstand that if you choose to dynamically assign a port number to an
>instance this is a once only operation performed the first time that instance
>is started and from then on it always attepts to use that port number.
>Having just started with a new company I'm trying to work out if instance
>port numbers were assigned dynamically or hardcoded during install as this
>affect the client connection properties for connections (if hardcoded each
>client needs to be configured with the port number rather than allowing an
>instance to be resolved to a port).
>Is there a regkey or anything that says how ports were assigned?
>Steve
|||Hi Sue
Thnx for the reply but thats not my question - I know how to check the port
numbers currently being used.
What I need to work out is whether during the install the option to
dynamically assign instance port numbers was choosen or if they were
pre-choosen & hardcoded.
According to a technet article on connectivity problems this decision
affects whether on the client machine you can use the <server>\<instance
name> in your connection string or whether you have to use <server>\<port
number>
I'm having intermittent connection problems usine <server>\<instance name>
and want to know if the install port decision could be the root cause.
Steve
"Sue Hoegemeier" wrote:

> If you are looking for a registry key to determine if SQL
> Server is listening on the default port, it's located at:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\SuperSocketNetLib\Tcp
> That will give you the listening port.
> You can also get the information from the SQL Server log.
> You can also get the information using various network
> commands, tools.
> -Sue
> On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
> <SteveMorgan@.discussions.microsoft.com> wrote:
>
>
|||Check the same area in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\<IYournstanceName>\MSSQLServer\SuperSocketN etLib\Tcp
The combination of the values for TCPDynamicPorts and
TCPPort will help you determine the setting. You can find
them outlined in the following article:
http://support.microsoft.com/?id=823938
-Sue
On Wed, 1 Dec 2004 02:11:02 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue
>Thnx for the reply but thats not my question - I know how to check the port
>numbers currently being used.
>What I need to work out is whether during the install the option to
>dynamically assign instance port numbers was choosen or if they were
>pre-choosen & hardcoded.
>According to a technet article on connectivity problems this decision
>affects whether on the client machine you can use the <server>\<instance
>name> in your connection string or whether you have to use <server>\<port
>number>
>I'm having intermittent connection problems usine <server>\<instance name>
>and want to know if the install port decision could be the root cause.
>Steve
>
>"Sue Hoegemeier" wrote:

Determin whether port number assigned dynamically or hardcoded

I undstand that if you choose to dynamically assign a port number to an
instance this is a once only operation performed the first time that instanc
e
is started and from then on it always attepts to use that port number.
Having just started with a new company I'm trying to work out if instance
port numbers were assigned dynamically or hardcoded during install as this
affect the client connection properties for connections (if hardcoded each
client needs to be configured with the port number rather than allowing an
instance to be resolved to a port).
Is there a regkey or anything that says how ports were assigned?
Steve
Steve Morgan
MCDBA
Snr Production DBAIf you are looking for a registry key to determine if SQL
Server is listening on the default port, it's located at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\SuperSocketNet
Lib\Tcp
That will give you the listening port.
You can also get the information from the SQL Server log.
You can also get the information using various network
commands, tools.
-Sue
On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:

>I undstand that if you choose to dynamically assign a port number to an
>instance this is a once only operation performed the first time that instan
ce
>is started and from then on it always attepts to use that port number.
>Having just started with a new company I'm trying to work out if instance
>port numbers were assigned dynamically or hardcoded during install as this
>affect the client connection properties for connections (if hardcoded each
>client needs to be configured with the port number rather than allowing an
>instance to be resolved to a port).
>Is there a regkey or anything that says how ports were assigned?
>Steve|||Hi Sue
Thnx for the reply but thats not my question - I know how to check the port
numbers currently being used.
What I need to work out is whether during the install the option to
dynamically assign instance port numbers was choosen or if they were
pre-choosen & hardcoded.
According to a technet article on connectivity problems this decision
affects whether on the client machine you can use the <server>\<instance
name> in your connection string or whether you have to use <server>\<port
number>
I'm having intermittent connection problems usine <server>\<instance name>
and want to know if the install port decision could be the root cause.
Steve
"Sue Hoegemeier" wrote:

> If you are looking for a registry key to determine if SQL
> Server is listening on the default port, it's located at:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\SuperSocketN
etLib\Tcp
> That will give you the listening port.
> You can also get the information from the SQL Server log.
> You can also get the information using various network
> commands, tools.
> -Sue
> On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
> <SteveMorgan@.discussions.microsoft.com> wrote:
>
>|||Check the same area in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\<IYournstanceName>\MSSQLServer\SuperSocketNetLib\Tcp
The combination of the values for TCPDynamicPorts and
TCPPort will help you determine the setting. You can find
them outlined in the following article:
http://support.microsoft.com/?id=823938
-Sue
On Wed, 1 Dec 2004 02:11:02 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue
>Thnx for the reply but thats not my question - I know how to check the port
>numbers currently being used.
>What I need to work out is whether during the install the option to
>dynamically assign instance port numbers was choosen or if they were
>pre-choosen & hardcoded.
>According to a technet article on connectivity problems this decision
>affects whether on the client machine you can use the <server>\<instance
>name> in your connection string or whether you have to use <server>\<port
>number>
>I'm having intermittent connection problems usine <server>\<instance name>
>and want to know if the install port decision could be the root cause.
>Steve
>
>"Sue Hoegemeier" wrote:
>

Design question SQL Server 2005

I am very new to SQL Server 2005. I have a database of criminal records consisting of a master table of names where the primary key is a case number and 3 related minor tables: violations, charges, and appeals. They are linked by the case number with a one-to-many relationship. Not all of these tables have a record for every master record.

I am designing a simple lookup program in VB 2005 in which info from all tables will be on one screen. A search will be done on either name or case number.

What is the best way so set up views and/or stored procs for such a program? Do I need a stored proc for each minor table or is there a way to set up one proc to pull all of the info? Did I mention I was new at this? I have worked a lot with Access and recognize just enough things in SQL Server to be really confused.

Thanks for the help!

To get you started, you can get all the info with a single query. eg if we assume case number:

SELECT m.col1, v.col1, c.col1, a.col1
FROM masterTable
LEFT JOIN violations v
ON m.caseid = v.caseid
LEFT JOIN charges c
ON m.caseid = c.caseid
LEFT JOIN appeals a
ON m.caseid = a.caseid
WHERE m.caseid = @.caseid

Depending on how your app should work, you may not want to display all details if name is serached for, but rather use name as a way to look up the case number first, then go query for the details.

/Kenneth

design best practices on series number

Hi,

I need to design a table header for inventory transactions with the specifications as follows:

1. System-generated series numbers (integer)

2. Series numbers must be unique by branch by transaction type. Thus if I have following:

Branches: Br1, Br2

Transaction Type: SRS (Stock Receipt from Supplier), SRB(.. from Branch)

The series number must be implemented in such a way that,

Br1 SRS 0000000001

Br1 SRB 0000000001

Br2 SRS 0000000001

Br2 SRB 0000000001

Then, in every INSERT, series number should be incremented by 1, grouped by branch by transaction type. That is, after INSERT with Br1/SRB the figure may now look like,

Br1 SRS 0000000001

Br1 SRB 0000000002

How do I design my table in order to achieve this? Note that this table header will have a detail (master/detail) referenced by foreign key.

Thanks in advance.

What I have come up so far are the following:

1. Create an identity field which will be the designated PK for the table header.

2. Branch, Trx_Type, SeriesNum will be a compounded index with a unique constraint.

3. In the branch office, there will be a shared text file containing the last series number for the branch so that, upon saving the transaction (setup is real-time online), the system will get the last series number from the file then increment by 1 and use it as the series number for the transaction. Upon completion, the system will update the file with the new last series number.

I need your comments on this, and if you have a better solution, pls let me know.

Design Aggregation Problem

I have some problem. design aggregation cannot be made on several cubes that have a lot of data, and

have a lot of measures and dimension (the number of fields in the table more or

less 150 fields). They return 0% optimization level when we run design

aggregation.

This link have been published a few posts down but I repeat it here again. This is a good guide of how to create good aggregations: http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

HTH

Thomas Ivarsson