September 3, 2015 at 8:00 am
Hello,
I am hoping this is possible to do in a select but I cannot seem to get sql for it.
We are running different types of tests per sample unit and logging whether it succeeded or failed. There is no limit to how many times a test will be run per unit. Here is what sample data looks like (0 - failed, 1- passed)
SampleUnitTestIDPassedTestDate
111111 02015-09-03 00:00:01.000
111111 02015-09-03 00:00:05.000
111111 12015-09-03 00:00:10.000
111111 02015-09-03 01:00:01.000
111111 02015-09-03 02:00:05.000
111111 12015-09-03 03:00:10.000
111111 12015-09-03 10:00:27.000
111112 12015-09-03 00:00:01.000
I need to be able to group all failed tries followed by the first succeeded one together as one group.
this is what I need for my output to look like
SampleUnitTestIDPassedTestDate TestGroup
111111 02015-09-03 00:00:01.000 1
111111 02015-09-03 00:00:05.000 1
111111 12015-09-03 00:00:10.000 1
111111 02015-09-03 01:00:01.000 2
111111 02015-09-03 02:00:05.000 2
111111 12015-09-03 03:00:10.000 2
111111 12015-09-03 10:00:27.000 3
111112 12015-09-03 00:00:01.000 1
Any help or ideas would be greatly appreciated. SQL with sample data population is attached
thanks!
September 3, 2015 at 8:17 am
Thanks for the sample data - what a treat 🙂
Have a play with this. To "ice the cake", use DENSE_RANK() to renumber the groups. Ask if you're unsure but you will probably want to play first:
DROP table #mytable
create table #mytable ([SampleUnit] varchar (20), TestID smallint, Passed bit, TestDate datetime)
insert into #mytable
values
('11111',1,0,'2015-09-03 00:00:01.000'),
('11111',1,0,'2015-09-03 00:00:05.000'),
('11111',1,1,'2015-09-03 00:00:10.000'),
('11111',1,0,'2015-09-03 01:00:01.000'),
('11111',1,0,'2015-09-03 02:00:05.000'),
('11111',1,1,'2015-09-03 03:00:10.000'),
('11111',1,1,'2015-09-03 10:00:27.000'),
('11111',2,1,'2015-09-03 00:00:01.000')
SELECT SampleUnit, TestID, Passed, TestDate,
grp = SUM(CAST(Passed AS TINYINT)) OVER(PARTITION BY SampleUnit, testid ORDER BY testdate DESC)
FROM #mytable
ORDER BY SampleUnit, TestID, TestDate
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
September 3, 2015 at 9:11 am
Sweet! I tried that approach with the order by testdate ACS and of course the grouping was wrong. I will figure out the regrouping with dense_rank()!
thanks so much!
September 3, 2015 at 9:13 am
VD (9/3/2015)
Sweet! I tried that approach with the order by testdate ACS and of course the grouping was wrong. I will figure out the regrouping with dense_rank()!thanks so much!
You're welcome! Post back if you get stuck.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply