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?

On Apr 22, 9:39*am, mcolson <mcolson1... (AT) gmail (DOT) com> wrote:
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)')

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
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

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

On Apr 22, 12:17*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:
Awesome! Thanks, I've never used case statements before.