Add two digits to the front?

Hi all,
I have a number like so 778625 and want to make
this a more meaningful date (UK) so how difficult is it to
add two digits in this case 19 to the front of this number?
Many thanks
SamThis is a date? I'm not sure I understand how 1977 is any more meaningful
than 77, when the rest of the number is 8625. But okay, I suppose you could
do this:
DECLARE @.i INT
SET @.i = 778625
SELECT '19'+CAST(@.i AS VARCHAR)
And if you have to do it conditionally, say if the first two digits are less
than 30 you assume it's 20xx, then
DECLARE @.i INT, @.ivc VARCHAR(12)
SELECT @.i = 778625, @.ivc = CAST(@.i AS VARCHAR(32))
SELECT CASE WHEN CONVERT(TINYINT, LEFT(@.ivc, 2))>=30 THEN '19' ELSE '20' END
+ @.ivc
"Sam" <sgpgpjr@.yahoo.ie> wrote in message
news:082a01c3462b$f1924960$a001280a@.phx.gbl...
> Hi all,
> I have a number like so 778625 and want to make
> this a more meaningful date (UK) so how difficult is it to
> add two digits in this case 19 to the front of this number?
> Many thanks
> Sam
>

Add new primary key column for replicated table with pull subscrip

I need to add a new column to a replicated table. But in my case a new column should be a part of a primary key. So, I can't use sp_repladdcolumn procedure since we need to completely repopulate the whole table and there is no default value for a new colu
mn. In addition to that this column should be first in the table's columns list, not the last one what happens when you usually alter the table adding a new column.
More there, I have a pull subscription and it looks like there is no way to drop subscription for a specified article only, like it is possible for a push subscription. I would appreciate any help pointing to an easiest way to add a new primary key column
for a replicated table with pull subscription.
TIA
Libra,
ordinarily you should be able to use sp_droparticle, sp_addarticle and
sp_refreshsubscriptions however to add the new column in a specific position
you'll need to drop the published article then recreate it and resubscribe.
You might use a nosync subscription to make things easier if the snapshot is
particularly large, but this will have restrictions if you later want to add
an article or column, so I'd recommend initializing with the complete
snapshot as per usual.
HTH,
Paul Ibison
(BTW no doubt you have seen other threads on this but placing columns in a
particular order is not generally considered 'good form' as TSQL should
ideally use explicit column names rather than rely on position.)

add digits to the front

Hi all,
I have a number like so 778625 and want to make this a more meaningful
date (UK) so how difficult is it to add two digits in this case 19 to
the front of this number?

Many thanks
Sam

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!actually i don't need it to be a date i just need to add 19 to the front
of the number.
Thanks again
Sam

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You can do:

SELECT CAST(CAST(19 AS VARCHAR) + CAST(778625 AS VARCHAR) AS INT)

Another option is to do:

SELECT 19 * POWER(10, LEN(778625)) + 778625

--
- Anith
( Please reply to newsgroups only )|||Not really sure what you mean but something like

select convert(int,'19'+convert(varchar(10),fld))

--
Posted via http://dbforums.com

Add defendant

I have a table in my report that shows cases, that are grouped by Court and
Case. Each case can have one or more defendants. How do I add the defendant
data to the table?
In other words, how do I add a sub-table in my current table and link by
Court and Case?HI,
The best way to me seems that you need a one-to-many relationship here. I
say one-to-many, as it seems unlikely that a defendant would me involved in
multiple cases. If so then you need to have a many-to-many n:n
Create a defendants table, with a foreign key. THe foreign key would then
contain the case key, thereby giveing one case the ability to have one or
more defendants. All thats left is to do the proper relation.
Robert
"Mike OKC" <MikeOKC@.discussions.microsoft.com> wrote in message
news:3CD766B4-487D-4DAD-8EC3-6120FBEFC8CA@.microsoft.com...
> I have a table in my report that shows cases, that are grouped by Court
and
> Case. Each case can have one or more defendants. How do I add the
defendant
> data to the table?
> In other words, how do I add a sub-table in my current table and link by
> Court and Case?
>|||I already have two tables. Case Table and Defentant Table. Its a one-many
join.
When I said table I mean the report table, the object off the toolbox. I
want the report table to show multiple defendants per case.
"Robert Bravery" wrote:
> HI,
> The best way to me seems that you need a one-to-many relationship here. I
> say one-to-many, as it seems unlikely that a defendant would me involved in
> multiple cases. If so then you need to have a many-to-many n:n
> Create a defendants table, with a foreign key. THe foreign key would then
> contain the case key, thereby giveing one case the ability to have one or
> more defendants. All thats left is to do the proper relation.
> Robert
> "Mike OKC" <MikeOKC@.discussions.microsoft.com> wrote in message
> news:3CD766B4-487D-4DAD-8EC3-6120FBEFC8CA@.microsoft.com...
> > I have a table in my report that shows cases, that are grouped by Court
> and
> > Case. Each case can have one or more defendants. How do I add the
> defendant
> > data to the table?
> >
> > In other words, how do I add a sub-table in my current table and link by
> > Court and Case?
> >
>
>|||Hello Mike,
I understand that you'd like to show cases and their defendants in one
report. If I'm off-base, please let me know.
Generally you shall use a query with inner join in dataset so that you
could list this informaiton in one report.
For example:
cases table: caseid, casename
casedefendants table: caseid, defendant
select cases.caseid, cases.name, casedefendants.defendant from cases inner
join casedefendants on cases.caseid=casedefendants.caseid
The report shall be sth like:
caseid casename defendant
1 case1 def1
1 case1 def2
2 case2 def3
2 case2 def4
If this does not meet your requirement, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I've changed my report to list boxes. This post is no longer required.
Thank you for your reply.
"Peter Yang [MSFT]" wrote:
> Hello Mike,
> I understand that you'd like to show cases and their defendants in one
> report. If I'm off-base, please let me know.
> Generally you shall use a query with inner join in dataset so that you
> could list this informaiton in one report.
> For example:
> cases table: caseid, casename
> casedefendants table: caseid, defendant
> select cases.caseid, cases.name, casedefendants.defendant from cases inner
> join casedefendants on cases.caseid=casedefendants.caseid
> The report shall be sth like:
> caseid casename defendant
> 1 case1 def1
> 1 case1 def2
> 2 case2 def3
> 2 case2 def4
> If this does not meet your requirement, please feel free to let's know.
> Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Add Column to an existing table and mention position of the column

Hi all,
Can anybody please help me in this case.

I have a table test(NAME,CITY)
Now i want to add a new column "ID" as primary key to table test and the position of the table should be before column "NAME".

If possible, Please suggest

Thanks
Bob

You can not do that using T-SQL. The way "Enterprise Manager" or "SSMS" does it, is creating a new table with layout you want, copying data, dropping constraint that references the old table, dropping the old table, renaming the new table and recreating all constraints and indexes.

In the relational world, the position of the column is not important.

AMB

|||The only way you can do that is to create a new table, then transfer the data from the old table to the new one, then drop the old table, and finally, rename the new table to the old table's name.|||

Are their any good examples for this action, i thinking it would be easier to add new columns to the aspnet_Profile Table. To expand the user's profile data into single columns instead of one blob of data that is potentially useless. When using a SqlDataProvider for the fact the data in the row comes out in one long string of information.

Unless theirs away to break that information up into useful sections for a FormView or GridView..

add additional right scale on chart report

Hi there,

I have a case. I have 3 numeric fields and 1 category field to be displayed on bar chart reports. the problem is 2 of the 3 numeric fields have significant different scale value. so I need to add additonal scale at the right side of bar chart to represent 1 numeric fields + the 2 other numeric fields at the left side scale (Y). Please advice. Thanks.

R'gards

Toni

A secondary y-axis is currently not supported natively. Adding this feature in RS charts natively is under consideration for a future release.

Note: RS 2005 also has the new CustomReportItem feature. This enables ISVs to offer add-on components which integrate with RS 2005 and e.g. provide secondary y-axis in charts already today. E.g. Dundas Software offers such a product which needs to be licensed separately.

-- Robert