May 11, 2011 at 8:10 pm
Hello,
I have a problem filtering some of the rows after select. My scenario has multiple widgets reporting their location and an error indicator. I would like to have the result set return the time when the error started and when it left the error condition. I only want to have a record when the widget first error and the following exit. Then the following enter and then the next exit. If a widget does not exit then I need to return NULL for the exit.
Here's a table with data
CREATE TABLE #widgets ( RecId int NULL, RecTime datetime NULL, SerNum int NULL, Summary varchar(50) NULL, Error bit NULL )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 1, '2011/05/10 00:00:00', 1, 'A', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 2, '2011/05/10 00:00:01', 2, 'B', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 3, '2011/05/10 00:00:02', 3, 'C', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 4, '2011/05/10 00:00:10', 1, 'D', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 5, '2011/05/10 00:00:20', 1, 'E', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 6, '2011/05/10 00:00:30', 3, 'F', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 7, '2011/05/10 00:00:33', 1, 'G', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 8, '2011/05/10 00:00:34', 2, 'H', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 9, '2011/05/10 00:00:40', 3, 'I', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 10, '2011/05/10 00:00:45', 3, 'J', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 11, '2011/05/10 00:00:50', 1, 'K', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 12, '2011/05/10 00:01:00', 2, 'L', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 13, '2011/05/10 00:01:10', 1, 'M', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 14, '2011/05/10 00:01:15', 1, 'N', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 15, '2011/05/10 00:01:20', 3, 'O', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 16, '2011/05/10 00:01:30', 2, 'P', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 17, '2011/05/10 00:01:45', 1, 'Q', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 18, '2011/05/10 00:01:47', 2, 'R', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 19, '2011/05/10 00:01:55', 1, 'S', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 20, '2011/05/10 00:01:59', 3, 'T', 0 )
SELECT * FROM #widgets ORDER BY [SerNum],[RecTime]
The best I could do for the SELECT statement that has both the enter and exit data on a row is this:
DECLARE @ErrorText varchar(50)
SET @ErrorText = 'Error'
;WITH Enter_CTE AS (
SELECT [RecId],[SerNum],[RecTime],[Error],@ErrorText AS 'Summary' FROM #widgets WHERE [Error] = 1
),
Exit_CTE AS (
SELECT [RecId],[SerNum],[RecTime],[Error],[Summary] FROM #widgets WHERE [Error] = 0
)
SELECT
e.[RecId] AS 'EnterRecId',e.[SerNum],e.[RecTime] AS 'EnterTime',e.[Error] AS 'EnterError',e.[Summary],
x.[RecId] AS 'ExitRecId', x.[SerNum],x.[RecTime] AS 'ExitTime', x.[Error] AS 'ExitError', x.[Summary]
FROM Enter_CTE e LEFT JOIN Exit_CTE x ON e.[SerNum] = x.[SerNum] AND e.[RecId] < x.[RecId]
--WHERE e.[RecId] < x.[RecId] OR x.[RecId] IS NULL
ORDER BY e.[SerNum],e.[RecTime]
Which returns my required data but some extra rows also
EnterRecIdSerNumEnterTimeEnterErrorSummaryExitRecIdSerNumExitTimeExitErrorSummary
412011-05-10 00:00:10.0001Error712011-05-10 00:00:33.0000G
412011-05-10 00:00:10.0001Error1912011-05-10 00:01:55.0000S
512011-05-10 00:00:20.0001Error712011-05-10 00:00:33.0000G
512011-05-10 00:00:20.0001Error1912011-05-10 00:01:55.0000S
1112011-05-10 00:00:50.0001Error1912011-05-10 00:01:55.0000S
1312011-05-10 00:01:10.0001Error1912011-05-10 00:01:55.0000S
1412011-05-10 00:01:15.0001Error1912011-05-10 00:01:55.0000S
1712011-05-10 00:01:45.0001Error1912011-05-10 00:01:55.0000S
822011-05-10 00:00:34.0001ErrorNULLNULLNULLNULLNULL
1222011-05-10 00:01:00.0001ErrorNULLNULLNULLNULLNULL
1622011-05-10 00:01:30.0001ErrorNULLNULLNULLNULLNULL
1822011-05-10 00:01:47.0001ErrorNULLNULLNULLNULLNULL
932011-05-10 00:00:40.0001Error1032011-05-10 00:00:45.0000J
932011-05-10 00:00:40.0001Error1532011-05-10 00:01:20.0000O
932011-05-10 00:00:40.0001Error2032011-05-10 00:01:59.0000T
This would be my perfect result set:
EnterRecIdSerNumEnterTimeEnterErrorSummaryExitRecIdSerNumExitTimeExitErrorSummary
412011-05-10 00:00:10.0001Error712011-05-10 00:00:33.0000G
1112011-05-10 00:00:50.0001Error1912011-05-10 00:01:55.0000S
822011-05-10 00:00:34.0001ErrorNULLNULLNULLNULLNULL
932011-05-10 00:00:40.0001Error1032011-05-10 00:00:45.0000J
Any help or pointers would be appreciated. What words would I enter into Google to learn how to do this?
Barry
Edit: Added missing variable.
May 11, 2011 at 9:09 pm
which one is the widget number, and what does the Summary column tell us? I was assuming the Summary or the widget numbers would have to be the same, but not sure that follows through in your 'ideal' situation.
Also, your first CTE has a summary value from a variable, assuming that should just come from the Summary column? Or if not, what is it?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 11, 2011 at 10:46 pm
Hello,
Thanks for the response. The SerNum is the widget number. Summary is actually based on three columns that gives its location. There is a one to one relationship with the shown Summary column and the other two columns. I use the two other location columns to generate the Error condition and then get the @ErrorText from another table. I already know how to do that so I tried to simplify the test data.
I realized I forgot the @ErrorText declaration while driving home. I edited my original post.
Barry
May 12, 2011 at 12:59 pm
Colleague threw this together when I got stuck, doesn't have your recordID, which might be a problem for you, let me know how it looks. (Thank Adam, not me :-))
--http://www.sqlservercentral.com/Forums/Topic1107435-338-1.aspx#bm1107467
IF OBJECT_ID('tempdb..#widgets') IS NOT NULL BEGIN DROP TABLE #widgets END
CREATE TABLE #widgets ( RecId int NULL, RecTime datetime NULL, SerNum int NULL, Summary varchar(50) NULL, Error bit NULL )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 1, '2011/05/10 00:00:00', 1, 'A', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 2, '2011/05/10 00:00:01', 2, 'B', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 3, '2011/05/10 00:00:02', 3, 'C', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 4, '2011/05/10 00:00:10', 1, 'D', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 5, '2011/05/10 00:00:20', 1, 'E', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 6, '2011/05/10 00:00:30', 3, 'F', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 7, '2011/05/10 00:00:33', 1, 'G', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 8, '2011/05/10 00:00:34', 2, 'H', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 9, '2011/05/10 00:00:40', 3, 'I', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 10, '2011/05/10 00:00:45', 3, 'J', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 11, '2011/05/10 00:00:50', 1, 'K', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 12, '2011/05/10 00:01:00', 2, 'L', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 13, '2011/05/10 00:01:10', 1, 'M', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 14, '2011/05/10 00:01:15', 1, 'N', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 15, '2011/05/10 00:01:20', 3, 'O', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 16, '2011/05/10 00:01:30', 2, 'P', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 17, '2011/05/10 00:01:45', 1, 'Q', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 18, '2011/05/10 00:01:47', 2, 'R', 1 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 19, '2011/05/10 00:01:55', 1, 'S', 0 )
INSERT INTO #widgets ( RecId, RecTime, SerNum, Summary, Error ) VALUES ( 20, '2011/05/10 00:01:59', 3, 'T', 0 )
SELECT * FROM #widgets ORDER BY [SerNum],[RecTime]
DECLARE @from DATETIME ,
@to DATETIME
SELECT @from = MIN(w.RecTime) ,
@to = DATEADD(s, 1, MAX(w.RecTime))
FROM #widgets AS w ;
WITH ordered
AS ( SELECT * ,
DENSE_RANK() OVER ( PARTITION BY SerNum ORDER BY RecTime ) AS drank
FROM #widgets
) ,
errors
AS ( SELECT o1.SerNum ,
o1.Rectime AS effDate ,
ISNULL(o2.Rectime, DATEADD(s, 1, o1.Rectime)) AS termDate ,
o1.Error AS ERROR
FROM ordered o1
FULL OUTER JOIN ordered o2 ON o2.SerNum = o1.SerNum
AND o1.drank = o2.drank
- 1
WHERE o1.SerNum IS NOT NULL
AND o1.Error = 1
) ,
Dates
AS ( SELECT DATEADD(s, n - 1, @from) AS dt
FROM OHIO_REPORT_DETAILS.dbo.Tally
WHERE n <= DATEDIFF(s, @from, @to) + 1
) ,
toDays
AS ( SELECT e.* ,
D.dt
FROM errors e
JOIN Dates D ON D.dt BETWEEN e.effdate AND e.termdate
) ,
groups
AS ( SELECT td.SerNum ,
td.ERROR ,
td.dt ,
DATEADD(s,
-1
* DENSE_RANK() OVER ( PARTITION BY td.SerNum ORDER BY td.dt ),
td.dt) AS grp
FROM toDays td
) ,
results
AS ( SELECT g.SerNum ,
MIN(g.dt) AS effdate ,
MAX(g.dt) AS termdate
FROM Groups g
GROUP BY g.SerNum ,
g.grp
)
SELECT r.SerNum ,
r.effdate ,
--CASE WHEN w.RecTime IS NOT NULL THEN NULL ELSE r.termdate END AS termdate
CASE WHEN DATEADD(s, -1, r.termdate) = ( SELECT MAX(Rectime)
FROM #widgets winner
WHERE winner.SerNum = r.SerNum
) THEN NULL
ELSE r.termdate
END AS termdate
FROM results r
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 12, 2011 at 2:18 pm
Thanks for the reply. I completely forgot about ranking.
In the Dates CTE, you reference a table OHIO_REPORT_DETAILS.dbo.Tally that has a column n. What is the purpose this table?
Thanks,
Barry
May 12, 2011 at 2:29 pm
bpar (5/12/2011)
Thanks for the reply. I completely forgot about ranking.In the Dates CTE, you reference a table OHIO_REPORT_DETAILS.dbo.Tally that has a column n. What is the purpose this table?
Thanks,
Barry
Oops, sorry about that.
Table that just holds numbers, very useful in many respects, basically allows you to avoid looping. See the Tally Table link in my sig for more info.
You can also create one inline, as a CTE, which code I'm stealing from Jeff Moden in a different thread today:
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
SELECT * FROM cteTally
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 12, 2011 at 3:53 pm
Hello again,
That is really impressive. Many thanks to you, Adam and Jeff Moden.
The result is missing the two Summary columns but I'll get them added.
Sorry I didn't see the your Tally link in your signature. It is very informative. I have another stored procedure that has a CSV as a parameter which the tally table might also help.
Thanks again,
Barry
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply