|
This can be done in various ways but one way could be to use a small scalar query in the select statement. Something like:
SELECT User AS OpenedBy,
AuditDatetime AS StartTime,
DATEDIFF(hh, AuditDatetime, ( SELECT MIN(AuditDatetime)
FROM TheTable tt2
WHERE tt2.AuditDatetime > tt.AuditDatetime)
)AS DurationInHours
FROM TheTable tt
WHERE tt.Task = 'Opens job'
Using a query like the above you can get the open/close pair data and you can use that as an inline view. So something like
SELECT AVG(DurationInHours)
FROM (SELECT User AS OpenedBy,
AuditDatetime AS StartTime,
DATEDIFF(hh, AuditDatetime, ( SELECT MIN(AuditDatetime)
FROM TheTable tt2
WHERE tt2.AuditDatetime > tt.AuditDatetime)
)AS DurationInHours
FROM TheTable tt
WHERE tt.Task = 'Opens job') iv
Probably I made a ton of typos but hope you get the idea 
[EDIT]
Just noticed that GuyThiebaut wrote almost same kind of answer at the same time. If the value of TaskId can be used to connect the open/close operations then his approach is better.
The reason I used the date field is that in your example data the same TaskId was opened twice so my interpretation was that the TaskId isn't unique among tasks:
User TaskID Task AuditDatetime
Bob 2 Opens job 1/8/2014 11:14:29
Bob 5 Closes job 2/8/2014 10:12:26
Joe 2 Opens job 2/8/2014 11:01:45
Bob 5 Closes job 3/8/2014 11:44:22
|
|
|
|
|
Hi Mike - I made the assumption that the taskid is the key and that one person may open the task while another may close it.
I don't think we have been given enough information to solve this without guessing 
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I hope that the TaskId is the key. It would greately simplify the situation
|
|
|
|
|
Sorry my data was bad. Hopefully this makes more sense:
1. The time a job takes for a person to close is the difference between when a job is assigned to a user and when that user closes the job.
2. A job can be re-opened which I guess means I need to use MIN and MAX for a given job.
Here's better data:
JobID User TaskID Task AuditDatetime
17 Bob 2 Opens job 1/8/2014 11:14:29
17 Bob 5 Closes job 2/8/2014 10:12:26
22 Joe 2 Opens job 2/8/2014 11:01:45
22 Bob 5 Closes job 3/8/2014 11:44:22
25 Bob 1 Opens job 4/8/2014 11:40:22
25 Joe 3 Assigned job 5/8/2014 11:47:22
25 Joe 5 Closes job 5/8/2014 11:49:22
The jobID is a request made to the service desk. The TaskID can be opens, assigns, closes.
I'll only be able to report on jobs closed too I suppose rather than open jobs too I guess? This is actually pretty complicated as there's also on hold items but I will forget about these for now.
|
|
|
|
|
No problem, if that's the case then perhaps something like
SELECT iv.JobId,
iv.OpenedBy,
iv.ClosedBy,
iv.OpenTime,
iv.CloseTime,
DATEDIFF(hh, iv.OpenTime, iv.CloseTime) AS DurationInHours
FROM (
SELECT DISTINCT
tt.JobId AS JobId,
tt_open.User AS OpenedBy,
tt_close.User AS ClosedBy,
MIN(tt_open.AuditDatetime) OVER (PARTITION BY tt_open.JobId ORDER BY tt_open.AuditDatetime) AS OpenTime,
MAX(tt_close.AuditDatetime) OVER (PARTITION BY tt_close.JobId ORDER BY tt_close.AuditDatetime) AS CloseTime
FROM TheTable tt_open LEFT OUTER JOIN TheTable tt_close
ON tt_open.JobId = tt_close.JobId
WHERE tt_open.TaskId IN (1,2)
AND tt_close.TaskId = 5) iv
Again, sorry about potential typos
|
|
|
|
|
That's perfect and brilliant, thank you very much!
|
|
|
|
|
Glad it helped
|
|
|
|
|
Hello All,
I'm very much interested in learning SSIS and SSAS.
It would be a great help to me if someone can please guide me about books to follow/read to be an expert in SSIS and SSAS.
Thanks & Regards,
Arun
|
|
|
|
|
|
|
I have this report from a customer that gives away product samples, and uses 0.00 as the price.
So I'm suppose to calculate the cost and the price, but there always different during the year.
Anyways; when I calculate this one item, in which they sold 100, but 90 of them were free, the numbers skew and don't make sense. So I figured perhaps I'll create 2 result sets, 1 for no free items and 1 for all free items.
What would you do?, 2 select statements and the reader will return 2 result sets, I'm not sure how to proceed here on this one.
" SELECT " & _
" v.FITEMNO " & _
", SUM(v.FSHIPQTY) " & _
", SUM(v.FCOST) / SUM(v.FSHIPQTY) " & _
", SUM(v.FPRICE) / SUM(v.FSHIPQTY) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf WHERE FITEMNO = v.FITEMNO) AS FREALDESC " & _
", SUM(v.FSHIPQTY * v.FPRICE - v.FSHIPQTY * v.FCOST)" & _
" FROM ARTRS01H.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" AND " & _
" FITEMNO = @FITEMNO " & _
" AND NOT " & _
" FPRICE = 0.00 "
|
|
|
|
|
I think you are on the right track.
Create 2 queries but use a union statement which will combine the result sets into one.
Good luck. 
|
|
|
|
|
Pls. tell me about good and interesting book to clear the concept of Sql,Dynamic Procedures and User defined Functions with good examples.
|
|
|
|
|
I do not read technical books anymore because information is much easier to find online now. I know some people still read books and it does have its place but I would suggest you google for some examples.
Often when I google how to do something in sql this blog is in the result set. http://blog.sqlauthority.com/[^] It is very well written. I highly recommend going through it.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
I have table looks like below.
EMPID QID QDesc Reason Sub Reason Remarks
201 1 Question 1 Reason1 Break Taking break
201 2 Question 2 Reason2 Poor
201 2 Question 2 Reason3 Good
201 2 Question 2 Reason4 Average
201 2 Question 2 Reason5 Good
201 2 Question 2 Reason6 Average
201 2 Question 2 Reason7 Average
201 2 Question 2 Reason8 Average
201 3 Question 3 NA
201 4 Question 4 Reason9
201 5 Question 5 Reason10 Yes
Required Output from the above table should be in the below format. Please help.
Emp ID Question 1 Question 2 Question 3 Question 4 Question 5 Remarks
Reason Sub reason Remarks Reason2 Reason3 to Reason8
modified 15-Oct-14 2:25am.
|
|
|
|
|
This article [^]might help you!
You are looking at a PIVOT table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
So I have 3 Tables
OrderInfo - Unique records
ShipRates - Unique records
OrderInfo_Emails - A list of non-unique records
There are 3 records in orderinfo, but I get 4 results back, because orderinfo_email has 2 records with the same orderinfo id. So I get that, why I get 4 back and not 3
Should I just drop the OrderInfo_Email Join and do something else, some other way, or is there a way to limit the results from the join?
SELECT
oi.OrderInfoID
, oi.OrderDate
, oi.OrderNum
, oi.LoginID
, oi.OrderStatus
, oi.AuthorizationID
, oi.ApprovalCode
, oi.Shipping
, oi.PaymentInfo
, oi.SubTotal
, oi.GrandTotal
, oi.RateAPICode
, oi.TotalNetCharge
, srr.RateName
, oie.MessageID
, oie.DateSent
, oie.SentBy
FROM OrderInfo oi
LEFT JOIN ORDERINFO_EMAIL oie
ON (oie.OrderID = oi.OrderInfoID )
LEFT JOIN Shipping_RealTime_Rates srr
ON (srr.RateAPICode = oi.RateAPICode)
ORDER BY oi.OrderDate DESC
|
|
|
|
|
How do you pick the one you want? What the criteria?
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Oh, the first one is fine,
It's for Abandoned Orders, in which a email is sent to the customer as a reminder. You can send multiple emails to the customer, in which the emails are logged in ORDERINFO_EMAIL, So When you load the Abandoned Orders, if a email record record exist, a little envelope icon appears.
|
|
|
|
|
You may try this:
SELECT
oi.OrderInfoID
, oi.OrderDate
, oi.OrderNum
, oi.LoginID
, oi.OrderStatus
, oi.AuthorizationID
, oi.ApprovalCode
, oi.Shipping
, oi.PaymentInfo
, oi.SubTotal
, oi.GrandTotal
, oi.RateAPICode
, oi.TotalNetCharge
, srr.RateName
, (select count(*) from ORDERINFO_EMAIL oie where oie.OrderID = oi.OrderInfoID) AS email_sent
FROM OrderInfo oi
LEFT JOIN Shipping_RealTime_Rates srr
ON (srr.RateAPICode = oi.RateAPICode)
ORDER BY oi.OrderDate DESC
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
I was thinking the same thing to keep it simple
OK, I'm going with that idea.
Thanks!
|
|
|
|
|
When and why this kind of exception occurs ?
If it occurs then why the SQL Server dont show the column name to which this exception belongs ?
|
|
|
|
|
Vijay G. Yadav wrote: why the SQL Server dont show the column name to which this exception belongs
Because SQL server is a PITA with this error. I have yet to find a simple way to debug this error, rabbiting through all the text fields and checking the length is just irritating.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Vijay G. Yadav wrote: why the SQL Server dont show the column name to which this exception belongs ?
Good question. There's a suggestion to fix this on Connect[^], which has been open since 2008. So far, Microsoft have not responded. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
