|
Hi frndz,
I want name of the foreign key column in a table using sqldmo ,can any one help me.
Rupa
|
|
|
|
|
supposed i have this table, the date is in d/m/y format
value date
100 6/11/2006
100 7/11/2006
100 8/11/2006
100 9/11/2006
100 10/11/2006
200 11/11/2006
200 12/11/2006
200 13/11/2006
200 14/11/2006
100 15/11/2006
100 16/11/2006
100 17/11/2006
200 18/11/2006
130 19/11/2006
130 20/11/2006
i want the result to be like this:
value min_date max_date
100 6/11/2006 10/11/2006
200 11/11/2006 14/11/2006
100 15/11/2006 17/11/2006
200 18/11/2006 18/11/2006
130 19/11/2006 20/11/2006
The only way i know to work this out is to make a cursor table, fetch each record and compare the value. If next value is different from first fetch up value then move to prior record, record it's date as max date. Put that value in temporary table.
Is it possible to retrieve the set i want like above example with only one select statement? I have failed to use select with group by clause.
Can anyone help me? I really need to get it done with only 1 select statement
|
|
|
|
|
select value, min_date, max_date
from
(select value, min(date) min_date
from table
group by value) a,
(select value, max(date) max_date
from table
group by value) b
where a.value = b.value
Mike Lasseter
|
|
|
|
|
mr_lasseter wrote: select value, min_date, max_date
from
(select value, min(date) min_date
from table
group by value) a,
(select value, max(date) max_date
from table
group by value) b
where a.value = b.value
Mike Lasseter
thanks for quick reply. Your query not doing exactly as i requested. Value 100 and 200 would be in the same block.
100 6/11/2006 17/11/2006
I need it to be:
100 6/11/2006 10/11/2006
100 15/11/2006 17/11/2006
200 11/11/2006 18/11/2006
I need it to be:
200 11/11/2006 14/11/2006
200 18/11/2006 18/11/2006
|
|
|
|
|
I am not sure in what context you are using this table, but if I was you I would seriously consider rethinking your design. What happens if records get entered into the table out of order? Is there a primary key on this table?
In addition, AFAIK there is no guarantee that select * from table will always pull the results in the same order.
Mike Lasseter
|
|
|
|
|
Actually there are 3 primary keys on the original table. I only pull out 2 definitive fields (value and date) from that table which is pointing out directly to the problem i had. Both fields are not primary keys.
I'm going to use the query in reporting service as a part of DataSet.
|
|
|
|
|
How do you determine that the first group of 100 is not the same as the second group of 100?
Mike Lasseter
|
|
|
|
|
This is a bank's application where for each change value, i need to store it's period, calculate tenor (max date - min date) and then times it with pre-stored fee to get amount to charge. Bank charge the customer based on change value, that's why the 100 must not be grouped, it has to be split
Table's below will be included as part of customer's invoice for Nov 2006
Stock Period Amount Tenor Fee (%) CCY Charge
Stock001 1 Nov 2006 - 23 Nov 2006 100.000 22 0.03 USD 30
23 Nov 2006 - 25 Nov 2006 200.000 2 0.03 USD 60
25 Nov 2006 - 30 Nov 2006 100.000 5 0.03 USD 30
SubTotal: USD 120
I just wonder whether this query with period could be done with only 1 select statement. Is it possible or not using a bit complex group by statement.
-- modified at 20:04 Monday 4th December, 2006
|
|
|
|
|
Is period stored as a field in the table?
Mike Lasseter
|
|
|
|
|
|
bayangan1 wrote: Actually there are 3 primary keys on the original table
Sorry for taking a pedantic interlude at this point, but...
A table can contain only ONE primary key. A primary key is always unique. A primary key can consist of multiple columns, this is called a compound primary key, however there remains only one primary key.
|
|
|
|
|
Oops, my mistake.. sorry. 3 foreign keys made up to a compound primary key.
|
|
|
|
|
Hello,
I'm newer in SQL sever, i'm having a problem to open a connectoin because the system tell me that the Named Pipes Provider aren't working.
I enabled it in the SQL Server configuration (cliente and server), but it sill not working.
Do you have any ideia to solve the problem?
Thanks,
Tino
System.Data.SqlClient.SqlException was unhandled
Message="An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=20
LineNumber=0
Number=2
Server=""
State=0
StackTrace:
...
Tino
|
|
|
|
|
tino00 wrote: When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
You need to configure it to allow remote connections...Look into the Surface Configuration tool that comes with SQL 2005...
If you try to write that in English, I might be able to understand more than a fraction of it. - Guffa
|
|
|
|
|
Hello All experts,
I am Jayachandra.C, My problem is
I am able to create New User in Oracle database and able to give previleges on sequences, insert, update like. User created. But when i am trying to create a table in the created user table is not creating. Please give me the solution & reason where i failed. please
thank you sir,
Jayachandra.C
|
|
|
|
|
Hi....
I have devoloped website in asp.net.
I just upload the project using ftp. Its works,
but i dont know how to upload sql database on server.
Can anybody please help me, Pls give me full details.
I used connectionstring in web.config which works for localhost.
but what connection string i should use for uploding on net.
|
|
|
|
|
Does you hosting account support SQL or do you have to use an access database ?
Which db are you trying to use.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
can any body tell me query about that if want to delete duplicate records from a table based on serial number and ID , means if i found two duplicate
records for a particular ID and i want to delete the shorter serial number record
hello
|
|
|
|
|
Something like this:
delete mytesttable from
(select id, minSerial
from (select count(*) as total,id,
(select min(serialnumber)
from mytesttable tInside
where tInside.id = t.id) as MinSerial
from mytesttable t group by id) as tbl
where total=2) as tbl
where mytesttable.id = tbl.id and mytesttable.serialnumber=tbl.minserial
--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
|
|
|
|
|
hi
i want to create a variable based connection string in sql server ,using c#.net
i want to give data source field in the connection string after getting it from a computer name variable , is that possible . if so how ,, can any body tell me the syntax
hello
|
|
|
|
|
Use strings....
eg....
<br />
<br />
string connStr;<br />
<br />
string Driver = "Driver={SQL Server};";<br />
string Database = "Database=db_name;";<br />
string connectionType = "Trusted_Connection=yes;";<br />
<br />
connStr = Driver + Database + connectionTypel<br />
<br />
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
hi
i want to scale down an image in sql server 2005 stored procedure to get a thumbnail image from that.i was trying clr but the System.Drawing did not usable there do u have any suggestion ?
I Wish the Life Had CTRL-Z
|
|
|
|
|
|
|
declare @foo decimal(18, 6)<br />
set @foo = 123.45678<br />
select cast(@foo as decimal(18, 2))
And don't shout next time, please.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
