Adjust number of decimal places

I have a column used in my report that has values such as:
496.1000
These are money values and thus I require them to be in the format:
496.10
How do I get rid of the two extra zeroes?
(my sql statement actually does it, but they still appear in reporting
services for some reason)
Thanks!Don't worry about the above - I found the answer in the SQL 2005
documentation
For those interested, it was under:
Formatting Numeric and Date Values in a Report
Cheers
On Dec 18, 9:27 am, "David Conte" <davco...@.gmail.com> wrote:
> I have a column used in my report that has values such as:
> 496.1000
> These are money values and thus I require them to be in the format:
> 496.10
> How do I get rid of the two extra zeroes?
> (my sql statement actually does it, but they still appear in reporting
> services for some reason)
> Thanks!

adjust my code

Hi,
I've created this code to determine the dates for an exam when a
certification must be met within a X number of months or years. The last
deadline date for an exam is the enddate. The marge where one can
schedule his time to study depends on how many exams he must take. So
based on the enddate i calculate backwards to the startdate to get
intervals of when the next examdate should be.
In my testscenario i used 12 months and the number of exams are 4. The
code gave me the results i needed, but when i tried e.g. 18 months the
results are not what i expected. The dates are somewhat correct but
there were too many dates.
Can someone see the error in my code?
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int,
set @.begindate = '2005-11-23'
set @.enddate = dateadd(mm,12,@.begindate)
set @.exams = 4
set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
create table #examdates(id int identity(1,1) , examdate datetime)
--i inserted the enddate as startingpoint, but this could be done
--better, i think
insert into #examdates(examdate)
values(@.enddate)
while (select count(*) from #examdates) <= @.intervals
begin
set @.examdate = (select examdate from #examdates where id in
(select max(id) from #examdates))
insert into #examdates(examdate)
select dateadd(mm,-@.exams,@.examdate) as examdate
end
select examdate from #examdates order by examdateJason
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
>
What do you want to return if you put 18 months in?
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:eonG0vfEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I've created this code to determine the dates for an exam when a
> certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can schedule
> his time to study depends on how many exams he must take. So based on the
> enddate i calculate backwards to the startdate to get intervals of when
> the next examdate should be.
> In my testscenario i used 12 months and the number of exams are 4. The
> code gave me the results i needed, but when i tried e.g. 18 months the
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
> Can someone see the error in my code?
> declare @.exams int,
> @.begindate datetime,
> @.enddate datetime,
> @.examdate datetime,
> @.intervals int,
> set @.begindate = '2005-11-23'
> set @.enddate = dateadd(mm,12,@.begindate)
> set @.exams = 4
> set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
> create table #examdates(id int identity(1,1) , examdate datetime)
> --i inserted the enddate as startingpoint, but this could be
> done --better, i think
> insert into #examdates(examdate)
> values(@.enddate)
> while (select count(*) from #examdates) <= @.intervals
> begin
> set @.examdate = (select examdate from #examdates where id in
> (select max(id) from #examdates))
> insert into #examdates(examdate)
> select dateadd(mm,-@.exams,@.examdate) as examdate
> end
> select examdate from #examdates order by examdate|||You are creating a variable called intervals which is really the number of
months that you have to take each exam based on the way that you set it.
datediff(mm,@.begindate,@.enddate)/@.exams
So for 12 months this would be 3 but for 18 months it would be 4.5.
You then use this as the loop constraint for adding in your exam dates.
It seems to me like your look constraint should just be the number of exams
that you need to take since you are trying to find a date when to take each
of the exams.
This is the core of your problem. I didn't put any thought into the rest of
the algorithm and how you might be able to accomplish your goal easier.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Jason" wrote:

> Hi,
> I've created this code to determine the dates for an exam when a
> certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can
> schedule his time to study depends on how many exams he must take. So
> based on the enddate i calculate backwards to the startdate to get
> intervals of when the next examdate should be.
> In my testscenario i used 12 months and the number of exams are 4. The
> code gave me the results i needed, but when i tried e.g. 18 months the
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
> Can someone see the error in my code?
> declare @.exams int,
> @.begindate datetime,
> @.enddate datetime,
> @.examdate datetime,
> @.intervals int,
> set @.begindate = '2005-11-23'
> set @.enddate = dateadd(mm,12,@.begindate)
> set @.exams = 4
> set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
> create table #examdates(id int identity(1,1) , examdate datetime)
> --i inserted the enddate as startingpoint, but this could be done
> --better, i think
> insert into #examdates(examdate)
> values(@.enddate)
> while (select count(*) from #examdates) <= @.intervals
> begin
> set @.examdate = (select examdate from #examdates where id in
> (select max(id) from #examdates))
> insert into #examdates(examdate)
> select dateadd(mm,-@.exams,@.examdate) as examdate
> end
> select examdate from #examdates order by examdate
>|||Ryan Powers wrote:
> You are creating a variable called intervals which is really the number of
> months that you have to take each exam based on the way that you set it.
> datediff(mm,@.begindate,@.enddate)/@.exams
> So for 12 months this would be 3 but for 18 months it would be 4.5.
> You then use this as the loop constraint for adding in your exam dates.
> It seems to me like your look constraint should just be the number of exam
s
> that you need to take since you are trying to find a date when to take eac
h
> of the exams.
>
> This is the core of your problem. I didn't put any thought into the rest
of
> the algorithm and how you might be able to accomplish your goal easier.
> HTH
>
Hi Ryan,
I want to calculate a date for taking an exam. The intervals are just
the number of months when the next exam must be taken.
Could you point out to me where i should better my code?|||Uri Dimant wrote:
> Jason
>
>
> What do you want to return if you put 18 months in?
>
> "Jason" <jasonlewis@.hotmail.com> wrote in message
> news:eonG0vfEGHA.2040@.TK2MSFTNGP14.phx.gbl...
>
>
>
Hi Uri,
I want to return 4 examdates because the number of exams to be taken are
4, the higher the months the longer someone may study until the next
date occurs.
In case of adding 18 months to the begindate, divide that with the
number of exams, you'll get 4.5 months. So knowing the enddate (is also
the last examdate deadline) i substract the 4.5 months from the enddate
which will give me the examdate before that and so on.
Can you find the mistake i have made in the code?|||Try the following script. I have given some hint about changes made in your
script. Inside the While loop, I have removed the select statement from the
#examdates table. This will enhance performance.
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int
-- Added script
declare @.NextExamDate datetime, @.LastExamDate datetime
--
set @.begindate = '20051101'
set @.enddate = dateadd(mm,12,@.begindate)
-- Added script
set @.LastExamDate = @.enddate
--
set @.exams = 4
--[replaced with below line] set @.intervals =
datediff(mm,@.begindate,@.enddate)/@.exams
set @.intervals = datediff(dd,@.begindate,@.enddate)/@.exams
--create table #examdates(id int identity(1,1) , examdate datetime)
--i inserted the enddate as startingpoint, but this could be done
--better, i think
insert into #examdates(examdate)
values(@.enddate)
-- While loop script with lots of changes
while @.exams > 1
begin
set @.NextExamDate = dateadd(dd,-@.intervals,@.LastExamDate)
insert into #examdates(examdate) values(@.NextExamDate)
set @.LastExamDate = @.NextExamDate
set @.exams = @.exams -1
end
--
select examdate from #examdates order by examdate
"Jason" wrote:

> Ryan Powers wrote:
> Hi Ryan,
> I want to calculate a date for taking an exam. The intervals are just
> the number of months when the next exam must be taken.
> Could you point out to me where i should better my code?
>|||Sure. You don't need the intervals variable, since you are actually
recalcing it on the fly within the loop. I am going to just keep your base
logic, and show you how you can correct it so it works.
I changed it slightly to find days between exams because I'm thinking that
you can evenly divide the months by the number of exams is not correct. Wha
t
I did will not necessarily give you your last exam on your end date due to
integer math. But, it should be close. We could put in a condition that
checks if we are setting the last date and just set it as the enddate. Let
me know if you need help with that.-
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int,
@.months int,
@.daysBetweenExams
set @.begindate = '2005-11-23'
set @.exams = 4
set @.months = 12
set @.enddate = dateadd(mm,@.months,@.begindate)
set @.daysBetweenExams = datediff(dd, @.begindate, @.enddate)/@.exams
create table #examdates(id int identity(1,1) , examdate datetime)
set @.examdate = @.begindate
while (select count(*) from #examdates) <= @.exams
begin
SELECT @.examdate = dateadd(dd, @.daysBetweenExams, @.examdate)
insert into #examdates(examdate)
values(@.examdate)
end
select examdate from #examdates order by examdate
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Jason" wrote:

> Ryan Powers wrote:
> Hi Ryan,
> I want to calculate a date for taking an exam. The intervals are just
> the number of months when the next exam must be taken.
> Could you point out to me where i should better my code?
>|||On Thu, 05 Jan 2006 14:28:42 +0100, Jason wrote:

>Hi,
>I've created this code to determine the dates for an exam when a
>certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can
>schedule his time to study depends on how many exams he must take. So
>based on the enddate i calculate backwards to the startdate to get
>intervals of when the next examdate should be.
>In my testscenario i used 12 months and the number of exams are 4. The
>code gave me the results i needed, but when i tried e.g. 18 months the
>results are not what i expected. The dates are somewhat correct but
>there were too many dates.
>Can someone see the error in my code?
Hi Jason,
Why not use a set-based query instead of looping?
-- inputs:
DECLARE @.begindate datetime,
@.enddate datetime,
@.exams int
SET @.begindate = '2005-11-23'
SET @.enddate = '2007-05-23'
SET @.exams = 4
-- generate exam dates
--INSERT INTO @.examdate(examdate)
SELECT DATEADD(month,
Number * DATEDIFF(month, @.begindate, @.enddate) / @.exams,
@.begindate)
FROM dbo.Numbers
WHERE Number BETWEEN 1 AND @.exams
Note: this requires the use of a numbers table. See www.aspfaq.com/2516.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> On Thu, 05 Jan 2006 14:28:42 +0100, Jason wrote:
>
>
> Hi Jason,
> Why not use a set-based query instead of looping?
> -- inputs:
> DECLARE @.begindate datetime,
> @.enddate datetime,
> @.exams int
> SET @.begindate = '2005-11-23'
> SET @.enddate = '2007-05-23'
> SET @.exams = 4
> -- generate exam dates
> --INSERT INTO @.examdate(examdate)
> SELECT DATEADD(month,
> Number * DATEDIFF(month, @.begindate, @.enddate) / @.exams,
> @.begindate)
> FROM dbo.Numbers
> WHERE Number BETWEEN 1 AND @.exams
> Note: this requires the use of a numbers table. See www.aspfaq.com/2516.
>
Hi hugo,
your solution did the job. Thnx!

Address field problem.

I have a number of text boxes (one on top of the other) to display the addresses in my report. I have Address_name, Address1, Address2, Address_city, Address_State, Address_PostalCode - Each with its own text box except for the city, state, and postal code for which I just combined the fields. However, I have many who do not have any data for Address2 and don't want an empty line in the address. Is there an expression I can use to display the next line or move/shift up the remaining parts of the address? Would the iif and isnothing expressions be helpful, and if so, how do I write it correctly?

If it is ok to put them all into one textbox, you could do this:

=Fields!Address_name.Value
& chr(10) & Fields!Address1.Value
& IIf(Len(Fields!Address2.Value) > 0, chr(10) & Fields!Address2.Value, "")
& chr(10) & Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value

If they have to have their own textbox and you just want to move it up if the Address2 is empty, then you could try something like this.

In the expression for the Address2 textbox:

=IIf(Len(Fields!Address2.Value) > 0, Fields!Address2.Value, Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value)

Then, in the expression for the City/State/Postal Code:

=IIf(Len(Fields!Address2.Value) > 0, Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value, "")

Hope this helps.

Jarret

|||

Thanks mate, I'll give it a try.!

What does "Len" do?

|||

Len gives you then length of the object you pass in. In this case, if the Address2 is NULL or an empty string, Len will return 0 and it will be skipped with the logic in the code.

Let me know if that fixes your issue.

Jarret

|||

I see, thanks for the info. I will try this in about an hour or so, I'll post back and give you an update.

Thanks again Jarret,

Bill

Additional 0 to column data

Hi

I have a column in the db with serial number data, on a export that I am doing the data has to be 10 digits long the problem is not all of them are eg

12234

122334343

1234234567

how can i get it to look like this adding an 0 to the front to make the row 10 digits

0000012234

0122334343

1234234567

Thanks

SELECT REPLICATE('0',10 - LEN(CONVERT(VARCHAR(10),Column1))) + CONVERT(VARCHAR(10),Column1)

HTH,

Babu

|||

select right('000000000' + convert(varchar(10),ColumnName),10)

example

declare @.i int
select @.i = 12345

select right('000000000' + convert(varchar(10),@.i),10)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Alternatively perform the formatting in your front end application|||

If the number is to be stored as a number, then this is the best advice. I wouldn't suggest it if this is a value that will be used many different places and never be used in a math equation. For that I would store it in a character string and prepend the zeros.

The real problem comes in all of the different places it is used (reports, data warehouse,etc.) Someone has to format it, and you don't want the user to have to use some UI function to format it. You might do the formatting on the way to the DW and to a reporting data store, but to me it begs the question of the nature of the data. If the nature of the data is a code that happens to be all numbers (but would perform just as well in the application if it was not all numbers, ie 'asd02020' would not change the application as opposed to '000022020', then store it as a character, format it when you save it, and get it over with :)

Addition of a number to an INT column

Hi,
I have several INT columns in a table that I need to update.
For example, in column 'aa' I need to add 2 to all of the values in that column.
I'm using Query Analyzer - what update statement should I write?Hmm - no answers - should I be in a different forum for this question?

I guess I could do:

UPDATE table_name SET aa = 16 WHERE aa = 14
UPDATE table_name SET aa = 15 WHERE aa = 13
UPDATE table_name SET aa = 14 WHERE aa = 12

and keep going like this until I have all the values updated.

Note that I've done it from highest number first, otherwise all of the data would get adjusted to the two highest numbers.

I was looking for a more elegant solution If anyone can think of one as I have several columns to update all with slightly different increases.|||UPDATE table_name SET aa = aa + 2

:D|||Of course - thank you - how could I miss it??

Woods for the trees and all...

Adding to 2 different tables

Hi Everyone,

I have a page with a textbox and a dropdown list.
The user will enter a company name in the text box and select a number from 1 - 20 (number of delegates for that company) in the dropdown list.

I've got the text box and dropdown writing to tblCompany but I would also like it to write to tblUsers at the same time. The reason for this is that i need it to set up the number of users that have been selected in the dropdown list.

Here is the codebehind file:

Imports System.Data.SqlClientImports System.Web.ConfigurationPartialClass cms_Management_Company_NewCompanyInherits System.Web.UI.PageDim companyNameAs String Dim companyActiveAs Boolean Dim companyArchivedAs Boolean Dim companyDelegatesAs Integer Dim userForeNameAs String Dim userSurnameAs String Dim userEmailAs String Dim userUsernameAs String Dim userPasswordAs String Dim userActiveAs Boolean Dim userTypeIDAs Integer Dim companyIDAs Integer Dim iAs Integer Dim NoLoopsAs Integer Protected Sub btnSave_Click(ByVal senderAs Object,ByVal eAs System.Web.UI.ImageClickEventArgs)Handles btnSave.ClickDim conStringAs String = WebConfigurationManager.ConnectionStrings("General").ConnectionStringDim conAs New SqlConnection(conString)Dim cmdAs New SqlCommand("INSERT INTO tblCompany (CompanyName, CompanyActive, CompanyArchived, CompanyDelegates) VALUES (@.CompanyName, @.CompanyActive, @.CompanyArchived, @.CompanyDelegates)", con) cmd.Parameters.AddWithValue("@.CompanyName", companyName) cmd.Parameters.Item("@.CompanyName").Value = txtCompanyName.Text cmd.Parameters.AddWithValue("@.CompanyDelegates", companyDelegates) cmd.Parameters.Item("@.CompanyDelegates").Value = lstDel.SelectedValue cmd.Parameters.AddWithValue("@.CompanyActive", companyActive) cmd.Parameters.Item("@.CompanyActive").Value =True cmd.Parameters.AddWithValue("@.CompanyArchived", companyArchived) cmd.Parameters.Item("@.CompanyArchived").Value =False Using con con.Open() cmd.ExecuteNonQuery() con.Close()End UsingDim con2As New SqlConnection(conString)Dim cmd2As New SqlCommand("INSERT INTO tblUsers (UserForeName, UserSurname, UserEmail, UserUsername, UserPassword, UserActive, UserTypeID, CompanyID) VALUES (@.UserForeName, @.UserSurname, @.UserEmail, @.UserUsername, @.UserPassword, @.UserActive, @.UserTypeID, @.CompanyID)", con2) cmd2.Parameters.AddWithValue("@.UserForeName", userForeName) cmd2.Parameters.Item("@.UserForeName").Value ="First Name - Delegate 1" cmd2.Parameters.AddWithValue("@.UserSurname", userSurname) cmd2.Parameters.Item("@.UserSurname").Value ="Surname - Delegate 1" cmd2.Parameters.AddWithValue("@.UserEmail", userEmail) cmd2.Parameters.Item("@.UserEmail").Value ="Email Address - Delegate 1" cmd2.Parameters.AddWithValue("@.UserUsername", userUsername) cmd2.Parameters.Item("@.UserUsername").Value ="Username - Delegate 1" cmd2.Parameters.AddWithValue("@.UserPassword", userPassword) cmd2.Parameters.Item("@.UserPassword").Value ="Password - Delegate 1" cmd2.Parameters.AddWithValue("@.UserActive", userActive) cmd2.Parameters.Item("@.UserActive").Value =True cmd2.Parameters.AddWithValue("@.UserTypeID", userTypeID) cmd2.Parameters.Item("@.UserTypeID").Value = 2 cmd2.Parameters.AddWithValue("@.UserTypeID", userTypeID) cmd2.Parameters.Item("@.UserTypeID").Value = 1 Using con2 con2.Open()For i = 1To NoLoops cmd2.ExecuteNonQuery()Next i con2.Close()End Using Response.Redirect("~/cms/Management/Company/Company.aspx")End SubEnd Class

The other thing I am not sure of is getting the ID of the new company and assiging it to the delegates in tblUsers (to associate them with the new company)

I hope this makes sense.

Thank you very much guys.

Scott.

Hi,

To get the recently added record's ID use

SELECT SCOPE_IDENTITY()

and catch the returned value using

cmd.ExecuteScalar()

HTH
Regards

|||

Hi,

Thanks for the reply, where in the code would I put these elements? I am very new to .NET.

thanks again,

Scott.

Adding subreport to report header?

Is it possible? Crystal allowed you to do it but RS doesn't seem to. I have
a number of reports that need to display same data (Org name, date etc.)
on the report header and I thought doing it as a subreport was a good idea.
Any workarounds?
Thanks in advance.Found report templates meet my needs better. Thanks anyway!
> Is it possible? Crystal allowed you to do it but RS doesn't seem to. I
> have a number of reports that need to display same data (Org name,
> date etc.) on the report header and I thought doing it as a subreport
> was a good idea.
> Any workarounds?
> Thanks in advance.
>sql

adding sql licenses

As I recall, I set the licensing format and number of licenses when I
installed SQL Server.
a) How can I tell how many and type of licenses that are setup
b) How can I increase the number.
I can't seem to get back to that original screen.
Thanks,
Rick
select serverproperty('LicenseType') as LicenseType
go
select serverproperty('NumLicenses') as NumLicenses
Thanks
Hari
"Rick" <rick@.abasoftware.com> wrote in message
news:1174501125.493472.118810@.l75g2000hse.googlegr oups.com...
> As I recall, I set the licensing format and number of licenses when I
> installed SQL Server.
> a) How can I tell how many and type of licenses that are setup
> b) How can I increase the number.
> I can't seem to get back to that original screen.
> Thanks,
> Rick
>

adding sql licenses

As I recall, I set the licensing format and number of licenses when I
installed SQL Server.
a) How can I tell how many and type of licenses that are setup
b) How can I increase the number.
I can't seem to get back to that original screen.
Thanks,
Rickselect serverproperty('LicenseType') as LicenseType
go
select serverproperty('NumLicenses') as NumLicenses
Thanks
Hari
"Rick" <rick@.abasoftware.com> wrote in message
news:1174501125.493472.118810@.l75g2000hse.googlegroups.com...
> As I recall, I set the licensing format and number of licenses when I
> installed SQL Server.
> a) How can I tell how many and type of licenses that are setup
> b) How can I increase the number.
> I can't seem to get back to that original screen.
> Thanks,
> Rick
>

adding sql licenses

As I recall, I set the licensing format and number of licenses when I
installed SQL Server.
a) How can I tell how many and type of licenses that are setup
b) How can I increase the number.
I can't seem to get back to that original screen.
Thanks,
Rickselect serverproperty('LicenseType') as LicenseType
go
select serverproperty('NumLicenses') as NumLicenses
Thanks
Hari
"Rick" <rick@.abasoftware.com> wrote in message
news:1174501125.493472.118810@.l75g2000hse.googlegroups.com...
> As I recall, I set the licensing format and number of licenses when I
> installed SQL Server.
> a) How can I tell how many and type of licenses that are setup
> b) How can I increase the number.
> I can't seem to get back to that original screen.
> Thanks,
> Rick
>

Adding Sequence number in SQL

Being a newbie to SQL programming, was hoping someone would be able to
guide me in the right direction.
I have a table that has the following data
Center Emp_ID
11 112
11 2254
11 346
12 456
12 138
13 8761
Etc, etc
I want to add a Sequence number which resets to 1 by center. So I want
it to look like this
List_ID Center Emp_ID
1 11 112
2 11 2254
3 11 346
1 12 456
2 12 138
1 13 8761
I've done the following:
SELECT TOP 100 PERCENT FIRST_NAME, LAST_NAME,
(SELECT COUNT(*)
FROM dbo.Planning_Heads e2
WHERE e2.ACCT_CD <=
dbo.Planning_Heads.ACCT_CD) AS List_ID, ACCT_CD
FROM dbo.Planning_Heads
ORDER BY ACCT_CD
but it's no "restarting" the List_Id or incrementing it properly
Sorry for the long post, but thought it would be best to give as much
info as I couldWhich criteria can we use to tell sql server that Emp_ID = 346 goes after
Emp_ID = 2254, other than analyzing row by row?.
In db [northwind], the orders for each customer are stored in table [orders]
and the column [orderid] is an identity one that we can use to sort them
chronologically by customer.
use northwind
go
select
count(*) as rank,
a.orderid,
a.employeeid
from
dbo.orders as a
inner join
dbo.orders as b
on a.employeeid = b.employeeid
and a.orderid >= b.orderid
group by
a.employeeid,
a.orderid
order by
a.employeeid,
rank
go
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"kimmal" wrote:

> Being a newbie to SQL programming, was hoping someone would be able to
> guide me in the right direction.
> I have a table that has the following data
> Center Emp_ID
> 11 112
> 11 2254
> 11 346
> 12 456
> 12 138
> 13 8761
> Etc, etc
> I want to add a Sequence number which resets to 1 by center. So I want
> it to look like this
> List_ID Center Emp_ID
> 1 11 112
> 2 11 2254
> 3 11 346
> 1 12 456
> 2 12 138
> 1 13 8761
> I've done the following:
> SELECT TOP 100 PERCENT FIRST_NAME, LAST_NAME,
> (SELECT COUNT(*)
> FROM dbo.Planning_Heads e2
> WHERE e2.ACCT_CD <=
> dbo.Planning_Heads.ACCT_CD) AS List_ID, ACCT_CD
> FROM dbo.Planning_Heads
> ORDER BY ACCT_CD
> but it's no "restarting" the List_Id or incrementing it properly
> Sorry for the long post, but thought it would be best to give as much
> info as I could
>

Adding 'Other' segment/slice in a pie chart

Hi guys,

I am creating a pie chart report from a cube. This report may contain unknown number of segments. Here is the thing; if more than 1 data slice is generated with a value less than 5% of the total, then a segment labelled 'other' will be generated, and data from all slices with value < 5% will be added to this 'Other' segment.

Is it possible to implement this functionality in the report layout level with out writing a complex MDX query? If this is not possible, can anybody give me a sample MDX query which implements similar issue(i.e. 'Other-ing' rule.)

For your information, this feature can be easily implemented using a third pary software such as 'Dundas chart for Reporting Service'. However, my client don't want to buy this third party software.

Please let me know if anybody has came accross with similar scenario?

Sincerely,

--Amde

Please help!!!!!!!!!!

|||

Do you have to use MDX or can you use T-SQL? I once used a derived table to get this type of data. Not pretty and not the quickest thing if you have huge datasets, but it works.

SELECT grouper, sum(total_charge), sum(pct)
FROM (

select
dx1_num "Diag", -- Item to list in pie slice
sum(charge_amount) "total_charge", --
(sum(charge_amount)/(SELECT SUM(charge_amount) FROM ar_billtrans_charge)) "pct", -- Percent of Everything
case
when (sum(charge_amount)/(SELECT SUM(charge_amount) FROM ar_billtrans_charge)) < .05 then 'Misc' -- Interim Group
else CAST(dx1_num AS VARCHAR(15))
end "grouper" -- what kind of name do you want for free?
from ar_billtrans_charge
group by dx1_num

) Y GROUP BY grouper;

I am sure some T-SQL gods out there can do much better.

R

|||

Hi,

Appreciate your response. Basically, I am using MDX query. Do you have any idea how to do the same thing using MDX?

Thank you for your cooperation.

--Amde

|||

Please read my response with a sample report in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=638700&SiteID=1&mode=1

-- Robert

Adding 'Other' segment/slice in a pie chart

Hi guys,

I am creating a pie chart report from a cube. This report may contain unknown number of segments. Here is the thing; if more than 1 data slice is generated with a value less than 5% of the total, then a segment labelled 'other' will be generated, and data from all slices with value < 5% will be added to this 'Other' segment.

Is it possible to implement this functionality in the report layout level with out writing a complex MDX query? If this is not possible, can anybody give me a sample MDX query which implements similar issue(i.e. 'Other-ing' rule.)

For your information, this feature can be easily implemented using a third pary software such as 'Dundas chart for Reporting Service'. However, my client don't want to buy this third party software.

Please let me know if anybody has came accross with similar scenario?

Sincerely,

--Amde

Please help!!!!!!!!!!

|||

Do you have to use MDX or can you use T-SQL? I once used a derived table to get this type of data. Not pretty and not the quickest thing if you have huge datasets, but it works.

SELECT grouper, sum(total_charge), sum(pct)
FROM (

select
dx1_num "Diag", -- Item to list in pie slice
sum(charge_amount) "total_charge", --
(sum(charge_amount)/(SELECT SUM(charge_amount) FROM ar_billtrans_charge)) "pct", -- Percent of Everything
case
when (sum(charge_amount)/(SELECT SUM(charge_amount) FROM ar_billtrans_charge)) < .05 then 'Misc' -- Interim Group
else CAST(dx1_num AS VARCHAR(15))
end "grouper" -- what kind of name do you want for free?
from ar_billtrans_charge
group by dx1_num

) Y GROUP BY grouper;

I am sure some T-SQL gods out there can do much better.

R

|||

Hi,

Appreciate your response. Basically, I am using MDX query. Do you have any idea how to do the same thing using MDX?

Thank you for your cooperation.

--Amde

|||

Please read my response with a sample report in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=638700&SiteID=1&mode=1

-- Robert

Adding number of decimal places during table design

Hello,

Nice easy one (hopefully) from a newbie on SQL 2000.

I have a table HolidayTakenBooked which is populated from a stored procedure via the following statement;

TRUNCATE TABLE HolidayTakenBooked
INSERT INTO HolidayTakenBooked
SELECT * FROM #TMP_HolidayTakenBooked ORDER BY ABR_Clock_No

I am finding that for certain values in the HolidayTakenBooked table decimals are not being transferred correctly. ie. 0.5 in the TMP table appears as 1 in the HolidayTakenBooked table.

I'm pretty sure that this is down to the data definition of the table see sample field below;
[HOL_DaysTaken1] [decimal](18, 0) NULL ,

So the simple question here is how do I define decimal places when I define a new table. When designing a new table in Enterprise Manager I select decimal and the server does not allow me to change the value of 9 it defaults to.

What simple thing I am not doing ?

Cheers
NealPut values in Scale properties,you will find it just below precision if you are creating tables in EM design Table option...|||Many thanks.
Neal

adding identifier number to records

I have a table already created and need to add an autonumber/ unique identif
ier
field.
If the table already contains records can I just add the new autonumber fiel
d
and run a query to populate this field for all records with unique values?
how do I do this?
are their other ways of doing this ? and how?
thanks
ChrisChris,
When you add and identity column to your table, SQL Server automatically
populates the column. You can not update an identity column.
Example:
use northwind
go
create table t (
colA char(1) not null unique
)
go
insert into t values('a')
insert into t values('b')
insert into t values('c')
go
select * from t
go
alter table t
add colB int not null identity
go
select * from t
go
drop table t
go
AMB
"Chris" wrote:

> I have a table already created and need to add an autonumber/ unique ident
ifier
> field.
> If the table already contains records can I just add the new autonumber fi
eld
> and run a query to populate this field for all records with unique values?
> how do I do this?
> are their other ways of doing this ? and how?
> thanks
> Chris|||I added the unique identifier column using table design.
However when I queried the database the values were all null.
Also since there are a lot of records in the table and generating the
alpha-numeric values does take processing time, I noticed adding the column
did not require any processing, (i.e. no hour glass).
Also I would prefer to just use a big int as it will take up less space and
be faster.
How can I use @.@.identity to update each record? is this possible?
Gracias Alejandro,
Tu eres muy asusto. He vido muchas siertos y respuestas de usted en los
forums.
Perdone mi espanol, estoy apprendiendo.
Por favor, constesta en ingles :)
"Alejandro Mesa" wrote:
> Chris,
> When you add and identity column to your table, SQL Server automatically
> populates the column. You can not update an identity column.
> Example:
> use northwind
> go
> create table t (
> colA char(1) not null unique
> )
> go
> insert into t values('a')
> insert into t values('b')
> insert into t values('c')
> go
> select * from t
> go
> alter table t
> add colB int not null identity
> go
> select * from t
> go
> drop table t
> go
>
> AMB
>
> "Chris" wrote:
>|||As I told you, you can not update an identity column.
AMB
"Chris" wrote:
> I added the unique identifier column using table design.
> However when I queried the database the values were all null.
> Also since there are a lot of records in the table and generating the
> alpha-numeric values does take processing time, I noticed adding the colum
n
> did not require any processing, (i.e. no hour glass).
> Also I would prefer to just use a big int as it will take up less space an
d
> be faster.
> How can I use @.@.identity to update each record? is this possible?
> Gracias Alejandro,
> Tu eres muy asusto. He vido muchas siertos y respuestas de usted en los
> forums.
> Perdone mi espanol, estoy apprendiendo.
> Por favor, constesta en ingles :)
> "Alejandro Mesa" wrote:
>|||okay, so a big int field that has been filled with a value using @.@.identity
can not be edited?
"Alejandro Mesa" wrote:
> As I told you, you can not update an identity column.
>
> AMB
> "Chris" wrote:
>|||I need to create a table with unique primary key on only one field.
How does one create or load a table with unique values in a primary key
field if
you have not been been provided unique values.
I'm happy to use just a big int data type to hold my unique values
"Chris" wrote:

> I have a table already created and need to add an autonumber/ unique ident
ifier
> field.
> If the table already contains records can I just add the new autonumber fi
eld
> and run a query to populate this field for all records with unique values?
> how do I do this?
> are their other ways of doing this ? and how?
> thanks
> Chris|||Correct.
Example:
use northwind
go
create table t (
colA char(1) not null unique
)
go
insert into t values('a')
insert into t values('b')
insert into t values('c')
go
select * from t
go
alter table t
add colB int not null identity
go
select * from t
go
-- this will give an error
update t
set colB = 4
where colB = 2
go
drop table t
go
AMB
"Chris" wrote:
> okay, so a big int field that has been filled with a value using @.@.identit
y
> can not be edited?
>
> "Alejandro Mesa" wrote:
>

Adding data with a query

I have a query that reads ""SELECT [po number], qty, cartons, outtrailer,
shipdate FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate""
and this returns data that falls within the dates specified.
Along with this, I would like to have something that marks another field
(Sent) telling me that the records returned from the above statement, have
been returned.
The purpose is so that I can easily see which records have been returned.UPDATE [tableWhere YouHaveSentColumn_ItCanBeThe_jcrew_IfYouWant] SET Sent =1
FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate
it will update every row where the WHERE CLAUSE apply
--
Bruno Alexandre
(a Portuguese in Denmark)
"Johnfli" <john@.ivhs.us> escreveu na mensagem
news:%23H$5etEeGHA.3888@.TK2MSFTNGP02.phx.gbl...
>I have a query that reads ""SELECT [po number], qty, cartons, outtrailer,
> shipdate FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
> tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate""
> and this returns data that falls within the dates specified.
> Along with this, I would like to have something that marks another field
> (Sent) telling me that the records returned from the above statement, have
> been returned.
> The purpose is so that I can easily see which records have been returned.
>
>

Adding data with a query

I have a query that reads ""SELECT [po number], qty, cartons, outtrailer
,
shipdate FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate""
and this returns data that falls within the dates specified.
Along with this, I would like to have something that marks another field
(Sent) telling me that the records returned from the above statement, have
been returned.
The purpose is so that I can easily see which records have been returned.UPDATE [tableWhere YouHaveSentColumn_ItCanBeThe_jcrew_IfYou
Want] SET Sen
t =
1
FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate
it will update every row where the WHERE CLAUSE apply
Bruno Alexandre
(a Portuguese in Denmark)
"Johnfli" <john@.ivhs.us> escreveu na mensagem
news:%23H$5etEeGHA.3888@.TK2MSFTNGP02.phx.gbl...
>I have a query that reads ""SELECT [po number], qty, cartons, outtraile
r,
> shipdate FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
> tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate""
> and this returns data that falls within the dates specified.
> Along with this, I would like to have something that marks another field
> (Sent) telling me that the records returned from the above statement, have
> been returned.
> The purpose is so that I can easily see which records have been returned.
>
>

Adding columns at runtime

I have a procedure that will return a dataset with an unknown number of columns (the user chooses a date range, and there will be one column per day). Since the columns are not always the same, the report designer doesn't want to help me with this. How can I make this work?

Thanks

Hello my friend,

For performance and ease-of-use reasons, I strongly recommend you take a different approach than using columns in this way, especially for reporting services. Please give details on what you are trying to do (the table structure and the query, etc) and I will try to suggest an alternative to achieving the same result.

Kind regards

Scotty

|||

Currently, I have this table:

CriticalUnitHistory
(
CritcalUnitHistory int (PK),
MarketID int,
UnitLCN int,
CriticalDate datetime,
CriticalReason varchar(50)
)

Every day, I look through a list of computers (each with a UnitLCN that is unique to its city) in different cities (MarketID corresponds to each city), and if its current status satisfies certain criteria, I add a record to this table with the MarketID, UnitLCN, current date and a short description of the criteria that it met to be included on the critical list.

I have been asked to create a report that will take a list of UnitLCNs and MarketIDs, and a date range, and show a table with the UnitLCNs down the left side, the dates across the top, and, if the computer was critical on a certain day, show the CriticalReason in the corresponding cell.

It would look something like this:

MarketID UnitLCN 1/20/2007 1/21/2007 1/22/2007 1/23/2007
1 519 No Contact No Contact
1 234 DL Error DL Error
1 219 GPS Fail GPS Fail

Hope that helps. Thanks for your assistance

|||

Hello my friend,

I take it you are having problems generating the data in this way from the original query. Refer to the following url: -

http://www.sqlteam.com/item.asp?ItemID=2955

It is really good. It shows you how to do a cross tab pivot to make data come out in this way. I tested the code myself with my own database tables and it works.

Kind regards

Scotty

Adding an incremental number to select result

Hi,
I will like to have, along with my quesry result, another
column that will store the number of the row, for example:
1 query results...
2 query results...
3 query results...
how can i do this?
Thanks!Refer to following url
HOW TO: Dynamically Number Rows in a Select Statement
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133
if you have any unique field in the table then you can try a query something
like this:
Ex:
use northwind
go
select (select count(customerid) from customers where customerid <=a.customerid) rank,
*
from customers a
go
--
-Vishal
"Juan Carlos" <jcarlos_mn@.hotmail.com> wrote in message
news:0bf201c34249$de23a670$a501280a@.phx.gbl...
> Hi,
> I will like to have, along with my quesry result, another
> column that will store the number of the row, for example:
> 1 query results...
> 2 query results...
> 3 query results...
> how can i do this?
> Thanks!|||http://www.aspfaq.com/2427
--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/
Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.
"Juan Carlos" <jcarlos_mn@.hotmail.com> wrote in message
news:0bf201c34249$de23a670$a501280a@.phx.gbl...
> Hi,
> I will like to have, along with my quesry result, another
> column that will store the number of the row, for example:
> 1 query results...
> 2 query results...
> 3 query results...
> how can i do this?
> Thanks!

Adding an auto-incrementing index column to a select statement

Good Morning
Is there a method of adding an extra column to a select statement which
would be an incrementing number?
ie)select aa.col_A,aa.col_B,(new_index_col)
from mytable aa
with a result set like this
col_a col_b new_index_col
hat dog 1
fred rat 2
mike pete 3
Thanks
Dave Hills
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!There was identity function but I do not remember exact syntax, something
like
Select identity(1,1) from mytable
Bojidar Alexandrov
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:uM$ecX0MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Good Morning
> Is there a method of adding an extra column to a select statement which
> would be an incrementing number?
> ie)select aa.col_A,aa.col_B,(new_index_col)
> from mytable aa
> with a result set like this
> col_a col_b new_index_col
> hat dog 1
> fred rat 2
> mike pete 3
>
> Thanks
>
> Dave Hills
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You're obliged to use INTO in the select statement, so
this type of syntax will work for you:
use pubs
go
select *, IDENTITY(int,1,1) as xxx into #myTempTable
from pub_info
select * from #myTempTable
drop table #myTempTable
HTH,
Paul Ibison