June 5, 2014 at 12:38 pm
Hi I am trying to build a query which will be used in an automated report to calculate failure rates of systems based on cases opened through support. Here is where I am stuck. Some systems may have multiple cases opened within the same span of another cases however we would consider this one failure:
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase22013-07-12 16:27:50.0002013-07-12 16:29:12.000
System ACase32013-07-12 17:30:32.0002013-07-12 17:40:11.000
System ACase42013-07-12 19:00:24.0002013-07-12 19:04:14.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
Lets say System A generated those 5 cases however Case 2,3 and 4 all happened within the same period as Case 1 so those 4 cases should count as one failure so my end result should be
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
And that system should show me 2 failures. I was thinking of using a temp table but not sure if that is possible as I am stumped on how to compare the dates to be able to validate if they fall within the range of an older case and whether or not to include them into the new Temp Table. Any help or advice would be greatly appreciated.
Thanks
June 5, 2014 at 12:45 pm
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2014 at 1:14 pm
Sorry about that it was real a Noob thing to do 🙂
I am using this code to extract all of my case data and system data from two other tables:
Select systemName, caseNumber, caseCreatedDate,
CASE
WHEN caseResolutionDate IS NULL Then getdate()
ELSE
caseResolutionDate
END as resolutiondate
From caseDB
WHERE systemname COLLATE Database_Default IN (select systemname COLLATE Database_default
from systemDB
)
and caseCreatedDate >= '2013-01-01'
and systemname = systemA
order by systemname, caseCreatedDate ASC
I'm testing this with specific system name right now to make it easier to validate if my logic works. This query returns me this data:
SystemName CaseNumberCaseCreatedDate resolutionDate
SystemA Case1 2013-07-11 13:17:09.0002013-07-15 12:05:03.000
SystemA Case2 2013-07-12 16:27:50.0002013-07-12 16:29:12.000
SystemA Case3 2013-07-12 17:30:32.0002013-07-12 17:40:11.000
SystemA Case4 2013-07-12 19:00:24.0002013-07-12 19:04:14.000
SystemA Case5 2013-10-01 18:02:23.0002013-10-01 18:11:26.000
What I want to do is take the first CaseNumber (Case1) and then check if Case2's created date and End date falls within the date range of Case1. If it does it does not get inserted to into the temp table. If it doesn't then it does. Based on that logic my temp table should look like this:
SystemName CaseNumberCaseCreatedDate resolutionDate
SystemA Case1 2013-07-11 13:17:09.0002013-07-15 12:05:03.000
SystemA Case5 2013-10-01 18:02:23.0002013-10-01 18:11:26.000
Where I am stuck is how to compare the dates and store them into the temp table. Its the first time I try doing something like this in SQL and took a look online to get some ideas but no luck. My instinct was to use a loop with an array but I don't think that is possible in SQL.
Any guidance or help is appreciated. Sorry about the first post. Its the first time I use a forum to ask for help.
Thanks
June 5, 2014 at 1:24 pm
centerman99 (6/5/2014)
Sorry about that it was real a Noob thing to do 🙂
No biggie. I would however suggest you actually do read that article. What we would like to see is ddl (create table scripts) and sample data (insert statements). That way we can setup your issue on our database. What you get in return is tested and accurate code.
And no, we don't need a loop. Loops in sql are very poor performers and should be avoided when possible. Toss up the info we need and we can help you find a solution that works.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 9, 2014 at 3:37 am
centerman99 (6/5/2014)
Hi I am trying to build a query which will be used in an automated report to calculate failure rates of systems based on cases opened through support. Here is where I am stuck. Some systems may have multiple cases opened within the same span of another cases however we would consider this one failure:System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase22013-07-12 16:27:50.0002013-07-12 16:29:12.000
System ACase32013-07-12 17:30:32.0002013-07-12 17:40:11.000
System ACase42013-07-12 19:00:24.0002013-07-12 19:04:14.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
Lets say System A generated those 5 cases however Case 2,3 and 4 all happened within the same period as Case 1 so those 4 cases should count as one failure so my end result should be
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
And that system should show me 2 failures. I was thinking of using a temp table but not sure if that is possible as I am stumped on how to compare the dates to be able to validate if they fall within the range of an older case and whether or not to include them into the new Temp Table. Any help or advice would be greatly appreciated.
Thanks
Hi.....what would you expect to return if case 4 overlapped case 1...eg: (note date change)
System ACase42013-07-12 19:00:24.0002013-07-16 19:04:14.000
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 9, 2014 at 5:41 am
WITH Cases AS (
SELECT * FROM (VALUES
('SystemA', 'Case1', '2013-07-11 13:17:09.000', '2013-07-15 12:05:03.000'),
('SystemA', 'Case2', '2013-07-12 16:27:50.000', '2013-07-12 16:29:12.000'),
('SystemA', 'Case3', '2013-07-12 17:30:32.000', '2013-07-12 17:40:11.000'),
('SystemA', 'Case4', '2013-07-12 19:00:24.000', '2013-07-12 19:04:14.000'),
('SystemA', 'Case5', '2013-10-01 18:02:23.000', '2013-10-01 18:11:26.000')
) d (SystemName, CaseNumber, CaseCreatedDate, resolutionDate)
)
SELECT *
INTO #Cases
FROM Cases;
----------------------------------------------------
-- Method courtesy Itzik Ben-Gan "Packing-Intervals"
WITH CTE AS (
SELECT SystemName, ts = CaseCreatedDate, [Type] = 1,
e = NULL,
s = (2*ROW_NUMBER() OVER (PARTITION BY SystemName ORDER BY CaseCreatedDate))-1
FROM #Cases
UNION ALL
SELECT SystemName, ts = resolutionDate, [Type] = -1,
e = (2*ROW_NUMBER() OVER (PARTITION BY SystemName ORDER BY resolutionDate)),
s = NULL
FROM #Cases
)
SELECT SystemName, SD = MIN(ts), ED = MAX(ts)
FROM ( -- c3
SELECT SystemName, ts,
Snapper = (ROW_NUMBER() OVER(ORDER BY SystemName, ts)+1)/2
FROM ( -- c1
SELECT SystemName, ts, s, e,
se = ROW_NUMBER() OVER (PARTITION BY SystemName ORDER BY ts, Type DESC),
Snapper = (ROW_NUMBER() OVER(ORDER BY SystemName, ts)+1)/2
FROM CTE
) c1
WHERE se IN (e,s)
) c3
GROUP BY SystemName, Snapper
ORDER BY SystemName, Snapper;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 23, 2014 at 12:42 pm
First off Sorry for the delay in replying to this. I was offsite training when you posted this and had a hectic week last week. I finally got a chance to try your response out today and it worked like a charm. Thanks for your help. I sort of understand what is going on in the code you posted but if you have any links on this that explains it in detail it would be greatly appreciated.
Again thanks for your help
June 24, 2014 at 1:43 am
No worries, thanks for the feedback. You'll find some discussion of the method and a link to IBG's original code here.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2014 at 12:58 pm
Awesome thanks
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply