|
I had an old boss who swore by this route, in favour of my NOT IN solution above. He said it was much more efficient.
He was right, and I forgot about that whole conversation till I saw your answer.
|
|
|
|
|
I have a table which is some thing like below..
Date ID
2009-07-01 1
2009-07-01 2
2009-07-01 3
2009-08-01 4
2009-08-01 5
2009-08-01 6
2009-09-01 7
2009-09-01 8
2009-10-01 9
2009-10-01 10
2009-11-01 11
....
Now I need to write a query which will show a output like below.
Date Start End
2009-07 1 3
2009-08 4 6
2009-09 7 8
...
How can I do this..
Any help would be highly appreciated
Thanking In Advance
Johnny
|
|
|
|
|
Thats about the easiest type of aggregate you can hope for. Simple MIN and MAX over a grouped set:
SELECT [date],
MIN([id]),
MAX([id])
FROM [YourTableName]
GROUP BY date
|
|
|
|
|
He doesn't want to group by date, he wants to group by month so it is a little more complicated.
(And before you ask, no, I don't know what the solution is.)
|
|
|
|
|
So he did, I missed that.
Still easy though:
SELECT MONTH(date),YEAR(date),
MIN([id]),
MAX([id])
FROM [YourTableName]
GROUP BY MONTH(date),YEAR(date)
and if you really must mash that first column back to the format he wanted
SELECT
CAST(YEAR(date) AS VARCHAR(4)) + '-' + CAST(MONTH(date) AS VARCHAR(2)),
MIN([id]),
MAX([id])
FROM [YourTableName]
GROUP BY MONTH(date),YEAR(date)
|
|
|
|
|
|
Hi,
Try this
SELECT DISTINCT d.Date1 as `ActionDate`, (SELECT MIN(ID) FROM dates WHERE Date1 = `ActionDate`) as `Start`, (SELECT MAX(ID) FROM dates WHERE Date1 = `ActionDate`) as `End` FROM dates d;
Happy Coding
<edit>This assumes table is called dates and date column is called date1
|
|
|
|
|
This gives the same result as mine above, but is far less readable and far less efficient.
Get friendly with the GROUP BY functionality of sql if you want to aggregate data.
|
|
|
|
|
Hi,
I wanna ask How can I verify that my TCP/IP connection to the server is working well???
My code is:
string sConnection = "Provider=SQLNCLI.1;Persist Security Info=False;User ID=sa;Initial Catalog=GMAO;Data Source=127.0.0.1,1433;Password=sa;";
string sSQL = "SELECT com FROM energie; ";
SqlConnection conn = new SqlConnection(sConnection);
SqlCommand comm = new SqlCommand(sSQL, conn);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sSQL,conn);
SqlDataReader dr = null;
try
{
comm.Connection.Open();
da.Fill(ds, "SQL Temp Table");
foreach (DataRow d in ds.Tables[0].Rows)
{
MessageBox.Show("xxxx");
comboBox1.Items.Add(d.ItemArray[0].ToString());
}
}
catch (SqlException ex)
{
MessageBox.Show("connexion impossible");
MessageBox.Show(ex.Message);
return;
}
dr.Close();
comm.Connection.Close();
the error that must appears when connection is failed doesn't existe when running,IS it an indice that my connection goes well??
When googling,I found that the job activity monitor in the sql server management indicates that.is that true??how can I use that in checking my connection result???
Thanks 
|
|
|
|
|
well firstly 127.0.0.1 is a ping back address..
to programatically ping an address have a look at this example ping example
what is the error message that you are getting?
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
Hi,
I wanna explain to u something:First,I work with the defualt instance of sql server 2005.So the server availabe is:Admin-PC\MSSQLSERVER that indicates a local IP@=127.0.0.1,1433.Is that true or I am wrong in my behavior?
Second,what u mean by ping back @??
Third,bebore attaching my database,the error"Specified sql server not found or access denied"annoys me.after the attachemment,no exception appears(in the catch block of my code)but still my combobox not filled by data from database.
Thanks a lot 
|
|
|
|
|
127.0.0.1 is normally an address you use to test a machines network card.
if you run a command prompy and type ipconifg you will see your network address is different.
on your connection string i would change the ip address for the name of the machine and try that
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
|
Hi,
I am successfully stored & retrieved image files of size less than 64 KB.But when I am storing a image file of size 3 MB, then there is no error,but it only stores partial data in database.
Please refer the code:
------------------------------------------------------------------
1. File CMyDb.hpp----->
class CMyDb : public CDatabase
{
public:
CMyDb();
virtual ~CMyDb();
virtual void BindParameters( HSTMT hstmt );
BYTE* m_pabImage; // address of the data of the blob
int m_nImageLen; // sizeof the data in the image, in bytes
CString m_sStmt; // looks like "UPDATE MyTable SET myFld=?
WHERE idxFld=123"
};
------------------------------------------------------------------
2. File CMyDb.cpp----->
CMyDb::CMyDb()
{
m_pabImage = NULL;
m_nImageLen = 0;
}
CMyDb::~CMyDb()
{
}
void CMyDb::BindParameters( HSTMT hstmt)
{
SQLINTEGER nLenOrInd= m_nImageLen;
SQLRETURN rc;
if ( m_sStmt.Find("?") == -1 )
{ // no binding needed
return;
}
rc= SQLBindParameter(
hstmt, (SQLSMALLINT)1, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_LONGVARCHAR,
m_nImageLen, 0, (void*)m_pabImage, m_nImageLen, &nLenOrInd);
}
------------------------------------------------------------------
3. File Main.cpp->
CFile file (sFileName, CFile::modeRead);//|CFile::typeBinary);
CFileStatus fileStatus;
file.GetStatus(fileStatus);
int nCntImgBytes= 3 * 1024 * 1024;//3MB buffer
BYTE* pabData = (BYTE*)calloc(nCntImgBytes,sizeof(BYTE));
DWORD lTemp = file.ReadHuge((void*)pabData,fileStatus.m_size);
pApp->pServerDatabase->m_pabImage = pabData;
pApp->pServerDatabase->m_nImageLen = fileStatus.m_size;
pApp->pServerDatabase->m_sStmt= "INSERT INTO TEST1 (\"NAME\" , \"myBlobfield\") VALUES ('2',?)";
pApp->pServerDatabase->ExecuteSQL( pApp->pServerDatabase->m_sStmt );
------------------------------------------------------------------
Please reply me as soon as possible.
Regards
Navneet
|
|
|
|
|
There could be a size limit of the BLOB data type in the database engine you use here. I do not think that all database engines throw an exception when the data is more than the specified field can store, it seems your database just trims it down.
I guess a better solution is to store the file somewhere on the file system, and to store the filename (including path) only in the database.
|
|
|
|
|
what database are you using?
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
Hi,
I am using Interbase 6.0 database.
My database table structure is like:
Create table "TEST"
(
"NAME" VARCHAR(10),
"BLOB_DATA" BLOB
)
Interbase 6.0 database only supports BLOB for storing images.It don't support LONGBLOB & other datatypes.
|
|
|
|
|
The BLOB type in your database seems limited to 64KB. There may be a larger type though.
Example: MySQL has TINYBLOB, MEDIUMBLOB, BLOB, and LONGBLOB.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
i what to have an insert transact in ms access 2003
this insert statement should retrieve an ID from another table and then
insert informations
some thing like :
insert into myTable values(123,'123',123,123,
(select DISTINCT ID from ServiceGroupsInfo where NodeName='abc') )
but it does not work
what should i do?????
please help me!
|
|
|
|
|
reza assar wrote: but it does not work
what should i do?????
In the first instance I would suggest you read the forum guidlines, In the second I would define "Doesn't work".
|
|
|
|
|
Looks to me like you are missing the field list. Eg.
insert into myTable
(field1, field2, field3, field4, field5)
values(123,'123',123,123,(select DISTINCT ID from ServiceGroupsInfo
where NodeName='abc'))
though I would further modify it to:
insert into myTable
(field1, field2, field3, field4, field5)
Select Distinct
123,'123',123,123, ID
from ServiceGroupsInfo
where NodeName='abc';
I'm not convinced by the distinct either, as surely ServiceGroupsInfo is unique by NodeName, or it will give multiple results for ID anyway.
|
|
|
|
|
hi,
I have to search 90% characters in a string.
Suppose the string is "abcdef"
and it;s character length is 6
and it's 90 percent character length will be 5
Now i have to search it's any 5 character in the following string
string1
stringabcdef
astrbincgdef
Please suggest something ...
Is there any way to do so with loop
Thanks
|
|
|
|
|
For that you need to write either Stored Procedure or Function.
Just calculate length of the search string,
and also need to calculate how many characters for 90% it.
Then you need to search character by character..
Sounds Non-trivial.
Jinal Desai - LIVE
Experience is mother of sage....
|
|
|
|
|
How about the following...
-- return 90% of a string
declare @s varchar(6)
set @s = '123456'
select substring(@s, 1, cast(floor(0.9 * len(@s)) as int))
-- usage
select *
from [table]
where charindex(substring(@s, 1, cast(floor(0.9 * len(@s)) as int)), [field]) > 0
Ryan
|
|
|
|
|
hi,
RyanEK wrote: -- return 90% of a string declare @s varchar(6)
set @s = '123456' select substring(@s, 1, cast(floor(0.9 * len(@s)) as int))
-- usage
select *
from [table]
where charindex(substring(@s, 1, cast(floor(0.9 * len(@s)) as int)), [field]) > 0
Thanks for the kind attention. It will work fine if I have to start from position 1 only.
But the string to search that I mentioned in the post. was abcdef
and it's 90% can be abcde or bcdef
similarly i can have larger strings as well and it's propability will be have more string to search.
Is it possible to resolve it without any iteration ?
Thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
