|
I have a table that has currency exchange rates as below
CCY EX_RATE RATE_DT
---- ------- -------
AED 1.1 7/31/06
AED 1.0 6/30/06
AED 1.5 5/31/06
What that means is that the exchange rate was 1.5 from 5/31 to 6/30 and is at 7/31 till eternity. What I want is the output to look like this
CCY EX_RATE START_DT END_DT
---- ------- -------- ------
AED 1.1 7/31/06 (NULL)
AED 1.0 6/30/06 7/31/06
AED 1.5 5/31/06 6/30/06
I think an self-join will give me the correct data but i am getting excessive rows when i do a join.
|
|
|
|
|
Asad,
This should work.
Select CCY, EX_RATE, MIN(RATE_DT) as START_DT, MAX(RATE_DT) as END_DT
From your_currency_table
Group by CCY, EX_RATE
Farhan Noor Qureshi
|
|
|
|
|
Thanks for your reply but that doesnt work. If I have this data
CCY EX_RATE RATE_DT
---- ------- -------
AED 1.1 7/31/06
AED 1.0 6/30/06
AED 1.5 5/31/06
It will return something like
CCY EX_RATE START_DT END_DT
---- ------- ------- ------
AED 1.1 7/31/06 7/31/06
AED 1.0 6/30/06 6/30/06
AED 1.5 5/31/06 5/31/06
I want it to give me ranges like
CCY EX_RATE START_DT END_DT
---- ------- -------- ------
AED 1.1 7/31/06 (NULL)
AED 1.0 6/30/06 7/31/06
AED 1.5 5/31/06 6/30/06
|
|
|
|
|
select ccy,ex_rate,rate_dt as start_dt,(select top 1 cur2.rate_dt from tblcurrencyrate cur2 where cur.ccy = cur2.ccy and cur2.rate_dt > cur.rate_dt order by cur2.rate_dt) as end_dt from tblcurrencyrate cur
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I'm still getting the hang of this new tool and for the life of me, I can't remember how to add SQL procedures in the Stored Procedures folder. It keeps saving them as SQL files, but storing them as files. Man I miss the old look and feel. I finally got used to that one.
"In this house, we obey the laws of thermodynamics!" - Homer Simpson
Web - Blog - RSS - Math - LinkedIn
|
|
|
|
|
You have to run the CREATE PROCEDURE sp_XYZ query for the procedure to be created. F5 usually works for me.
-- modified at 18:33 Tuesday 15th August, 2006
Farhan Noor Qureshi
|
|
|
|
|
D'oh! Thanks. I was always removing that CREATE function since I was copying it from my old procedures. That kinda sucks the way they did it. I much prefer the older method. F5 doesn't create it if CREATE isn't there.
"I know which side I want to win regardless of how many wrongs they have to commit to achieve it." - Stan Shannon
Web - Blog - RSS - Math - LinkedIn
|
|
|
|
|
i´m trying to select the names of dimension members from a data cube. when working under sql server 2000, i´m using this mdx query select distinct [Projekt_Owner].[Projekt Owner] from datacube and it´s working just fine... but when i´m trying to do the same under sql server 2005, i always get the same error:Error (Data mining): Either the user, does not have permission to access the referenced mining model or the object does not exist. can anyone help my with this?
cellardoor
|
|
|
|
|
using VS2005;
Can someone please help me out with this:
I have a datatable that contains several columns including "ReportDate" and "Amount". What I want to be able to do is retrieve the value from the datatable in the "Amount" column when all I know is the same rows "ReportDate" value. The "ReportDate" column is DateTime and the "Amount" column is Decimal. I know how to get this direct from the database table but not the corresponding datatable.
As part of the above answer, how do you pass a variable to the Select statement of a datatable lookup. I assume you can't use the same method of passing variables/paramaters to a SQL Statement because I can't seem to be able to do that either!
Glen Harvy
|
|
|
|
|
You could use DataView over your DataTable by applying a filter and then query the value for the desired column, for e.g. "Where ReportDate = '1/1/2006'".
Farhan Noor Qureshi
|
|
|
|
|
Hello:
Now i want to bulk insert a dataset into an excel file, is there any good method?Thanks in advance!
Plus: i am wodering the method of firstly store the datatable in sql sever,then use dts. but i think it seems a little redundant and time-wasting.
|
|
|
|
|
I don't know how do it from TSQL but here is how I have done it form C++
CString str;
str.Format("SELECT * INTO [Excel 8.0;DATABASE=%s].[%s] FROM [%s]", ExcelFile, Worksheet, Table);
pConn->Execute(str.AllocSysString(), NULL, adExecuteNoRecords);
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
Where is it?
--
-= Proudly Made on Earth =-
|
|
|
|
|
IIRC, it is in the Server Management Studio.
That's no moon, it's a space station. - Obi-wan KenobiLast modified: Monday, August 14, 2006 2:29:30 PM --
|
|
|
|
|
There isn't one, SQL Server Management Studio replaces both Enterprise Manager and Query Analyzer.
If you only have SQL Server 2005 Express Edition, you have to download Management Studio Express separately. Otherwise you have to use Server Explorer in VS, or the sqlcmd utility (a replacement for isql and osql, although osql is still supplied).
|
|
|
|
|
I have the developer's edition of the server. I couldn't find such a tool on the DVD. Is the management studio a "real server" only thing? I suppose that if you can download it for SQL Server Express, there ought to be one available for the developer's edition. I'll check it out. Thanks!
--
Hey, TiVo! Suggest this!
|
|
|
|
|
There's a checkbox for it in the installer: select 'Workstation components'. It's not checked by default.
|
|
|
|
|
I'm pretty sure I checked everything except all the buzzword stuff (data mining, reporting, etc).
--
Behold, for I am THE CORRUPTOR!
|
|
|
|
|
Go to Control Panel, Add/Remove Programs, select Microsoft SQL Server 2005 from the list, then click Change. If 'Workstation components' appears under 'SQL Server 2005 common components', you have it installed.
If not, insert the CD or DVD, click the 'To install a new component, click here' link, enter the path to setup.exe (on the DVD, for the 32-bit version, this is under (DVD-drive):\SQL Server x86\Servers) and OK. Click through the wizard until you get to the 'Components to install' screen, then check 'Workstation components, Books Online and development tools', which is the bottom checkbox.
Yeah. Weird installer. SQL Server supports multiple independent instances of the product installed simultaneously with different service pack levels. I think it even supports having different instances being different editions: right now I have a default instance of SQL 2000 Developer Edition and a named one, and an instance of SQL Express, and a named instance of SQL Server 2005 Developer Edition. That's pretty tricky to accomplish with Windows Installer. I wish it didn't create so much clutter in Add/Remove Programs though.
|
|
|
|
|
Private Function createThumnail(ByVal ImageStream As Stream,
ByVal tWidth As Double, ByVal tHeight As Double) As Byte()
Function NewthumbSize(ByVal currentwidth As Double, ByVal
currentheight As Double, ByVal newWidth As Double, ByVal newHeight As Double)
tech_garden@yahoo.com
|
|
|
|
|
Wrong forum. You'll get a better response in the C# or VB.NET forum
|
|
|
|
|
Stream is an object and it should be passed by ref which is the default action for ref types so ...
Actually, I was going to answer but I can't be bothered to correct the spelling and capitalization.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
I have a class which looks like that:
class Stuff
{
Dictionary<string, object> properties;
List<Stuff> children;
}
I'm writing a home made XML 'serialization'.
I quote that, because it's not serialization, I'm writing the XML writer myself (without System.Reflection).
I'm using a shema like that:
<stuff count=2 key1="value1" key2="value2" key3="value3">
<stuf>
.........
</stuff>
<stuf>
.........
</stuff>
</stuff>
where count = children.Count
and key1,2,3 are the keys in the properties dictionary and value1,2,3 their values.
properties is to be filled by the user with whatever (s)he wants.
Now my problem is:
==============
As the user could put whatever (s)he wants in the properties dictionary (s)he could as well create a key named 'count', therefore the 'count' atribute will be present twice in the tag.
It's not a problem for my reader (which expect a first count followed by any kind of attribute) but it might be for other people wanting to consume my XML documents.
What do you think?
it's actually much easier this way...
(particularly because this sample output is far from the whole story)
|
|
|
|
|
Have you looked into xsd.exe for class generation? Personally I like strong types. Count should be implicit, btw.
<stuff>
<item key="value1" value="someValue">
<item key="value2" value="someValue">
<item key="value3" value="someValue">
<item key="value4" value="someValue">
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
uh?
I don't think auto-generated code will work in this case...
count is explicit only for performance reason....
|
|
|
|