|
Ambertje wrote: a View doesn't allow to create indexes
Yes it does[^]. You just need to make sure the view is created WITH SCHEMABINDING.
However, to properly use an indexed view on any edition lower than SQL Enterprise, you'll need to use the WITH ( NOEXPAND ) query hint.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
My view contains aggregates, outer joins, subqueries so With Schemabinding wont work
|
|
|
|
|
Ambertje wrote: The view gets the data out of the bottom (STAGING) area in the form of text files.
Ah I thought you were doing something sensible, expecting performance from text files is not going to work.
I suggest you bulk copy the text files into staging tables, do attempt to apply transformations, and then use a stored proc to do the transforms from staging to your destination.
I would expect an order of magnitude performance gain with proper tuning of the staging indexes and procedure design. If the data is extreme you may want to look into dropping staging indexes before the insert and reimplementing them after import.
Doing transforms on the way in is a really lousy design, I know SSIS and all the tools do this but it is just wrong. Extract, Load, Transform
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try adding WITH SCHEMABINDING to your functions, between the RETURNS <type> and As lines.
http://www.sqlservercentral.com/blogs/sqlstudies/2014/09/15/i-schemabound-my-scalar-udf-and-you-wont-believe-what-happened-next/[^]
ISNUMERIC is not reliable. All of the following values will be considered numeric, but cannot be cast to an integer:
- ISNUMERIC('-')
- ISNUMERIC('.')
- ISNUMERIC('-$.')
If you're using SQL 2012 or higher, use the new TRY_PARSE function[^] instead. Otherwise, test that the string doesn't contain any non-numeric characters:
If @ValueString Like '%[^0-9]%' Or @ArticleString Like '%[^0-9]%'
Begin
set @result = 0
End
Assuming you're starting with a date or datetime / datetime2 type, your CONVERT_DATE_TO_NUMERIC function can be replaced with:
Convert(int, Convert(char(8), YourDateColumn, 112))
Passing 112 to the Convert function[^] formats the date as yyyyMMdd. Converting that to an integer gives the same result as your function.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I'm going to suggest something weird.
It looks as if you are using the scalar function to convert a single column into another. That could also be done before your query is executed, and that "might" save time. Might, as I haven't tested it 
Add the required int-column for the date, and run an update-query to do the conversion. Your table is now one column larger, but lost a calculation. You could keep the converted date consistent by updating them over a trigger when a record is inserted/modified. If you're not allowed to modify the original table, you can always create a new table and add the primary key of the original.
That way you can eliminate the need for the function completely, and move the calculation to the moment the records is inserted/updated. That delay may be hardly noticeable when manipulating a single record, while it adds up to a lot of calculations if it has to be done "on the fly".
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I am making a stored procedure in "SQL SERVER 2008" for "item allotment" Which has a different items and has to assign to particular user.
What I am doing is, A table has a three columns 1-Total_item,2-Allotted_item and 3-Available_item , When i click the button ASSIGN it has to assign the item to particular User and the available item must have to be shown in Available_item column
Suppose---Before allotment
Total_item Allotted_item Available_item
10 0 0
8 0 0
Now After Allotment----
Total_item Allotted_item Available_item
10 1 9
8 2 6
I am going wrong Please help How could I make This happen
Thanks
|
|
|
|
|
How does your item table look like?
Wrong is evil and must be defeated. - Jeff Ello
Any organization is like a tree full of monkeys. The monkeys on top look down and see a tree full of smiling faces. The monkeys on the bottom look up and see nothing but assholes.
|
|
|
|
|
I HAVE 2 TABLES
TABLE 1 = "ITEM ALLOTMENT" it has initially "total_item" = 10 or some other value, "allotted_item" = 0 and "item_available" = 0
CONSISTS OF COLUMNS---
ITEM_NAME NVARCHAR(50)
TOTAL_ITEM--- INT
ALLOTTED_ITEM---- INT
ITEM_AVAILABLE--- INT
AND
TABLE 2 = ITEM it only stores the allocation details after clicking assign button
CONSISTS OF COLUMNS---
ITEM_ID (PRIMARY KEY)
PRODUCT_NO -- nvarchar(50)
ITEM_NAME -- nvarchar(50)
now after allotment details are filled in "ITEM" table that this "item_name" is allocated to this "product_no" and what is has to done in "item allotment" table is just after allotment its value must be updated that means column name
"allotted_item" value must be increased and in "item_available" column the subtracted value is have to shown example
If in "total_item" there is 10 items stored after allocation "allotted_item" value will be increased to 1 and subtracted value will be shown in "item_available" column
all these have to be done on assign button click. So, I that i am using Stored Procedure
Please help me out ,I am failing to which.
Thanks
|
|
|
|
|
What is problem you are facing?, You should use the Begin and end trans block and update both tables in the same block on click of assign button.
|
|
|
|
|
The Problem is in deceasing the value after assigning item.
|
|
|
|
|
What is the command/Query you are using to do so and what issue do you face.
|
|
|
|
|
This isn't normalized.
I'd suggest that you drop table1 and add an allotted column to table2 which contains to whom it's allotted (null or ID).
Then you use a query to get the state of allottments, such as:
SELECT ITEM_NAME
,Count(ITEM_ID) Total_item
,Count(Alloted) Allotted_item
,Count(ITEM_ID) - Count(Alloted) Available_item
FROM Table2
GROUP BY ITEM_NAME
Wrong is evil and must be defeated. - Jeff Ello
Any organization is like a tree full of monkeys. The monkeys on top look down and see a tree full of smiling faces. The monkeys on the bottom look up and see nothing but assholes.
|
|
|
|
|
I have a table with students' details and email address and I send email to them
I want to see the logs for each student if it was sent successfully or not. for example:
STUD_1 student1@email.com failed
STUD_2 student2@email.com sent
Can we do it like this? The idea is to get the sent_status from
sysmail_mailitems (or any other accurate/relevant source) for each student.
|
|
|
|
|
If I understand the question correctly, you can use a query like
SELECT mailitem_id,
recipients,
subject,
sent_status
FROM sysmail_allitems
For table description, see sysmail_allitems[^]
|
|
|
|
|
can we link sysmail_allitems to my student table? to see if the email was sent or not to a particular student?
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
