sql reports: columns

Showing posts with label columns. Show all posts

Thursday, March 29, 2012

Can we insert NULL in place of foreign key.

I have a couple of tables in my database (SQL server 2000)
1. Student -- Columns are as
"StudentID" -- INT
"StudentName" -- NVARCHAR
"DepartmentID" -- INT
2. Department -- Columns are as
"DepartmentID" -- INT
"DepartmentName" -- NVARCHAR
So in the table "Student", the field "DepartmentID" is a foreign key. Now I
tried out turning "Allow Null" on for the column "DepartmentID" in the
"Student" table. As soon as I did this, I was able to insert a row with
"DepartmentID" as <NULL>.
Now isn't this wrong? Or is it that the database overrides the checking of
the foreign key against the table "Department" when I check the "Allow Null"
option ON?
It would be really helpful if someone could elaborate on this.
Locate me in the blogosphere: http://spaces.msn.com/aayushpuriHi
Yes, referencing table may accept NULL's. Like people that don't have
children.
"Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
news:C5A2D39D-5C28-4661-B68B-B2CA6BA48CDF@.microsoft.com...
>I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
> I
> tried out turning "Allow Null" on for the column "DepartmentID" in the
> "Student" table. As soon as I did this, I was able to insert a row with
> "DepartmentID" as <NULL>.
> Now isn't this wrong? Or is it that the database overrides the checking of
> the foreign key against the table "Department" when I check the "Allow
> Null"
> option ON?
> It would be really helpful if someone could elaborate on this.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri|||Aayush Puri wrote:
> I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
I
> tried out turning "Allow Null" on for the column "DepartmentID" in the
> "Student" table. As soon as I did this, I was able to insert a row with
> "DepartmentID" as <NULL>.
> Now isn't this wrong? Or is it that the database overrides the checking of
> the foreign key against the table "Department" when I check the "Allow Nul
l"
> option ON?
> It would be really helpful if someone could elaborate on this.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
SQL's foreign key constraints work like check constraints.
Specifically, values are permitted as long as the constraint isn't
violated. A null comparison returns an UNKNOWN result rather than a
FALSE one and UNKNOWN isn't regarded as a violation of the constraint.
So the behaviour you mentioned is "right" according to the SQL
standard. Whether it makes good logical sense is a different matter.
I'd suggest that you either disallow nulls or that you create some
default value in the Depatrtments table to represent the case that you
would otherwise have used a null for - a "Not Applicable" department
for example.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Tuesday, March 27, 2012

Can we define multiple key columns for a mining structure?

Hi, all,

Just found that we are not able to define multiple key columns for a mining structure in SQL Server 2005 Data Mining engine, just wondering is there other way to define multiple key columns for a mining structure there? As in many cases, the table we are mining are with composite key consisting of different foriengn keys, e.g. A fact table are with transaction information and other foreign keys. If I am not able to define these composite key here for this fact table, I will have to have a named calculation in data source view to have a key column which is based on these original composite keys? Is this a better way to solve this problem or there is any other alternatives to figure it out?

Hope my question is clear for your help and I am looking forward to heaing from you shortly for your kind advices and help and thanks a lot in advance.

With best regards,

Yours sincerely,

Multiple key columns are not permitted in a mining structure. However, you can specify multiple bindings for the Key column in the mining structure: in the Mining Structure tab in BI Dev Studio, click on KeyColumns in the Properties pane and then click on the "..." button in the value field to bring up an editor that allows you add additional bindings. You will also need to specify a NameColumn binding (the next field in Properties) that binds to a source that contains unique names for the composite key you've specified via the multiple bindings under KeyColumns.

The option you suggest (adding a named calculation in the DSV) will also work.

|||

Hi, Raman,

Thanks for your advices.

But still we need a name column whenever we have a muitiple columns bindings for the key attribute?

With best regards,

Yours sincerely,

|||Yes, that's correct - otherwise the server does not know what to name the composite key value (it can't do a simple concatenation because of type differences).|||

Hi, Raman,

Thanks.

Best regards,

Yours sincerely,

Can we create composite logical key in data source view?

Hi, all,

Just found that I need to create a composite logical key consisting of a few columns in data source view in order to uniquely identify each row of record for the table in data source view, but then found that I am not able to create this kind of logical key? Is there any ways for us to create such a compoiste logical key in the data source view?

Thanks in advance and I am very much looking forward to hearing from you for your kind and helpful advices.

With best regards,

Yours sincerely,

You can create composite logical keys in the DSV, just multi-select columns, right click, and mark as logical key. However, if an existing physical key (i.e. a key reported by the relational source), then the DSV will not allow you to create any logical key.|||

Hi,

Thanks a lot.

WIth best regards,

Yours sincerely,

Sunday, March 25, 2012

Can we change table structure in Mirroring

Can we change table structure in Mirroring. Like adding or deleting columns and tables and how can we do it. Thank you very much.Yes, you can change table structure in Database Mirroring. Changing the structure in the primary database is reflected over the mirror database. Think of database mirroring as "first class" log shipping with automatic failover. Initially, I thought only the data is mirrored since the mirror database is on read-only mode. But testing showed that DDL changes also get "mirrored."|||

Database Mirroring was initially called "Real Time Log Shipping." It is basically taking the log stream and "restoring" it on the mirror server in real time.

So, yes, you can change the table structure in database mirroring.

Thanks,

Mark

Can we change table structure in Mirroring

Can we change table structure in Mirroring. Like adding or deleting columns and tables and how can we do it. Thank you very much.

Yes you can, and you do it just like you would if Mirroring was not being used (i.e. ALTER TABLE, CREATE TABLE, etc.)...DB mirroring moves logged transactions to the mirror as they are applied to the principle, and this includes DDL statements.

HTH,

sql

Can we change table structure in Mirroring

Can we change table structure in Mirroring. Like adding or deleting columns and tables and how can we do it. Thank you very much.

HTH,

Can view accept parameters

Thursday, March 22, 2012

Can view accept parameters

Hi All ,
i wonder if a view can accept parameters becoz i only
want to show certain columns based on user's input.
if it's not possible, could i know is there other
alternative as to accept both the user's input as well as
restrict the column to be displayed as readonly
thks & rdgsWhat version are you running? Please always state that information.
If you are using SQL Server 2000, you can create a table valued function
that can be used EXACTLY like a view which accepts parameters. Please read
about CREATE FUNCTION in the Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:056c01c47aae$ea2814d0$a501280a@.phx.gbl...
> Hi All ,
> i wonder if a view can accept parameters becoz i only
> want to show certain columns based on user's input.
> if it's not possible, could i know is there other
> alternative as to accept both the user's input as well as
> restrict the column to be displayed as readonly
> thks & rdgs|||Hi Kalen ,
my appologies ..
btw i am using SQL 7.0 , is this possible then ?
thks
>--Original Message--
>What version are you running? Please always state that
information.
>If you are using SQL Server 2000, you can create a table
valued function
>that can be used EXACTLY like a view which accepts
parameters. Please read
>about CREATE FUNCTION in the Books Online.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:056c01c47aae$ea2814d0$a501280a@.phx.gbl...
>> Hi All ,
>> i wonder if a view can accept parameters becoz i only
>> want to show certain columns based on user's input.
>> if it's not possible, could i know is there other
>> alternative as to accept both the user's input as well
as
>> restrict the column to be displayed as readonly
>> thks & rdgs
>
>.
>|||No. In the relational model, a view is just like a table and a table doesn't accept parameters.
In-line table values functions is what some call "a view that accepts parameters" but that feature was added
in 2000. Can you use a stored procedure instead?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:042d01c47ab0$eb6bcd80$a301280a@.phx.gbl...
> Hi Kalen ,
> my appologies ..
> btw i am using SQL 7.0 , is this possible then ?
> thks
> >--Original Message--
> >What version are you running? Please always state that
> information.
> >
> >If you are using SQL Server 2000, you can create a table
> valued function
> >that can be used EXACTLY like a view which accepts
> parameters. Please read
> >about CREATE FUNCTION in the Books Online.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:056c01c47aae$ea2814d0$a501280a@.phx.gbl...
> >> Hi All ,
> >>
> >> i wonder if a view can accept parameters becoz i only
> >> want to show certain columns based on user's input.
> >>
> >> if it's not possible, could i know is there other
> >> alternative as to accept both the user's input as well
> as
> >> restrict the column to be displayed as readonly
> >>
> >> thks & rdgs
> >
> >
> >.
> >

Can Users choose Rows & columns for a matrix?

Hi,
Is it possible to create a report which allows users to select the fields
for the matrix in a report. To explain in detail, can we allow the users to
select the X and Y axis for a matrix in a report?
For ex., there is a report containing a matrix which shows the total sales(
data cell) by month (X axis) and by Company( Y axis). Can we have some option
so that if the users select Year as the X axis and SalesPerson as the Y axis
then they can view the same report but by SalesPerson and Year instead od
Month and Company?
Any help is highly appreciated.
Thanks
--
pmudYes, this is possible by using dynamic field references for the grouping
expression and the matrix group header textbox expression. E.g.
=Fields(Parameters!RowGroup.Value).Value
Note: the actual field name in the expression above will be determined by
the parameter's value at runtime. You can use this for the row and the
column grouping of the matrix.
A RS 2005 sample report (which also includes InteractiveSort on the dynamic
matrix row groups) is attached. Note: you cannot load this sample in the RS
2000 report designer.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:B91AE70B-5CEF-402E-B9A5-30BD193CBCE0@.microsoft.com...
> Hi,
> Is it possible to create a report which allows users to select the fields
> for the matrix in a report. To explain in detail, can we allow the users
> to
> select the X and Y axis for a matrix in a report?
> For ex., there is a report containing a matrix which shows the total
> sales(
> data cell) by month (X axis) and by Company( Y axis). Can we have some
> option
> so that if the users select Year as the X axis and SalesPerson as the Y
> axis
> then they can view the same report but by SalesPerson and Year instead od
> Month and Company?
> Any help is highly appreciated.
> Thanks
> --
> pmud
=====================================================
<?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="northwind">
<DataSourceReference>northwind</DataSourceReference>
<rd:DataSourceID>66a72cd8-749c-4971-b5d6-05b2612a4d40</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="RowGroup">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>ProductName</Value>
</Values>
</DefaultValue>
<Prompt>RowGroup</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>ProductName</Value>
<Label>By Product Name</Label>
</ParameterValue>
<ParameterValue>
<Value>SupplierID</Value>
<Label>By Supplier ID</Label>
</ParameterValue>
<ParameterValue>
<Value>CategoryID</Value>
<Label>By Category ID</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="ColumnGroup">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>ReorderLevel</Value>
</Values>
</DefaultValue>
<Prompt>ColumnGroup</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>ReorderLevel</Value>
<Label>By Reorder Level</Label>
</ParameterValue>
<ParameterValue>
<Value>UnitsInStock</Value>
<Label>By Stock</Label>
</ParameterValue>
<ParameterValue>
<Value>SupplierID</Value>
<Label>By Supplier ID</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox3">
<Left>0.125in</Left>
<Top>0.375in</Top>
<ZIndex>2</ZIndex>
<Width>3in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="Matrix columns " & Parameters!ColumnGroup.Label</Value>
</Textbox>
<Textbox Name="textbox1">
<Left>0.125in</Left>
<Top>0.125in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Width>3in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="Matrix rows " & Parameters!RowGroup.Label</Value>
</Textbox>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>2.125in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields(Parameters!RowGroup.Value).Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_RowGroup">
<GroupExpressions>
<GroupExpression>=Fields(Parameters!RowGroup.Value).Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="ReorderLevel">
<rd:DefaultName>ReorderLevel</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields(Parameters!ColumnGroup.Value).Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields(Parameters!ColumnGroup.Value).Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="matrix1_ColumnGroup">
<GroupExpressions>
<GroupExpression>=Fields(Parameters!ColumnGroup.Value).Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Top>0.875in</Top>
<Width>3.125in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortExpression>=Fields(Parameters!RowGroup.Value).Value</SortExpression>
<SortExpressionScope>matrix1_RowGroup</SortExpressionScope>
</UserSort>
<Value>Sort rows</Value>
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ProductID">
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!ProductID.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>4614d21e-03f0-4b4b-8270-a40c31094d26</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from products</CommandText>
<DataSourceName>northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>3.375in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>|||Hi Robert,
I dont have RS 2005. So the way you told ( by having
=Fields(Parameters!RowGroup.Value)) will work for RS 2000 too?
Thanks
--
pmud
"Robert Bruckner [MSFT]" wrote:
> Yes, this is possible by using dynamic field references for the grouping
> expression and the matrix group header textbox expression. E.g.
> =Fields(Parameters!RowGroup.Value).Value
> Note: the actual field name in the expression above will be determined by
> the parameter's value at runtime. You can use this for the row and the
> column grouping of the matrix.
> A RS 2005 sample report (which also includes InteractiveSort on the dynamic
> matrix row groups) is attached. Note: you cannot load this sample in the RS
> 2000 report designer.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:B91AE70B-5CEF-402E-B9A5-30BD193CBCE0@.microsoft.com...
> > Hi,
> >
> > Is it possible to create a report which allows users to select the fields
> > for the matrix in a report. To explain in detail, can we allow the users
> > to
> > select the X and Y axis for a matrix in a report?
> >
> > For ex., there is a report containing a matrix which shows the total
> > sales(
> > data cell) by month (X axis) and by Company( Y axis). Can we have some
> > option
> > so that if the users select Year as the X axis and SalesPerson as the Y
> > axis
> > then they can view the same report but by SalesPerson and Year instead od
> > Month and Company?
> >
> > Any help is highly appreciated.
> >
> > Thanks
> > --
> > pmud
>
> =====================================================> <?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="northwind">
> <DataSourceReference>northwind</DataSourceReference>
> <rd:DataSourceID>66a72cd8-749c-4971-b5d6-05b2612a4d40</rd:DataSourceID>
> </DataSource>
> </DataSources>
> <BottomMargin>1in</BottomMargin>
> <RightMargin>1in</RightMargin>
> <ReportParameters>
> <ReportParameter Name="RowGroup">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>ProductName</Value>
> </Values>
> </DefaultValue>
> <Prompt>RowGroup</Prompt>
> <ValidValues>
> <ParameterValues>
> <ParameterValue>
> <Value>ProductName</Value>
> <Label>By Product Name</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>SupplierID</Value>
> <Label>By Supplier ID</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>CategoryID</Value>
> <Label>By Category ID</Label>
> </ParameterValue>
> </ParameterValues>
> </ValidValues>
> </ReportParameter>
> <ReportParameter Name="ColumnGroup">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>ReorderLevel</Value>
> </Values>
> </DefaultValue>
> <Prompt>ColumnGroup</Prompt>
> <ValidValues>
> <ParameterValues>
> <ParameterValue>
> <Value>ReorderLevel</Value>
> <Label>By Reorder Level</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>UnitsInStock</Value>
> <Label>By Stock</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>SupplierID</Value>
> <Label>By Supplier ID</Label>
> </ParameterValue>
> </ParameterValues>
> </ValidValues>
> </ReportParameter>
> </ReportParameters>
> <rd:DrawGrid>true</rd:DrawGrid>
> <InteractiveWidth>8.5in</InteractiveWidth>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Body>
> <ReportItems>
> <Textbox Name="textbox3">
> <Left>0.125in</Left>
> <Top>0.375in</Top>
> <ZIndex>2</ZIndex>
> <Width>3in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>="Matrix columns " & Parameters!ColumnGroup.Label</Value>
> </Textbox>
> <Textbox Name="textbox1">
> <Left>0.125in</Left>
> <Top>0.125in</Top>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Width>3in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>="Matrix rows " & Parameters!RowGroup.Label</Value>
> </Textbox>
> <Matrix Name="matrix1">
> <MatrixColumns>
> <MatrixColumn>
> <Width>1in</Width>
> </MatrixColumn>
> </MatrixColumns>
> <Left>0.125in</Left>
> <RowGroupings>
> <RowGrouping>
> <Width>2.125in</Width>
> <DynamicRows>
> <ReportItems>
> <Textbox Name="CategoryID">
> <rd:DefaultName>CategoryID</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields(Parameters!RowGroup.Value).Value</Value>
> </Textbox>
> </ReportItems>
> <Grouping Name="matrix1_RowGroup">
> <GroupExpressions>
> <GroupExpression>=Fields(Parameters!RowGroup.Value).Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </DynamicRows>
> </RowGrouping>
> </RowGroupings>
> <ColumnGroupings>
> <ColumnGrouping>
> <DynamicColumns>
> <ReportItems>
> <Textbox Name="ReorderLevel">
> <rd:DefaultName>ReorderLevel</rd:DefaultName>
> <ZIndex>2</ZIndex>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields(Parameters!ColumnGroup.Value).Value</Value>
> </Textbox>
> </ReportItems>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields(Parameters!ColumnGroup.Value).Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <Grouping Name="matrix1_ColumnGroup">
> <GroupExpressions>
> <GroupExpression>=Fields(Parameters!ColumnGroup.Value).Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </DynamicColumns>
> <Height>0.25in</Height>
> </ColumnGrouping>
> </ColumnGroupings>
> <DataSetName>DataSet1</DataSetName>
> <Top>0.875in</Top>
> <Width>3.125in</Width>
> <Corner>
> <ReportItems>
> <Textbox Name="textbox4">
> <rd:DefaultName>textbox4</rd:DefaultName>
> <ZIndex>3</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <UserSort>
> <SortExpression>=Fields(Parameters!RowGroup.Value).Value</SortExpression>
> <SortExpressionScope>matrix1_RowGroup</SortExpressionScope>
> </UserSort>
> <Value>Sort rows</Value>
> </Textbox>
> </ReportItems>
> </Corner>
> <Height>0.5in</Height>
> <MatrixRows>
> <MatrixRow>
> <Height>0.25in</Height>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="ProductID">
> <rd:DefaultName>ProductID</rd:DefaultName>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Count(Fields!ProductID.Value)</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> </MatrixRow>
> </MatrixRows>
> </Matrix>
> </ReportItems>
> <Height>2in</Height>
> </Body>
> <rd:ReportID>4614d21e-03f0-4b4b-8270-a40c31094d26</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="DataSet1">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>select * from products</CommandText>
> <DataSourceName>northwind</DataSourceName>
> </Query>
> <Fields>
> <Field Name="ProductID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>ProductID</DataField>
> </Field>
> <Field Name="ProductName">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>ProductName</DataField>
> </Field>
> <Field Name="SupplierID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>SupplierID</DataField>
> </Field>
> <Field Name="CategoryID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>CategoryID</DataField>
> </Field>
> <Field Name="QuantityPerUnit">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>QuantityPerUnit</DataField>
> </Field>
> <Field Name="UnitPrice">
> <rd:TypeName>System.Decimal</rd:TypeName>
> <DataField>UnitPrice</DataField>
> </Field>
> <Field Name="UnitsInStock">
> <rd:TypeName>System.Int16</rd:TypeName>
> <DataField>UnitsInStock</DataField>
> </Field>
> <Field Name="UnitsOnOrder">
> <rd:TypeName>System.Int16</rd:TypeName>|||Robert -
I have been trying something very similar to this except that it takes
it one step further. My matrix has multiple RowGroups and multiple
ColumnGroups. I'd like the user to be able to turn some off, but I
cannot keep the RowGroup fields from always appearing in the matrix.
I use expressions to turn the RowGrouping on/off. When the RowGroup is
not to appear, the expression evaluates to "".
I also use expressions to set the RowGroup visibility to false.
Is there any way to do this? Maybe I am doing something wrong?
Thanks.

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satishIt is recommended to never do SELECT * or INSERT without column list in prod
uction code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-cre
ating the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegroups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satish
It is recommended to never do SELECT * or INSERT without column list in production code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-creating the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegr oups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>
|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish
sql

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satishIt is recommended to never do SELECT * or INSERT without column list in production code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-creating the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegroups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish

can u help with my select ?

Hello All,

I have a simple table with 2 columns.

datetime int
27.05.04 1
29.05.04 2
31.05.04 5

and i need to get a view that looks like this below :

27.05.04 1 1
29.05.04 2 3
31.05.04 5 8

The third column shows a sum of numbers that were display before present one
...
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(

can anybody help ?

thx in advance
Krzysiek"Krzysiek" <krzysiek79@.hotmail.com> wrote in message
news:c9fq48$25ka$1@.news2.ipartners.pl...
> Hello All,
> I have a simple table with 2 columns.
> datetime int
> 27.05.04 1
> 29.05.04 2
> 31.05.04 5
> and i need to get a view that looks like this below :
> 27.05.04 1 1
> 29.05.04 2 3
> 31.05.04 5 8
> The third column shows a sum of numbers that were display before present
one
> ..
> I was trying build stored proc however I'm not so familiar with SQL so I
> failed.. :-(
> can anybody help ?
> thx in advance
> Krzysiek

It looks like you got an answer in another group - please do not post to
multiple groups independently.

Simon

Can TSQL query create new output column ?

How can i write a query to split a database column and shows 2 new columns. In my database column

I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter

and run another query.

For example a database column with average number and range number.

Thanks

Daniel

Can you post some DDL, sample data and expected results?

AMB

|||

Hai,

Can you try the below query, and let me know that, it relates to your requirement or not:

DECLARE @.Columns varchar(1000)

SET @.Columns = ''

-- Create a temporary table.

CREATE TABLE #TempTable(Items varchar(50))

INSERT INTO #TempTable(Items) VALUES('A')

INSERT INTO #TempTable(Items) VALUES('B')

INSERT INTO #TempTable(Items) VALUES('C')

INSERT INTO #TempTable(Items) VALUES('D')

-- Before

SELECT * FROM #TempTable

-- Make a column list

SELECT

@.Columns = @.Columns + '[' + Items + '], '

FROM #TempTable

GROUP BY Items

-- Check the column values exits or not.

IF ( @.Columns IS NOT NULL ) AND ( @.Columns <> '' )

BEGIN

DECLARE @.Query nvarchar(1000)

SELECT @.Columns = SUBSTRING(@.Columns,1, LEN(@.Columns)-1)

SELECT @.Query = '

SELECT

FROM

(

SELECT

Items

FROM #TempTable

) AS Dummy

PIVOT

(

MAX(Items)

FOR Items IN (' + @.Columns + ')

)AS PvtTable'

EXEC(@.Query)

END

-- Drop the temporary table.

DROP TABLE #TempTable

Please clarify If I did any wrong.

Regards,

Kiran.Y

|||

Perhaps something like:

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyGroup int,
MyValue decimal(10,2)
)

INSERT INTO @.MyTable VALUES ( 1, 25 )
INSERT INTO @.MyTable VALUES ( 2, 5 )
INSERT INTO @.MyTable VALUES ( 1, 10 )
INSERT INTO @.MyTable VALUES ( 1, 15 )
INSERT INTO @.MyTable VALUES ( 1, 4 )
INSERT INTO @.MyTable VALUES ( 2, 6 )
INSERT INTO @.MyTable VALUES ( 2, 11 )
INSERT INTO @.MyTable VALUES ( 2, 0 )
INSERT INTO @.MyTable VALUES ( 1, 12 )

SELECT
Average = cast( avg( MyValue ) AS decimal(10,2)),
Range = ( cast( min( MyValue ) AS varchar(10)) + '-' +
cast( max( MyValue ) AS varchar(10)))
FROM @.MyTable
GROUP BY MyGroup

Average Range
13.20 4.00-25.00
5.50 0.00-11.00

|||

Hi Kiran

Thanks for answering my email. To clarify this below are my tables and columns and my query

Table: Item Stat_label Stat_value

column: Pack ID Stat_label_ID Stat_value_ID

Pack_Num Label ( has 2 rows Value

Ave and Range)

My query to list Pack_Num, Ave and it's value

SELECT Item.Pack_Num, Stat_label.Label, Stat_value.Value

FROM Item, Stat_label, Stat_value

WHERE Item.packID=Stat_label.Stat_label_ID AND

Stat_label.Stat_lavel_ID=Stat_value.Stat_value_ID

AND Stat_label.Label= Ave

My question: I want a query to list Pack_Num, Ave, Range and value

How can I do it?

That's mean this query need to split the Stat_label and list another

column name"Range".

Thanks
Daniel

|||

If you are using SQL 2005, look into the PIVOT function.

If you are using SQL 2000, explore using CASE.

Maybe these articles will help:

Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/

|||

Thanks all

I can not use "insert" because my account for this is read only and I avoid to list everything in a column and and use Excel pivot to summary.

Daniel

|||

Daniel,

If you would carefully examine the code provided, you will see that the INSERT statements are only building a sample table so that we could demonstrate a query suggestion.

You didn't bother to provide the table DDL, or sample data, so we have to waste our time creating sample data for you. and apparently, you can't read and understand example code.

|||

This may be closer to what you are hoping to find:

SELECT

i.Pack_Num,

sl.Stat_Label,
Average = cast( avg( sv.Stat_Value ) AS decimal(10,2)),
Range = ( cast( min( sv.Stat_Value ) AS varchar(10)) + '-' +
cast( max( sv.Stat_Value ) AS varchar(10)))
FROM Item i

JOIN Stat_Label sl

ON i.Pack_ID = sl.Stat_Label_ID

JOIN Stat_Value sv

ON sl.Stat_Label_ID = sv.Stat_Value_ID

WHERE sl.Label = 'Ave'
GROUP BY

i.Pack_Num,

sl.Stat_Label

|||

Thanks Anrnie but It is not working

Error at Average= cast......

Error at Range= (cast......

My Average and Range are decimal, no need cast

Do I have to declare a temp table?

Daniel

|||

Actually, it appears that the Stat_Value is most most likely a varchar().

Before we can help you any further, please post the table DDL and some sample data in the form of INSERT statements. Please refer to this link for help in preparing your material.

|||

Can SQL query create a new column or not?. DO NOT want to make a temp table.

Thanks

Daniel

|||

Can TSQL create a new column at the output?

If not I need 2 select statement but how to joint them? Can not use EXCEPT in TSQL? Tried to use UNION but

the results in one column.

It's complicated with creating a temp table since I do not know how to insert to temp table from database.

Thanks

Daniel

|||Please supply the requested information. (See my previous post.)

Tuesday, March 20, 2012

Can timestamp columns be updated?

Hi,
Is there any way timestamp columns can be updated in Microsoft SQL Server
2000 (SP3)?
-- Many thanks, OskarNot directly.
Linchi
"Oskar" wrote:

> Hi,
> Is there any way timestamp columns can be updated in Microsoft SQL Server
> 2000 (SP3)?
> -- Many thanks, Oskar
>|||update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=pk
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...[vbcol=seagreen]
> Not directly.
> Linchi
> "Oskar" wrote:
>|||Thanks. Not sure if this helps though. I really wanted to update the
timestamp column with the value I choose not the one of the internal @.@.DBTS
counter.
Any ideas if there's a way of updating the value the internal @.@.DBTS
counter, besides updating a zillion of records? Linchi?
"Hilary Cotter" wrote:

> update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=p
k
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...
>
>|||"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:C9211B6D-90B4-43B2-AD80-F068FB8ACF17@.microsoft.com...
> Thanks. Not sure if this helps though. I really wanted to update the
> timestamp column with the value I choose not the one of the internal
@.@.DBTS
> counter.
There is no way. Not really sure why you'd want to anyway. There's
probably better ways of doing what you want to do.

> Any ideas if there's a way of updating the value the internal @.@.DBTS
> counter, besides updating a zillion of records? Linchi?|||No, you can't directly update it. You can't do something like the following:
update myTable
set my_timestamp_column = ...
Linchi
"Oskar" wrote:
[vbcol=seagreen]
> Thanks. Not sure if this helps though. I really wanted to update the
> timestamp column with the value I choose not the one of the internal @.@.DBT
S
> counter.
> Any ideas if there's a way of updating the value the internal @.@.DBTS
> counter, besides updating a zillion of records? Linchi?
>
> "Hilary Cotter" wrote:
>

Can timestamp columns be updated?

Hi,
Is there any way timestamp columns can be updated in Microsoft SQL Server
2000 (SP3)?
-- Many thanks, OskarNot directly.
Linchi
"Oskar" wrote:
> Hi,
> Is there any way timestamp columns can be updated in Microsoft SQL Server
> 2000 (SP3)?
> -- Many thanks, Oskar
>|||update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=pk
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...
> Not directly.
> Linchi
> "Oskar" wrote:
>> Hi,
>> Is there any way timestamp columns can be updated in Microsoft SQL Server
>> 2000 (SP3)?
>> -- Many thanks, Oskar|||Thanks. Not sure if this helps though. I really wanted to update the
timestamp column with the value I choose not the one of the internal @.@.DBTS
counter.
Any ideas if there's a way of updating the value the internal @.@.DBTS
counter, besides updating a zillion of records? Linchi?
"Hilary Cotter" wrote:
> update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=pk
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...
> > Not directly.
> >
> > Linchi
> >
> > "Oskar" wrote:
> >
> >> Hi,
> >> Is there any way timestamp columns can be updated in Microsoft SQL Server
> >> 2000 (SP3)?
> >>
> >> -- Many thanks, Oskar
> >>
>
>|||"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:C9211B6D-90B4-43B2-AD80-F068FB8ACF17@.microsoft.com...
> Thanks. Not sure if this helps though. I really wanted to update the
> timestamp column with the value I choose not the one of the internal
@.@.DBTS
> counter.
There is no way. Not really sure why you'd want to anyway. There's
probably better ways of doing what you want to do.
> Any ideas if there's a way of updating the value the internal @.@.DBTS
> counter, besides updating a zillion of records? Linchi?|||No, you can't directly update it. You can't do something like the following:
update myTable
set my_timestamp_column = ...
Linchi
"Oskar" wrote:
> Thanks. Not sure if this helps though. I really wanted to update the
> timestamp column with the value I choose not the one of the internal @.@.DBTS
> counter.
> Any ideas if there's a way of updating the value the internal @.@.DBTS
> counter, besides updating a zillion of records? Linchi?
>
> "Hilary Cotter" wrote:
> > update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=pk
> >
> > --
> > Hilary Cotter
> > Director of Text Mining and Database Strategy
> > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> >
> > This posting is my own and doesn't necessarily represent RelevantNoise's
> > positions, strategies or opinions.
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> >
> >
> >
> > "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> > news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...
> > > Not directly.
> > >
> > > Linchi
> > >
> > > "Oskar" wrote:
> > >
> > >> Hi,
> > >> Is there any way timestamp columns can be updated in Microsoft SQL Server
> > >> 2000 (SP3)?
> > >>
> > >> -- Many thanks, Oskar
> > >>
> >
> >
> >

Monday, March 19, 2012

Can this be done (Query)?

Thanks in advance for any direction given!!!!!!!!

I have a table that has production infromation in it. One of the columns is to flag an exception for lack of production (Preventative Maintenance).
The table includes Equipment number, production units, date, shift and PM.

What I have been asked to do is to get the average number of units produced between PM's for each piece of equipment. I could pull the data out and put it in Excells and get the answer, but I want to automate the system and build a query that would give me the answer anytime my boss wants it.

My question is

Is It/CAN/HOW would I structure a query to average the production between the PM dates for an extended period of time (over several PM's)??

Table and data would be similar to the following

EntryDate EquipNum Shift Production PM
4/1/2003 1 1 250000 1
4/2/2003 1 2 350000 0
4/3/2003 1 3 220000 0
4/4/2003 1 1 560000 0
4/5/2003 1 2 545000 0
4/6/2003 1 3 625000 0
4/7/2003 1 1 705000 1
4/8/2003 1 2 785000 0
4/9/2003 1 3 865000 0
4/10/2003 1 1 945000 0
4/11/2003 1 2 1025000 0
4/12/2003 1 3 1105000 0
4/13/2003 1 1 1185000 1
4/14/2003 1 2 1265000 0
4/15/2003 1 3 1345000 0

Thanks,
Leenot sure if this is what you are looking for but give it a go

create table #Tmp(EntryDate datetime, EQuipNum int, Shift int, Production int, PM bit)

insert into #Tmp values('4/1/2003', 1, 1, 250000, 1)
insert into #Tmp values('4/2/2003', 1, 2, 350000, 0)
insert into #Tmp values('4/3/2003', 1, 3, 220000, 0)
insert into #Tmp values('4/4/2003', 1, 1, 560000, 0)
insert into #Tmp values('4/5/2003', 1, 2, 545000, 0)
insert into #Tmp values('4/6/2003', 1, 3, 625000, 0)
insert into #Tmp values('4/7/2003', 1, 1, 705000, 1)
insert into #Tmp values('4/8/2003', 1, 2, 785000, 0)
insert into #Tmp values('4/9/2003', 1, 3, 865000, 0)
insert into #Tmp values('4/10/2003', 1, 1, 945000, 0)
insert into #Tmp values('4/11/2003', 1, 2, 1025000, 0)
insert into #Tmp values('4/12/2003', 1, 3, 1105000, 0)
insert into #Tmp values('4/13/2003', 1, 1, 1185000, 1)
insert into #Tmp values('4/14/2003', 1, 2, 1265000, 0)
insert into #Tmp values('4/15/2003', 1, 3, 1345000, 0)

insert into #Tmp values('4/1/2003', 2, 1, 25000, 1)
insert into #Tmp values('4/2/2003', 2, 2, 35000, 0)
insert into #Tmp values('4/3/2003', 2, 3, 22000, 0)
insert into #Tmp values('4/4/2003', 2, 1, 56000, 0)
insert into #Tmp values('4/5/2003', 2, 2, 54500, 0)
insert into #Tmp values('4/6/2003', 2, 3, 62500, 0)
insert into #Tmp values('4/7/2003', 2, 1, 70500, 1)
insert into #Tmp values('4/8/2003', 2, 2, 78500, 0)
insert into #Tmp values('4/9/2003', 2, 3, 86500, 0)
insert into #Tmp values('4/10/2003', 2, 1, 94500, 0)
insert into #Tmp values('4/11/2003', 2, 2, 102500, 0)
insert into #Tmp values('4/12/2003', 2, 3, 110500, 0)
insert into #Tmp values('4/13/2003', 2, 1, 118500, 1)
insert into #Tmp values('4/14/2003', 2, 2, 126500, 0)
insert into #Tmp values('4/15/2003', 2, 3, 134500, 0)

insert into #Tmp values('4/1/2003', 3, 1, 2500, 1)
insert into #Tmp values('4/2/2003', 3, 2, 3500, 0)
insert into #Tmp values('4/3/2003', 3, 3, 2200, 0)
insert into #Tmp values('4/4/2003', 3, 1, 5600, 0)
insert into #Tmp values('4/5/2003', 3, 2, 5450, 0)
insert into #Tmp values('4/6/2003', 3, 3, 6250, 0)
insert into #Tmp values('4/7/2003', 3, 1, 7050, 1)
insert into #Tmp values('4/8/2003', 3, 2, 7850, 0)
insert into #Tmp values('4/9/2003', 3, 3, 8650, 0)
insert into #Tmp values('4/10/2003', 3, 1, 9450, 0)
insert into #Tmp values('4/11/2003', 3, 2, 10250, 0)
insert into #Tmp values('4/12/2003', 3, 3, 11050, 0)
insert into #Tmp values('4/13/2003', 3, 1, 11850, 1)
insert into #Tmp values('4/14/2003', 3, 2, 12650, 0)
insert into #Tmp values('4/15/2003', 3, 3, 13450, 0)

declare @.dtFrom datetime, @.dtTo datetime
select @.dtFrom = min(EntryDate) from #Tmp where PM = 1
select @.dtTo = min(EntryDate) from #Tmp where PM = 1 and EntryDate > @.dtFrom
while (@.dtFrom is not null and @.dtTo is not null) begin
select @.dtFrom as 'From', @.dtTo as 'To', EquipNum, avg(Production) as 'Average Units' from #Tmp where EntryDate >= @.dtFrom and EntryDate < @.dtTo group by EquipNum
set @.dtFrom = @.dtTo
select @.dtTo = min(EntryDate) from #Tmp where PM = 1 and EntryDate > @.dtFrom
end
select @.dtTo = max(EntryDate) from #Tmp
select @.dtFrom as 'From', @.dtTo as 'To', EquipNum, avg(Production) as 'Average Units' from #Tmp where EntryDate between @.dtFrom and @.dtTo group by EquipNum|||Thanks Paul for the quick response! (had issues at home and had to leave work early, checking this from home)

Please bare with me and don't laugh as I am learning this stuff on the fly and I am somewhat dense!

I think I understand the part where you are actually defining the begin and ending dates by using min() along with AND > first min(). The part I am not sure of is the #Tmp table and how it is used.

The table I will be using has 27 entries per day for the past 5 months. I assume I can create the tmp table using create with a select to populate the tmp table, but do I need to create a tmp table for each PM that has occured for the past 5 months?

I am thinking that you might have created 3 tables to see the different returns from each table as each table has a drop in value by 10. And the fact that I just so happend to have 3 PM's in the time span is just coincidence??

If my assumtion is wrong, then I don't fully understand how it loops through the 3 tables.

Thanks again,
Lee|||Just one table, the "#Tmp" should be the same as your production table, I just didn't know the name.

You should be able to cut and past the code in QA and run it as is or change the table name to your production table name and give it a go.

Basically, The loop looks for the PM dates and does an AVG() for all data between the PM dates grop by EquipNum.

Not very pretty, but works.

Can the report table have varying number of columns ??

The requierment is tyo have a dynamic report, that has a table with number of
columns decided on the input from recordset.
The SQL procedure takes in parameters and returns recordset, that can have
varying number of columns. All we want to do is to link this recordset to a
table on the report.
The report table should show the columns as they are in the Recordset. ( and
the column name in the recordset shall appear as the column header in the
Reporting table.)
-- Alok Kumar GuptaThere's really no good way to do this in the current version, no.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Alok" <Alok@.discussions.microsoft.com> wrote in message
news:47BD7BFE-DCAF-4DC1-A360-98BF8F9FB586@.microsoft.com...
> The requierment is tyo have a dynamic report, that has a table with number
of
> columns decided on the input from recordset.
> The SQL procedure takes in parameters and returns recordset, that can have
> varying number of columns. All we want to do is to link this recordset to
a
> table on the report.
> The report table should show the columns as they are in the Recordset. (
and
> the column name in the recordset shall appear as the column header in the
> Reporting table.)
> -- Alok Kumar Gupta
>

Thursday, March 8, 2012

Can SQL server return text in column instead of row?

Hi
Consider the query
"SELECT names FROM Table1"
It would return one column with some rows.
Abc
Xyz
Pqr
Mno
I want SQL server to return is in columns like
Abc Xyz Pqr Mno
Thank YouChakravarti Mukesh wrote:
> Hi
> Consider the query
> "SELECT names FROM Table1"
> It would return one column with some rows.
> Abc
> Xyz
> Pqr
> Mno
> I want SQL server to return is in columns like
> Abc Xyz Pqr Mno
> Thank You
That's just formatting a list, which something controlled by your
client application, not by SQL Server. Take a look at the ADO GetString
method for example.
http://msdn.microsoft.com/library/d...
etstringmethod(recordset)ado.asp
David Portas
SQL Server MVP
--

Thursday, March 29, 2012

Can we insert NULL in place of foreign key.

Tuesday, March 27, 2012

Can we define multiple key columns for a mining structure?

Can we create composite logical key in data source view?

Sunday, March 25, 2012

Can we change table structure in Mirroring

Can we change table structure in Mirroring

Can we change table structure in Mirroring

Can view accept parameters

Can view accept parameters

Thursday, March 22, 2012

Can view accept parameters

Can Users choose Rows & columns for a matrix?

Can u swap the columns after creation of table

Can u swap the columns after creation of table

Can u swap the columns after creation of table

can u help with my select ?

Can TSQL query create new output column ?

Tuesday, March 20, 2012

Can timestamp columns be updated?

Can timestamp columns be updated?

Monday, March 19, 2012

Can this be done (Query)?

Can the report table have varying number of columns ??

Thursday, March 8, 2012

Can SQL server return text in column instead of row?

sql reports

Blog Archive

About Me