How can I conditionally ignore rows in QAnalyser? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How can I conditionally ignore rows in QAnalyser?

Author

Topic

dsunbury
Starting Member

2 Posts

Posted - 2011-02-15 : 09:00:29

Hi everyone, I have the following select statement in Query Analyser which basically shows me the average selling price, by product, for each month, over a 12 month time frame.

I created a column that basically shows a "1" if there is a sale in the most recent month (AvPr_12) AND at least one other sale in one of the other months. Otherwise it shows a "0" - I've called this Totals column.

What I would like to do is have Query Analyser show only shows rows where there is a "1" in the Totals column - can someone suggest how I do this? Here is the query:

SELECT
ProductNumber,
CASE WHEN TotalCount_1 = 0 THEN NULL ELSE SumOfPrices_1 / TotalCount_1 END AS AvPr_1,
CASE WHEN TotalCount_2 = 0 THEN NULL ELSE SumOfPrices_2 / TotalCount_2 END AS AvPr_2,
CASE WHEN TotalCount_3 = 0 THEN NULL ELSE SumOfPrices_3 / TotalCount_3 END AS AvPr_3,
CASE WHEN TotalCount_4 = 0 THEN NULL ELSE SumOfPrices_4 / TotalCount_4 END AS AvPr_4,
CASE WHEN TotalCount_5 = 0 THEN NULL ELSE SumOfPrices_5 / TotalCount_5 END AS AvPr_5,
CASE WHEN TotalCount_6 = 0 THEN NULL ELSE SumOfPrices_6 / TotalCount_6 END AS AvPr_6,
CASE WHEN TotalCount_7 = 0 THEN NULL ELSE SumOfPrices_7 / TotalCount_7 END AS AvPr_7,
CASE WHEN TotalCount_8 = 0 THEN NULL ELSE SumOfPrices_8 / TotalCount_8 END AS AvPr_8,
CASE WHEN TotalCount_9 = 0 THEN NULL ELSE SumOfPrices_9 / TotalCount_9 END AS AvPr_9,
CASE WHEN TotalCount_10 = 0 THEN NULL ELSE SumOfPrices_10 / TotalCount_10 END AS AvPr_10,
CASE WHEN TotalCount_11 = 0 THEN NULL ELSE SumOfPrices_11 / TotalCount_11 END AS AvPr_11,
CASE WHEN TotalCount_12 = 0 THEN NULL ELSE SumOfPrices_12 / TotalCount_12 END AS AvPr_12,

CASE WHEN (TotalCount_1 > 0 OR TotalCount_2 > 0 OR TotalCount_3 > 0 OR TotalCount_4 > 0 OR TotalCount_5 > 0 OR TotalCount_6 > 0
OR TotalCount_7 > 0 OR TotalCount_8 > 0 OR TotalCount_9 > 0 OR TotalCount_10 > 0 OR TotalCount_11 > 0) AND TotalCount_12 > 0 THEN 1 ELSE 0 END AS Totals

FROM (SELECT ProductNumber,

SUM(CASE WHEN SaleDate >= '1/1/2010' AND SaleDate < '2/1/2010' THEN SalePrice ELSE 0 END) AS SumOfPrices_1,
SUM(CASE WHEN SaleDate >= '1/1/2010' AND SaleDate < '2/1/2010' THEN 1 ELSE 0 END) AS TotalCount_1,
SUM(CASE WHEN SaleDate >= '2/1/2010' AND SaleDate < '3/1/2010' THEN SalePrice ELSE 0 END) AS SumOfPrices_2,
SUM(CASE WHEN SaleDate >= '2/1/2010' AND SaleDate < '3/1/2010' THEN 1 ELSE 0 END) AS TotalCount_2,
SUM(CASE WHEN SaleDate >= '3/1/2010' AND SaleDate < '4/1/2010' THEN SalePrice ELSE 0 END) AS SumOfPrices_3,
SUM(CASE WHEN SaleDate >= '3/1/2010' AND SaleDate < '4/1/2010' THEN 1 ELSE 0 END) AS TotalCount_3,
SUM(CASE WHEN SaleDate >= '4/1/2010' AND SaleDate < '5/1/2010' THEN SalePrice ELSE 0 END) AS SumOfPrices_4,
SUM(CASE WHEN SaleDate >= '4/1/2010' AND SaleDate < '5/1/2010' THEN 1 ELSE 0 END) AS TotalCount_4,
SUM(CASE WHEN SaleDate >= '5/1/2010' AND SaleDate < '6/1/2010' THEN SalePrice ELSE 0 END) AS SumOfPrices_5,
SUM(CASE WHEN SaleDate >= '5/1/2010' AND SaleDate < '6/1/2010' THEN 1 ELSE 0 END) AS TotalCount_5,
SUM(CASE WHEN SaleDate >= '6/1/2010' AND SaleDate < '7/1/2010' THEN SalePrice ELSE 0 END) AS SumOfPrices_6,
SUM(CASE WHEN SaleDate >= '6/1/2010' AND SaleDate < '7/1/2010' THEN 1 ELSE 0 END) AS TotalCount_6,
SUM(CASE WHEN SaleDate >= '7/1/2010' AND SaleDate < '8/1/2010' THEN SalePrice ELSE 0 END) AS SumOfPrices_7,
SUM(CASE WHEN SaleDate >= '7/1/2010' AND SaleDate < '8/1/2010' THEN 1 ELSE 0 END) AS TotalCount_7,
SUM(CASE WHEN SaleDate >= '8/1/2010' AND SaleDate < '9/1/2010' THEN SalePrice ELSE 0 END) AS SumOfPrices_8,
SUM(CASE WHEN SaleDate >= '8/1/2010' AND SaleDate < '9/1/2010' THEN 1 ELSE 0 END) AS TotalCount_8,
SUM(CASE WHEN SaleDate >= '9/1/2010' AND SaleDate < '10/1/2010' THEN SalePrice ELSE 0 END) AS SumOfPrices_9,
SUM(CASE WHEN SaleDate >= '9/1/2010' AND SaleDate < '10/1/2010' THEN 1 ELSE 0 END) AS TotalCount_9,
SUM(CASE WHEN SaleDate >= '10/1/2010' AND SaleDate < '11/1/2010' THEN SalePrice ELSE 0 END) AS SumOfPrices_10,
SUM(CASE WHEN SaleDate >= '10/1/2010' AND SaleDate < '11/1/2010' THEN 1 ELSE 0 END) AS TotalCount_10,
SUM(CASE WHEN SaleDate >= '11/1/2010' AND SaleDate < '12/1/2010' THEN SalePrice ELSE 0 END) AS SumOfPrices_11,
SUM(CASE WHEN SaleDate >= '11/1/2010' AND SaleDate < '12/1/2010' THEN 1 ELSE 0 END) AS TotalCount_11,
SUM(CASE WHEN SaleDate >= '12/1/2010' AND SaleDate < '1/1/2011' THEN SalePrice ELSE 0 END) AS SumOfPrices_12,
SUM(CASE WHEN SaleDate >= '12/1/2010' AND SaleDate < '1/1/2011' THEN 1 ELSE 0 END) AS TotalCount_12

FROM tSales
where (ProductID = 13)
GROUP BY ProductNumber) AS pom
GO

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-15 : 09:32:28

Try:

quote:
FROM tSales
where (ProductID = 13)
GROUP BY ProductNumber) AS pom
where (TotalCount_1 > 0 OR TotalCount_2 > 0 OR TotalCount_3 > 0 OR TotalCount_4 > 0 OR TotalCount_5 > 0 OR TotalCount_6 > 0
OR TotalCount_7 > 0 OR TotalCount_8 > 0 OR TotalCount_9 > 0 OR TotalCount_10 > 0 OR TotalCount_11 > 0) AND TotalCount_12 > 0
GO

dsunbury
Starting Member

2 Posts

Posted - 2011-02-15 : 09:38:52

Brilliant - thank you for the quick response

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-15 : 10:26:52

quote:
Originally posted by dsunbury

Brilliant - thank you for the quick response

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources