Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pivot help? ...or other approach? Expand / Collapse
Author
Message
Posted Tuesday, July 8, 2014 1:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 71, Visits: 309
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)
);


Post #1590497
Posted Tuesday, July 8, 2014 2:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 3,313, Visits: 7,151
And what do you need?


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1590506
Posted Tuesday, July 8, 2014 3:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 71, Visits: 309
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!
Post #1590549
Posted Tuesday, July 8, 2014 4:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 3,313, Visits: 7,151
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




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1590553
Posted Wednesday, July 9, 2014 6:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 71, Visits: 309
Thanks.
..and by that I mean: "thank you very much!"

Who are you and why are you so helpful???

Post #1590703
Posted Wednesday, July 9, 2014 8:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 3,313, Visits: 7,151
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.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1590799
Posted Wednesday, July 9, 2014 9:06 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1590805
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse