|
If the students table contains the email address, then you can use that to check if the recipient is equal to student.email. If multiple recipients can be found ind recipients field then you can join using a string search with CHARINDEX function.
So either something like:
SELECT ...
FROM sysmail_allitems sai,
student s
WHERE LOWER(sai.recipients) = LOWER(s.email)
or
SELECT ...
FROM sysmail_allitems sai,
student s
WHERE CHARINDEX(LOWER(s.email), LOWER(sai.recipients)) > 0
|
|
|
|
|
It's usually cleaner to use the ANSI JOIN syntax, so that you keep the join conditions separate from any filter conditions.
SELECT
...
FROM
sysmail_allitems sai
INNER JOIN student s
ON sai.recipients = s.email
Also, most databases don't use a case-sensitive collation, so there shouldn't be any need to use the LOWER function here.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks for the suggestion 
Richard Deeming wrote: It's usually cleaner to use the ANSI JOIN syntax
I usually use ANSI join but in this case I felt it would be more understandable to use the 'old' syntax because the variations for the join itself are quite different.
Richard Deeming wrote: most databases don't use a case-sensitive collation, so there shouldn't be any need to use the LOWER function here.
Not sure about this so I wanted to be on the safe side.
I should have written these reasons to the original post in the first place.
|
|
|
|
|
Hi, I've been asked to write a report which lists the average time it takes for a helpdesk job to be closed. There isn't a closed date as such, the table column is simply an audit date/time which can be for various tasks.
I need to use this column along with another which is an ID of the action. For example:
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
My SQL is very rusty, I'm sure the report is technically possible but I can't for the life of me figure out what the SQL is. Can anybody please help?
I'm aware of the SQL AVG function and also know I may need to GROUP BY the user. I'm thinking some kind of CASE statement perhaps but again I can't think of the SQL to achieve this...
|
|
|
|
|
Try something like this:
select avg(diff)
from
(
select datediff(d,h1.auditdatetime,h2.auditdatetime) diff
from helpdesk h1
join helpdesk h2
on h1.taskid = h2.taskid
and h2.task = 'closes job'
where h1.task = 'opens job'
)x
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
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)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
