|
Krish - KP,
This one should do it. I created the same table you had, ran this one and it gives the right results
<code>select name,max(date),
sum(case when type='A' then value else 0 end) as 'A',
sum(case when type='B' then value else 0 end) as 'B',
sum(case when type='C' then value else 0 end) as 'C'
from table_1 where date=(select max(date) from table_1)
group by name </code>
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
|
|
|
|
|
Now syntax is right.
what about logic. is this what required
Regards
KP
|
|
|
|
|
About logic is OK, I already use convert.
And about your code below:
SELECT a.name, MAX(a.dt) AS [date],
SUM(CASE WHEN type = 'A' THEN valu ELSE 0 END) AS A,
SUM(CASE WHEN type = 'B' THEN valu ELSE 0 END) AS B,
SUM(CASE WHEN type = 'C' THEN valu ELSE 0 END) AS C
FROM TblB a
INNER JOIN (SELECT name, MAX(dt) AS dt FROM tblB GROUP by name) b
ON a.name = b.name and a.dt = b.dt
GROUP BY a.name
this only show each name, with maximum value
not each name, each type with maximum value.
I have been tried to modified inside inner join code but, still don't get.
Pls help mee.....
|
|
|
|
|
example given gives max with name. that's why in inner join table (subquery)
i've not used type.
include type also in subquery's group by clause.
Regards
KP
|
|
|
|
|
OK Krish, I done with your code, it's very2 work thanks.
But how I write sql syntax if I want to select another table
(for example tbl2) instead of a.tbl1 INNER JOIN b.tbl1 ???
Thanks a lot
B.regards
|
|
|
|
|
Hi Jonh, I have error on your code
Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
I try to modified the code below:
select name,date,
sum(case when type='A' then value else 0 end) as 'A',
sum(case when type='B' then value else 0 end) as 'B',
sum(case when type='C' then value else 0 end) as 'C'
from table1 where date=(select max(date) from table1)
group by name
it's work but, this code will only show maximum date on whole table,
not show each name which have maximum date.
Need your suggestion. thanks
|
|
|
|
|
SELECT a.name, MAX(a.dt) AS [date], <br />
SUM(CASE WHEN type = 'A' THEN valu ELSE 0 END) AS A, <br />
SUM(CASE WHEN type = 'B' THEN valu ELSE 0 END) AS B, <br />
SUM(CASE WHEN type = 'C' THEN valu ELSE 0 END) AS C<br />
FROM TblB a <br />
INNER JOIN (SELECT name, MAX(dt) AS dt FROM tblB GROUP by name) b <br />
ON a.name = b.name and a.dt = b.dt<br />
GROUP BY a.name
Regards
KP
|
|
|
|
|
Does that one do what you need it to?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
|
|
|
|
|
Yes, you all great !!!
Thanks a lot.
B.regards
|
|
|
|
|
Hi All, sorry to bothering you again, need your help.
my format date is complete like "7/12/2007 10:40:49 AM"
so, concern that time/date is different each row, example (i write date as integer)
name type value date
-----------------------------------------------
a A 1 1
a B 1 2
a C 1 3
a A 2 4
a B 2 5
a C 2 6
when I run the code the result is:
name date A B C
----------------------------------------
a 6 0 0 2
so, the code show value each name with maximum date,
not show value each name, each type with maximum date.
so, i very confuse, realy need your help, thanks
|
|
|
|
|
how do i make a backup plan that can backup the database to another location automaticly
|
|
|
|
|
|
Use maintenance plans in SQL Management!!!
I was born dumb!!
Programming made me laugh !!!
--sid--
|
|
|
|
|
How to Import the SQL DataBase Diagram In visio
merwa
|
|
|
|
|
I don't think you can import it directly... but depending on the version of Visio you are using, you can tell it to create a diagram from a database by going to:
Tools > Options > Add-ins > Database Wizard
or Database > Reverse Engineer...
and following the instructions.
"It was the day before today.... I remember it like it was yesterday."
-Moleman
|
|
|
|
|
How to write query to find relationship between the primary key and foreign key re lation ship between the tables
|
|
|
|
|
Elaborate a bit more about your question. What database are you using?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Rmesh wrote: How to write query to find relationship between the primary key and foreign key re lation ship between the tables
That depends on the database you are using. In SQL Server there are sys* tables and INFORMATION_SCHEMA views that will allow you to get at this information.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Colin Angus Mackay wrote: depends on the database you are using
That is why I asked earlier. easy in SQL, but I am not sure about Access. I've looked at some of the system table and it might be a little more work.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Having a website that is dealing with large numbers, I am storing values inside a decimal(36,18) SQL Server 2005 column.
Until recently, everything run smooth for about 1 year.
Then it crashed with a System.OverflowException exception ("Conversion overflows.") and the following stack trace (excerpt):
at System.Data.SqlClient.SqlBuffer.get_Decimal()
at System.Data.SqlClient.SqlBuffer.get_Value()
at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
...
The value it seems to be unable to read from the database is 90807364313.118401000000000000 (i.e. a rather huge, approx. 90 billions ).
Now after digging, I found this thread at Google Groups. [^].
This thread, from 2004, tells that this is a bug in the .NET framework and that it will be fixed in next version. Now since it is already the next version, I am rather clueless.
Question: Anyone knows a hint or a workaround/fix for this issue? Maybe a hotfix from Microsoft is available?
Thanks
Uwe
|
|
|
|
|
The .NET Framework documentation states:
"The Decimal data type provides the greatest number of significant digits for a number. It supports up to 29 significant digits and can represent values in excess of 7.9228 x 10^28. It is particularly suitable for calculations, such as financial, that require a large number of digits but cannot tolerate rounding errors."
Your number is larger than 2^96 in the mantissa portion. It simply won't fit in the data type. Your only workaround is to use ToDouble , as in the thread, which will lose some precision (as double is only 64 bits). If you need something larger, you will need to implement your own scaled integer class and access the data as raw binary using GetBytes .
[EDIT:] It looks like you should be able to use the SqlDecimal type. I see you're using GetValues to retrieve all of the row's values in one go. Consider using GetSqlValues instead, to use the SQL types. I've not tried this.
-- modified at 11:57 Saturday 14th July, 2007
|
|
|
|
|
D'oh!
Thank you very much, Mike, I didn't even care to look for the docs of double , since I implied that decimal is the largest numeric type. Stupid me!
Thanks for your hints!
|
|
|
|
|
Trying a litte bit more, I wrote this simple test application:
internal sealed class Program
{
private static void Main()
{
double v5 = 90807364313.118401000000000000;
decimal v6 = 90807364313.118401000000000000m;
Console.WriteLine( v5 );
Console.WriteLine( v6 );
}
}
Since both data types are able to store the original value, I cannot follow your initial explainations.
I still do think this is a bug in the ADO.NET framework.
|
|
|
|
|
Both of those are losing significant digits. double uses binary floating point notation - the exponent is expressed internally as a power of two. 11 bits are used for the exponent and 52 for the mantissa (the actual significant digits), the final bit being used for the sign. The largest mantissa that can be represented is 2^52 - 1 which is 4,503,599,627,370,495, effectively 15 significant decimal digits. For more on double , see IEEE-754 on Wikipedia[^] and What Every Computer Scientist Should Know About Floating-Point Arithmetic[^]. Since the position of the decimal point is represented as a binary exponent, only negative powers of two and sums of negative powers of two can be represented. Everything else is rounded. For example, consider 1/3 in decimal (0.3333 recurring) which can never be truly represented.
The decimal value only has 17 digits after the decimal point. It's kept the magnitude correct at the expense of losing the final 0. You can only get 28 whole significant digits with this format - your database definition has 38. SqlDecimal supports the same range that SQL Server does.
|
|
|
|
|
Thanks, Mike, I really do appreciate your feedback.
Even if you (which I have no doubt!) are correct with your explainations, what practical chance do I have to apply them?
Since I'm simply doing kind of "SELECT * FROM ... " and receiving a DataTable , i do see no simply/any way to tell ADO.NET not to do the automatic conversion to decimal .
Any ideas?
Thanks
uwe
|
|
|
|