Problem with Having and Max

Hello,
I have the following query:
Select
#tWidra.Code,
Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
From
#tWidra
Where
TestCode = 20000
Group By
Code,
TestNo
Having
TestNo = Max(TestNo)
Order By
Code
What I want to achieve is summing only the weight for Max(TestNo) for each
code. But since I have to group by TestNo to use it in the Having clause it
is giving me the sum for all TestNo for each Code.
How can I achieve summing only the weight for Max(TestNo) for each code?
Thanks.
MichelTry,
Select
#tWidra.Code,
Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
From
#tWidra
Where
TestCode = 20000
TestNo = (select max(a.TestNo) from #tWidra as a where a.Code =
#tWidra.Code)
Group By
Code,
TestNo
Order By
Code
AMB
"Michel Hardy" wrote:

> Hello,
> I have the following query:
> Select
> #tWidra.Code,
> Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
> Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
> From
> #tWidra
> Where
> TestCode = 20000
> Group By
> Code,
> TestNo
> Having
> TestNo = Max(TestNo)
> Order By
> Code
> What I want to achieve is summing only the weight for Max(TestNo) for each
> code. But since I have to group by TestNo to use it in the Having clause i
t
> is giving me the sum for all TestNo for each Code.
> How can I achieve summing only the weight for Max(TestNo) for each code?
> Thanks.
> Michel|||Correction,
Select
#tWidra.Code,
Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
From
#tWidra
Where
TestCode = 20000
and TestNo = (select max(a.TestNo) from #tWidra as a where a.Code =
#tWidra.Code)
Group By
Code,
TestNo
Order By
Code
AMB
"Alejandro Mesa" wrote:
> Try,
> Select
> #tWidra.Code,
> Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
> Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
> From
> #tWidra
> Where
> TestCode = 20000
> TestNo = (select max(a.TestNo) from #tWidra as a where a.Code =
> #tWidra.Code)
> Group By
> Code,
> TestNo
> Order By
> Code
>
> AMB
> "Michel Hardy" wrote:
>

Problem with decimal point

I have got txt file and I am trying to import this file into the database. For that action I use SQL Server Import and Export Wizard. I use Locale Czech and CodePage 1250. In the text file is the column in that format: 18152.65 - it is number with decimal point. When I use BCP utility for importing data, I use datatype decimal(10,2) and everything is OK. But when I try to use Import and Export Wizard, I choose for that column datatype numeric (DT_NUMERIC - precision 10, scale 2), the Import doesn't start and occurs the error:

- Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "PRED_CEL " returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)

Is it always a precision of 10, and a scale of 2? You may have some inconsistent data. There's two things you could do.

1) Increase the precision and scale.

or

2) Create a custom SSIS package, and redirect the error rows to see where the data inconsistency is. The Import/Export Wizard is great for straight forward tasks, but you'll find that with the smallest blib you'll need to create a custom package.

|||The import is very picky about number formats when importing from flat files. It will NOT import zero padded numbers into a decimal field. Even though it will implicetly convert it internally.

For example:
3.99 - OK
003.99 - FAILS
+003.99 - OK
-003.00 - OK

If you import "003.99" into a varchar(12) and then update a decimal(10,2), with the value of teh varchar, it will work fine.