Problem with an update

Hi, I've a table "Table1" with:
ID - Cod - Type - Value - Period
1-COD1-AAA-0-Jan
2-COD2-BBB-0-Feb
3-COD3-AAA-0-Feb
4-COD4-CCC-0-Feb
Now I want to UPDATE this records using a Second Table "Table2"
Type-Qt-Value-Period
AAA-10-10-Jan
AAA-3-2-Feb
BBB-3-2-Feb
CCC-4-6-Feb
...
If, in table1, I've AAA I want to search in table2 the value of type AAA in
the table1.period
If, in table1, I've BBB I want to search in table2 the value of type BBB in
the table1.period
How Can I create this update'
After the UPDATE, I would like to obtain:
ID - Cod - Type - Value - Period
1-COD1-AAA-100-Jan (value = 10*10)
2-COD2-BBB-6-Feb (value = 3*2)
3-COD3-AAA-6-Feb (value = 3*2)
4-COD4-CCC-24-Feb (value = 6*4)
ThanksIdentity wrote:
> Hi, I've a table "Table1" with:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-0-Jan
> 2-COD2-BBB-0-Feb
> 3-COD3-AAA-0-Feb
> 4-COD4-CCC-0-Feb
> Now I want to UPDATE this records using a Second Table "Table2"
> Type-Qt-Value-Period
> AAA-10-10-Jan
> AAA-3-2-Feb
> BBB-3-2-Feb
> CCC-4-6-Feb
> ...
> If, in table1, I've AAA I want to search in table2 the value of type AAA in
> the table1.period
> If, in table1, I've BBB I want to search in table2 the value of type BBB in
> the table1.period
>
> How Can I create this update'
> After the UPDATE, I would like to obtain:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-100-Jan (value = 10*10)
> 2-COD2-BBB-6-Feb (value = 3*2)
> 3-COD3-AAA-6-Feb (value = 3*2)
> 4-COD4-CCC-24-Feb (value = 6*4)
> Thanks
It really helps if you post DDL and INSERT statements instead of
sketches of tables - it could even save you some typing. Looking at the
information given it appears that the UPDATE is to be based on joining
the tables on type and period, but you've left us to guess whether
those two columns are unique in either table. If the combination of
those two columns isn't unique in Table2 then do you want to SUM the
multiple rows? If they aren't unique in Table1 then do you want the
same value figure to appear on multiple rows? If they are unique in
BOTH tables then is there a particular reason for having two tables
instead of one?
Notwithstanding the above uncertainties, here's my untested guess at
what you want:
UPDATE Table1
SET value = (SELECT SUM(qt*value)
FROM Table2
WHERE type = Table1.type
AND period = Table1.period);
--
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
--|||Here's one method:
CREATE TABLE dbo.FirstTable
(
ID int NOT NULL
CONSTRAINT PK_FirstTable PRIMARY KEY,
Cod char(4) NOT NULL,
[Type] char(3) NOT NULL,
[Value] int NOT NULL,
Period char(3) NOT NULL
)
INSERT INTO dbo.FirstTable
SELECT 1,'COD1','AAA',0,'Jan'
UNION ALL SELECT 2,'COD2','BBB',0,'Feb'
UNION ALL SELECT 3,'COD3','AAA',0,'Feb'
UNION ALL SELECT 4,'COD4','CCC',0,'Feb'
CREATE TABLE dbo.SecondTable
(
[Type] char(3) NOT NULL,
[Value] int NOT NULL,
Qt int NOT NULL,
Period char(3) NOT NULL,
CONSTRAINT PK_SecondTable
PRIMARY KEY ([Type], Period)
)
INSERT INTO dbo.SecondTable
SELECT 'AAA',10,10,'Jan'
UNION ALL SELECT 'AAA',3,2,'Feb'
UNION ALL SELECT 'BBB',3,2,'Feb'
UNION ALL SELECT 'CCC',4,6,'Feb'
UPDATE dbo.FirstTable
SET
Value = (SELECT [Value]*Qt
FROM dbo.SecondTable
WHERE
SecondTable.[Type] = FirstTable.[Type] AND
SecondTable.[Period] = FirstTable.[Period]
)
SELECT
ID,
Cod,
[Type],
[Value],
Period
FROM dbo.FirstTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Identity" <id@.id.it> wrote in message
news:uQxOzbpoGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Hi, I've a table "Table1" with:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-0-Jan
> 2-COD2-BBB-0-Feb
> 3-COD3-AAA-0-Feb
> 4-COD4-CCC-0-Feb
> Now I want to UPDATE this records using a Second Table "Table2"
> Type-Qt-Value-Period
> AAA-10-10-Jan
> AAA-3-2-Feb
> BBB-3-2-Feb
> CCC-4-6-Feb
> ...
> If, in table1, I've AAA I want to search in table2 the value of type AAA
> in
> the table1.period
> If, in table1, I've BBB I want to search in table2 the value of type BBB
> in
> the table1.period
>
> How Can I create this update'
> After the UPDATE, I would like to obtain:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-100-Jan (value = 10*10)
> 2-COD2-BBB-6-Feb (value = 3*2)
> 3-COD3-AAA-6-Feb (value = 3*2)
> 4-COD4-CCC-24-Feb (value = 6*4)
> Thanks|||Thanks for help!
But If I've many fields I must to use
> value => (SELECT SUM(qt*value)
> FROM Table2
> WHERE type = Table1.type
> AND period = Table1.period);
for all fields
Thanks

Problem with 2 Dataset Sharing Parameters

I have a report that will pull out a list of bugs assigned to users for a
period of time (@.StartDate, @.EndDate). Optionally the report has option to
select a single user (@.UserName) or just all of them. There are 2 datasets.
First dataset: Bugs requires 3 parameters: @.StartDate, @.EndDate, @.UserName.
Second dataset: UserName. At first I have a query to list all users. Since
the list has hundreds of users while the result set from dataset Bugs mostly
has less than 10 users, I built another query to list only users having bugs
assigned from @.StartDate to @.EndDate. So it requires 2 parameters @.StartDate
and @.Enddate.
Now both datasets use @.StartDate and @.EndDate. The list to select for
@.UserName is produced after @.StartDate and @.EndDate are entered. I expected
the report to let me select the username after I enter @.StartDate and
@.EndDate. However in the preview tab right at the moment when I clicked on
the box for @.EndDate to enter a value the program hang up (VS 2003). I
uploaded it to a report server to see how it behaves then I got this error
after I click on the EndDate box: The value provided for the report
parameter 'EndDate' is not valid for its type.
(rsReportParameterTypeMismatch).
I don't know what's wrong. If I change the dataset UserName to list all
users (no parameter required) then the report runs fine. I use Visual
Studio .NET 2003 Service Pack 1, report server is SQL 2000 with latest SP on
the O/S, SQL, and SQL RS.
An thought?
Thanks.On May 2, 5:31 pm, "ME" <M...@.mail.com> wrote:
> I have a report that will pull out a list of bugs assigned to users for a
> period of time (@.StartDate, @.EndDate). Optionally the report has option to
> select a single user (@.UserName) or just all of them. There are 2 datasets.
> First dataset: Bugs requires 3 parameters: @.StartDate, @.EndDate, @.UserName.
> Second dataset: UserName. At first I have a query to list all users. Since
> the list has hundreds of users while the result set from dataset Bugs mostly
> has less than 10 users, I built another query to list only users having bugs
> assigned from @.StartDate to @.EndDate. So it requires 2 parameters @.StartDate
> and @.Enddate.
> Now both datasets use @.StartDate and @.EndDate. The list to select for
> @.UserName is produced after @.StartDate and @.EndDate are entered. I expected
> the report to let me select the username after I enter @.StartDate and
> @.EndDate. However in the preview tab right at the moment when I clicked on
> the box for @.EndDate to enter a value the program hang up (VS 2003). I
> uploaded it to a report server to see how it behaves then I got this error
> after I click on the EndDate box: The value provided for the report
> parameter 'EndDate' is not valid for its type.
> (rsReportParameterTypeMismatch).
> I don't know what's wrong. If I change the dataset UserName to list all
> users (no parameter required) then the report runs fine. I use Visual
> Studio .NET 2003 Service Pack 1, report server is SQL 2000 with latest SP on
> the O/S, SQL, and SQL RS.
> An thought?
> Thanks.
The issue sounds kind of strange. Maybe you should check to make sure
that you have the correct @.EndDate mapping as part of the UserName
dataset (via the Data tab -> Edit Selected Dataset button [...] ->
Parameters tab). Also, make sure that the stored procedure/query that
sources the UserName dataset is not expecting a different data type
for @.EndDate (for whatever reason). Sorry I could not be of greater
assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks for the reply. I use datatime as data type for @.StartDate, @.EndDate
in both datasets. Double checked everything but still have no clue why.
When I learned and practiced SQL 2005 I had no problem doing similar thing.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178160515.514988.169100@.e65g2000hsc.googlegroups.com...
> On May 2, 5:31 pm, "ME" <M...@.mail.com> wrote:
>> I have a report that will pull out a list of bugs assigned to users for a
>> period of time (@.StartDate, @.EndDate). Optionally the report has option
>> to
>> select a single user (@.UserName) or just all of them. There are 2
>> datasets.
>> First dataset: Bugs requires 3 parameters: @.StartDate, @.EndDate,
>> @.UserName.
>> Second dataset: UserName. At first I have a query to list all users.
>> Since
>> the list has hundreds of users while the result set from dataset Bugs
>> mostly
>> has less than 10 users, I built another query to list only users having
>> bugs
>> assigned from @.StartDate to @.EndDate. So it requires 2 parameters
>> @.StartDate
>> and @.Enddate.
>> Now both datasets use @.StartDate and @.EndDate. The list to select for
>> @.UserName is produced after @.StartDate and @.EndDate are entered. I
>> expected
>> the report to let me select the username after I enter @.StartDate and
>> @.EndDate. However in the preview tab right at the moment when I clicked
>> on
>> the box for @.EndDate to enter a value the program hang up (VS 2003). I
>> uploaded it to a report server to see how it behaves then I got this
>> error
>> after I click on the EndDate box: The value provided for the report
>> parameter 'EndDate' is not valid for its type.
>> (rsReportParameterTypeMismatch).
>> I don't know what's wrong. If I change the dataset UserName to list all
>> users (no parameter required) then the report runs fine. I use Visual
>> Studio .NET 2003 Service Pack 1, report server is SQL 2000 with latest SP
>> on
>> the O/S, SQL, and SQL RS.
>> An thought?
>> Thanks.
>
> The issue sounds kind of strange. Maybe you should check to make sure
> that you have the correct @.EndDate mapping as part of the UserName
> dataset (via the Data tab -> Edit Selected Dataset button [...] ->
> Parameters tab). Also, make sure that the stored procedure/query that
> sources the UserName dataset is not expecting a different data type
> for @.EndDate (for whatever reason). Sorry I could not be of greater
> assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Problem Upgrading from SQL Server 2000 to SQLServer 2005

My upgrade of SQL Server 2000 to SQL Server 2005 is being blocked as follows:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

FTCATALOGPATH

DEPRECATEDSP

FTCATALOGNAMERESTRICTION

FTNONPERSISTEDCOMPCOL

Many thanks

JMX

Can you supply the upgrade command that you are running? And which versions/editions are you going from and to?

Thanks,
Sam Lester (MSFT)

Problem Upgrading from SQL Server 2000 to SQLServer 2005

My upgrade of SQL Server 2000 to SQL Server 2005 is being blocked as follows:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

FTCATALOGPATH

DEPRECATEDSP

FTCATALOGNAMERESTRICTION

FTNONPERSISTEDCOMPCOL

Many thanks

JMX

Can you supply the upgrade command that you are running? And which versions/editions are you going from and to?

Thanks,
Sam Lester (MSFT)