Mark a linie anyone

Is it possible to change the background color for a tablerow if you click on
a field, so that it is easier to see the data when there are many columns.
I tried this on the visibilty button on a field but it says tablerow2 is not
declared
=TableRow2.BackgroundColor.Value = "Silver";
----
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 38295 spam-mails
Betalende brugere får ikke denne besked i deres e-mails.
Hent en gratis SPAMfighter her.Event driven is provided just for hyperlinks and there is no such provision
for background color.
Amarnath
"CRM Master" wrote:
> Is it possible to change the background color for a tablerow if you click on
> a field, so that it is easier to see the data when there are many columns.
> I tried this on the visibilty button on a field but it says tablerow2 is not
> declared
> =TableRow2.BackgroundColor.Value = "Silver";
>
>
>
> ----
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at få 38295 spam-mails
> Betalende brugere får ikke denne besked i deres e-mails.
> Hent en gratis SPAMfighter her.
>
>

Mapping Elements to Database Fields in XSD for Bulkload

Hi Everyone,
Was wondering whether it is possible to map an element in a xml
document to a field in db table which has a different name.
A fragment of my Bulkload.xsd is below: Say I Changed my XML document
so that Event_ID was called EE_ID, could I still get EE_ID to map to
the Event_ID field in the database?
<xsd:element name="XML_WORKBOOKS" msch:relation="XML_WORKBOOKS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Event_ID" type="xsd:int" />
<xsd:element name="LastAuthor" type="xsd:string" />
<xsd:element name="Version" type="xsd:string" />
<xsd:element name="SaveComment" type="xsd:string" />
<xsd:element name="TM_ID" type="xsd:int" />
<xsd:element name="File_ID" type="xsd:int" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
Thanks for your help.
Yes, just use the field annotation.
e.g.
<xsd:element name="EE_ID" type="xsd:int" msch:field="Event_ID"/>
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"jnc" <j_culverwell@.yahoo.co.uk> wrote in message
news:44a3f856.0405091143.297ea942@.posting.google.c om...
> Hi Everyone,
> Was wondering whether it is possible to map an element in a xml
> document to a field in db table which has a different name.
> A fragment of my Bulkload.xsd is below: Say I Changed my XML document
> so that Event_ID was called EE_ID, could I still get EE_ID to map to
> the Event_ID field in the database?
>
> <xsd:element name="XML_WORKBOOKS" msch:relation="XML_WORKBOOKS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Event_ID" type="xsd:int" />
> <xsd:element name="LastAuthor" type="xsd:string" />
> <xsd:element name="Version" type="xsd:string" />
> <xsd:element name="SaveComment" type="xsd:string" />
> <xsd:element name="TM_ID" type="xsd:int" />
> <xsd:element name="File_ID" type="xsd:int" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> Thanks for your help.
|||Thanks, exactly what I was after.
Cheers....Jim
sql

Mapping columns of complexType to different table

In my xml file I have an element called pointInTime which has 4 elements
associated with it.
Among these 4 fields, I need to map 1 field to one SQL table and remaining
fields to another table.
How do I write XSD schema for this.
<xs:element name="pointInTime" sql:relation="SpanFileDetailsTemp">
<xs:complexType>
<xs:sequence>
<xs:element ref="date" sql:field="FileDate"/>
<xs:element ref="isSetl"/>
<xs:element ref="setlQualifier"/>
<xs:element ref="clearingOrg"/>
</xs:sequence>
</xs:complexType>
</xs:element>
I tried to map 1 field to the table.
Now should I repeat this for other table?
But If I repeat this, the lement name PointInTime would be duplicated,
right?
So, should I use <xs:element ref="PointInTime" sql:relation="xxxx"> like
this ?
What is the difference between <xs:> and <xsd:>
I found both these while surfing about XSD's.
Regards
Meena
Meenakshi wrote:

> What is the difference between <xs:> and <xsd:>
> I found both these while surfing about XSD's.
Whether the prefix is xs or xsd or some other prefix (e.g. pf) does not
matter in terms of XML, what matters is the namespace URI bound to the
prefix. So you can use
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"></xs:schema>
or
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"></xsd:schema>
or
<pf:schema xmlns:pf="http://www.w3.org/2001/XMLSchema"></pf:schema>
or any other allowed prefix you like as long as the prefix is bound to
the namespace URI http://www.w3.org/2001/XMLSchema.

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Mapping columns of complexType to different table

In my xml file I have an element called pointInTime which has 4 elements
associated with it.
Among these 4 fields, I need to map 1 field to one SQL table and remaining
fields to another table.
How do I write XSD schema for this.
<xs:element name="pointInTime" sql:relation="SpanFileDetailsTemp">
<xs:complexType>
<xs:sequence>
<xs:element ref="date" sql:field="FileDate"/>
<xs:element ref="isSetl"/>
<xs:element ref="setlQualifier"/>
<xs:element ref="clearingOrg"/>
</xs:sequence>
</xs:complexType>
</xs:element>
I tried to map 1 field to the table.
Now should I repeat this for other table?
But If I repeat this, the lement name PointInTime would be duplicated,
right?
So, should I use <xs:element ref="PointInTime" sql:relation="xxxx"> like
this ?
What is the difference between <xs:> and <xsd:>
I found both these while surfing about XSD's.
Regards
MeenaMeenakshi wrote:

> What is the difference between <xs:> and <xsd:>
> I found both these while surfing about XSD's.
Whether the prefix is xs or xsd or some other prefix (e.g. pf) does not
matter in terms of XML, what matters is the namespace URI bound to the
prefix. So you can use
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"></xs:schema>
or
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"></xsd:schema>
or
<pf:schema xmlns:pf="http://www.w3.org/2001/XMLSchema"></pf:schema>
or any other allowed prefix you like as long as the prefix is bound to
the namespace URI http://www.w3.org/2001/XMLSchema.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/sql

Mapping a string field to Boolean output in SELECT clause

Hello,
I am facing a problem in a SELECT clause which i cannot solve.
In my SQL table ("myTable") i have a few columns ("Column1", "Column2", "TypeColumn"). When I select different columns of the table, instead of getting the value of TypeColumn, i would like to get a boolean indicating whether its value is a certain string or not.
For example, the TypeColumn accepts only a number of selected strings: "AAA", "BBB", "CCC".
when i do a select query on the table, instead of asking for TypeColumn i would like to ask a boolean value of 1 if TypeColumn is "AAA" and 0 if TypeColumn is "BBB" or "CCC". Also, i would like to make this query while I am also fetching the other columns. And i would like to use one query to get all that. I thought something like thsi would work:

SELECT Column1 AS Col1, Column2 AS Col2, IF(TypeColumn = "AAA", 1, 0) AS Col3
FROM myTable

but this doesn't work in SQL 2005!
Is it possible to do something similar in SQL 2005 using one query only? i am trying to avoid multiple queries for this.

thanks a lot for your help!

Hi,

try this here:

SELECT Column1 AS Col1, Column2 AS Col2, CASE WHEN TypeColumn = "AAA" THEN 1 ELSE 0 END AS Col3
FROM myTable

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||It works!
Thank you, thank you, thank you!!!!!!!sql

Manually Inserting Data in DateTime Field

Using Server Explorer in VB 2005, I am manually entering data in a table in a SQL Server 2005 Express database that includes a DateTime field. I have tried every conceivable format, but no matter what I try get one of these 2 errors:

1. String was not recognized as valid DateTime

2. Operand type class; text incompatible with DateTime

I have Googled this to death, but no example which involves trying to enter the data manually, say from Server Explorer.

Formats tried include all datetime formats (mmddyy, yymmdd, using dashes or slashes, enclosing in single quotes or pound signs).

I would appreciate if someone could please give me an example that I can literally insert without error.

hi,

I tried myself inserting via the VS designer the following:

2006-09-19 (it works)
2006/09/19 (it works)
19/09/2006 (it works)
19-09-2006 (it works)

the 2 last entries are dangerous as the server could misunderstand the datetime format... my pc has the italian locale set, so it could be ok, but I do advise to use a "general" ISO format that can not bring to misunderstanding, thus YYYY-MM-DD

regards

|||

Andrea, appreciate the response, but I tried your suggestions, and some related ones (09/19/2006), and they did not work. The error was Operand type clash; text incompatible with DateTime.

Again, I am opening the SQL Server 2005 Express database, then the table, from Server Explorer, and then choosing Show Table Data from the shortcut menu, and then typing the data into the datetime column field. Any further ideas would be appreciated.

|||I am an idiot. I was typing the data into the wrong field. Your suggestions work. Sorry for wasting your time.

Manually Insert a Primary Key Value

I have a colleague who mysteriously lost his record in our Employee table.
The "employee ID" field serves as the primary key on the table.
How do I manually insert his record, including the old primary key value,
back into the table? That is, how do I bypass the primary-key constraint?
Thanks in advance,
Mark HolahanWhat is the definition of the table?
AMB
"Mark Holahan" wrote:

> I have a colleague who mysteriously lost his record in our Employee table.
> The "employee ID" field serves as the primary key on the table.
> How do I manually insert his record, including the old primary key value,
> back into the table? That is, how do I bypass the primary-key constraint?
> Thanks in advance,
> Mark Holahan
>
>|||Is this an identity field? if so use:
SET IDENTITY_INSERT ON
--execute insert statement here
SET IDENTITY_INSERT OFF|||You can't "bypass" a primary key constraint unless you drop it. I
assume you are actually referring to the IDENTITY property on this
column. The IDENTITY property is quite distinct from a PRIMARY KEY
constraint. If you want to insert an explicit IDENTITY value then use
the SET IDENTITY_INSERT table_name ON option.
Why does it matter to you if the row gets inserted with a different
IDENTITY value to the one it originally had? It shouldn't have been
possible for the accidental delete to cause "orphan" rows in a
referencing table - That's assuming you have correctly declared foreign
key constraints against the employee ID column. If you don't have
foreign keys then that's something you really ought to fix.
David Portas
SQL Server MVP
--|||AMB,
The table definition follows:
CREATE TABLE [dbo].[Employee] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[FName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BranchId] [int] NULL ,
[SalesRepId] [int] NULL ,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NetworkId] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Deactivated] [datetime] NULL ,
[ResetPW] [bit] NOT NULL ,
[Tries] [tinyint] NULL ,
[LastLoginDtm] [datetime] NULL ,
[PendingInfoUpdate] [bit] NOT NULL ,
[IsSalesRep] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] ADD
CONSTRAINT [DF_Employee_ResetPW] DEFAULT (0) FOR [ResetPW],
CONSTRAINT [DF_Employee_PendingInfoUpdate] DEFAULT (0) FOR
[PendingInfoUpdate],
CONSTRAINT [DF_Employee_IsSalesRep] DEFAULT (0) FOR [IsSalesRep]
GO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3E956B3B-CF85-4FBA-B885-41BE4C9A96FD@.microsoft.com...
> What is the definition of the table?
>
> AMB
> "Mark Holahan" wrote:
>|||Read David's post.
AMB
"Mark Holahan" wrote:

> AMB,
> The table definition follows:
> CREATE TABLE [dbo].[Employee] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [FName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BranchId] [int] NULL ,
> [SalesRepId] [int] NULL ,
> [Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NetworkId] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Deactivated] [datetime] NULL ,
> [ResetPW] [bit] NOT NULL ,
> [Tries] [tinyint] NULL ,
> [LastLoginDtm] [datetime] NULL ,
> [PendingInfoUpdate] [bit] NOT NULL ,
> [IsSalesRep] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
> CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Employee] ADD
> CONSTRAINT [DF_Employee_ResetPW] DEFAULT (0) FOR [ResetPW],
> CONSTRAINT [DF_Employee_PendingInfoUpdate] DEFAULT (0) FOR
> [PendingInfoUpdate],
> CONSTRAINT [DF_Employee_IsSalesRep] DEFAULT (0) FOR [IsSalesRep]
> GO
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:3E956B3B-CF85-4FBA-B885-41BE4C9A96FD@.microsoft.com...
>
>|||Distinction noted.
CIO of company claims RI puts unneeded burden on SQL Server. Therefore we
handle RI on the front end. I don't necessarily agree, especially when I
read in BOL that, "The query optimizer also uses constraint definitions to
build high-performance query execution plans." But I've never done the
homework to disprove his theory. So I abide.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107450710.189817.206210@.g14g2000cwa.googlegroups.com...
> You can't "bypass" a primary key constraint unless you drop it. I
> assume you are actually referring to the IDENTITY property on this
> column. The IDENTITY property is quite distinct from a PRIMARY KEY
> constraint. If you want to insert an explicit IDENTITY value then use
> the SET IDENTITY_INSERT table_name ON option.
> Why does it matter to you if the row gets inserted with a different
> IDENTITY value to the one it originally had? It shouldn't have been
> possible for the accidental delete to cause "orphan" rows in a
> referencing table - That's assuming you have correctly declared foreign
> key constraints against the employee ID column. If you don't have
> foreign keys then that's something you really ought to fix.
> --
> David Portas
> SQL Server MVP
> --
>|||The CIO is wrong. If he wants to design databases he should take a course
first ;-)
Obviously handling RI on the front end isn't working otherwise you wouldn't
have this problem. No surprises there.
David Portas
SQL Server MVP
--

Manipulating Xml using Sql Server

Hi.
I need some help please...
There is a field, type text, in a table, that conatins xml
document.
Is it possible, using sql server to do the following?
1. Get the Xml from that field
2. Search the xml for a specific element ( by using its
name )
3. Search in this element, a certain attribute.
4. If the attribute exists, modify its value.
5. Save it bacl to the table
I guess that I am looking for some kind of an XML DOM
inside Sql server.
Is it possible?
Thanks a lot
RoyAt the server level SQL provides very limited support for manipulating the
XML DOM.
A better option is to use client side libraries like SQLXML to manipulate
DOM objects.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Roy" <roy@.smsolutions.co.nz> wrote in message
news:07ce01c3b30a$3d9cffd0$a401280a@.phx.gbl...
> Hi.
> I need some help please...
> There is a field, type text, in a table, that conatins xml
> document.
> Is it possible, using sql server to do the following?
> 1. Get the Xml from that field
> 2. Search the xml for a specific element ( by using its
> name )
> 3. Search in this element, a certain attribute.
> 4. If the attribute exists, modify its value.
> 5. Save it bacl to the table
> I guess that I am looking for some kind of an XML DOM
> inside Sql server.
>
> Is it possible?
> Thanks a lot
> Roy
>|||"Roy" <roy@.smsolutions.co.nz> wrote in message
news:07ce01c3b30a$3d9cffd0$a401280a@.phx.gbl...
Roy, you can use sp_OA procs to instantie XML Dom object. The basic idea is
to create stored proc that receives xml data through text type input
parameter. now you can (using sp_OA) create xmldocument object and work with
it. It's complicated, and i'm not sure if this is the best way to do it (or
even recomended), but you can do it this way.
Regards,
Tomislav Kralj
MCSD/.NET, MCDBA
tomislav.kralj1@.zg.tel.hr

Manipulating dates

Hi
I have a field called paid to date and need to calculate the next date with
the same day of the month from getdate(). For example, paid to date of
13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
Any help in achieving this would be greatly appreciated
"Dene" <Dene@.discussions.microsoft.com> wrote in message
news:7C91B3BF-738D-4842-BD64-33863B2EC559@.microsoft.com...
> Hi
> I have a field called paid to date and need to calculate the next date
> with
> the same day of the month from getdate(). For example, paid to date of
> 13/02/2005 should give a next date of 13/02/2005 where getdate is
> 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> Any help in achieving this would be greatly appreciated
Can you post some DDL and SQL for what you are currently doing and what you
are trying to achieve.
I can't tell from your question what it is that you are after.
Rick Sawtell
MCT, MCSD, MCDBA
|||On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:

>Hi
>I have a field called paid to date and need to calculate the next date with
>the same day of the month from getdate(). For example, paid to date of
>13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
>Any help in achieving this would be greatly appreciated
Hi Dene,
Is this what you are after?
declare @.basedate smalldatetime
declare @.now smalldatetime
set @.basedate = '20050213'
set @.now = '20050210'
SELECT DATEADD(month,
DATEDIFF(month, @.basedate, @.now)
+ CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
@.basedate)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo.
This looks exactly what I'm after
Reards
Dene
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
>
> Hi Dene,
> Is this what you are after?
> declare @.basedate smalldatetime
> declare @.now smalldatetime
> set @.basedate = '20050213'
> set @.now = '20050210'
> SELECT DATEADD(month,
> DATEDIFF(month, @.basedate, @.now)
> + CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
> @.basedate)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Manipulating dates

Hi
I have a field called paid to date and need to calculate the next date with
the same day of the month from getdate(). For example, paid to date of
13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005
.
However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
Any help in achieving this would be greatly appreciated"Dene" <Dene@.discussions.microsoft.com> wrote in message
news:7C91B3BF-738D-4842-BD64-33863B2EC559@.microsoft.com...
> Hi
> I have a field called paid to date and need to calculate the next date
> with
> the same day of the month from getdate(). For example, paid to date of
> 13/02/2005 should give a next date of 13/02/2005 where getdate is
> 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> Any help in achieving this would be greatly appreciated
Can you post some DDL and SQL for what you are currently doing and what you
are trying to achieve.
I can't tell from your question what it is that you are after.
Rick Sawtell
MCT, MCSD, MCDBA|||On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:

>Hi
>I have a field called paid to date and need to calculate the next date with
>the same day of the month from getdate(). For example, paid to date of
>13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/200
5.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
>Any help in achieving this would be greatly appreciated
Hi Dene,
Is this what you are after?
declare @.basedate smalldatetime
declare @.now smalldatetime
set @.basedate = '20050213'
set @.now = '20050210'
SELECT DATEADD(month,
DATEDIFF(month, @.basedate, @.now)
+ CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
@.basedate)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo.
This looks exactly what I'm after
Reards
Dene
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
>
> Hi Dene,
> Is this what you are after?
> declare @.basedate smalldatetime
> declare @.now smalldatetime
> set @.basedate = '20050213'
> set @.now = '20050210'
> SELECT DATEADD(month,
> DATEDIFF(month, @.basedate, @.now)
> + CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
> @.basedate)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Manipulating dates

Hi
I have a field called paid to date and need to calculate the next date with
the same day of the month from getdate(). For example, paid to date of
13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
Any help in achieving this would be greatly appreciated"Dene" <Dene@.discussions.microsoft.com> wrote in message
news:7C91B3BF-738D-4842-BD64-33863B2EC559@.microsoft.com...
> Hi
> I have a field called paid to date and need to calculate the next date
> with
> the same day of the month from getdate(). For example, paid to date of
> 13/02/2005 should give a next date of 13/02/2005 where getdate is
> 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> Any help in achieving this would be greatly appreciated
Can you post some DDL and SQL for what you are currently doing and what you
are trying to achieve.
I can't tell from your question what it is that you are after.
Rick Sawtell
MCT, MCSD, MCDBA|||On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
>Hi
>I have a field called paid to date and need to calculate the next date with
>the same day of the month from getdate(). For example, paid to date of
>13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
>Any help in achieving this would be greatly appreciated
Hi Dene,
Is this what you are after?
declare @.basedate smalldatetime
declare @.now smalldatetime
set @.basedate = '20050213'
set @.now = '20050210'
SELECT DATEADD(month,
DATEDIFF(month, @.basedate, @.now)
+ CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
@.basedate)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo.
This looks exactly what I'm after
Reards
Dene
"Hugo Kornelis" wrote:
> On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
> >Hi
> >
> >I have a field called paid to date and need to calculate the next date with
> >the same day of the month from getdate(). For example, paid to date of
> >13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
> > However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> >
> >Any help in achieving this would be greatly appreciated
> Hi Dene,
> Is this what you are after?
> declare @.basedate smalldatetime
> declare @.now smalldatetime
> set @.basedate = '20050213'
> set @.now = '20050210'
> SELECT DATEADD(month,
> DATEDIFF(month, @.basedate, @.now)
> + CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
> @.basedate)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Manipulating Data

Hello all...

I have a table that inlcudes a field that represents a percentage value (like 1.23), but the data is presented as 000123 (hope that makes sense). I get the data from a location outside of my office and I have no say in how the data is formatted.

I need to run a query that multiplies one field by this above mentioned field.

Is there a way to convert the value to the way I need it programmatically (in a query) without putting it in a temporary table?

Example:

Table 1 - fields - ID, Amount
Table 2 - fields - Type, Percentage

Table 1 (one row of data) - 1 234
Table 2 (one row of data) - A 000123

I want to multiply the Amount from Table 1 (234) by the Percentage from Table 2 (000123) where the Type from Table 2 is equal to A.

This should be worked out as 234 * 1.23

Thanks for any help.

Quote:

Originally Posted by narpet

Hello all...

I have a table that inlcudes a field that represents a percentage value (like 1.23), but the data is presented as 000123 (hope that makes sense). I get the data from a location outside of my office and I have no say in how the data is formatted.

I need to run a query that multiplies one field by this above mentioned field.

Is there a way to convert the value to the way I need it programmatically (in a query) without putting it in a temporary table?

Example:

Table 1 - fields - ID, Amount
Table 2 - fields - Type, Percentage

Table 1 (one row of data) - 1 234
Table 2 (one row of data) - A 000123

I want to multiply the Amount from Table 1 (234) by the Percentage from Table 2 (000123) where the Type from Table 2 is equal to A.

This should be worked out as 234 * 1.23

Thanks for any help.

first, how are the two tables related? although these two tables may still be joined even if they are not related, it's a rare situation that you will join two unrelated tables.

try this:
select 1, amount, type, percentage, amount * cast(percentage as float)/100.00
from table1
full outer join table2 on cast(id as varchar(2)) = cast(type as varchar(2)).
and type = 'A'

since "full outer join" joins the two table wheather there matched records or not, it will always return the values on the right

i did not test this query, is this right?|||

Quote:

Originally Posted by ck9663

first, how are the two tables related? although these two tables may still be joined even if they are not related, it's a rare situation that you will join two unrelated tables.

try this:
select 1, amount, type, percentage, amount * cast(percentage as float)/100.00
from table1
full outer join table2 on cast(id as varchar(2)) = cast(type as varchar(2)).
and type = 'A'

since "full outer join" joins the two table wheather there matched records or not, it will always return the values on the right

i did not test this query, is this right?

I'm not at work right now. I will test this when I get in on Monday morning. Thanks for the info... I will post and let you know how this works. As an answer to your question... the two tables will be joined by a common account number.|||

Quote:

Originally Posted by narpet

I'm not at work right now. I will test this when I get in on Monday morning. Thanks for the info... I will post and let you know how this works. As an answer to your question... the two tables will be joined by a common account number.

then you use account number as the join key. whether it'll be an outer, left, right or inner join will be up to your requirement|||That worked perfectly. Thanks very much for the help!

manipulate field value from select statement

Hi all,

any assistance will be much appreciated on this one .... a bit clueless at the mo!

I've been trying to execute the code below in which part of my select statement is a calculated value i.e. Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE

code:

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) & "/" & SUBSTRING([SD],5,2) & "/" & Left([SD],4) AS STARTDATE,
Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE, vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

however this error message keeps coming up at runtime:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ENDDATE'.

My guess is it's happening when I try to get the date difference (DATEDIFF)....

help!!

Try this..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

|||

Sh... should have seen that one.

Cheers mate .. however I'm still having an error from that code:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_spend'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_dates'.

Is there some sort of restriction on selecting from a view in sql server?

|||

Bolugbe wrote:

My guess is it's happening when I try to get the date difference (DATEDIFF)....

You're right, the problem is in the DATEDIFF statement.
You cannot use just assigned aliases in calculations, so you should either copy/paste the formula for getting ENDDATE into DATEDIFF function or use nested select statements|||

Try this one..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), Convert(datetime,Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4))) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

Manipulate duplicate rows

I have two of the exact same rows, but I want to change one field of one of
the rows. How do I do this?
i.e.
table_a
group name revenue
A jim 20
A jim 20
I want to change one jim to ken.
I tried
update table_a
set name = 'ken'
from (select top 1 * from table_a) as t1
where table_a.group = t1.group
but this updates both records
On Fri, 15 Apr 2005 09:59:02 -0700, bamboo wrote:

> I have two of the exact same rows, but I want to change one field of one of
>the rows. How do I do this?
>i.e.
>table_a
>group name revenue
>A jim 20
>A jim 20
>I want to change one jim to ken.
>I tried
>update table_a
>set name = 'ken'
>from (select top 1 * from table_a) as t1
>where table_a.group = t1.group
>but this updates both records
Hi bamboo,
This really should not have happened - each table in a database should
always have a primary key. You have just found out why: there is no way
for SQL Server to distinguish one row from the other, so all operations
will affect both or none.
Here's a link to an article that discusses some techniques to clean up
the mess: http://www.aspfaq.com/show.asp?id=2431.
And here's a link to an article that explains what you can do to prevent
this from happening again: http://www.aspfaq.com/show.asp?id=2509.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Managing XML field with Enterprise Manager

With SQL Server 2000, I have a table with a field called fldhistory, defined
as a ntext [16] field.
This column is intended for storing some archived history data in XML format.
I have an example XML which is valid and:
1) has <1300 characters, including spaces
2) Wel-formed, readable by IE
3) <10 lines
However, I can't put anything more than say a few hundred charaters in this
column under Enterprise Manager (for testing purposes), the past option is
simply disabled and if I try ctrl-V, I get a Windows warning tone.
why is this and how can I fix this? the ntext column should be capable of
handling >1300 characters!
Hello,
Please refer to the following information in SQL server Books Online(BOL):
Topic: Adding ntext, text, or image Data to Inserted Rows
These are ways to add ntext, text, or image values to a row:
"Specify relatively short amounts of data in an INSERT statement in the
same way char, nchar, or binary data is.
"Use the WRITETEXT statement. For more information, see WRITETEXT.
"ADO applications can use the AppendChunk method to specify long amounts
of ntext, text, or image data. For more information, see Managing Long Data
Types.
"OLE DB applications can use the ISequentialStream interface to write new
ntext, text, or image values. For more information, see BLOBs and OLE
Objects.
"ODBC applications can use the data-at-execution form of SQLPutData to
write new ntext, text, or image values. For more information, see Managing
text and image Columns.
"DB-Library applications can use the dbwritetext function. For more
information, see Text and Image Functions.
You can use above ways to insert ntext data. Please also refer to the
following topics in BOL:
"Using text and image Data"
"Managing ntext, text, and image Data"
"text, ntext, and image Data When text in row Is Set to ON"
You can also refer to the following articles which provide good information:
194975 How To Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/?id=194975
258038 How To Access and Modify SQL Server BLOB Data by Using the ADO Stream
http://support.microsoft.com/?id=258038
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Managing XML field with Enterprise Manager

With SQL Server 2000, I have a table with a field called fldhistory, defined
as a ntext [16] field.
This column is intended for storing some archived history data in XML format.
I have an example XML which is valid and:
1) has <1300 characters, including spaces
2) Wel-formed, readable by IE
3) <10 lines
However, I can't put anything more than say a few hundred charaters in this
column under Enterprise Manager (for testing purposes), the past option is
simply disabled and if I try ctrl-V, I get a Windows warning tone.
why is this and how can I fix this? the ntext column should be capable of
handling >1300 characters!Hello,
Please refer to the following information in SQL server Books Online(BOL):
Topic: Adding ntext, text, or image Data to Inserted Rows
---
These are ways to add ntext, text, or image values to a row:
" Specify relatively short amounts of data in an INSERT statement in the
same way char, nchar, or binary data is.
" Use the WRITETEXT statement. For more information, see WRITETEXT.
" ADO applications can use the AppendChunk method to specify long amounts
of ntext, text, or image data. For more information, see Managing Long Data
Types.
" OLE DB applications can use the ISequentialStream interface to write new
ntext, text, or image values. For more information, see BLOBs and OLE
Objects.
" ODBC applications can use the data-at-execution form of SQLPutData to
write new ntext, text, or image values. For more information, see Managing
text and image Columns.
" DB-Library applications can use the dbwritetext function. For more
information, see Text and Image Functions.
---
You can use above ways to insert ntext data. Please also refer to the
following topics in BOL:
"Using text and image Data"
"Managing ntext, text, and image Data"
"text, ntext, and image Data When text in row Is Set to ON"
You can also refer to the following articles which provide good information:
194975 How To Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/?id=194975
258038 How To Access and Modify SQL Server BLOB Data by Using the ADO Stream
http://support.microsoft.com/?id=258038
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Managing XML field with Enterprise Manager

With SQL Server 2000, I have a table with a field called fldhistory, defined
as a ntext [16] field.
This column is intended for storing some archived history data in XML format
.
I have an example XML which is valid and:
1) has <1300 characters, including spaces
2) Wel-formed, readable by IE
3) <10 lines
However, I can't put anything more than say a few hundred charaters in this
column under Enterprise Manager (for testing purposes), the past option is
simply disabled and if I try ctrl-V, I get a Windows warning tone.
why is this and how can I fix this? the ntext column should be capable of
handling >1300 characters!Hello,
Please refer to the following information in SQL server Books Online(BOL):
Topic: Adding ntext, text, or image Data to Inserted Rows
---
These are ways to add ntext, text, or image values to a row:
" Specify relatively short amounts of data in an INSERT statement in the
same way char, nchar, or binary data is.
" Use the WRITETEXT statement. For more information, see WRITETEXT.
" ADO applications can use the AppendChunk method to specify long amounts
of ntext, text, or image data. For more information, see Managing Long Data
Types.
" OLE DB applications can use the ISequentialStream interface to write new
ntext, text, or image values. For more information, see BLOBs and OLE
Objects.
" ODBC applications can use the data-at-execution form of SQLPutData to
write new ntext, text, or image values. For more information, see Managing
text and image Columns.
" DB-Library applications can use the dbwritetext function. For more
information, see Text and Image Functions.
---
You can use above ways to insert ntext data. Please also refer to the
following topics in BOL:
"Using text and image Data"
"Managing ntext, text, and image Data"
"text, ntext, and image Data When text in row Is Set to ON"
You can also refer to the following articles which provide good information:
194975 How To Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/?id=194975
258038 How To Access and Modify SQL Server BLOB Data by Using the ADO Stream
http://support.microsoft.com/?id=258038
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.