Ed Murphy wrote:
> philipbennett25 wrote:
>
> > I have a table of test results and each time the result is written up,
> > it writes the software level of the test machine along with the test
> > result, the date and what was tested. Sample below as Test_Table
> >
> > DATE PROD PASS_FAIL SWLVL
> > -----------------------------------------------------------------------
> > 011206 21 F sw1
> > 021206 21 P sw1
> > 031206 21 P sw1
> > 051206 21 F sw2
> > 061206 21 P sw2
> > 081206 21 P sw3
> > 091206 21 P sw3
> >
> > Using:
> >
> > Select SWLVL,
> > SUM(CASE WHEN PASS_FAIL ='P' THEN 1 ELSE 0 END) AS PASS,
> > SUM(CASE WHEN PASS_FAIL ='F' THEN 1 ELSE 0 END) AS FAIL
> > FROM Test_Table
> > WHERE PROD =21
> > GROUP BY SWLVL
> > ORDER BY SWLVL
> >
> > The output would be:
> >
> > SWLVL PASS FAIL
> > --------------------------------------------
> > sw1 2 1
> > sw2 1 1
> > sw3 2 0
> >
> >
> > -I would like the output to include the first time that new SW was
> > used, ie show the date of the first use of the SWLVL against the
> > aggregate. The output would look something like this:
> >
> > FIRST_USE SWLVL PASS FAIL
> > ---------------------------------------------------------------------------
> > 010106 sw1 2 1
> > 051206 sw2 1 1
> > 091206 sw3 2 0
>
> Why do you have sw3 -> 091206 rather than 081206? Assuming that's a
> typo, simply add MIN(FIRST_USE) to the SELECT portion of your query.

philipbennett25 wrote:

> I have a table of test results and each time the result is written up,
> it writes the software level of the test machine along with the test
> result, the date and what was tested. Sample below as Test_Table
>
> DATE PROD PASS_FAIL SWLVL
> -----------------------------------------------------------------------
> 011206 21 F sw1
> 021206 21 P sw1
> 031206 21 P sw1
> 051206 21 F sw2
> 061206 21 P sw2
> 081206 21 P sw3
> 091206 21 P sw3
>
> Using:
>
> Select SWLVL,
> SUM(CASE WHEN PASS_FAIL ='P' THEN 1 ELSE 0 END) AS PASS,
> SUM(CASE WHEN PASS_FAIL ='F' THEN 1 ELSE 0 END) AS FAIL
> FROM Test_Table
> WHERE PROD =21
> GROUP BY SWLVL
> ORDER BY SWLVL
>
> The output would be:
>
> SWLVL PASS FAIL
> --------------------------------------------
> sw1 2 1
> sw2 1 1
> sw3 2 0
>
>
> -I would like the output to include the first time that new SW was
> used, ie show the date of the first use of the SWLVL against the
> aggregate. The output would look something like this:
>
> FIRST_USE SWLVL PASS FAIL
> ---------------------------------------------------------------------------
> 010106 sw1 2 1
> 051206 sw2 1 1
> 091206 sw3 2 0