|
thanks for ur reply..its working but logically how it happens in sqlserver..by using a=b or b=a ..it will not give the same values in the both the columns...
|
|
|
|
|
hi this is ajay rathi,
i am facing a very big problem,
select month(Start_date) as start_Month, year(Start_date) as start_Year,
sum(case when Status = 'Active' then 1
else 0 end) as Active,
sum(case when Status = 'Inactive' then 1
else 0 end) as Billed
from tbl_Post_Job where Company_Id_Fk = 60 and Start_date between Start_date and Exp_date group by year(Start_date), month(Start_date)order by year(Start_date), month(Start_date)
in this query i want many things,first of all i want to say this is not right query i want query from you..
(1)month(Start_date) as start_Month, year(Start_date) as start_Year
from this line i want month and year from Start_date to Exp_date
(2)in active condition,if any company's status is active from january to april so in this condition the entry of active should be come in january to april in every month
(3)group by year(Start_date), month(Start_date)
in group by section same problem,i want data from Start_date to exp_date
actually the main problem i m facing is this that when i need month and year together and active postion and billed position saperatlly..
actually i need this data in following form...
Date | Active | Billed
------------------------------------
january,2007 | 4 | 2
feburary,2007 | 7 | 5
march,2007 | 4 | 2
april,2007 | 6 | 4
------------------------------------
january,2007 is the start_date and april,2007 is the exp_date
actualy above data january,2007 is coming in form of 1,2007 but i need this data in form of january,2007 so plz tell me how can i convert this data...
plz if any one needs any clarification then plz ask me about this because i need this query immediatly..
plz try to help me as soon as posible
Ajay Rathi
software engineer
NOIDA(UP),INDIA
|
|
|
|
|
hi Ajay
Actually i didn't undestand your Qn...
but using the below qry u will get the name of the month..
select datename(month,month(start_date)) from <tablename>
Regards
Joe
|
|
|
|
|
you gave me solution of one problem "datename(month,month(start_date))"
but this is giving me again and again only "january" nothing else..
so plz give me the other solution..
and wht you could not under stand in my first problem plz tell me....
i need the solution...
plzzzzzzzz
Ajay Rathi
software engineer
NOIDA(UP),INDIA
|
|
|
|
|
Arihant rathi wrote: plzzzzzzzz
Enough with the textspeak please. It's really annoying.
If you want to get the year/month description, concatenate the result of datename(month,month(start_date)) and year(start_date).
Paul Marfleet
|
|
|
|
|
sorry for the wrong Qry
correct one is
select DATENAME(month, start_date) from <tablename>
|
|
|
|
|
hi,
I am using MySql .NET Connector 5.1.2.
Following is the code I am using to read a longblob field from a table..
string selCmd = "SELECT * FROM document";<br />
MySqlCommand allDocsCmd = new MySqlCommand(selCmd, con);<br />
MySqlDataReader reader = allDocsCmd.ExecuteReader();<br />
reader.Close();
The execution hangs at reader.Close();
When I selected everything else but the blob field from the table and it worked fine i.e.
"select id,data from document" hangs but
"select id from document" works ok.
('data' is a LONGBLOB field in document table)
I am searching a cause and fix but lemme know if anyone has ne idea why this is happening..
Thanx.
-- modified at 9:56 Wednesday 10th October, 2007
|
|
|
|
|
hi!1
Actually i am a beginner with asp.net and databases .
i want to know that how can i access values from a table where some column name has null value say parentId ..
Mrinal
|
|
|
|
|
what if one value is int type and the other is null type
Mrinal
|
|
|
|
|
SqlDataReader r =//..some code..
int ParentId;
//short form
r.IsDBNull(r.GetOrdinal("ParentId"))?-1:Convert.ToInt32(r["ParentId"]);
OR
int ParentId = -1;
if (!r.IsDbNull(r.GetOrdinal("ParentId")))
{
ParentId = Convert.ToInt32(r["ParentId"]);
}
Intelligence is measured by common sense not by how many scholarly books you read.
|
|
|
|
|
SELECT AField FROM ATable WHERE parentId is null
|
|
|
|
|
Hi,
I have a simple doubt.The maximum number of parameters in a stored procedure is 2100.
Like what is the maximum number of stored procedure in a MSSQL database ????
Be very grateful for any advice.
Regards
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
Vipin dev wrote: The maximum number of parameters in a stored procedure is 2100.
I find that would be hard to reach that number.
Have you looked around google for your question about the max. number of stored procs?
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi,
i am using sql server 2005 and i am writing one query to concatename 3 column values..
SELECT distinct(P.firstname + ' ' + P.middlename + ' ' + P.lastname) AS UserName ,P.Id as UserId,G.Id,G.Fkgroupid FROM c_Groupmember G,c_Person P where P.Id=G.FKUserid and G.FKgroupId=" + Id + " and P.Status=0 order by P.firstName asc
it will disply the username if first name and lastname is there .if there is only firstname .then i won't display any value?....
what is the solution for this ?.in query itself i need to get all the usernames.
with regards
anu
|
|
|
|
|
Rewrite query as
SELECT distinct( COALESEC(P.firstname,P.firstname,'') + ' ' + COALESEC(P.middlename,P.middlename,'') + ' ' + COALESEC(P.lastname,P.lastname,'')) AS UserName ,P.Id as UserId,G.Id,G.Fkgroupid FROM c_Groupmember G,c_Person P where P.Id=G.FKUserid and G.FKgroupId=" + Id + " and P.Status=0 order by P.firstName asc
for more information see COALESCE function documentation.
Intelligence is measured by common sense not by how many scholarly books you read.
|
|
|
|
|
it is showing an error
Msg 195, Level 15, State 10, Line 1
'COALESEC' is not a recognized built-in function name.
|
|
|
|
|
ya.i got .it is 'COALESCE'. Not 'COALESEC'
|
|
|
|
|
anujose wrote: ?.in query itself i need to get all the usernames
Better way of working on queries will be putting them up in a stored proc, to avoid sql injection attacks.[^]
|
|
|
|
|
Try SET CONCAT_NULL_YIELDS_NULL OFF before you begin the query. Works in 2000, not usre about 2005.
|
|
|
|
|
I posted this question before but I'm not sure it was fully understood:
I'm using MSSQL7, NT authentication and application roles so only my application can access the data. Also, other applications (like Excel) can not access the data and read it. So far, so good...
Yet, I noticed that if I try to access the SQL Server from another SQL Server on the network, it is allowed to see the list of tables, SP, etc. It is not allowed to open the table, but the Import/Export wizard is working and will allow retrieving data from the secured tables.
If I change to MSSQL authentication, any user will be able to access the data from my application and I don't want that either.
Unless I'm missing something, this is a big problem, especially today where any VPN connection with valid user name and password can actually log in to the domain and therefore connect to the database via SQL Server.
By the way, the server still must allow access to users via applications so logins must exist. I just don't want other SQL servers on the network to be able to connect to and import/export, view table and SP, etc.
Any ideas?
|
|
|
|
|
you're saying that if a user connects to your DB via the SQL Server tools, they have access to stuff that the role they log in under should not see ?
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Correct...
It seems as if any user that can log in to SQL, can use SQL Server to see the list of table, list of SP and import/export data even though their user role does not allow SELECT, UPDATE, INSERT or EXECUTE.
|
|
|
|
|
Hi,
In a table with a primary key and a foreign key like this.
I-------I-------I-------I
I pkey I fkey I name I
I-------I-------I-------I
I 1 I 1 I ITEM I
I-------I-------I-------I
I 2 I 1 I ITEM2 I
I-------i-------I-------I
If we were to run a select query against this table that returns just one row. Would be any performance benefit if we have this select ?:
SELECT * FROM The_Table WHERE pkey=2 AND fkey=1
over when we have this select ?:
SELECT * FROM The_Table WHERE pkey=2
|
|
|
|
|
As the primary key is unique (a condition of being a primary key is that it uniquely identifies the row) then I wouldn't see any need for any additional information in the WHERE clause.
Whether it has any benefit or not depends on how you set up the table. If you go with the traditional defaults then I don't see it having any additional benefit. But I doubt it would have any negative effect either.
|
|
|
|
|
If we define an index on the foreign key field , will be any performance gain ?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
