Creating DB from scripts: row

Showing posts with label row. Show all posts

Sunday, March 25, 2012

Creating a trigger for the row being updated

Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux
Try:
update m
set
UpdatedDate = getdate ()
, UpdatedBy = CURRENT_USER
from
MyTable m
join
inserted i on i.ItemCode = m.ItemCode
join
deleted d on d.ItemCode = i.ItemCode
where
i.ShortDescription <> d.ShortDescription
or i.Quantity <> d.Quantity
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux
|||leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>
|||The problem with this code is that is does not exactly meet the spec:
'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
fields when either the "ShortDescription" or "Quantity" fields get updated.'
IOW, if both of these columns are set to their original values, the you are
updating the UpdatedDate and UpdatedBy columns, which you don't want to do.
You need to join also on the deleted virtual table and compare the values in
inserted vs. deleted.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>
|||Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
>
|||That emphasizes the fact that a PK should never change. There are
exceptions, though.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uh5I%23VIqHHA.3892@.TK2MSFTNGP05.phx.gbl...
Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
>

Creating a trigger for the row being updated

Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tuxTry:
update m
set
UpdatedDate = getdate ()
, UpdatedBy = CURRENT_USER
from
MyTable m
join
inserted i on i.ItemCode = m.ItemCode
join
deleted d on d.ItemCode = i.ItemCode
where
i.ShortDescription <> d.ShortDescription
or i.Quantity <> d.Quantity
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux|||leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>|||The problem with this code is that is does not exactly meet the spec:
'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
fields when either the "ShortDescription" or "Quantity" fields get updated.'
IOW, if both of these columns are set to their original values, the you are
updating the UpdatedDate and UpdatedBy columns, which you don't want to do.
You need to join also on the deleted virtual table and compare the values in
inserted vs. deleted.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>|||Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
>> Hello group,
>> I have a question regarding an update trigger.
>> For example, I have the following fields in a table..
>> ItemCode
>> ShortDescription
>> Quantity
>> UpdatedDate
>> UpdatedBy
>> I want to create a trigger that updates the "UpdatedDate" and
>> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
>> fields get updated.
>> I have figured out how to use the "bitwise" operators so as to target
>> only columns 2 and 3 for chages, however, telling the trigger to only
>> update the records which has been changed is proving to be a
>> challenge.
>> Thanks in advance
>> Leenux_tux
>|||That emphasizes the fact that a PK should never change. There are
exceptions, though.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uh5I%23VIqHHA.3892@.TK2MSFTNGP05.phx.gbl...
Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
>> Hello group,
>> I have a question regarding an update trigger.
>> For example, I have the following fields in a table..
>> ItemCode
>> ShortDescription
>> Quantity
>> UpdatedDate
>> UpdatedBy
>> I want to create a trigger that updates the "UpdatedDate" and
>> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
>> fields get updated.
>> I have figured out how to use the "bitwise" operators so as to target
>> only columns 2 and 3 for chages, however, telling the trigger to only
>> update the records which has been changed is proving to be a
>> challenge.
>> Thanks in advance
>> Leenux_tux
>

Thursday, March 22, 2012

Creating a Subtotal of select Groups

Hi,

I am working on a new reporting system using reporting services, but I cannot figure out how to create a footer row which will only subtotal select group totals. If anyone has a method to do this please help!

Nathan

If you have a matrix report right click on the group and select the option subtotal.
If you have a tabular report right click on the left side of the table and select table footer. Then in each field you want to summarize put = SUM(Fields!FieldName.Value)
That's all|||

I've been able to do that for individual groups, but what I want to do is make footer subtotal of a select set of groups. So say I have data grouped by Credit card type. I have a group for MC, and another for Visa, and another for American express.

I want a footer total of just the MC and Visa groups, excluding the total for American express.

|||You could use an expression similar to this in the footer (it will add 0 instead of the actual amount if the card was Amex):
=Sum(iif(Fields!CardType.Value = "Amex", 0, Fields!TransactionAmount.Value))

--Robert|||Alright!

Thank you Robert. you made my day Smile |||

When running this selective sum, i get a scope error. I have tried giving it a group name and a dataset. What am i doing wrong? here is my code:

=Sum(iff(Fields!CardType.Value = "Visa/Mc" OR Fields!cardType.Value = "Diner" OR Fields!cardType.Value = "JCB", Fields!amount.Value, 0))
This is my error:

"The value expression for the textbox ‘textbox9’ refers to the field ‘CardType’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."

Whats wrong?

|||I have the same problem. I don't want to sum a select number of groups, but all groups within the report. The principle is the same as the above, and I get the same scope error.
In my case I have a bunch of items grouped by customer. Each customer has a subtotal, and I want to have a grand total of all the customers.
Any ideas/workarounds?|||Note: Field names are case-sensitive. In your expression it seems like you have upper-case and lower-case "CardType" fields.
Also, are the cardType field and the amount field in the same dataset?

-- Robert|||If you want to get the grand total, you just need to specify either the data region name (i.e. table, list, or matrix report item name) or the data set name.
E.g.
=Sum(Fields!Amount.Value, "DataSet1")

-- Robert|||Thanks, Robert! I knew it had to be something simple. Smile |||Hi, somehow related with the topic:
Is posible to have something like: the sum of the ValueField from all the rows of DataSet2 that have CompareFiled equal with the current value of ComparedWithField from DataSet1?
In other words: in the expression of the SUM function can be used more then one scope?
=Sum(iff( DS2!Fields!CardType.Value = Fields!CT.Value, DS2!Fields!amount.Value, 0))

Creating a string - Cursor or Case

Hi all,
I need to perform a validation test on fields in stored data for each row and
updating the Comment field with the reasons why test failed where appropriate.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update comment
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment =
CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via droptable.com"
<u24035@.uwe> wrote:

>Hi all,
>I need to perform a validation test on fields in stored data for each row and
>updating the Comment field with the reasons why test failed where appropriate.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comment
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
>[StartDate] [datetime] NULL ,
>[EndDate] [datetime] NULL ,
>[Quantity] [int] NULL ,
>[Bar_Code] [varchar] (10) NULL ,
>[Comment] [varchar] (255)NULL
>) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =
>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))
|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via droptable.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment =
> CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>
sql

Creating a string - Cursor or Case

Hi all,
I need to perform a validation test on fields in stored data for each row an
d
updating the Comment field with the reasons why test failed where appropriat
e.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update commen
t
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment =
CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via droptable.com"
<u24035@.uwe> wrote:

>Hi all,
>I need to perform a validation test on fields in stored data for each row a
nd
>updating the Comment field with the reasons why test failed where appropria
te.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comme
nt
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =
>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
<
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via droptable.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment =
> CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>

Creating a string - Cursor or Case

Hi all,
I need to perform a validation test on fields in stored data for each row and
updating the Comment field with the reasons why test failed where appropriate.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update comment
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment = CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
--
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via SQLMonster.com"
<u24035@.uwe> wrote:
>Hi all,
>I need to perform a validation test on fields in stored data for each row and
>updating the Comment field with the reasons why test failed where appropriate.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comment
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
>) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via SQLMonster.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment => CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>

Wednesday, March 21, 2012

Creating a sequential number in a column.

Hi,
I'd like to generate a column in a query which shows the row number
chronologically (Num) as:
Cust_ID Sales Date Num
526 12.350 12/5/2007 1
632 11.520 5/5/2007 2
123 10.899 6/6/2007 3
.. ... ... 4
Howto achieve it?
TIA
Ana
That doesn't look chronological to me. Why is 12/5/2007 1 and 5/5/2007 2?
Can you apply the same numbers in some logical way *without* visually
inspecting the arbitrary order of rows that come back from SELECT * FROM
table ?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>
|||Hi,
Sorry, I didn't explain myself well. The result of a query is a ranking
based on customers' sales.
The fields from a table are:
Cust_ID
Sales
Date (European format)
The query generates the following results:
Cust_ID Sales Date
526 12.350 12/5/2007
632 11.520 5/5/2007
123 10.899 6/6/2007
Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
Customer ID 632 generated 11.520 euros so should be 2.
And Cust. ID 123 should be 3. and etc.
So I was wondering if a column can be generated in a query which would label
the ranking from 1 to wherever ends the query. Meaning, if I have 10 rows so
will be till 10.
Hope I have been a bit clearer.
Thank you much for your prompt response.
Ana
"Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>
|||Customers sell things? Okay, so what is the key on this table? Is it
Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
rows:
526 12.350 12/5/2007
526 12.250 6/6/2007
525 12.300 12/5/2007
525 12.400 12/4/2007
What should the result be?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Sorry, I didn't explain myself well. The result of a query is a ranking
> based on customers' sales.
> The fields from a table are:
> Cust_ID
> Sales
> Date (European format)
> The query generates the following results:
> Cust_ID Sales Date
> 526 12.350 12/5/2007
> 632 11.520 5/5/2007
> 123 10.899 6/6/2007
>
> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
> Customer ID 632 generated 11.520 euros so should be 2.
> And Cust. ID 123 should be 3. and etc.
> So I was wondering if a column can be generated in a query which would
> label the ranking from 1 to wherever ends the query. Meaning, if I have 10
> rows so will be till 10.
> Hope I have been a bit clearer.
> Thank you much for your prompt response.
> Ana
>
> "Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>
|||Ha, ha, ha. Well it's rather odd but yes, customers do sell because they
convert themselves into agents under some conditions. But it's a side
matter.
In my query I use the SUM(CASE .WHEN.) to sum their sells within a specific
period (let's forget the dates) which generates a single line per customer
therefore the results could be as:
526 12.350
525 12.400
Where Cust_ID is PK, sales is numeric and date is dates. Meaning that cust
526 has generated 12.350 euros vs. cust 525 who generated 12.400 euros.
Now in my ranking I want to label cust 525 as a 1 and cust 526 as a 2 and so
on.
Thank you, and sorry for the confusion.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribi en el
mensaje de noticias news:u$iERPGqHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Customers sell things? Okay, so what is the key on this table? Is it
> Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
> rows:
> 526 12.350 12/5/2007
> 526 12.250 6/6/2007
> 525 12.300 12/5/2007
> 525 12.400 12/4/2007
> What should the result be?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Ana" <ananospam@.yahoo.es> wrote in message
> news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
>

Creating a sequential number in a column.

Hi,
I'd like to generate a column in a query which shows the row number
chronologically (Num) as:
Cust_ID Sales Date Num
526 12.350 12/5/2007 1
632 11.520 5/5/2007 2
123 10.899 6/6/2007 3
. ... ... 4
Howto achieve it?
TIA
Anahi
set the num column as IDENTITY. see bol for more on IDENTITY
Regards
--
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||That doesn't look chronological to me. Why is 12/5/2007 1 and 5/5/2007 2?
Can you apply the same numbers in some logical way *without* visually
inspecting the arbitrary order of rows that come back from SELECT * FROM
table ?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Hi,
Sorry, I didn't explain myself well. The result of a query is a ranking
based on customers' sales.
The fields from a table are:
Cust_ID
Sales
Date (European format)
The query generates the following results:
Cust_ID Sales Date
526 12.350 12/5/2007
632 11.520 5/5/2007
123 10.899 6/6/2007
Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
Customer ID 632 generated 11.520 euros so should be 2.
And Cust. ID 123 should be 3. and etc.
So I was wondering if a column can be generated in a query which would label
the ranking from 1 to wherever ends the query. Meaning, if I have 10 rows so
will be till 10.
Hope I have been a bit clearer.
Thank you much for your prompt response.
Ana
"Ana" <ananospam@.yahoo.es> escribió en el mensaje de noticias
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Customers sell things? Okay, so what is the key on this table? Is it
Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
rows:
526 12.350 12/5/2007
526 12.250 6/6/2007
525 12.300 12/5/2007
525 12.400 12/4/2007
What should the result be?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Sorry, I didn't explain myself well. The result of a query is a ranking
> based on customers' sales.
> The fields from a table are:
> Cust_ID
> Sales
> Date (European format)
> The query generates the following results:
> Cust_ID Sales Date
> 526 12.350 12/5/2007
> 632 11.520 5/5/2007
> 123 10.899 6/6/2007
>
> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
> Customer ID 632 generated 11.520 euros so should be 2.
> And Cust. ID 123 should be 3. and etc.
> So I was wondering if a column can be generated in a query which would
> label the ranking from 1 to wherever ends the query. Meaning, if I have 10
> rows so will be till 10.
> Hope I have been a bit clearer.
> Thank you much for your prompt response.
> Ana
>
> "Ana" <ananospam@.yahoo.es> escribió en el mensaje de noticias
> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>> Hi,
>> I'd like to generate a column in a query which shows the row number
>> chronologically (Num) as:
>>
>> Cust_ID Sales Date Num
>> 526 12.350 12/5/2007 1
>> 632 11.520 5/5/2007 2
>> 123 10.899 6/6/2007 3
>> . ... ... 4
>>
>> Howto achieve it?
>> TIA
>> Ana
>|||Ha, ha, ha. Well it's rather odd but yes, customers do sell because they
convert themselves into agents under some conditions. But it's a side
matter.
In my query I use the SUM(CASE .WHEN.) to sum their sells within a specific
period (let's forget the dates) which generates a single line per customer
therefore the results could be as:
526 12.350
525 12.400
Where Cust_ID is PK, sales is numeric and date is dates. Meaning that cust
526 has generated 12.350 euros vs. cust 525 who generated 12.400 euros.
Now in my ranking I want to label cust 525 as a 1 and cust 526 as a 2 and so
on.
Thank you, and sorry for the confusion.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribió en el
mensaje de noticias news:u$iERPGqHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Customers sell things? Okay, so what is the key on this table? Is it
> Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
> rows:
> 526 12.350 12/5/2007
> 526 12.250 6/6/2007
> 525 12.300 12/5/2007
> 525 12.400 12/4/2007
> What should the result be?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Ana" <ananospam@.yahoo.es> wrote in message
> news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Sorry, I didn't explain myself well. The result of a query is a ranking
>> based on customers' sales.
>> The fields from a table are:
>> Cust_ID
>> Sales
>> Date (European format)
>> The query generates the following results:
>> Cust_ID Sales Date
>> 526 12.350 12/5/2007
>> 632 11.520 5/5/2007
>> 123 10.899 6/6/2007
>>
>> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
>> Customer ID 632 generated 11.520 euros so should be 2.
>> And Cust. ID 123 should be 3. and etc.
>> So I was wondering if a column can be generated in a query which would
>> label the ranking from 1 to wherever ends the query. Meaning, if I have
>> 10 rows so will be till 10.
>> Hope I have been a bit clearer.
>> Thank you much for your prompt response.
>> Ana
>>
>> "Ana" <ananospam@.yahoo.es> escribió en el mensaje de noticias
>> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>> Hi,
>> I'd like to generate a column in a query which shows the row number
>> chronologically (Num) as:
>>
>> Cust_ID Sales Date Num
>> 526 12.350 12/5/2007 1
>> 632 11.520 5/5/2007 2
>> 123 10.899 6/6/2007 3
>> . ... ... 4
>>
>> Howto achieve it?
>> TIA
>> Ana
>>
>sql

Creating a sequential number in a column.

Hi,
I'd like to generate a column in a query which shows the row number
chronologically (Num) as:
Cust_ID Sales Date Num
526 12.350 12/5/2007 1
632 11.520 5/5/2007 2
123 10.899 6/6/2007 3
. ... ... 4
Howto achieve it?
TIA
Anahi
set the num column as IDENTITY. see bol for more on IDENTITY
Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||That doesn't look chronological to me. Why is 12/5/2007 1 and 5/5/2007 2?
Can you apply the same numbers in some logical way *without* visually
inspecting the arbitrary order of rows that come back from SELECT * FROM
table ?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Hi,
Sorry, I didn't explain myself well. The result of a query is a ranking
based on customers' sales.
The fields from a table are:
Cust_ID
Sales
Date (European format)
The query generates the following results:
Cust_ID Sales Date
526 12.350 12/5/2007
632 11.520 5/5/2007
123 10.899 6/6/2007
Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
Customer ID 632 generated 11.520 euros so should be 2.
And Cust. ID 123 should be 3. and etc.
So I was wondering if a column can be generated in a query which would label
the ranking from 1 to wherever ends the query. Meaning, if I have 10 rows so
will be till 10.
Hope I have been a bit clearer.
Thank you much for your prompt response.
Ana
"Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Customers sell things? Okay, so what is the key on this table? Is it
Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
rows:
526 12.350 12/5/2007
526 12.250 6/6/2007
525 12.300 12/5/2007
525 12.400 12/4/2007
What should the result be?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Sorry, I didn't explain myself well. The result of a query is a ranking
> based on customers' sales.
> The fields from a table are:
> Cust_ID
> Sales
> Date (European format)
> The query generates the following results:
> Cust_ID Sales Date
> 526 12.350 12/5/2007
> 632 11.520 5/5/2007
> 123 10.899 6/6/2007
>
> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
> Customer ID 632 generated 11.520 euros so should be 2.
> And Cust. ID 123 should be 3. and etc.
> So I was wondering if a column can be generated in a query which would
> label the ranking from 1 to wherever ends the query. Meaning, if I have 10
> rows so will be till 10.
> Hope I have been a bit clearer.
> Thank you much for your prompt response.
> Ana
>
> "Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>|||Ha, ha, ha. Well it's rather odd but yes, customers do sell because they
convert themselves into agents under some conditions. But it's a side
matter.
In my query I use the SUM(CASE .WHEN.) to sum their sells within a specific
period (let's forget the dates) which generates a single line per customer
therefore the results could be as:
526 12.350
525 12.400
Where Cust_ID is PK, sales is numeric and date is dates. Meaning that cust
526 has generated 12.350 euros vs. cust 525 who generated 12.400 euros.
Now in my ranking I want to label cust 525 as a 1 and cust 526 as a 2 and so
on.
Thank you, and sorry for the confusion.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribi en e
l
mensaje de noticias news:u$iERPGqHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Customers sell things? Okay, so what is the key on this table? Is it
> Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
> rows:
> 526 12.350 12/5/2007
> 526 12.250 6/6/2007
> 525 12.300 12/5/2007
> 525 12.400 12/4/2007
> What should the result be?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Ana" <ananospam@.yahoo.es> wrote in message
> news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
>

Creating a row guid in SQL Express

I am an asp developer who is finally serious about learning .net. I have downloaded SQL Express and am working through the ADO Step by Step book. I can easily create a table and set all the columns. What I cannot do is create a row guid. The option for this is grayed out in the column properties window and when I try to set a field in the "Row Guid Column" in the right side properties window I get an error mesage stating the field must match an entry in the list.

I have set the id field to primary key and no nulls and to identity and I still cannot set as row guid. I even downloaded Northwind and these tables also do not have a rowguid and I cannot set.

I noticed this as I tried to create a data adapter. I was able to create the Select and Insert statements but the update and delete failed I think due to the rowguid issue. Any help is appreciated Thanks Brad

Hi Brad...in order to set the rowguid column attribute, the column must be of the datatype 'uniqueidentifier'...once you have a column of that datatype, you can then assign the column the rowguid attribute. Note that you can only have a single rowguid column in a single table, though you can have multiple columns of the datatype uniqueidentifier.

HTH

|||Thanks Chad I have been working with MySql the last 3 years and was not used to the uniqueidentifier column. I now know and have fixed my db.

Sunday, March 11, 2012

Creating a Matrix report from an OLAP cube

Hello there
I am trying to create a Matrix report from an OLAP cube
but not having any luck. the end result I want is the
UserName along the Row section, Months of the year along
the column section (the date range will be specified in
the MDX statement), and the Data will be a count of hits
(which is my Measure) to my website on a per month basis
and Grouped By the UserName. My MDX statement looks like
this:
SELECT
{[Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].
[June]} on Columns ,
{[Business].[User].[Business Entity Id].[5611].Children }
on Rows
FROM UsageStats_Phase1
This query executed in the MDX Sample Application returns
exactly what i want. but I am having trouble getting the
same information in a Matrix or Table report. Would anyone
have any idea how to go about doing this? I'm quite new to
MDX, OLAP, and Reporting Services (not a very strong
position to be in, i know!) - so any help would be very
much appreciated!
Thanks in anticipation
MariaMaybe you need to explicit the measures (something like:
SELECT {[Measures].[<MISURE_NAME>], [Measures].[<MISURE_NAme>],...} on Columns,
{[Business].[User].[Business Entity Id].[5611].Children }
on Rows,
{[Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].
[June]} ON PAGES
FROM UsageStats_Phase1
maybe you need also to use the DESCENDANTS function for rows and pages
)
than, using a matrix control, drag the measures in the data fields.
hth
Antonio
"Jano" wrote:
> Hello there
> I am trying to create a Matrix report from an OLAP cube
> but not having any luck. the end result I want is the
> UserName along the Row section, Months of the year along
> the column section (the date range will be specified in
> the MDX statement), and the Data will be a count of hits
> (which is my Measure) to my website on a per month basis
> and Grouped By the UserName. My MDX statement looks like
> this:
> SELECT
> {[Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].
> [June]} on Columns ,
> {[Business].[User].[Business Entity Id].[5611].Children }
> on Rows
> FROM UsageStats_Phase1
>
> This query executed in the MDX Sample Application returns
> exactly what i want. but I am having trouble getting the
> same information in a Matrix or Table report. Would anyone
> have any idea how to go about doing this? I'm quite new to
> MDX, OLAP, and Reporting Services (not a very strong
> position to be in, i know!) - so any help would be very
> much appreciated!
> Thanks in anticipation
> Maria
>|||Thanks for the reply. I managed to get around it by doing
the following:
SELECT
{ Measures.members } on Columns ,
{ Crossjoin( [Time].[Calendar].[2004].[May]:[Time].
[Calendar].[2004].[July], [Business].[User].[Business
Entity Id].[5611].Children ) } on Rows
FROM UsageStats_Phase1
All the best
Jano
>--Original Message--
>Maybe you need to explicit the measures (something like:
>
>SELECT {[Measures].[<MISURE_NAME>], [Measures].
[<MISURE_NAme>],...} on Columns,
>{[Business].[User].[Business Entity Id].
[5611].Children }
>on Rows,
>{[Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].
>[June]} ON PAGES
>FROM UsageStats_Phase1
>maybe you need also to use the DESCENDANTS function for
rows and pages
> )
>
>than, using a matrix control, drag the measures in the
data fields.
>
>hth
>Antonio
>
>"Jano" wrote:
>> Hello there
>> I am trying to create a Matrix report from an OLAP cube
>> but not having any luck. the end result I want is the
>> UserName along the Row section, Months of the year
along
>> the column section (the date range will be specified in
>> the MDX statement), and the Data will be a count of
hits
>> (which is my Measure) to my website on a per month
basis
>> and Grouped By the UserName. My MDX statement looks
like
>> this:
>> SELECT
>> {[Time].[Calendar].[2004].[May]:[Time].[Calendar].
[2004].
>> [June]} on Columns ,
>> {[Business].[User].[Business Entity Id].
[5611].Children }
>> on Rows
>> FROM UsageStats_Phase1
>>
>> This query executed in the MDX Sample Application
returns
>> exactly what i want. but I am having trouble getting
the
>> same information in a Matrix or Table report. Would
anyone
>> have any idea how to go about doing this? I'm quite new
to
>> MDX, OLAP, and Reporting Services (not a very strong
>> position to be in, i know!) - so any help would be very
>> much appreciated!
>> Thanks in anticipation
>> Maria
>.
>

Friday, February 24, 2012

Creating a comma delimited list

Hi,
I have a complex query where each row in the final dataset is a product.
However each product has a number of authors associated with it. What I
would like to do is have a query/subroutine join the authors to the product,
as a string:

ProductID Title Authors
1 The Sacred and the Profane John Rieggle, George Alexi
2 Dancingin the Dark DanBrown, Peter Kay, Paul
Dwebinski
Products Table
==============
ProductID
Title
Authors Table
=============
AuthorID
Name
Product Authors Table
=====================
AuthorID
ProductID
Is this at all possible?
Thanks
jr.
It is possible with the use of a UDF. However, I highly suggestthat you do this work on the front end. Preparing the data foroutput is not something the database engine should be doing, and theUDF method is fairly intensive as it results in additional queries.
Here's a link showing the UDF method:Joining Strings into Delimiter Separated Lists.

.
|||Terri,
Thanks for your reply. I understand that this is a UI rendering issue, and I have been toying with how best to proceed. Currently my paging routine returns one resultset, were each row represents a product. To return the authors, I would need to return a second resultset, and join them at the middle tier:
Pseudo steps:
1. Do complex paging query
2. Based on items in paging query, join them to the authors table and return that resultset too.
This would solve the problem, but sends more data across the wire which I was thinknig could be avoided, even though I know that the DB shouldnt be doing ui rendering steps. Other than "best practice" I see no other reason to put this code in the ui, since it is just a dumb string without much meaning when rendered in a listing. As you mention though, this maybe putting too much stress on the DB when using a UDF...I am relatively new to this so forgive my naivete...
Thanks Terri,
jr.|||

This might not be very efficient if you have a large sum of data, but here is how to do it.

create table #product (
productID int,
productTitle varchar(50)
)
go

create table #author (
authorID int,
name varchar(100)
)
go

create table #productAuthor (
productID int,
authorID int
)
go

insert into #product values (1,'product1')
insert into #product values (2,'product2')

insert into #author values (1,'author1')
insert into #author values (2,'author2')
insert into #author values (3,'author3')
insert into #author values (4,'author4')

insert into #productAuthor values (1,1)
insert into #productAuthor values (1,2)
insert into #productAuthor values (2,3)
insert into #productAuthor values (2,4)

create table #nameList (
productid int,
names varchar(1000)
)
go

select distinct p.productid into #tmp
from #product p
inner join #productAuthor pa on p.productid = pa.productid

declare @.productid int,
@.names varchar(1000)

while exists (select null from #tmp)
begin
set @.names = ''
select top 1 @.productid = productid from #tmp

select @.names = CASE WHEN @.names = '' THEN name ELSE @.names + ',' + name END
from #product p
inner join #productAuthor pa on p.productid = pa.productid
inner join #author a on pa.authorid = a.authorid
where p.productid = @.productid

insert into #nameList values (@.productid, @.names)

delete from #tmp where productid = @.productid
end

select p.productID, productTitle, nl.names
from #product p
inner join #nameList nl ON p.productid = nl.productID

Let me know if you have any questions.

Nick

creating a child row with two parents? (in one column)

Hi!

How can I make a relation that would let me relate an employee to two departments?

There is a table with emloyees and each one works in a department and there is a foreign key relation in the child (employees) table to the parent (departments) table. So can an employee work in two departments? (it's not impossible to imagine, is it?) And how would I retrieve all the employees of a department?

Or in other words, if I were working on some kind of software for my local supermarket and they need to keep track of all bills they ever gave out. I would make this products table and this bills table. Then I would create a foreign key column in the products table and fill it with bill_ids from the bills table and make retrieving bill items a simple getchildrow[](bill_row). Note that this is only an analogy, I need it done this way. How? (using C# express 2005, and SQL express and DataSets)

Use 3 tables, Employee, Department, and EmployeeDepartment. The table EmplyeeDepartment contains 2 fields only, EmployeeID and Department ID (actually, you might want to give it an IDENTITY column too, but logically it contains just the 2).

|||For more information look at the definition of n-m relationships.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||OK. Thank you. Wouldn't it be a lot easier if the cell that is designed to contain the foreign key could contain an array by default? We would just have a simple query as in one to many and SQL would search through these elements. I still think it's unbelievable that such a thing has not been implemented.

So as a resume,.. there is this dB that keeps track of movies: It has two tables: movies and actors:

So, to relate an actor to the cast column in movies I have to create an intermediate table
MovieActor -> title* | firstName* | lastName* for each actor in every movie?

There has to be an easier way to do this, I think I've set it up right and the solution seems complicated..
Btw, this movies analogy is almost the same as the problem I'm working on..|||

Hi,

an array would be not as good to maintain and query like the n-m relationship. You table design should be something like the following:

Movie table
========
MovieId
MovieName (etc. columns)

Actor Table
=========
ActorId
ActorName (ect. Columns)

MovieToActor Table
================
MovieId
ActorId

Assuming that you want to find out how much movies the actor With a specialId was involved in you would crawl through EVERY Movie in the movie table and chop the array in pieces to find out the right movies. In the above mentioned case you only would have to query the MovieToActor Table to find out the same thing, this is much more easier (!).

If you have any further questions, don′t hesitate to write back.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Ok. I understand, it makes sence to do it this way. Only, the actorToMovie table will tend to get very very very long

If we assume that only a limited number of actors can star in a movie...I would make my movies table like this: (like a flag cell only wiht content)

and have a "select * from movies where ((actorId01 = someId) or (actor02 = someId) ....)"

Would that waste more processing power than it would save space? (Empty cells don't take up space, do they?) It would probably NOT save processing power as the dB engine would have to search through all records (in the actorToMovie table) anyhow, or would it as it would have less records (rows) to look in? (in above mentioned Movie table)

Thank you..
|||Hmmh, no this is really preferable. Do you know how many actors will act in a movie ? WHat about if someone wants to input all of the actors from the battlefields from Star Wars Clone Wars in the movies table. You will be pretty fast run out of columns, not to mention that the queries will get quite big and slow, so this is really really not practicable.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||OK. I see that point, but I've said "assume". As this is not a movie application, I can know for sure that no "movie" will have more that 18 (or so) "actors". Would that make my aproach practical?

In general terms, is there a way to figure out the limit value for the quanity of possible "actors" that would make this approach practical. Obiously, if only two actors could appear in a movie, a seperate table might not be the best solution.|||From a design point it really is. Based on my experiences due to extensibiliy, maintainability and ease to use this is the best solution. Right now your are having a limited but varying amount of actors in the table. What about the assumption that there could be more one day ? What about if you extend your table to 18 or more columns and you also use the movie table for storing animal movies (with no actors until you give them names, just kidding :-) ). Would that be 18 columns with no values ? As i said before that should be the most practical way to do this. But perhaps there might be another one giving his two cents about that from the groups.
I will ping another expert for this to convince you :-)
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Use the Force Luke :)

Thank you.
|||

>if only two actors could appear in a movie, a seperate table might not be the best solution.

Yes it would. The waknesses of poor design may not cause a problem in some situations, but good design generally works well in all situations.

|||

Reading through this thread I see a couple of things:

" if the cell that is designed to contain the foreign key could contain an array by default? "

Not at all. An array would require different handling than a seperate table. What is the difficulty in having another table? The fact is, the SQL language was meant to work with sets of scalar values. Period. Not arrays, not vectors (which the 2-18 column approach you also mention is.) Joins are not nearly as colstly as would be interrogating an array in every row to determine the number of values that are related.

and have a "select * from movies where ((actorId01 = someId) or (actor02 = someId) ....)"
"

But it is so much more natural to say:

select *
from movies
join movieActors
on movies.movieId = movieActors.movieId
where movieActors.actorId in (someId, someOtherId,...)

The major deficiency in your query is that looking for multiple values like this (someId, someOtherId,... ) would take tons of values in the where clause. Sure, for two max values this is a problem, but what about 18? Or 1800. The normalized design is not limited in solution to a given number.

And when you want to join to the actor table,

select *
from movies
join movieActors
on movies.movieId = movieActors.movieId
join actors
on movieActors.actorId = movieActors.actorId

is very optimizable.

select *
from movies
join actors
on actors.actorId = movies.actorId1
or actors.actorId = movies.actorId2
or actors.actorId = movies.actorId3

will likely be horrible to optimize.

"(Empty cells don't take up space, do they?) It would probably NOT save processing power "

The space saved would likely be with your solution, without the intermediate table. But, this is more a matter of how SQL is suited to be used rather than a matter of space. The space difference will be very slight, if anything. The processing however would be great.

Bottom line is, building this table:

and using SQL on it is like buying a box of screws and a hammer. If you want to do all of the processing the hard way manually, then you can hammer the screws in. But designing tables that conform to what the creators of SQL had in mind is like purchasing a power screwdriver to use screws with. It just plain fits.

creating a child row with two parents? (in one column)

Hi!

How can I make a relation that would let me relate an employee to two departments?

|||For more information look at the definition of n-m relationships.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||OK. Thank you. Wouldn't it be a lot easier if the cell that is designed to contain the foreign key could contain an array by default? We would just have a simple query as in one to many and SQL would search through these elements. I still think it's unbelievable that such a thing has not been implemented.

So as a resume,.. there is this dB that keeps track of movies: It has two tables: movies and actors:

So, to relate an actor to the cast column in movies I have to create an intermediate table
MovieActor -> title* | firstName* | lastName* for each actor in every movie?

There has to be an easier way to do this, I think I've set it up right and the solution seems complicated..
Btw, this movies analogy is almost the same as the problem I'm working on..|||

Hi,

an array would be not as good to maintain and query like the n-m relationship. You table design should be something like the following:

Movie table
========
MovieId
MovieName (etc. columns)

Actor Table
=========
ActorId
ActorName (ect. Columns)

MovieToActor Table
================
MovieId
ActorId

If you have any further questions, don′t hesitate to write back.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Ok. I understand, it makes sence to do it this way. Only, the actorToMovie table will tend to get very very very long

If we assume that only a limited number of actors can star in a movie...I would make my movies table like this: (like a flag cell only wiht content)

and have a "select * from movies where ((actorId01 = someId) or (actor02 = someId) ....)"

Thank you..|||Hmmh, no this is really preferable. Do you know how many actors will act in a movie ? WHat about if someone wants to input all of the actors from the battlefields from Star Wars Clone Wars in the movies table. You will be pretty fast run out of columns, not to mention that the queries will get quite big and slow, so this is really really not practicable.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||OK. I see that point, but I've said "assume". As this is not a movie application, I can know for sure that no "movie" will have more that 18 (or so) "actors". Would that make my aproach practical?

Thank you.|||

>if only two actors could appear in a movie, a seperate table might not be the best solution.

Yes it would. The waknesses of poor design may not cause a problem in some situations, but good design generally works well in all situations.

|||

Reading through this thread I see a couple of things:

" if the cell that is designed to contain the foreign key could contain an array by default? "

and have a "select * from movies where ((actorId01 = someId) or (actor02 = someId) ....)"
"

But it is so much more natural to say:

select *
from movies
join movieActors
on movies.movieId = movieActors.movieId
where movieActors.actorId in (someId, someOtherId,...)

And when you want to join to the actor table,

select *
from movies
join movieActors
on movies.movieId = movieActors.movieId
join actors
on movieActors.actorId = movieActors.actorId

is very optimizable.

select *
from movies
join actors
on actors.actorId = movies.actorId1
or actors.actorId = movies.actorId2
or actors.actorId = movies.actorId3

will likely be horrible to optimize.

"(Empty cells don't take up space, do they?) It would probably NOT save processing power "

Bottom line is, building this table:

Creating a chart from only one row of data

I am trying to generate a chart from only one row of data that contains the
twelve fields that represent the data-points for the chart ( the chart will
have 4 sets of two columns and a 4 point line graph for a total of twelve
points). I know which field is which datapoint but the chart object will not
let me define individual data-points. It expects to create a series from
multiple rows of data. Grouping and Series fields also rely on multiple rows
of data. I can put the fields into a table on the report, but cannot
generate a chart off of the table. I am not a programmer, meaning that I
only know how to manipulate properties - code samples will not help. I would
appreciate any help that could be offered to a layman report designerIt may be possible to write a query such that the columns in the table
appear as rows. For example, if A, B and C are columns of table FOO then
SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO will
return 3 rows even if the table has only 1 row.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
>I am trying to generate a chart from only one row of data that contains the
> twelve fields that represent the data-points for the chart ( the chart
> will
> have 4 sets of two columns and a 4 point line graph for a total of twelve
> points). I know which field is which datapoint but the chart object will
> not
> let me define individual data-points. It expects to create a series from
> multiple rows of data. Grouping and Series fields also rely on multiple
> rows
> of data. I can put the fields into a table on the report, but cannot
> generate a chart off of the table. I am not a programmer, meaning that I
> only know how to manipulate properties - code samples will not help. I
> would
> appreciate any help that could be offered to a layman report designer|||A minor clarification: You'd have to convert all columns in the SELECTs in
the UNION to same datatype using Convert() function.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rajeev Karunakaran [MSFT]" <rajeevkarunakaran@.online.microsoft.com> wrote
in message news:eDCdqlthEHA.596@.TK2MSFTNGP11.phx.gbl...
> It may be possible to write a query such that the columns in the table
> appear as rows. For example, if A, B and C are columns of table FOO then
> SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO will
> return 3 rows even if the table has only 1 row.
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
> news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
> >I am trying to generate a chart from only one row of data that contains
the
> > twelve fields that represent the data-points for the chart ( the chart
> > will
> > have 4 sets of two columns and a 4 point line graph for a total of
twelve
> > points). I know which field is which datapoint but the chart object
will
> > not
> > let me define individual data-points. It expects to create a series
from
> > multiple rows of data. Grouping and Series fields also rely on multiple
> > rows
> > of data. I can put the fields into a table on the report, but cannot
> > generate a chart off of the table. I am not a programmer, meaning that
I
> > only know how to manipulate properties - code samples will not help. I
> > would
> > appreciate any help that could be offered to a layman report designer
>|||I am having a similar problem. I have 3 columns (cntY1, cntY2, cntY3)
I would like each one to be a separate bar on the graph. The stored
Procedure will only ever return one row.
How can you do this using Reporting Services? What should I put in the
category column?
Any help will be appreciated,
Rajeev Karunakaran [MSFT] wrote:
> It may be possible to write a query such that the columns in the
table
> appear as rows. For example, if A, B and C are columns of table FOO
then
> SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO
will
> return 3 rows even if the table has only 1 row.
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
> news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
> >I am trying to generate a chart from only one row of data that
contains the
> > twelve fields that represent the data-points for the chart ( the
chart
> > will
> > have 4 sets of two columns and a 4 point line graph for a total of
twelve
> > points). I know which field is which datapoint but the chart
object will
> > not
> > let me define individual data-points. It expects to create a
series from
> > multiple rows of data. Grouping and Series fields also rely on
multiple
> > rows
> > of data. I can put the fields into a table on the report, but
cannot
> > generate a chart off of the table. I am not a programmer, meaning
that I
> > only know how to manipulate properties - code samples will not
help. I
> > would
> > appreciate any help that could be offered to a layman report
designer|||Changing the stored procedure is not an option.|||Just create 3 values - one for each column, no categories or series group.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<hovercraft2x@.gmail.com> wrote in message
news:1109017211.242947.155410@.c13g2000cwb.googlegroups.com...
>I am having a similar problem. I have 3 columns (cntY1, cntY2, cntY3)
> I would like each one to be a separate bar on the graph. The stored
> Procedure will only ever return one row.
> How can you do this using Reporting Services? What should I put in the
> category column?
> Any help will be appreciated,
> Rajeev Karunakaran [MSFT] wrote:
>> It may be possible to write a query such that the columns in the
> table
>> appear as rows. For example, if A, B and C are columns of table FOO
> then
>> SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO
> will
>> return 3 rows even if the table has only 1 row.
>> --
>> Rajeev Karunakaran [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
>> news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
>> >I am trying to generate a chart from only one row of data that
> contains the
>> > twelve fields that represent the data-points for the chart ( the
> chart
>> > will
>> > have 4 sets of two columns and a 4 point line graph for a total of
> twelve
>> > points). I know which field is which datapoint but the chart
> object will
>> > not
>> > let me define individual data-points. It expects to create a
> series from
>> > multiple rows of data. Grouping and Series fields also rely on
> multiple
>> > rows
>> > of data. I can put the fields into a table on the report, but
> cannot
>> > generate a chart off of the table. I am not a programmer, meaning
> that I
>> > only know how to manipulate properties - code samples will not
> help. I
>> > would
>> > appreciate any help that could be offered to a layman report
> designer
>

Sunday, March 25, 2012

Creating a trigger for the row being updated

Creating a trigger for the row being updated

Thursday, March 22, 2012

Creating a Subtotal of select Groups

Creating a string - Cursor or Case

Creating a string - Cursor or Case

Creating a string - Cursor or Case

Wednesday, March 21, 2012

Creating a sequential number in a column.

Creating a sequential number in a column.

Creating a sequential number in a column.

Creating a row guid in SQL Express

Sunday, March 11, 2012

Creating a Matrix report from an OLAP cube

Friday, February 24, 2012

Creating a comma delimited list

creating a child row with two parents? (in one column)

creating a child row with two parents? (in one column)

Creating a chart from only one row of data

Creating DB from scripts

Blog Archive

About Me