|
Hi guys i have doubt in max values...,
See this is my table...,
salary
====
1000
2000
3000
4000
4500
1500
my expectation is produce only the 4000 from the given table..
which means second maximum salary..?
How to do that...,
Plz help me..,
Magi
|
|
|
|
|
select * from tblname where sal=(select max(sal) from tblname
where sal < (select max(
sal) from tblname));
Magesh
|
|
|
|
|
DECLARE @nth as bigint
SET @nth = 10
SELECT * FROM TblA AS a WHERE @nth = (SELECT COUNT(ColA) FROM
TblB AS b WHERE b.ColA >= a.ColA)
This will return 10th Max.
change value of the variable @nth to get the required Max.
The same i've given earlier for finding Nth Max value
in your case assign 2 to @nth variable
Regards
KP
|
|
|
|
|
DECLARE @nth as bigint
SET @nth = 10
SELECT * FROM TblA AS a WHERE @nth = (SELECT COUNT(ColA) FROM
TblB AS b WHERE b.ColA >= a.ColA)
This will return 10th Max.
change value of the variable @nth to get the required Max.
The same i've given earlier for finding Nth Max value
in your case assign 2 to @nth variable
Regards
KP
|
|
|
|
|
hey its very simple man.
select top second salary from table_name order by salary desc.
Since in your table the 4000 is the second top salary.
Regards,
Satips.
|
|
|
|
|
Hi satips plz check ur query in ur query analyzer...,
It willn't work...,
Regards,
Magi
|
|
|
|
|
select max(salary) from mytable where salary < (select max(salary) from mytable)
Regards,
Sylvester G
sylvester_g_m@yahoo.com
|
|
|
|
|
C# And Access DB.
There is an Autonumber field in the table, 'PP_WF_ID'. Every field is Text Type except Personnel_Id And Company_Id
<br />
bool InsertWorkFlowPP()<br />
{<br />
if (Sel.IsAuthenticated())<br />
{<br />
try<br />
{<br />
using (OleDbConnection myconn = new OleDbConnection())<br />
{<br />
OleDbCommand mycomm;<br />
string sSQL = "INSERT INTO PP_WF(Personnel_ID,Photo_Location,pp_Name,Position,";<br />
sSQL += "Highlights,Education,Projects,";<br />
sSQL += "Full_Description,Summary,Company_ID,WF_STATUS,Is_CEO)";<br />
sSQL += " VALUES(@Personnel_ID,@Photo_Location,@pp_Name,@Position,";<br />
sSQL += "@Highlights,@Education,@Projects,@Full_Description,@Summary,@Company_ID,@WF_STATUS,@Is_CEO);";<br />
myconn.ConnectionString = ConfigHelper.GetWFString();<br />
mycomm = new OleDbCommand(sSQL, myconn);<br />
mycomm.Parameters.AddWithValue("@Personnel_ID", System.Convert.ToInt32(Labelcode.Text));<br />
mycomm.Parameters.AddWithValue("@Photo_Location", picture.ImageUrl);<br />
mycomm.Parameters.AddWithValue("@pp_Name", name.Text.Trim());<br />
mycomm.Parameters.AddWithValue("@Position", position.Text.Trim());<br />
mycomm.Parameters.AddWithValue("@Highlights", highlights.Text.Trim());<br />
mycomm.Parameters.AddWithValue("@Education", education.Text.Trim());<br />
mycomm.Parameters.AddWithValue("@Projects", projects.Text.Trim());<br />
mycomm.Parameters.AddWithValue("@Full_Description", longbio.Text.Trim());<br />
mycomm.Parameters.AddWithValue("@Summary", shortbio.Text.Trim());<br />
mycomm.Parameters.AddWithValue("@Company_Id",Sel.GetCookies("Company_id"));<br />
mycomm.Parameters.AddWithValue("@WF_STATUS", "NA");<br />
mycomm.Parameters.AddWithValue("@is_CEO", chkCEO.Checked.ToString());<br />
myconn.Open();<br />
mycomm.ExecuteNonQuery();<br />
return true;<br />
}<br />
}<br />
catch (Exception)<br />
{<br />
}<br />
finally<br />
{<br />
}<br />
}<br />
return false;<br />
}<br />
|
|
|
|
|
Can someone move this to the ASP.NET Category perhaps?
I would like to solve this today, thanks.
|
|
|
|
|
In this
<< "@Highlights,@Education,@Projects,@Full_Description,@Summary,@Company_ID,@WF_STATUS,@Is_CEO)(this one here) ;"; >>
Wat is the ;(semicolon) doing just before this << "; >>
I guess you need to remove that!!!
I was born dumb!!
Programming made me laugh !!!
--sid--
|
|
|
|
|
Hi All,
1st off what is the best T-SQL book I can get for a novice? obviously I have much to learn.
If I run the query below as is I get this:
Msg 8120, Level 16, State 1, Line 12
Column 'job_ticket.LOCATION_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Okay so I add it to the "group by" and it still does not work.
If I removed the piece in bold the query executes, but does not break down by "client".
Also the AVG should be the AVG of @startdate and @enddate; I know it's incorrect now but I cant get it to work the way I need it to.
I needed help to get where I am now....Thanks Roy
Table Job_ticket
Status_type_id varchar
Location_id varchar
Table Location
Location_ID varchar
Location_name varchar
SELECT Opened, [Total Open at Start], [Total Open at End],
[Total Open at End] - [Total Open at Start] as [Total Closed],
Client, [Average days open]
FROM (SELECT count(*) AS 'Opened',
SUM(CASE WHEN status_type_id LIKE '[1,2,6,7]'
THEN 1 ELSE 0
END) AS 'Total Open at Start',
(select count(*) from dbo.job_ticket as b
where j.location_id = b.location_id
and status_type_id like '[3,4,5]'
and last_updated between @startdate and @enddate)
AS 'Total Open at End',
l.location_name AS 'Client',
AVG(datediff(d,report_date,getdate() ))
AS [Average days open]
FROM job_ticket j
JOIN location l on l.location_id = j.location_id
WHERE (report_date BETWEEN @startdate AND @enddate)
GROUP BY l.location_name) as X
Regards,
Hulicat
|
|
|
|
|
report_date is from which table?
also not getting what you are exactly trying for
Is that want location wise
1. count number of rows falls in a date range
2. count number of rows having status_type_id 1, 2, 6 or 7
3. count number of rows having status_type_id 3, 4 or 5
is it some thing like this
Regards
KP
|
|
|
|
|
I think what you're after is:
SELECT L.location_name AS Client,
TotalOpenAtStart,
TotalOpenAtEnd,
(TotalOpenAtEnd - TotalOpenAtEnd) AS TotalClosed,
AvgDaysOpen
FROM location l
LEFT OUTER JOIN (
SELECT j.location_id,
SUM(CASE WHEN j.status_type_id IN (1,2,6,7) THEN 1 ELSE 0 END) AS TotalOpenAtStart,
SUM(CASE WHEN j.status_type_id IN (3,4,5) THEN 1 ELSE 0 END) AS TotalOpenAtEnd,
AVG(DATEDIFF(d, j.report_date, GETDATE())) AS AvgDaysOpen
FROM job_ticket j
WHERE j.report_date BETWEEN @startdate AND @enddate)
GROUP BY j.location_id
) x
ON x.location_id = l.location_id
ORDER BY L.location_name Regards
Andy
|
|
|
|
|
Thanks Andy it does not like the group by
"Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'GROUP'."
If it helps the report_date is in the job_ticket table datetime. thanks for taking the time to help me...It's greatly appreciated. I tried tweaking it a bit and no luck........
SELECT L.location_name AS Client, TotalOpenAtStart, TotalOpenAtEnd,
(TotalOpenAtEnd - TotalOpenAtEnd) AS TotalClosed, AvgDaysOpen FROM location l
LEFT OUTER JOIN ( SELECT j.location_id,
SUM(CASE WHEN j.status_type_id IN (1,2,6,7) THEN 1 ELSE 0 END)
AS TotalOpenAtStart, SUM(CASE WHEN j.status_type_id IN (3,4,5) THEN 1 ELSE 0 END)
AS TotalOpenAtEnd, AVG(DATEDIFF(d, j.report_date, GETDATE()))
AS AvgDaysOpen FROM job_ticket j WHERE j.report_date BETWEEN @startdate AND @enddate)
GROUP BY j.location_id
) x
ON x.location_id = l.location_id
ORDER BY l.location_name
Regards,
Hulicat
|
|
|
|
|
Remove the bracket that follows the @enddate parameter.
|
|
|
|
|
Thanks I got it to execute, however the math did not come out correct.
Although, I think I got enought here to figure it out from here.
Thanks to everyone that replied and helped.
Regards,
Hulicat
|
|
|
|
|
I am sure there is a better way but, without knowing your data - try this:
SELECT
Opened,
[Total Open at Start],
[Total Open at End],
[Total Open at End] -
(SELECT
COUNT(*)
FROM
dbo.job_ticket
WHERE
location_id = Source.location_id AND
status_type_id like '[3,4,5]' AND
last_updated between @startdate and @enddate) as [Total Closed],
Client,
[Average days open]
FROM
(SELECT
l.location_id,
l.location_name AS 'Client',
COUNT(*) AS 'Opened',
SUM(CASE
WHEN status_type_id LIKE '[1,2,6,7]' THEN 1
ELSE 0
END) AS 'Total Open at Start',
AVG(datediff(d,report_date,getdate())) AS [Average days open]
FROM
job_ticket j
INNER JOIN
location l
ON (l.location_id = j.location_id)
WHERE
report_date BETWEEN @startdate AND @enddate
GROUP BY
l.location_id,
l.location_name) AS Source
|
|
|
|
|
Thanks Michael,
I got this when I ran it:
Msg 207, Level 16, State 1, Line 14
Invalid column name 'Total Open at End'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'Total Open at End'.
I tried to troubleshoot it with no luck....
I really appreciate the help.
Regards
Regards,
Hulicat
|
|
|
|
|
Whoops, that is the problem with not having data to test with. Try this:
SELECT
Opened,
[Total Open at Start],
(SELECT
COUNT(*)
FROM
dbo.job_ticket
WHERE
location_id = Source.location_id AND
status_type_id like '[3,4,5]' AND
last_updated between @startdate and @enddate) AS [Total Open at End],
(SELECT
COUNT(*)
FROM
dbo.job_ticket
WHERE
location_id = Source.location_id AND
status_type_id like '[3,4,5]' AND
last_updated between @startdate and @enddate) - [Total Open at Start] AS [Total Closed],
Client,
[Average days open]
FROM
(SELECT
l.location_id,
l.location_name AS 'Client',
COUNT(*) AS 'Opened',
SUM(CASE
WHEN status_type_id LIKE '[1,2,6,7]' THEN 1
ELSE 0
END) AS 'Total Open at Start',
AVG(datediff(d,report_date,getdate())) AS [Average days open]
FROM
job_ticket j
INNER JOIN
location l
ON (l.location_id = j.location_id)
WHERE
report_date BETWEEN @startdate AND @enddate
GROUP BY
l.location_id,
l.location_name) AS Source
|
|
|
|
|
Bingo!!!
muchos gracias
Regards,
Hulicat
|
|
|
|
|
Hi
When a input sqlparameter value contain special character(eg.') the SP is not able to execute.When the same query is executed independently it's working fine.
My query is.. Select * from SearchMetadata where title like '%IF I DON''T WIN, THE WORLD WILL SUFFER FOR IT%'
My SP is
ALTER procedure [dbo].[Search_METADATA]
@SearchCrteria varchar(8000)
as
BEGIN
Declare
@Text nvarchar(4000)
Set @Text='Select top 1000 * from vw_SearchMetadata where '+@SearchCrteria
exec (@Text)
END
I have tried with all possible solution.Placed double quote whereever single quote appeared.Tried ESCAPE claue.Couldn't find soluiton
Need suggestion to solve this.Appreciate your help.
Regards
Krish
|
|
|
|
|
chovdry wrote: When a input sqlparameter value contain special character(eg.') the SP is not able to execute
That's because you are injecting into the SQL command. A very bad and dangerous practice. You should read up on SQL Injection Attacks and how to prevent them[^]
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
I am not bothered about sql injection.i want the SP to be executed. Is there a way to escape special characters.
|
|
|
|
|
You should be worried about SQL Injection - your database can be completely trashed or information could be disclosed if the user can sneak in data that you're not handling correctly. Parameters correctly handle any escaping necessary - I believe for SQL Server that they're passed 'out-of-band', not actually replaced in the text passed to the database engine.
There's not much point packing this in a stored procedure. You don't have the flexibility of variable parameter lists.
Instead you should form the query text yourself. Any time you need to insert user data into the query text, instead use a parameter name (@param ) and add a corresponding parameter to the command's parameters collection.
For example:
using ( SqlConnection conn = new SqlConnection( connectString ) )
{
using ( SqlCommand cmd = new SqlCommand( conn ) )
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT top 1000 * from vw_SearchMetadata where ";
if ( title != "" )
{
cmd.CommandText += "title like @title ";
cmd.Parameters.Add( new SqlParameter( "@title", title ) );
}
using ( SqlDataReader rdr = cmd.ExecuteReader() )
{
}
}
} SQL Server will cache the query plan for a parameterised query. It has a slightly lower weight than for a stored procedure, but a higher weight than an auto-parameterized query (where SQL Server tries to figure out what's replaceable itself).
|
|
|
|
|
check this
DECLARE @SearchCrteria varchar(8000)
Declare @Text nvarchar(4000)
SET @SearchCrteria = 'title like ''%IF I DON''''T WIN, THE WORLD WILL SUFFER FOR IT%'''
--print @SearchCrteria
Set @Text='Select top 1000 * from TblA where '+@SearchCrteria
exec (@Text)
Regards
KP
|
|
|
|
|