|
I have the following stored prodecure and I need to add the average time of the "Total closed for range" was opened for.
(
@startdate datetime,
@enddate datetime
)
as
select count(*) as 'Total closed for range', Priority_type_name 'Priority', location_name 'Cient', email 'Engineer'
from job_ticket j
inner join priority_type p on p.priority_type_id = j.priority_type_id
inner join tech t on t.client_id = j.assigned_tech_id
inner join location l on l.location_id = j.location_id
where (last_status_update_time between @startdate and @enddate) and status_type_id ='3'
group by l.location_name, t.email, p.Priority_type_name
The where clause is specifying when the last update = 3 which is closed.
I suspect I need a sub query for a field named report_date and my where clause (last_status_update_time between @startdate and @enddate) and status_type_id ='3'?
here is what the table looks like:
ASSET_ID int Checked
ASSIGNED_TECH_ID int Checked
BILLING_RATE_ID int Checked
BILLING_TERM_ID int Checked
CLIENT_ID int Checked
CLOSE_DATE datetime Checked
DELETED int Checked
DEPARTMENT_ID int Checked
DISCOUNT money Checked
DUE_DATE_OVERRIDE datetime Checked
DUE_HOURS_MANUAL int Checked
FIRST_RESPONSE_DATE datetime Checked
IS_HOT int Checked
JOB_COST money Checked
JOB_TICKET_ID int Unchecked
JOB_TIME int Checked
LABOR_TAX_RATE money Checked
LAST_REMINDER_DATE datetime Checked
LAST_STATUS_UPDATE_TIME datetime Checked
LAST_UPDATED datetime Checked
LOCATION_ID int Checked
LOGGED_BY_ID int Checked
MAIL_CC_ADDRESS varchar(255) Checked
MODEL_ID int Checked
PARENT_ID int Checked
PHONE varchar(40) Checked
PO_NUMBER varchar(75) Checked
PRIORITY_TYPE_ID int Checked
PROBLEM_TYPE_ID int Checked
QUESTION_TEXT varchar(4000) Checked
REPORT_DATE datetime Unchecked
ROOM varchar(80) Checked
SEND_CARBON_COPY int Checked
SEND_CLIENT_EMAIL int Checked
SEND_TECH_EMAIL int Checked
SHIPPING money Checked
SHOW_DUE_DATE_ON_CALENDAR int Checked
STATUS_RED_NOTIFICATION_DT datetime Checked
STATUS_TYPE_ID int Checked
STATUS_YELLOW_NOTIFICATION_DT datetime Checked
SUBJECT varchar(255) Checked
SUBSCRIBER_ID int Checked
TASK_ELEMENT_ID int Checked
TECH_GROUP_ID int Checked
TICKET_TIME_AS_OF_LAST_UPDATE int Checked
TRAVEL_COST money Checked
TRAVEL_RATE_ID int Checked
TRAVEL_TIME int Checked
UPDATED_FLAG int Checked
USE_DUE_DATE_OVERRIDE_INTEGER int Checked
WORK_END_DATE datetime Checked
WORK_START_DATE datetime Checked
CC_ADDRESS_FOR_CLIENT varchar(255) Checked
INITIALIZED_CUSTOM_FIELDS int Checked
IS_PRIVATE int Checked
IS_TAX_FREE int Checked
LAST_CLIENT_REMINDER_DATE datetime Checked
LAST_CLIENT_UPDATE datetime Checked
SEND_TO_CLIENT_CC_LIST int Checked
SERVICE_TIME_ENABLED int Checked
SURVEY_RESPONSE_ID int Checked
TASK_ELEMENT_COMPLETE int Checked
UPDATED_BY_TECH_FLAG int Checked
ESCALATION_LEVEL int Checked
Unchecked
Results currently look like:
Total closed for range....... Priority........ Cient......... Engineer ........
24 ............................... Sev1.............. CA ............. swilliams........................
Desired Results:
Total closed for range....... Priority........ Cient......... Engineer ........ Avaerage time open
24 ............................... Sev1.............. CA ............. swilliams........................ value
I can not wrap my head around this for the life of me @ all.....any help or direction would be greatly appreciated.
Regards,
Hulicat
|
|
|
|
|
Got it...Thanks Srinivas!
select count(*) as 'Total closed for range', Priority_type_name
'Priority', location_name 'Cient', email 'Engineer' ,
AVG(datediff(d,report_date,last_status_update_time )) AS [Avaerage
time open]
from job_ticket j
inner join priority_type p on p.priority_type_id = j.priority_type_id
inner join tech t on t.client_id = j.assigned_tech_id
inner join location l on l.location_id = j.location_id
where (last_status_update_time between @startdate and @enddate) and
status_type_id ='3'
group by l.location_name, t.email, p.Priority_type_name
Regards,
Hulicat
|
|
|
|
|
Hi,
I'm trying to get running sql servers in my machine and I'm using SqlDataSourceEnumerator class. The code below does not return any result.
SqlDataSourceEnumerator servers = SqlDataSourceEnumerator.Instance;
DataTable table = servers.GetDataSources();
table is null.
Why can't I get these servers?
Thanks..
Savas Cilve
|
|
|
|
|
Hi there,
Is there a way to include a value from the affected record into a AFTER UPDATE trigger? And if there is a way, can you give me an exapmle, please?
Thanks, Werries
A programmer's life is good... or is it?? Ek dink nie so nie!
|
|
|
|
|
Use the inserted or deleted pseudo-tables depending on whether you want the values after or before the update occurred. Remember that more than one record can be affected by a statement, but that the trigger only fires once, regardless of the number of records affected.
|
|
|
|
|
Thanks for your reply Chris, I appreciate it.
Regards, Werries
A programmer's life is good... or is it?? Ek dink nie so nie!
|
|
|
|
|
Werries wrote: Chris
Who's Chris?
|
|
|
|
|
Hi everybody.....searched all over the place and couldn't find any info: I'm building an ASP.NET maintenance form to query/upsert records into an Oracle table. I haven't worked with PL/SQL in a while, and I can't find any straight forward help on syntax and my 9i Ref manual is at home :oP . ...Anyway, I'm trying to put my stored proc in a package and it won't let me specify the scale & precision of a number. When I desc the table, I see that the field is a
NUMBER(6,5)
So, I did this:
(Inside Package Spec)
Procedure Upsert_xxx(p_startDate IN DATE,
p_stopDate IN DATE,
p_scaleFactor IN Number(6,5)
);
and matched up the Package Body...I keep getting the following msg (SELECT * from USER_ERRORS)
PLS-00103: Encountered the symbol "(" when expecting one of the following:
:= . ) , @ % default character
The symbol ":=" was substituted for "(" to continue.
on the specification of p_scaleFactor inside the Package Spec. wtf???
Welcome my son...Welcome..to the Machine
|
|
|
|
|
Unlike other databases you don't specify the size of the parameter just the type
Create proc xxx_Update(
p_Name IN nvarchar2
,p_age IN number
)
Or even better is:
create proc xxx_Update(
p_Name IN TableName.ColumnName%TYPE
,p_Age IN TableName.ColumnName%TYPE
)
For some concise examples of proc/procedures try http://www.techonthenet.com/oracle/index.php [^]
hth
Al
|
|
|
|
|
thanks for your help...problem is solved.
Welcome my son...Welcome..to the Machine
|
|
|
|
|
Is it possible to create a custom collation. They need a sort that is drastically different than the standard collation (commas before spaces, ignore commas, etc) and there are nearly 10,000 rows in the table in question so manually doing it is not an issue. I can do it at run time with C#, but it seems like it would make more sense to do it on the database.
|
|
|
|
|
hi friend.
in sql server2005 text save in MIME format for Example->
---------------------------------------------------------------------------------------------------
This is a multi-part message in MIME format. ------=_NextPart_034_0265_011ABCC4.0E3F1F48 Content-Type: text/plain; charset=UTF-8;msgr=WAAtAE0ATQBTAC0ASQBNAC0ARgBvAHIAbQBhAHQAOgAgAEYATgA9AE0AUwAlADIAMABTAGgAZQBsAGwAJQAyADAARABsAGcAJQAyADAAMgA7ACAARQBGAD0AOwAgAEMATwA9ADAAOwAgAEMAUwA9ADAAOwAgAFAARgA9ADAACgANAAoADQA Content-Transfer-Encoding: binary piyush ------=_NextPart_034_0265_011ABCC4.0E3F1F48 Content-Type: text/rtf Content-Transfer-Encoding: binary {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Shell Dlg 2;}} {\colortbl ;\red0\green0\blue0;} {\*\generator Msftedit 5.41.15.1507;}\viewkind4\uc1\pard\tx720\cf1\f0\fs20 piyush\par } ------=_NextPart_034_0265_011ABCC4.0E3F1F48--
----------------------------------------------------------------------------------------------------
Input Text is->piyush
so I want to filter only input text. how it possible...
please help me......
Piyush Vardhan Singh
Programmer
TAS NewDelhi
India
9313077379
|
|
|
|
|
Hi all
please anybody help me in this:
"How to export hsqldb into sql serverdb".
thanking you advancedly
Thanks&Regards
RENU
|
|
|
|
|
You'll have to find some common format that both can support. At a very quick glance, you could probably define a 'text table' in HSQLDB (documentation[^]) then use one of the many ways to import a text file into SQL Server (DTS Import Wizard in SQL Server 2000, SQL Server Integration Services import feature in SQL Server 2005, BULK INSERT, bcp).
|
|
|
|
|
I am using two different tables products and product details . Product ID is the primary key of the products table and is the foreign key reference to the Product details table. I want to fetch only five 5 records from the products table and for each productID I need two records from the product details table. It will be like
M1 C11
M1 C12
M2 C21
M2 C22
I am using oracle 9i database. Please help
|
|
|
|
|
Hi
I am not getting you .. explain clearly how you want output from the result
Harini
|
|
|
|
|
We have two tables
One containing the list of products p1,p2,p3,p4,p5
other any contents associated with the products
p1,c1
p1,c2
p1,c3
p2,c4
p2,c5
p2,c6
p3,c7
p4,c8
p4,c9
p4,c10
I would like to get the below resultset
p1,c1
p1,c2
p2,c4
p2,c5
p3,c7
p4,c8
p4,c9
Basically master detail for data binding. My situation is that I need to some x records, let's say 5 products and on pagination next 5 products as a master and 2 contents(at the maximum) within each products, (if one content show only one) and view all button within that(.NET) to show the remaining contents for that product. It's a master detail grid. I want to retrive that kind of resultset from the query
Thanks,
K.P.Kannan
|
|
|
|
|
CREATE TABLE t1(col1 NUMBER)
CREATE TABLE t2(col1 NUMBER,col2 NUMBER)
INSERT INTO t1 VALUES(1);INSERT INTO t1 VALUES(2);INSERT INTO t1 VALUES(3);
INSERT INTO t1 VALUES(4);INSERT INTO t1 VALUES(5);INSERT INTO t1 VALUES(6);
INSERT INTO t2 VALUES(1,1);INSERT INTO t2 VALUES(2,1);INSERT INTO t2 VALUES(3,1);INSERT INTO t2 VALUES(4,1);INSERT INTO t2 VALUES(5,1);INSERT INTO t2 VALUES(6,1);INSERT INTO t2 VALUES(1,2);INSERT INTO t2 VALUES(3,2);INSERT INTO t2 VALUES(4,2);INSERT INTO t2 VALUES(6,2);INSERT INTO t2 VALUES(1,3);
INSERT INTO t2 VALUES(3,3);INSERT INTO t2 VALUES(6,3);
SELECT col1, col2 FROM t2 d WHERE col1 IN
(SELECT col1 FROM
(SELECT ROWNUM AS rownumber1,col1,col2 FROM t2 WHERE col1 IN
(SELECT col1 FROM (SELECT ROWNUM AS rownumber,col1 FROM t1) WHERE rownumber<=3)--WHERE c.col1=col1
)WHERE rownumber1<=2 AND d.col1=col1)
I want a query which will produce the following result.I just tried with the above one which is failing(no result or not compiling).
1 1
1 2
2 1
3 1
3 2
|
|
|
|
|
Hi
I will try out your in SQL Server 2000 and let you know as Oracle is not installed on my machine.
Harini
|
|
|
|
|
Hi
I tried out this query in SQL Server 2000. It is working as expected and I am not sure whether this will work in Oracle
select t2.col1, t2.col2 from t2
inner join t1 on t1.col1 = t2.col2
and t2.col1 in (select top 2 a.col1 from t2 a where a.col2 = t1.col1 order by a.col1)
Harini
|
|
|
|
|
Thank You Harini. I am trying to convert this into Oracle. There are some problems. I will let you know once done
|
|
|
|
|
hi all
How can i do insert multiple rows on Accsess
INSERT INTO demo(id, name, role) VALUES ('demo', 'demo1', 'demo2');
INSERT INTO demo(id, name, role) VALUES ('demo3', 'demo4', 'demo5');
INSERT INTO demo(id, name, role) VALUES ('demo6', 'demo6', 'demo7');
When i insert by SQL have Exception:
Characters found after and of SQL statement
|
|
|
|
|
Can you bit elaborate your problem.
Thanks and Wishes
Navneet Hegde
Nashik
Develop2Program & Program2Develop
|
|
|
|
|
Access/Jet does not support multi-statement batches. You must send a single statement at a time.
|
|
|
|
|
Houdy all,
I have to take over am application that works with MySQL.
I'm new to MySQL, but have worked quite a lot with SQL Server 2k, and 2005
Now, what i got is a bunch of .frm files and one .opt file,
I have down loaded MySQL 5 and installed it on my console.
What do i do next ?
How to create and import data to the MySQL DB?
Or maybe you guys think its best to just migrate to SQL Server...
is there a helpful free tool to use as a database admin interface ?
Regards,
Tzumer Edo.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
