Absurd project: hii

Showing posts with label hii. Show all posts

Friday, March 30, 2012

Help in creating Dynamic Rows!

I am trying to create a dynamic row. Say for example, I want to list all the "Employees" belonging to a particular department say "Physics". So, I would want every employee name to be in a separate row. How do I go about doing this? Any help/suggestions is appreciated.

Thanks

Using a matrix, this is possible if you add a group based on Department, a group based on Emeployee name, and data values in the data section of the matrix. Here is an example of the output:

Phisics

Joe Smith

123

Mike Jones

222

Kelly Roberts

111

Chemistry

Sarah Paulson

145

Kinestetics

Robert Paulson

321

Rob Sam

235

You can also do this with a table. Add a group based on Department and add the the employee fields to the details row. The output would look something like this,

Physics

Joe Smith

123

Mike Jones

222

Kelly Roberts

111

Chemistry

Sarah Paulson

145

Kinestetics

Robert Paulson

321

Jimmy James

235

Below is an RDL that demonstrates this.

Ian

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=localhost; Initial Catalog="AdventureWorks"</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>dafaddb3-d659-48f2-a1bc-6f040759edf9</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>DataSet2</DataSetName>
<Top>2.25in</Top>
<TableGroups>
<TableGroup>
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<rd:DefaultName>textbox15</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<rd:DefaultName>textbox16</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Department_1">
<rd:DefaultName>Department_1</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Red</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Department.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Red</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!Department.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<ZIndex>1</ZIndex>
<Width>5.16667in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="EmployeeName_1">
<rd:DefaultName>EmployeeName_1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!EmployeeName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="DataValue_1">
<rd:DefaultName>DataValue_1</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!DataValue.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>2.58333in</Width>
</TableColumn>
<TableColumn>
<Width>2.58333in</Width>
</TableColumn>
</TableColumns>
<Height>1.25in</Height>
</Table>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<RowGroupings>
<RowGrouping>
<Width>1.375in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="Department">
<rd:DefaultName>Department</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Default>Red</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Department.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_Department">
<GroupExpressions>
<GroupExpression>=Fields!Department.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
<RowGrouping>
<Width>1.375in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="EmployeeName">
<rd:DefaultName>EmployeeName</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!EmployeeName.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_EmployeeName">
<GroupExpressions>
<GroupExpression>=Fields!EmployeeName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
<Grouping Name="matrix1_ColumnGroup1">
<GroupExpressions>
<GroupExpression />
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet2</DataSetName>
<Top>1in</Top>
<Width>3.75in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="DataValue">
<rd:DefaultName>DataValue</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!DataValue.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>6.125in</Height>
</Body>
<rd:ReportID>819bacc0-4ffc-4d72-90d6-8d01b9a574e8</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet2">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>Select 'Physics' As Department, 'Joe Smith' As EmployeeName, 123 As DataValue
Union All
Select 'Physics', 'Mike Jones', 222
Union All
Select 'Physics', 'Kelly Roberts', 111
Union All
Select 'Chemistry', 'Sarah Paulson', 145
Union All
Select 'Kinestetics', 'Robert Paulson', 321
Union All
Select 'Kinestetics', 'Jimmy James', 235</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="Department">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Department</DataField>
</Field>
<Field Name="EmployeeName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>EmployeeName</DataField>
</Field>
<Field Name="DataValue">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>DataValue</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<CodeModules>
<CodeModule>System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</CodeModule>
</CodeModules>
<Code />
<Width>7.75in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>|||

Ian

I'm getting the following error message: when I add a group by row on my matrix1.

[rsAggregateInGroupExpression] A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions.

Build complete -- 1 errors, 0 warnings

The query is pretty tricky. In my report, I have a report parameter that takes in an employee id and does a select on the Department ID. After getting the Department ID, I select all the other employees corresponding to it. All the data is in one table. So, the SQL query would be like this

SELECT emp_id FROM emp_dept WHERE dept_id IN
(SELECT dept_id FROM emp_dept WHERE emp_id=@.emp_id_param)

In this report I'm trying to display all the emp_id's.

Your suggestions are greatly appreciated.

Thanks

preps.

|||

Ian

In addition, to the information in my previous mail. I'm building this report in Visual Studio2005 and not in Report Builder.

preps.

|||The error you are getting is caused by using an aggregate on a field in the group expression. Aggregates are not allowed in this context, and in your case does not seem to be needed.

If all you are displaying is the emp_id field values, then you don't really need to use a matrix. A table should give you what you need. If there is more information to display, including another dimension of data, and aggregations that need to be performed based on the dimensionality of the data, then a matrix would be the report item to use.

That being said, if you continue to use the matrix, make sure that the query includes the dept_id field in the field list, along with the emp_id. This value is needed for the first group.

The first group should have the group expression "=Fields!dept_id.Value" to first group the data by department. The second group should have the expression "=Fields!emp_id.Value" to group the data by employee. Based on the example query, this should give you the matrix described in the previous post.|||

Hi Ian

Thanks for you reply. As per your suggestion I used a table data region (in Report Designer). But for some reason only one emp_id is being display. Instead of three emp_ids which is the actual resultset.

I don't know why the other two emp_id's are not being displayed. Do you have any idea?

|||Which row in the table are you placing the emp_id field in? The one with the three horzontal lines in the box to the left of the row? Are you adding any groups to the table? If so, what fields are you grouping on?

Ian|||

Which row in the table are you placing the emp_id field in? The one with the three horzontal lines in the box to the left of the row? - Yes

Are you adding any groups to the table? - No

preps

|||On the data screen, you see three rows of data, but only one is being displayed in the table? Can you post the rdl, so that I can take a closer look? Make sure to remove any sensitive data from it before posting.

Ian|||

Ian

I figured out the problem. The table that I was trying to create was inside a List report item and hence it was not displaying the emp_ids. I put the table outside the List. It gave my the result I was looking for. If you know why or what caused this behaviour that would give me a better understanding. Otherwise, don't bother about it.

Thanks for all the help and quick response that drove me towards the right solution.

Cheers

preps

Help in creating dynamic rows!

Thanks

Take a look at the Matrix report item.

With a matrix you can define a group of say 'department' and then show in the details the 'Employees'

Take a look at http://msdn2.microsoft.com/en-us/library/ms157334.aspx

Friday, March 9, 2012

HELP - SQL Server 2000 Install Files needed urgently

Hi!

I have a large project that is due to complete this week. In order to
complete it I need SQL Server 2000 installed on a remote server. My
disk is corrupt and to order another media disk would damage my
deadline. I have the licence and serial key, but now need good install
files. I am even ready to buy another retail box, if I can find a
supplier that would give me a download site for the media, while I wait
for the shipment!

Please PLEASE help!

Regards,

BarryHi,

Sorry if i'm stating the obvious - but it wasn't commented on in your
post.. If you have the licence and serial key shouldn't you be
contacting Microsoft?

Greg|||Hi Greg. They will send me another media disk, but that doesnt help me
getting it installed for tomorrow.

Thanks for your reply.

Barry|||"TheFoot" <workshop@.carib-sys.com> wrote in message
news:1112918510.565481.327210@.g14g2000cwa.googlegr oups.com...
> Hi Greg. They will send me another media disk, but that doesnt help me
> getting it installed for tomorrow.

Unfortuantely I doubt most anyone here would be comfortable putting binaries
out there to be copied. Even if you're totally legit, MS would have a field
day if they found out.

Sorry.

> Thanks for your reply.
> Barry|||"TheFoot" <workshop@.carib-sys.com> wrote in message
news:1112918510.565481.327210@.g14g2000cwa.googlegr oups.com...
> Hi Greg. They will send me another media disk, but that doesnt help me
> getting it installed for tomorrow.
> Thanks for your reply.

Oh, one other thought.. may be too late by an hour or two, but a couple of
places like PC Connection on the East coast used to have a "order by
midnight and get it the next day" policy. MIGHT have luck with them.

> Barry|||I agree with Greg, nobody's going to "lend" you the SQL Server binaries
based on the premise that you're entitled to them - too much personal
risk.

I see two options:

1. Delay the project and wait for the shipment
2. Incur the extra cost for the "retail box" and hopefully charge it
back to the business|||Download the eval and then upgrade later.

http://www.microsoft.com/sql/evalua...ial/default.asp

--
David Portas
SQL Server MVP
--

Friday, February 24, 2012

help

Hi
I got a table with 2 columns as follows
col1 col2
10 193.51
10 194.5
10 202.71
20 192.79
20 197.6
20 192.9
30 192.76
30 191.91
30 187.9
Now i need to add a column dynamically thru sql statement to the table
so that my output should be as follows
here
0.511601468=(194.5/193.51-1)*100
4.221079692=(202.71/194.5-1)*100
and so on
col1 col2 col3
10 193.51 0.511601468
10 194.5 4.221079692
10 202.71 null
20 192.79 2.494942684
20 197.6 -2.37854251
20 192.9 null
30 192.76 -0.440962855
30 191.91 -2.08952113
30 187.9 NullPlease do not multipost, answered in: comp.databases.ms-sqlserver
Thanks, Jens Suessmeyer.

Help

Hi
I am using Visual studio 2005 to write a web service. I am not able to
connect to database. I get the usual Named pipes provider error:could
not open a connection to the server. So I read up some material and
found out that I have to start the sqlbrowser service(using surface
area configuration manager) and create exceptions for the firewall for
the sqlexpress and sqlbrowser. I did all the things, still I get the
same error. However , when I add the datasource from the datasource
configuration wizard and test the connection, the test was succesfull.
Thanks in advance for the helpIt's best not to hijack a thread with a new topic -there is no certainty
anyone will see your post since they may think that the thread is complete.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"sunil" <sairaj.sunil@.gmail.com> wrote in message
news:1154953948.775714.18740@.p79g2000cwp.googlegroups.com...
> Hi
> I am using Visual studio 2005 to write a web service. I am not able to
> connect to database. I get the usual Named pipes provider error:could
> not open a connection to the server. So I read up some material and
> found out that I have to start the sqlbrowser service(using surface
> area configuration manager) and create exceptions for the firewall for
> the sqlexpress and sqlbrowser. I did all the things, still I get the
> same error. However , when I add the datasource from the datasource
> configuration wizard and test the connection, the test was succesfull.
> Thanks in advance for the help
>|||This may not actually be a case of hijacking. It may just be a case of using
a totally meaningless subject that the newsreader assumes is part of the
same thread with the same less-than-useful subject.
HTH
Kalen Delaney, SQL Server MVP
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uKT6kAmuGHA.4748@.TK2MSFTNGP03.phx.gbl...
> It's best not to hijack a thread with a new topic -there is no certainty
> anyone will see your post since they may think that the thread is
> complete.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "sunil" <sairaj.sunil@.gmail.com> wrote in message
> news:1154953948.775714.18740@.p79g2000cwp.googlegroups.com...
>

Sunday, February 19, 2012

help

i have table with folowing columns (subid,itemid)

i want to write select stmt to get subid where itemid =all group of value('1','2')

like this

Select subid from subscriptionItem where itemId in all('1','2')

but this stmt not work

help me

Somthing like this ?

Select SubID From SubscriptionItem Where ItemID in ('1','2')|||

Hi,

If you mean you want those SubID that have both ItemID 1 and 2, then the above will not work.

what you should is..

Select SubID From subscriptionItem Where ItemID in (1,2) group by SubID Having count(0) =2

Assuming that the combination SubID and ItemID is primary (unique).

If they are not unique, which I doubt, then try:

Select SubID FROM (Select distinct SubID, ItemID from SubscriptionItem where ItemID in (1,2)) si Group by SubID having count(0) = 2

Hope this helps

help

Hi
I got a table with 2 columns as follows
col1 col2
10 193.51
10 194.5
10 202.71
20 192.79
20 197.6
20 192.9
30 192.76
30 191.91
30 187.9
Now i need to add a column dynamically thru sql statement to the table
so that my output should be as follows
here
0.511601468=(194.5/193.51-1)*100
4.221079692=(202.71/194.5-1)*100
and so on
col1 col2 col3
10 193.51 0.511601468
10 194.5 4.221079692
10 202.71 null
20 192.79 2.494942684
20 197.6 -2.37854251
20 192.9 null
30 192.76 -0.440962855
30 191.91 -2.08952113
30 187.9 Null
Please do not multipost, answered in: comp.databases.ms-sqlserver
Thanks, Jens Suessmeyer.

help

I got a table with 2 columns as follows

col1 col2

10 193.51
10 194.5
10 202.71

20 192.79
20 197.6
20 192.9

30 192.76
30 191.91
30 187.9

Now i need to add a column dynamically thru sql statement to the table
so that my output should be as follows

here

0.511601468=(194.5/193.51-1)*100
4.221079692=(202.71/194.5-1)*100
and so on

col1 col2 col3

10 193.51 0.511601468
10 194.5 4.221079692
10 202.71 null

20 192.79 2.494942684
20 197.6 -2.37854251
20 192.9 null

30 192.76 -0.440962855
30 191.91 -2.08952113
30 187.9 NullHi Kali,

create table #Tempone
(
SomeID INT,
SomeValue REAL
)

INSERT INTO #Tempone
SELECT 10 , 193.51

INSERT INTO #Tempone
SELECT 10 , 194.5

INSERT INTO #Tempone
SELECT 10 , 202.71

INSERT INTO #Tempone
SELECT 20 , 192.79

INSERT INTO #Tempone
SELECT 20 , 197.6

INSERT INTO #Tempone
SELECT 20 , 192.9

INSERT INTO #Tempone
SELECT 30 , 192.76

INSERT INTO #Tempone
SELECT 30 , 191.91

INSERT INTO #Tempone
SELECT 30 , 187.9

--Query
SELECT T1.SomeId, T1.SomeValue, ((SELECT TOP 1 SomeValue FROM #Tempone
T2 WHERE T1.SomeId = T2.SomeID AND T2.SomeValue > T1.SomeValue ORDER BY
SomeValue ) / SomeValue-1)*100
>From #Tempone T1
order by T1.SomeValue

but you should consider the order of the inserted rows, I saw that you
didnt have an order of the value, so in this scenario they will be
fetched as they will come in to the query processor.

HTH, jens Suessmeyer.|||Hi Suessmeyer

thanks for u r reply.
U r solution is good
can i get my result as column to the existing table(#Tempone ) that is
an update statement instead of select
ofcourse we can add column thru alter syntax

Hope U understand

Thanks & Regards
kalyan|||What is the primary key here? Please post DDL for your table(s) so that
we don't have to guess.

Your example calculations seem to imply a sequence to the data. i.e.
193.51 comes "first", followed by 194.5, followed by 202.71. Have I got
that right? If so, what defines the sequence? Is it just that the Col2
values are taken lowest first? Please explain a bit more.

--
David Portas
SQL Server MVP
--|||Hi Suessmeyer

Also how should we handle division by zero errors in u r case

Thanks & Regards
kalyan|||Hi David Portas

here col1 is the companyids
col2 is the prices of those ids for a period of 3 days
and col3 is the return calculation

as for each day return of a
companyid=(price(previousday)/price(currentday)-1)*100

here the period may vary

Hope U understand

Thanks & Regards
kalyan|||What do you want to be the result when a zero divide occurs? If you
want nulls you can use NULLIF:

(x / NULLIF(z,0))

--
David Portas
SQL Server MVP
--|||You didn't answer any of the questions I asked.

> companyid=(price(previousday)/price(currentday)-1)*100

Yes, but what is current and previous here? You cannot know which row
represents the previous day unless you have a date column or similar.

--
David Portas
SQL Server MVP
--|||--Create the other column

ALTER TABLE #tempone ADD SomeCalcValue REAL NULL

UPDATE #Tempone SET
col3 =
((SELECT TOP 1 SomeValue
FROM #Tempone T2
WHERE T1.SomeId = T2.SomeID
AND T2.SomeValue > T1.SomeValue
ORDER BY SomeValue )
/ (CASE SomeValue WHEN 0 THEN NULL ELSE SomeValue
END)-1)*100
>From #Tempone T1

But what I already mentioned and David mentioned is the sequence in
which the calculations is done in place, because in your sample data
there was no structure to find any logic of the sequence in there.

BTW, Call me Jens :-D

HTH, jens Suessmeyer.|||Hi David Portas

here col2 values are arranged in the descending order of date

this is the query is used

select companyid,latestclosingprice into #Tempone from
backscreeningdata3 where dailydate
between '12/3/04' and '01/05/05' order by companyid,dailydate desc

Hope U understand

Thanks & Regards
kalyan|||UPDATE #Tempone SET
differenceValue =
((SELECT TOP 1 latestclosingprice
FROM #Tempone T2
WHERE T1.companyid = T2.companyid
AND T2.dailydate > T1.dailydate
ORDER BY dailydate ASC )
/ (CASE SomeValue WHEN 0 THEN NULL ELSE SomeValue END)-1)*100
FROM #Tempone T1

That assumes that only one entry per date exists, because otherwise
there would be a unpredicted way to sort the results.|||Hi jens

I got a problem by using u r query

i generated a temp table using the following query as

select companyid,latestclosingprice,dailydate into #Tempone from
backscreeningdata3 where dailydate
between '12/3/04' and '01/05/05' order by companyid,dailydate desc

if i took records for 2 companies these r the results

32.0500 2005-01-05 00:00:00.000
32.1800 2005-01-04 00:00:00.000
32.2600 2005-01-03 00:00:00.000
32.1500 2004-12-31 00:00:00.000
32.1400 2004-12-30 00:00:00.000
32.1400 2004-12-29 00:00:00.000
32.1800 2004-12-28 00:00:00.000
32.1900 2004-12-27 00:00:00.000
32.1700 2004-12-23 00:00:00.000
32.0900 2004-12-22 00:00:00.000
32.0700 2004-12-21 00:00:00.000
32.0700 2004-12-20 00:00:00.000
32.1000 2004-12-17 00:00:00.000
32.3400 2004-12-16 00:00:00.000
31.7300 2004-12-15 00:00:00.000
31.7300 2004-12-14 00:00:00.000
31.8200 2004-12-13 00:00:00.000
31.7800 2004-12-10 00:00:00.000
31.7000 2004-12-09 00:00:00.000
31.6300 2004-12-08 00:00:00.000
31.6500 2004-12-07 00:00:00.000
31.6700 2004-12-06 00:00:00.000
31.7000 2004-12-03 00:00:00.000

16112.18002005-01-05 00:00:00.000
16112.89002005-01-04 00:00:00.000
16114.55002005-01-03 00:00:00.000
16115.12002004-12-31 00:00:00.000
16115.50002004-12-30 00:00:00.000
16115.06002004-12-29 00:00:00.000
16114.80002004-12-28 00:00:00.000
16114.71002004-12-27 00:00:00.000
16114.22002004-12-23 00:00:00.000
16113.14002004-12-22 00:00:00.000
16111.67002004-12-21 00:00:00.000
16112.39002004-12-20 00:00:00.000
16109.47002004-12-17 00:00:00.000
16108.49002004-12-16 00:00:00.000
16108.96002004-12-15 00:00:00.000
16108.19002004-12-14 00:00:00.000
16107.09002004-12-13 00:00:00.000
16106.12002004-12-10 00:00:00.000
16106.02002004-12-09 00:00:00.000
16105.41002004-12-08 00:00:00.000
16107.88002004-12-07 00:00:00.000
16106.77002004-12-06 00:00:00.000
16107.68002004-12-03 00:00:00.000

so by using u r query

i got the following

32.0500.97560975609756102005-01-05 00:00:00.000
32.1800.45871559633027522005-01-04 00:00:00.000
32.26003.53982300884955752005-01-03 00:00:00.000
32.1500.93023255813953492004-12-31 00:00:00.000
32.1400.46728971962616822004-12-30 00:00:00.000
32.1400.46728971962616822004-12-29 00:00:00.000
32.1800.45871559633027522004-12-28 00:00:00.000
32.19003.19634703196347032004-12-27 00:00:00.000
32.1700.46082949308755762004-12-23 00:00:00.000
32.0900.47846889952153112004-12-22 00:00:00.000
32.0700.96618357487922712004-12-21 00:00:00.000
32.0700.96618357487922712004-12-20 00:00:00.000
32.10001.90476190476190482004-12-17 00:00:00.000
32.3400NULL2004-12-16 00:00:00.000
31.73002.89017341040462432004-12-15 00:00:00.000
31.73002.89017341040462432004-12-14 00:00:00.000
31.820012.63736263736263742004-12-13 00:00:00.000
31.78002.24719101123595512004-12-10 00:00:00.000
31.70001.76470588235294122004-12-09 00:00:00.000
31.63001.22699386503067482004-12-08 00:00:00.000
31.65001.21212121212121212004-12-07 00:00:00.000
31.67001.79640718562874252004-12-06 00:00:00.000

here i should not get a value for col3 as there is no value for the
next date of this id..same for the next id also

31.70001.76470588235294122004-12-03 00:00:00.000

16112.1800.18719914423248352005-01-05 00:00:00.000
16112.8900.22145451324297992005-01-04 00:00:00.000
16114.5500.13967699694456572005-01-03 00:00:00.000
16115.1200.33009034051424602004-12-31 00:00:00.000
16115.5000NULL2004-12-30 00:00:00.000
16115.0600.05214670606640012004-12-29 00:00:00.000
16114.8000.22648083623693382004-12-28 00:00:00.000
16114.7100.07845872199459512004-12-27 00:00:00.000
16114.2200.28891612677289442004-12-23 00:00:00.000
16113.1400.95456955983736962004-12-22 00:00:00.000
16111.6700.45670278499149282004-12-21 00:00:00.000
16112.3900.44487943767239082004-12-20 00:00:00.000
16109.47002.00968301817849642004-12-17 00:00:00.000
16108.4900.43321965158079092004-12-16 00:00:00.000
16108.9600.46806167400881062004-12-15 00:00:00.000
16108.1900.27728995286070802004-12-14 00:00:00.000
16107.0900.55093846297506772004-12-13 00:00:00.000
16106.1200.61251413494157562004-12-10 00:00:00.000
16106.0200.09432182607055272004-12-09 00:00:00.000
16105.4100.57869272365050752004-12-08 00:00:00.000
16107.8800.28735632183908052004-12-07 00:00:00.000
16106.7700.29970965627048802004-12-06 00:00:00.000
16107.6800.18573551263001492004-12-03 00:00:00.000

please help

thanks & regards
kalyan|||Wrong. ORDER BY on SELECT INTO does NOT order the values because tables
are always unordered. Don't use ORDER BY with SELECT INTO, it serves no
purpose except maybe to slow things down. See the UPDATE that Jens
posted. Note that you need the date in there too.

--
David Portas
SQL Server MVP
--|||PLEASE post DDL. PLEASE include the KEYS and CONSTRAINTS with the DDL.
Without this information any answers you get will just be guesswork.
Read the following article to understand how to do this and how to
include sample data as INSERT statements to make it easier for others
to test out solutions:

http://www.aspfaq.com/etiquette.asp?id=5006

You SELECT INTO is more or less irrelevent here. What we really need to
know is the KEYS for the base table, or the table on which you want to
perform the UPDATE or SELECT.

--
David Portas
SQL Server MVP
--|||Hi David Portas

If order by doesn't work then how can i frame my temp table so that i
can work on Jen's query

Hope u got my requirement

thanks & Regards
kalyan|||Hi OP,

Select SomeID,SomeValue,(SELECT TOP 1 Somevalue from #tempone t2 Where
t1.SomeId = t2.Someid AND t2.lastestDate<t1.lastestDate Order by
lastestDate DESC),
((SELECT TOP 1 Somevalue from #tempone t2 Where t1.SomeId = t2.Someid
AND t2.lastestDate<t1.lastestDate Order by lastestDate DESC)/
(CASE SomeValue WHEN 0 THEN NULL ELSE SomeValue END)-1)*100 as
DifferenceValue,lastestDate
FROM (SELECT TOP 100 PERCENT * from #TempOne order by lastestdate DESC)
T1

next time you are asking please provide directly the sample and DDL
data, thatll help us to help you father rather than just guessing.

Jens.|||Hi OP,

next time you are asking please provide directly the sample and DDL
data, thatll help us to help you father rather than just guessing.

Jens.|||Hi Jens

Sorry for testing u r patience.
Actually i'm a new bie to sql

i must frame a temp table from a history table

select companyid,latestclosingprice,dailydate into #Tempone from
backscreeningdata3 where dailydate
between '12/3/04' and '01/05/05' order by companyid,dailydate desc

so i get the records for each companyid for a period of 23 days
from these i've calculate returns for each companyid for the entire
period
as

for each companyid for each day
return=(price(previousday)/price(currentday)-1)*100

hope u understand

i'm extremely sorry if i'm troubling u alot

hope u got me

BTW what do u mean by OP

Thanks & Regards
kalyan

here backscreeningdata3 is the history table which is updated daily
since 5 years

so for each day we have 4000-4500 records will be updated each
identified by companyid|||kalikoi@.gmail.com wrote:
> Hi David Portas
> If order by doesn't work then how can i frame my temp table so that i
> can work on Jen's query
> Hope u got my requirement
> thanks & Regards
> kalyan

Lacking a date column, you could add an identity (or timestamp?) column
that would track the sequence in which rows are inserted. If the
business
process causes rows to be inserted in chronological sequence, this
column would allow you to identify rows from consecutive days for a
given ID and determine their place in the arithmetic.|||(kalikoi@.gmail.com) writes:
> select companyid,latestclosingprice,dailydate into #Tempone from
> backscreeningdata3 where dailydate
> between '12/3/04' and '01/05/05' order by companyid,dailydate desc

The ORDER BY is still meaningless.

> so i get the records for each companyid for a period of 23 days
> from these i've calculate returns for each companyid for the entire
> period
> as
> for each companyid for each day
> return=(price(previousday)/price(currentday)-1)*100

UPDATE a
SET col3 = (b.price/a.price - 1) * 100
FROM tbl a
JOIN tbl b ON a.companyid = b.companyid
AND a.date = daetadd(DAY, 1, b.date)

This is untested. Had you included the following:

o CREATE TABLE statement for the table.
o Sample data *as INSERT statements*
o The desired result given the sample.

You would have gotten a tested solution.

If this does not give exacatly the right result, play around with
1 and -1, and moving around the alias.

> BTW what do u mean by OP

Some people use "the OP" to refer to someone who have posted. I don't know
if they read it out as "the other person" or "the original poster", but
personally I find it impolite to talk about someone as OP when this
person is present - and adressing someone as OP?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 3 Nov 2005 22:56:11 +0000 (UTC), Erland Sommarskog wrote:

>> BTW what do u mean by OP
>Some people use "the OP" to refer to someone who have posted. I don't know
>if they read it out as "the other person" or "the original poster", but
>personally I find it impolite to talk about someone as OP when this
>person is present - and adressing someone as OP?

Hi Erland,

The normal meaning for OP is indeed Original Poster. Not Other person.

http://slang.acronymfinder.com/af-q...xact&acronym=OP

I sometimes refer to the OP of a thread as OP. Never when writing a
response to the OP, but sometimes after lots of messages between others.
In that case, "but is that what the OP asked" will be easier to
understand than "but is that what Frank asked" (Frank? Who is Frank??).

Also, I'm more inclined to use the abbreviation OP when the OP uses a
nickname with unprintable characters or foul words.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> The normal meaning for OP is indeed Original Poster. Not Other person.
> http://slang.acronymfinder.com/af-q...xact&acronym=OP
>
> I sometimes refer to the OP of a thread as OP. Never when writing a

And just like DDL, BOL and other funny abbreviations, this is something
that is to be avoided. Not everyone who is posting here knows what they
mean, and it's only confusing to them.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 3 Nov 2005 23:50:44 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> The normal meaning for OP is indeed Original Poster. Not Other person.
>>
>> http://slang.acronymfinder.com/af-q...xact&acronym=OP
>>
>>
>> I sometimes refer to the OP of a thread as OP. Never when writing a
>And just like DDL, BOL and other funny abbreviations, this is something
>that is to be avoided. Not everyone who is posting here knows what they
>mean, and it's only confusing to them.

Hi Erland,

You're right - thanks for the reminder!

(I must admit that I was tempted to write YR-TFTR, though :-)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, March 30, 2012

Help in creating Dynamic Rows!

Help in creating dynamic rows!

Friday, March 9, 2012

HELP - SQL Server 2000 Install Files needed urgently

Friday, February 24, 2012

help

Help

Sunday, February 19, 2012

help

help

help

Absurd project

Blog Archive

About Me