July 8, 2014 at 1:53 pm
This is the type of data that I'm working with:
USE TestDB
GO
CREATE TABLE testtable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CompanyNumber nvarchar(50),
UserNumber nvarchar(50),
Birthdate datetime,
TestDate datetime,
TestCode nvarchar(50),
Score nvarchar(50),
FailFlag nvarchar(50)
);
July 8, 2014 at 2:01 pm
July 8, 2014 at 3:27 pm
Sorry - apparently posting from work is a problem.
This is the type of data that I'm working with:
USE TestDB
GO
CREATE TABLE testtable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CompanyNumber nvarchar(50),
UserNumber nvarchar(50),
Birthdate datetime,
TestDate datetime,
TestCode nvarchar(50),
Score nvarchar(50),
FailFlag nvarchar(50)
);
INSERT INTO testtable
SELECT
'M101', '123123', '1990-04-22', '2014-07-07', 'TestA', '11', NULL UNION ALL SELECT
'M101', '123123', '1990-04-22', '2014-07-07', 'TestB', '4.1', NULL UNION ALL SELECT
'M101', '123123', '1990-04-22', '2014-07-07', 'TestC', '76', NULL UNION ALL SELECT
'M101', '123123', '1990-04-22', '2014-07-07', 'TestD', '14', NULL UNION ALL SELECT
'M625', '689228', '1984-09-13', '2014-07-07', 'TestA', '11', NULL UNION ALL SELECT
'M625', '689228', '1984-09-13', '2014-07-07', 'TestB', '4.0', NULL UNION ALL SELECT
'M625', '689228', '1984-09-13', '2014-07-07', 'TestC', '80', NULL UNION ALL SELECT
'M625', '689228', '1984-09-13', '2014-07-07', 'TestD', '47', 'Fail' UNION ALL SELECT
'M425', '585629', '1988-06-06', '2014-07-07', 'TestA', '15', 'Fail' UNION ALL SELECT
'M425', '585629', '1988-06-06', '2014-07-07', 'TestB', '4.1', NULL UNION ALL SELECT
'M425', '585629', '1988-06-06', '2014-07-07', 'TestC', '53', NULL UNION ALL SELECT
'M425', '585629', '1988-06-06', '2014-07-07', 'TestD', '36', NULL UNION ALL SELECT
'M829', '282220', '1985-03-13', '2014-07-07', 'TestA', '10', NULL UNION ALL SELECT
'M829', '282220', '1985-03-13', '2014-07-07', 'TestB', '3.8', NULL UNION ALL SELECT
'M829', '282220', '1985-03-13', '2014-07-07', 'TestC', '60', NULL UNION ALL SELECT
'M829', '282220', '1985-03-13', '2014-07-07', 'TestD', '12', NULL
I would like to formulate a SQL query to achieve this result, with one row per ComapnyNumber:
ComapnyNumber UserNumber Birthdate TestDate TestA TestB TestC TestD
------------- ---------- ---------- ---------- ----- ----- ----- -----
M101 123123 1990-04-22 2014-07-07 11 4.1 76 14
M625 689228 1984-09-13 2014-07-07 11 4.0 80 37
M425 585629 1988-06-06 2014-07-07 15 4.1 53 36
M829 282220 1985-03-13 2014-07-07 10 3.8 60 12
I can do a pivot:
SELECT CompanyNumber, TestA, TestB, TestC, TestD
FROM
(SELECT CompanyNumber, TestCode, Score
FROM dbo.testtable) AS S
PIVOT (
max(Score)
for TestCode in ([TestA], [TestB], [TestC], [TestD])
)
AS T
which yields:
CompanyNumber UserNumber Birthdate TestDate TestA TestB TestC TestD
------------- ---------- ----------------------- ----------------------- ----- ----- ----- -----
M101 123123 1990-04-22 00:00:00.000 2014-07-07 00:00:00.000 11 4.1 76 14
M425 585629 1988-06-06 00:00:00.000 2014-07-07 00:00:00.000 15 4.1 53 36
M625 689228 1984-09-13 00:00:00.000 2014-07-07 00:00:00.000 11 4.0 80 47
M829 282220 1985-03-13 00:00:00.000 2014-07-07 00:00:00.000 10 3.8 60 12
Which is what I want, but... there are additional considerations.
I need to exclude cases where TestA, TestB or TestC have the FailFlag set to 'Fail', but include all TestD results, whether NULL or 'Fail'
They are actually a battery of tests and I need to return the results of the battery.
So, when TestA, TestB or TestC have the FailFlag set to 'Fail' - I want to exclude the results for each test in the battery.
With the data above, I would want this result, because TestA had failed for CompanyNumber M425.
ComapnyNumber UserNumber Birthdate TestDate TestA TestB TestC TestD
------------- ---------- ---------- ---------- ----- ----- ----- -----
M101 123123 1990-04-22 2014-07-07 11 4.1 76 14
M625 689228 1984-09-13 2014-07-07 11 4.0 80 37
M829 282220 1985-03-13 2014-07-07 10 3.8 60 12
If I do this Pivot:
SELECT CompanyNumber, UserNumber, Birthdate, TestDate, FailFlag, TestA, TestB, TestC, TestD
FROM
(SELECT CompanyNumber, UserNumber, Birthdate, TestDate, FailFlag, TestCode, Score
FROM dbo.testtable) AS S
PIVOT (
max(Score)
for TestCode in ([TestA], [TestB], [TestC], [TestD])
)
AS T
I get this:
CompanyNumber UserNumber Birthdate TestDate FailFlag TestA TestB TestC TestD
------------- ---------- ----------------------- ----------------------- -------- ----- ----- ----- -----
M101 123123 1990-04-22 00:00:00.000 2014-07-07 00:00:00.000 NULL 11 4.1 76 14
M425 585629 1988-06-06 00:00:00.000 2014-07-07 00:00:00.000 NULL NULL 4.1 53 36
M425 585629 1988-06-06 00:00:00.000 2014-07-07 00:00:00.000 Fail 15 NULL NULL NULL
M625 689228 1984-09-13 00:00:00.000 2014-07-07 00:00:00.000 NULL 11 4.0 80 NULL
M625 689228 1984-09-13 00:00:00.000 2014-07-07 00:00:00.000 Fail NULL NULL NULL 47
M829 282220 1985-03-13 00:00:00.000 2014-07-07 00:00:00.000 NULL 10 3.8 60 12
I'm not sure where to go from here, or even if I took a good approach to the problem.
Any advice is most appreciated!
July 8, 2014 at 4:05 pm
I came up with 2 solutions, but I changed the pivot to a cross tabs approach. I prefer cross tabs because they give more flexibility and with more than one pivot, you get better performance.
WITH CTE AS(
SELECT CompanyNumber,
UserNumber,
Birthdate,
TestDate,
MAX(CASE WHEN TestCode = 'TestA' AND ISNULL( FailFlag, '') <> 'Fail' THEN Score END) TestA,
MAX(CASE WHEN TestCode = 'TestB' AND ISNULL( FailFlag, '') <> 'Fail' THEN Score END) TestB,
MAX(CASE WHEN TestCode = 'TestC' AND ISNULL( FailFlag, '') <> 'Fail' THEN Score END) TestC,
MAX(CASE WHEN TestCode = 'TestD' THEN Score END) TestD
FROM testtable
GROUP BY CompanyNumber,
UserNumber,
Birthdate,
TestDate
)
SELECT *
FROM CTE
WHERE TestA IS NOT NULL
AND TestB IS NOT NULL
AND TestC IS NOT NULL
SELECT CompanyNumber,
UserNumber,
Birthdate,
TestDate,
MAX(CASE WHEN TestCode = 'TestA' THEN Score END) TestA,
MAX(CASE WHEN TestCode = 'TestB' THEN Score END) TestB,
MAX(CASE WHEN TestCode = 'TestC' THEN Score END) TestC,
MAX(CASE WHEN TestCode = 'TestD' THEN Score END) TestD
FROM testtable t
WHERE NOT EXISTS( SELECT *
FROM testtable x
WHERE x.CompanyNumber = t.CompanyNumber
AND x.UserNumber = t.UserNumber
AND x.TestDate = t.TestDate
AND (x.FailFlag = 'Fail'AND x.TestCode <> 'TestD'))
GROUP BY CompanyNumber,
UserNumber,
Birthdate,
TestDate
July 9, 2014 at 6:35 am
Thanks.
..and by that I mean: "thank you very much!"
Who are you and why are you so helpful???
July 9, 2014 at 8:59 am
I'm Batman shhh.... No, really, I'm a billionaire and philanthropist called Bruce Wayne. 😀
I just found that helping in here keeps me learning and practicing to become better each day. And I like to pay it forward, I've learned a lot in this site.
July 9, 2014 at 9:06 am
Luis Cazares (7/9/2014)
I just found that helping in here keeps me learning and practicing to become better each day. And I like to pay it forward, I've learned a lot in this site.
Well said, Luis.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply