|
Please help
I want to create stored procedure using two tables &
Want to commit data of first table & rollback .
Thnx
CARe CAREER
|
|
|
|
|
are the DML operations are done one after the other in batch or jumbled
Regards
KP
|
|
|
|
|
I want to create a stored procedure in which while inserting or updating a record if a flag is set then i have to insert some other values in the parent table and based on the id which is auto generated by the parent table i have to insert that value into the child table How can i perform this.
I have got already two procedures for inserting and updating the data into the tables.
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
IF @flag = <value>
BEGIN
<insert data into master table>
<insert data into child table>
END
@@IDENTITY returns last generated value in Identity column.
Regards
KP
|
|
|
|
|
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
|
|
|
|
|