Prev: Yellowfin 5.0 release
Next: SQL-DMO Process Issue
From: mcolson on 22 Apr 2010 10:39
How would I join these statements together into 1 sql statement? They
all have the same criteria for timestamps, but the locations are
different and sometimes I look at a 3rd criteria. Do I need to run
them all individually?
From: mcolson on 22 Apr 2010 11:08
On Apr 22, 9:39 am, mcolson <mcolson1...(a)gmail.com> wrote:
> How would I join these statements together into 1 sql statement?  They
> all have the same criteria for timestamps, but the locations are
> different and sometimes I look at a 3rd criteria.  Do I need to run
> them all individually?

And I forgot to include the statements... Its 10am and it already
feels like a long day.
SELECT COUNT(MFG_SN) AS InnerCount
FROM ChartMES.dbo.RecourceActualEPA_Report
WHERE (Timestamp > '20100422050000') AND (Timestamp <
'20100422143000') AND (Location = N'Inner Mass Spec.') AND (EpaName =
N'leak1')
SELECT COUNT(MFG_SN) AS OuterCount
FROM ChartMES.dbo.RecourceActualEPA_Report
WHERE (Timestamp > '20100422050000') AND (Timestamp <
'20100422143000') AND (Location = N'Outer Mass Spec.') AND (EpaName =
N'leak1')
SELECT COUNT(DISTINCT (MFG_SN)) AS Wrapping
FROM ChartMES.dbo.RecourceActualEPA_Report
WHERE (Timestamp > '20100422050000') AND (Timestamp <
'20100422143000') AND (Location = N'Wrapping')
SELECT COUNT(DISTINCT (MFG_SN)) AS Vacuum
FROM ChartMES.dbo.RecourceActualEPA_Report
WHERE (Timestamp > '20100422050000') AND (Timestamp <
'20100422143000') AND (Location = N'Vacuum')
SELECT COUNT(DISTINCT (MFG_SN)) AS FinalAssembly
FROM ChartMES.dbo.RecourceActualEPA_Report
WHERE (Timestamp > '20100422050000') AND (Timestamp <
'20100422143000') AND (Location = N'Final Assembly (Box)')
From: bill on 22 Apr 2010 12:49
I think you want a result set that looks like this (hope the
formatting doesn't get messed up):

Location row_count_nr
------------ ----------------
In M Spec 28
Wrap 54
Vacuum 19


If so, do this:

SELECT
Location
,COUNT(MFG_SN) AS row_count_nr
FROM
ChartMES.dbo.RecourceActualEPA_Report
WHERE
Timestamp > '20100422050000' AND Timestamp <
'20100422143000'
GROUP BY
Location



If you have to sometimes look for additional criteria, you have two
choices:
1. Do a seperate query for those cases and UNION in the results
2. Use a CASE statement to generate a "category" column on the fly,
wrap the whole thing in CTE, and then include the category in the
GROUP
BY.

In the WHERE clause, you might consider the BETWEEN operator. Be
aware
that BETWEEN is inclusive, so it is equivalent to <= (less than or
equal) and
>= (greater than or equal), not just less than and greater than.

Also, is MFG_SN non-nullable? You may get unexpected results (or
maybe those
are the results you want, depends on the business need) from
COUNT(<nullable column>).
COUNT(*) will count any row no matter what is in it. Depends what you
need.

Thanks,

Bill
From: Plamen Ratchev on 22 Apr 2010 13:17
You can use CASE expressions:

SELECT COUNT(CASE WHEN Location = N'Inner Mass Spec.' THEN MFG_SN END) AS InnerCount,
COUNT(CASE WHEN Location = N'Outer Mass Spec.' THEN MFG_SN END) AS OuterCount,
COUNT(DISTINCT CASE WHEN Location = N'Wrapping' THEN MFG_SN END) AS Wrapping,
COUNT(DISTINCT CASE WHEN Location = N'Vacuum' THEN MFG_SN END) AS Vacuum,
COUNT(DISTINCT CASE WHEN Location = N'Final Assembly (Box)' THEN MFG_SN END) AS FinalAssembly
FROM ChartMES.dbo.RecourceActualEPA_Report
WHERE Timestamp > '20100422050000'
AND Timestamp < '20100422143000'
AND EpaName = N'leak1';

--
Plamen Ratchev
http://www.SQLStudio.com
From: mcolson on 22 Apr 2010 15:31
On Apr 22, 12:17 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> You can use CASE expressions:
>
> SELECT COUNT(CASE WHEN Location = N'Inner Mass Spec.' THEN MFG_SN END) AS InnerCount,
>         COUNT(CASE WHEN Location = N'Outer Mass Spec.' THEN MFG_SN END) AS OuterCount,
>         COUNT(DISTINCT CASE WHEN Location = N'Wrapping' THEN MFG_SN END) AS Wrapping,
>         COUNT(DISTINCT CASE WHEN Location = N'Vacuum' THEN MFG_SN END) AS Vacuum,
>         COUNT(DISTINCT CASE WHEN Location = N'Final Assembly (Box)' THEN MFG_SN END) AS FinalAssembly
> FROM ChartMES.dbo.RecourceActualEPA_Report
> WHERE Timestamp > '20100422050000'
>    AND Timestamp < '20100422143000'
>    AND EpaName = N'leak1';
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

Awesome! Thanks, I've never used case statements before.
 | 
Pages: 1
Prev: Yellowfin 5.0 release
Next: SQL-DMO Process Issue