March 24, 2009 at 12:37 pm
Here is my simplified schema
-- STATIC TABLES FOR PRODUCT/STANDARDS Setup
Table A
---------
ProductID As INT
StandardID AS INT
--A product can be linked to one or more standards we need to meet
Table B
---------
StandardID AS INT
TestTypeID AS INT
-- A standard can be composed of multiple required tests
-- PRODUCT TESTING RESULT TABLES
Table C
---------
ReportID AS INT
ProductID AS INT
-- A test report can be linked to multiple products
Table D
----------
ReportID AS INT
ReportDate AS DATETIME
-- A test report for which a product might have many reports over time with various test types attached.
Table E
---------
ReportID AS INT
TestTypeID AS INT
PassFail AS BIT
So to put it in 'english':
Product 123 requires us to meet STANDARD A
STANDARD A requires us to conduct TEST1, TEST2, TEST3
Product 123 has REPORT1 which has passing test results for TEST1, and a failing test report for TYPE2, and no results for TEST 3
Product 123 has another test REPORT2 which has passing test results for TEST2, and for TEST 3.
Together I should be able to certify PRODUCT 123 to STANDARD A.
What I would like to do is answer the questions:
1. Which standards do I meet (i.e. have passing test reports that make up that ENTIRE standard) given a product ID given the most recent test of a given type
2. Which standards do I NOT meet (opposite of above)
I am having a real problem visualizing the query to give me the above results.
A quick tip or some pseudo code would be much appreciated. I just need some inspiration here.
Thank you.
March 24, 2009 at 12:53 pm
russellmhirsch (3/24/2009)
Table D
----------
ReportID AS INT
ReportDate AS DATETIME
-- A test report for which a product might have many reports over time with various test types attached.
Table E
---------
ReportID AS INT
TestTypeID AS INT
PassFail AS BIT
Hi
Is there a reason why you cant have all fields from Table D and E in the same table?
March 24, 2009 at 12:54 pm
Russell,
We can help you out, but it would be very helpful if you could take a minute and set up your simplified schema with a little sample data in a script and then show your expected results like my example below. It makes it much easier for us to test our code to be sure we have a good solution for you.
Thanks,
Bob
------------------------------------------------------------------------------------------------
create table #A (productID int, standardID int)
insert into #A
select 1,1 union all
select 2,1 union all
select 3,2
/*
expected results (I'm just making this up)
1,1
1,2
2,3
*/
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 24, 2009 at 12:56 pm
There would be a ton of duplicated data. Table D has 20-30 fields that aren't relevant to my query (so not included in the basic schema), but I don't want to duplicate for every test type performed. TestDate is one of those fields, it is common for all test types performed.
March 24, 2009 at 1:00 pm
russellmhirsch (3/24/2009)
I am having a real problem visualizing the query to give me the above results.
A quick tip or some pseudo code would be much appreciated. I just need some inspiration here.
Here is my attempt to inspire you without giving you the actual code!
Select your product, join on standard, join on test type
product1 - stantardA - test1
product1 - stantardA - test2
product1 - stantardA - test3
LEFT JOIN
Select the most recent test for any product for any test type
product1 - test1 - pass - dateX
product1 - test2 - pass - dateY
product1 - test3 - pass - dateY
Let me know if you have questions.
Maxim
March 24, 2009 at 1:03 pm
russellmhirsch (3/24/2009)
There would be a ton of duplicated data. Table D has 20-30 fields that aren't relevant to my query (so not included in the basic schema), but I don't want to duplicate for every test type performed. TestDate is one of those fields, it is common for all test types performed.
My bad, I read everything all over again and I understand, a report contains many test.
The first time around I thought D was the actual test... oops 🙂
March 24, 2009 at 2:25 pm
You'll need to expand and translated this to work with your actual tables and actual results, but here's something that will test them.
-- Create test tables
create table #A (
ProductID int primary key,
StandardID int);
--
create table #B (
StandardID int,
TestTypeID int);
--
create table #C (
ReportID int,
ProductID int);
--
create table #D (
ReportID int,
ReportDate datetime);
--
create table #E (
ReportID int,
TestTypeID int,
PassFail bit);
--
-- Populate test tables
insert into #A (ProductID, StandardID)
select 1, 1;
--
insert into #B (StandardID, TestTypeID)
select 1, 1 union all
select 1, 2 union all
select 1, 3;
--
insert into #C (ReportID, ProductID)
select 1, 1;
--
insert into #D (ReportID, ReportDate)
select 1, getdate()-1 union all
select 2, getdate();
--
insert into #E (ReportID, TestTypeID, PassFail)
select 1, 1, 1 union all
select 1, 2, 0 union all
select 2, 2, 1 union all
select 2, 3, 1;
--
-- Begin test
;with
LastTest as
(select TestTypeID, max(ReportDate) as LastReport
from #D
inner join #E
on #D.ReportID = #E.ReportID
group by TestTypeID),
LastResults as
(select PassFail
from #D
inner join #E
on #D.ReportID = #E.ReportID
inner join LastTest
on #D.ReportDate = LastReport
and #E.TestTypeID = LastTest.TestTypeID)
select
case
when exists
(select *
from LastResults
where PassFail = 0)
then 'No'
else 'Yes'
end as PassesStandard;
You can add input parameters to this, or turn it into something that can be joined to a table of products and a table of standards. Could be pivoted after that, or left as multi-row, depending on what you need to do with it.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply