View <>0 returns no results - SQL Server Forums

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

Transact-SQL (2005)

View <>0 returns no results

Author

Topic

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-07-07 : 18:45:51

Does anyone have a link I can read up on to help me with this.

I've created my first View today, I have a number of columns that have value fields, I have these set to SUM and <>0 in the filter, against each column.

Annoyingly, I get no results. However, when I remove the <>0 I get loads of zero results in my Pivot front end.

What's the right way to exclude zero's in the View?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-07 : 19:03:27

Can you post the query? Hard to say what might be wrong without it from your description. Following query will return data if the sum of col1 is not zero.


select
   sum(col1),
   sum(col2)
from
   YourTable
having
   sum(col1) <> 0

If col1 can have null values and all of them happen to be null, you need to decide what you want to return in that case.

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-07-08 : 04:40:07

Thanks for looking, to keep it small, it's the Having bit I've posted I hope this is sufficient.

HAVING (dbo.Tbl_Business.BusinessDesc = 'GBL2 BEVERAGE') AND (SUM(dbo.T003_Trade_Reporting.BBDEverydaySalesNPS) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TAROldActualstodate) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSProductListing) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSProductDelisting) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPlacementInStoreDisplays) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSPlacementShelfSpace) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPlacementSpaceOther) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSPromotionPriceOffPromotions) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPromotionCouponActivity) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToConsumer) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToTrade) <> 0)
AND (SUM(dbo.T003_Trade_Reporting.TDSPromotionOtherPromotion) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPriceDefferredPriceChange) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSPriceEDLP) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSSTIVolumeValueIncentives) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSSTIOtherPerformanceIncentives) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSNPTEAdHocRelationship) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSNPTEOnGoingRelationship) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSTDS) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSProduct) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPlacement) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSPromotion) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPrice) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSSpecificTargetIncentives) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSNPTE) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TAEPromotionNPS) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TAENetIncrementalNPS) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TAETotalSpend) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TAEFixedSpend) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TAEVariableSpend) <> 0)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-08 : 05:41:24

If you are trying to select only if NONE of those sums are zero, the query seems perfectly fine to me. I can think of only one possible reason why you may get results:

If some of those columns are of data type float, the sum may not be exactly zero, even though it appears to be zero in the display.

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-07-08 : 07:13:38

That's very interesting, thank you.

I am indeed using float, is that the best type for numbers such as spend and volume?

I've looked and it does appear I do have some nulls. What's the best way to deal with this?

After SELECT put:

CASE WHEN (SUM(dbo.T003_Trade_Reporting.TAROldActualstodate) AS [TAR Actuals to date] = NULL) THEN 0 ELSE SUM(dbo.T003_Trade_Reporting.TAROldActualstodate) AS [TAR Actuals to date]

For Each Line?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-08 : 07:25:52

NULL is never equal to anything (well, almost never), so that may not be the cause for your query returning incorrect results. Nonetheless, it would be a good idea to take into account NULLs. You can do that using coalesce function (or isnull function). For example if you want to treat nulls as being equivalent to zero, you can do this:


COALESCE(SUM(dbo.T003_Trade_Reporting.TAROldActualstodate),0) <> 0

It is the same idea as what you had in your posting, but is a little bit shorter.

As for data types, float is approximate representation. So if you need exact representation, decimal would be better, but it may use more storage than float - but in most cases that would not be a consideration.

If you want to keep floats, you could define a small number and check to see if the sum is greater than that. For example:


declare @epsilon float;
set @epsilon = 0.01;

ABS(COALESCE(SUM(dbo.T003_Trade_Reporting.TAROldActualstodate),0)) > @epsilon
...

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-07-08 : 07:57:44

Awesome, I shall hae a play. Thanks for you time.

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-07-10 : 07:34:09

Sorry having a little trouble:

I've replaced:

SELECT CONVERT(varchar(10), dbo.T003_Trade_Reporting.ImportDate, 121) AS [Import Date], dbo.Tbl_Business.BusinessDesc,
dbo.T003_Trade_Reporting.SalesOrganization, dbo.Tbl_SalesOrganization.SalesOrganizationDesc, dbo.Tbl_Category.CategoryDesc,
dbo.Tbl_SubCategory.SubCategoryDesc, dbo.Tbl_DistributionChannel.DistributionChannelDesc, dbo.Tbl_CL3.CL3Desc, dbo.Tbl_CL4.CL4Desc,
dbo.Tbl_CL5.CL5Desc, dbo.Tbl_CalendarYearMonth.CalendarYearMonthDesc, dbo.T003_Trade_Reporting.TradeActivity, dbo.Tbl_Activity.ActivityDesc,
dbo.T003_Trade_Reporting.MKTPLUserStatus, dbo.T003_Trade_Reporting.TradeActivityFrom, dbo.T003_Trade_Reporting.TradeActivityTo,
dbo.T003_Trade_Reporting.Type, dbo.T003_Trade_Reporting.CustomerChannel3, SUM(dbo.T003_Trade_Reporting.BBDEverydaySalesNPS)
AS [Everyday Sales NPS], SUM(dbo.T003_Trade_Reporting.TAROldActualstodate) AS [TAR Actuals to date],
SUM(dbo.T003_Trade_Reporting.TDSProductListing) AS [Product Listing], SUM(dbo.T003_Trade_Reporting.TDSProductDelisting) AS [Product Delisting],
SUM(dbo.T003_Trade_Reporting.TDSPlacementInStoreDisplays) AS [Placement InStore Displays],
SUM(dbo.T003_Trade_Reporting.TDSPlacementShelfSpace) AS [Placement Shelf Space], SUM(dbo.T003_Trade_Reporting.TDSPlacementSpaceOther)
AS [Placement Space Other], SUM(dbo.T003_Trade_Reporting.TDSPromotionPriceOffPromotions) AS [Promotion Price Off Promotions],
SUM(dbo.T003_Trade_Reporting.TDSPromotionCouponActivity) AS [Promotion Coupon Activity],
SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToConsumer) AS [Promotion Free Goods To Consumer],
SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToTrade) AS [Promotion Free Goods To Trade],
SUM(dbo.T003_Trade_Reporting.TDSPromotionOtherPromotion) AS [Promotion Other Promotion],
SUM(dbo.T003_Trade_Reporting.TDSPriceDefferredPriceChange) AS [Price Defferred Price Change], SUM(dbo.T003_Trade_Reporting.TDSPriceEDLP)
AS [Price EDLP], SUM(dbo.T003_Trade_Reporting.TDSSTIVolumeValueIncentives) AS [Volume Value Incentives],
SUM(dbo.T003_Trade_Reporting.TDSSTIOtherPerformanceIncentives) AS [Other Performance Incentives],
SUM(dbo.T003_Trade_Reporting.TDSNPTEAdHocRelationship) AS [NPTE AdHoc Relationship],
SUM(dbo.T003_Trade_Reporting.TDSNPTEOnGoingRelationship) AS [NPTE OnGoing Relationship], SUM(dbo.T003_Trade_Reporting.TDSTDS) AS TDS,
SUM(dbo.T003_Trade_Reporting.TDSProduct) AS Product, SUM(dbo.T003_Trade_Reporting.TDSPlacement) AS Placement,
SUM(dbo.T003_Trade_Reporting.TDSPromotion) AS Promotion, SUM(dbo.T003_Trade_Reporting.TDSPrice) AS Price,
SUM(dbo.T003_Trade_Reporting.TDSSpecificTargetIncentives) AS [Specific Target Incentives], SUM(dbo.T003_Trade_Reporting.TDSNPTE) AS NPTE,
SUM(dbo.T003_Trade_Reporting.TAEPromotionNPS) AS [Promotion NPS], SUM(dbo.T003_Trade_Reporting.TAENetIncrementalNPS) AS [Net Incremental NPS],
SUM(dbo.T003_Trade_Reporting.TAETotalSpend) AS [Total Spend], SUM(dbo.T003_Trade_Reporting.TAEFixedSpend) AS [Fixed Spend],
SUM(dbo.T003_Trade_Reporting.TAEVariableSpend) AS [Variable Spend]

with:

SELECT CONVERT(varchar(10), dbo.T003_Trade_Reporting.ImportDate, 121) AS [Import Date], dbo.Tbl_Business.BusinessDesc,dbo.T003_Trade_Reporting.SalesOrganization, dbo.Tbl_SalesOrganization.SalesOrganizationDesc, dbo.Tbl_Category.CategoryDesc,dbo.Tbl_SubCategory.SubCategoryDesc, dbo.Tbl_DistributionChannel.DistributionChannelDesc, dbo.Tbl_CL3.CL3Desc, dbo.Tbl_CL4.CL4Desc, dbo.Tbl_CL5.CL5Desc, dbo.Tbl_CalendarYearMonth.CalendarYearMonthDesc, dbo.T003_Trade_Reporting.TradeActivity, dbo.Tbl_Activity.ActivityDesc, dbo.T003_Trade_Reporting.MKTPLUserStatus, dbo.T003_Trade_Reporting.TradeActivityFrom, dbo.T003_Trade_Reporting.TradeActivityTo,
dbo.T003_Trade_Reporting.Type, dbo.T003_Trade_Reporting.CustomerChannel3,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSProduct),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSPlacement),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSPromotion),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSPrice),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSSpecificTargetIncentives),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSNPTE),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TAEPromotionNPS),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TAENetIncrementalNPS),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TAETotalSpend),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TAEFixedSpend),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TAEVariableSpend),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.BBDEverydaySalesNPS),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TAROldActualstodate),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSProductListing),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSProductDelisting),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSPlacementInStoreDisplays),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSPlacementShelfSpace),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSPlacementSpaceOther),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSPromotionCouponActivity),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSPromotionPriceOffPromotions),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToConsumer),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToTrade),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSPromotionOtherPromotion),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSPriceDefferredPriceChange),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSPriceEDLP),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSSTIVolumeValueIncentives),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSSTIOtherPerformanceIncentives),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSNPTEAdHocRelationship),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSNPTEOnGoingRelationship),0) <> 0,
COALESCE(SUM(dbo.T003_Trade_Reporting.TDSTDS),0) <> 0

But I get the error:

Error List of Function Arguments '<' not recognised.

I added some () but that just changed the Error to:

Error List of Function Arguments '' not recognised.

Is this anything to do with the Alias?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-10 : 07:52:21

quote:
Originally posted by PanzerAttack

Sorry having a little trouble:
But I get the error:
Is this anything to do with the Alias?

The query you posted seems incomplete. There is no FROM clause or WHERE clause in it. The conditionals such as "COALESCE(SUM(dbo.T003_Trade_Reporting.TDSProduct), 0) <> 0," should be in the where clause.

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-07-10 : 08:28:43

Sorry I was trying to keep it small and readable. I have no WHERE should I put where I have the HAVING?

SELECT CONVERT(varchar(10), dbo.T003_Trade_Reporting.ImportDate, 121) AS [Import Date], dbo.Tbl_Business.BusinessDesc,
dbo.T003_Trade_Reporting.SalesOrganization, dbo.Tbl_SalesOrganization.SalesOrganizationDesc, dbo.Tbl_Category.CategoryDesc,
dbo.Tbl_SubCategory.SubCategoryDesc, dbo.Tbl_DistributionChannel.DistributionChannelDesc, dbo.Tbl_CL3.CL3Desc, dbo.Tbl_CL4.CL4Desc,
dbo.Tbl_CL5.CL5Desc, dbo.Tbl_CalendarYearMonth.CalendarYearMonthDesc, dbo.T003_Trade_Reporting.TradeActivity, dbo.Tbl_Activity.ActivityDesc,
dbo.T003_Trade_Reporting.MKTPLUserStatus, dbo.T003_Trade_Reporting.TradeActivityFrom, dbo.T003_Trade_Reporting.TradeActivityTo,
dbo.T003_Trade_Reporting.Type, dbo.T003_Trade_Reporting.CustomerChannel3, SUM(dbo.T003_Trade_Reporting.BBDEverydaySalesNPS)
AS [Everyday Sales NPS], SUM(dbo.T003_Trade_Reporting.TAROldActualstodate) AS [TAR Actuals to date],
SUM(dbo.T003_Trade_Reporting.TDSProductListing) AS [Product Listing], SUM(dbo.T003_Trade_Reporting.TDSProductDelisting) AS [Product Delisting],
SUM(dbo.T003_Trade_Reporting.TDSPlacementInStoreDisplays) AS [Placement InStore Displays],
SUM(dbo.T003_Trade_Reporting.TDSPlacementShelfSpace) AS [Placement Shelf Space], SUM(dbo.T003_Trade_Reporting.TDSPlacementSpaceOther)
AS [Placement Space Other], SUM(dbo.T003_Trade_Reporting.TDSPromotionPriceOffPromotions) AS [Promotion Price Off Promotions],
SUM(dbo.T003_Trade_Reporting.TDSPromotionCouponActivity) AS [Promotion Coupon Activity],
SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToConsumer) AS [Promotion Free Goods To Consumer],
SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToTrade) AS [Promotion Free Goods To Trade],
SUM(dbo.T003_Trade_Reporting.TDSPromotionOtherPromotion) AS [Promotion Other Promotion],
SUM(dbo.T003_Trade_Reporting.TDSPriceDefferredPriceChange) AS [Price Defferred Price Change], SUM(dbo.T003_Trade_Reporting.TDSPriceEDLP)
AS [Price EDLP], SUM(dbo.T003_Trade_Reporting.TDSSTIVolumeValueIncentives) AS [Volume Value Incentives],
SUM(dbo.T003_Trade_Reporting.TDSSTIOtherPerformanceIncentives) AS [Other Performance Incentives],
SUM(dbo.T003_Trade_Reporting.TDSNPTEAdHocRelationship) AS [NPTE AdHoc Relationship],
SUM(dbo.T003_Trade_Reporting.TDSNPTEOnGoingRelationship) AS [NPTE OnGoing Relationship], SUM(dbo.T003_Trade_Reporting.TDSTDS) AS TDS,
SUM(dbo.T003_Trade_Reporting.TDSProduct) AS Product, SUM(dbo.T003_Trade_Reporting.TDSPlacement) AS Placement,
SUM(dbo.T003_Trade_Reporting.TDSPromotion) AS Promotion, SUM(dbo.T003_Trade_Reporting.TDSPrice) AS Price,
SUM(dbo.T003_Trade_Reporting.TDSSpecificTargetIncentives) AS [Specific Target Incentives], SUM(dbo.T003_Trade_Reporting.TDSNPTE) AS NPTE,
SUM(dbo.T003_Trade_Reporting.TAEPromotionNPS) AS [Promotion NPS], SUM(dbo.T003_Trade_Reporting.TAENetIncrementalNPS) AS [Net Incremental NPS],
SUM(dbo.T003_Trade_Reporting.TAETotalSpend) AS [Total Spend], SUM(dbo.T003_Trade_Reporting.TAEFixedSpend) AS [Fixed Spend],
SUM(dbo.T003_Trade_Reporting.TAEVariableSpend) AS [Variable Spend]
FROM dbo.T003_Trade_Reporting LEFT OUTER JOIN
dbo.Tbl_Category ON dbo.T003_Trade_Reporting.Category = dbo.Tbl_Category.Category LEFT OUTER JOIN
dbo.Tbl_DistributionChannel ON dbo.T003_Trade_Reporting.DistributionChannel = dbo.Tbl_DistributionChannel.DistributionChannel LEFT OUTER JOIN
dbo.Tbl_SubCategory ON dbo.T003_Trade_Reporting.SubCategory = dbo.Tbl_SubCategory.SubCategory LEFT OUTER JOIN
dbo.Tbl_Business ON dbo.T003_Trade_Reporting.Business = dbo.Tbl_Business.Business LEFT OUTER JOIN
dbo.Tbl_SalesOrganization ON dbo.T003_Trade_Reporting.SalesOrganization = dbo.Tbl_SalesOrganization.SalesOrganization LEFT OUTER JOIN
dbo.Tbl_CL3 ON dbo.T003_Trade_Reporting.CL3 = dbo.Tbl_CL3.CL3 LEFT OUTER JOIN
dbo.Tbl_CL5 ON dbo.T003_Trade_Reporting.CL5 = dbo.Tbl_CL5.CL5 LEFT OUTER JOIN
dbo.Tbl_Activity ON dbo.T003_Trade_Reporting.TradeActivity = dbo.Tbl_Activity.Activity LEFT OUTER JOIN
dbo.Tbl_CL4 ON dbo.T003_Trade_Reporting.CL4 = dbo.Tbl_CL4.CL4 LEFT OUTER JOIN
dbo.Tbl_CalendarYearMonth ON dbo.T003_Trade_Reporting.CalendarYearMonth = dbo.Tbl_CalendarYearMonth.CalendarYearMonth
GROUP BY CONVERT(varchar(10), dbo.T003_Trade_Reporting.ImportDate, 121), dbo.Tbl_Business.BusinessDesc, dbo.T003_Trade_Reporting.SalesOrganization,
dbo.Tbl_SalesOrganization.SalesOrganizationDesc, dbo.Tbl_Category.CategoryDesc, dbo.Tbl_SubCategory.SubCategoryDesc,
dbo.Tbl_DistributionChannel.DistributionChannelDesc, dbo.Tbl_CL3.CL3Desc, dbo.Tbl_CL4.CL4Desc, dbo.Tbl_CL5.CL5Desc,
dbo.Tbl_CalendarYearMonth.CalendarYearMonthDesc, dbo.T003_Trade_Reporting.TradeActivity, dbo.Tbl_Activity.ActivityDesc,
dbo.T003_Trade_Reporting.MKTPLUserStatus, dbo.T003_Trade_Reporting.TradeActivityFrom, dbo.T003_Trade_Reporting.TradeActivityTo,
dbo.T003_Trade_Reporting.Type, dbo.T003_Trade_Reporting.CustomerChannel3
HAVING (dbo.Tbl_Business.BusinessDesc = 'GBL2 BEVERAGE') AND (SUM(dbo.T003_Trade_Reporting.BBDEverydaySalesNPS) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TAROldActualstodate) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSProductListing) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSProductDelisting) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPlacementInStoreDisplays) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSPlacementShelfSpace) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPlacementSpaceOther) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSPromotionPriceOffPromotions) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPromotionCouponActivity) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToConsumer) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToTrade) <> 0)
AND (SUM(dbo.T003_Trade_Reporting.TDSPromotionOtherPromotion) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPriceDefferredPriceChange) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSPriceEDLP) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSSTIVolumeValueIncentives) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSSTIOtherPerformanceIncentives) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSNPTEAdHocRelationship) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSNPTEOnGoingRelationship) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSTDS) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSProduct) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPlacement) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSPromotion) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPrice) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSSpecificTargetIncentives) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSNPTE) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TAEPromotionNPS) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TAENetIncrementalNPS) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TAETotalSpend) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TAEFixedSpend) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TAEVariableSpend) <> 0)

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-07-10 : 09:09:48

That seems to have worked, I'll finish it off and confirm thanks again.

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-07-10 : 09:41:40

That Didn't work, same result in the Pivot. Could you just confirm this should be in the HAVING Section or SHOULD I Create a WHERE?

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-07-10 : 09:52:47

I'm taking a break I've given myself a headache. I've added a WHERE Section and that didn't work. When I added it to the HAVING Section if added it to the SELECT Section and the result in the Pivot is the same.

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-07-10 : 10:22:24

Latest Code:

SELECT CONVERT(varchar(10), dbo.T003_Trade_Reporting.ImportDate, 121) AS [Import Date], dbo.Tbl_Business.BusinessDesc,
dbo.T003_Trade_Reporting.SalesOrganization, dbo.Tbl_SalesOrganization.SalesOrganizationDesc, dbo.Tbl_Category.CategoryDesc,
dbo.Tbl_SubCategory.SubCategoryDesc, dbo.Tbl_DistributionChannel.DistributionChannelDesc, dbo.Tbl_CL3.CL3Desc, dbo.Tbl_CL4.CL4Desc,
dbo.Tbl_CL5.CL5Desc, dbo.Tbl_CalendarYearMonth.CalendarYearMonthDesc, dbo.T003_Trade_Reporting.TradeActivity, dbo.Tbl_Activity.ActivityDesc,
dbo.T003_Trade_Reporting.MKTPLUserStatus, dbo.T003_Trade_Reporting.TradeActivityFrom, dbo.T003_Trade_Reporting.TradeActivityTo,
dbo.T003_Trade_Reporting.Type, dbo.T003_Trade_Reporting.CustomerChannel3, COALESCE (SUM(dbo.T003_Trade_Reporting.BBDEverydaySalesNPS), 0)
AS Expr1, COALESCE (SUM(dbo.T003_Trade_Reporting.TAROldActualstodate), 0) AS Expr2, COALESCE (SUM(dbo.T003_Trade_Reporting.TDSProductListing),
0) AS Expr3, COALESCE (SUM(dbo.T003_Trade_Reporting.TDSProductDelisting), 0) AS Expr4,
COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPlacementInStoreDisplays), 0) AS Expr5,
COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPlacementShelfSpace), 0) AS Expr6,
COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPlacementSpaceOther), 0) AS Expr7,
COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPromotionPriceOffPromotions), 0) AS Expr8,
COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPromotionCouponActivity), 0) AS Expr9,
COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToConsumer), 0) AS Expr10,
COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToTrade), 0) AS Expr11,
COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPromotionOtherPromotion), 0) AS Expr12,
COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPriceDefferredPriceChange), 0) AS Expr13, COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPriceEDLP), 0)
AS Expr14, COALESCE (SUM(dbo.T003_Trade_Reporting.TDSSTIVolumeValueIncentives), 0) AS Expr15,
COALESCE (SUM(dbo.T003_Trade_Reporting.TDSSTIOtherPerformanceIncentives), 0) AS Expr16,
COALESCE (SUM(dbo.T003_Trade_Reporting.TDSNPTEAdHocRelationship), 0) AS Expr17,
COALESCE (SUM(dbo.T003_Trade_Reporting.TDSNPTEOnGoingRelationship), 0) AS Expr18, COALESCE (SUM(dbo.T003_Trade_Reporting.TDSTDS), 0)
AS Expr19, COALESCE (SUM(dbo.T003_Trade_Reporting.TDSProduct), 0) AS Expr20, COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPlacement), 0)
AS Expr21, COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPromotion), 0) AS Expr22, COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPrice), 0) AS Expr23,
COALESCE (SUM(dbo.T003_Trade_Reporting.TDSSpecificTargetIncentives), 0) AS Expr24, COALESCE (SUM(dbo.T003_Trade_Reporting.TDSNPTE), 0)
AS Expr25, COALESCE (SUM(dbo.T003_Trade_Reporting.TAEPromotionNPS), 0) AS Expr26,
COALESCE (SUM(dbo.T003_Trade_Reporting.TAENetIncrementalNPS), 0) AS Expr27, COALESCE (SUM(dbo.T003_Trade_Reporting.TAETotalSpend), 0)
AS Expr28, COALESCE (SUM(dbo.T003_Trade_Reporting.TAEFixedSpend), 0) AS Expr29, COALESCE (SUM(dbo.T003_Trade_Reporting.TAEVariableSpend), 0)
AS Expr30
FROM dbo.T003_Trade_Reporting LEFT OUTER JOIN
dbo.Tbl_Category ON dbo.T003_Trade_Reporting.Category = dbo.Tbl_Category.Category LEFT OUTER JOIN
dbo.Tbl_DistributionChannel ON dbo.T003_Trade_Reporting.DistributionChannel = dbo.Tbl_DistributionChannel.DistributionChannel LEFT OUTER JOIN
dbo.Tbl_SubCategory ON dbo.T003_Trade_Reporting.SubCategory = dbo.Tbl_SubCategory.SubCategory LEFT OUTER JOIN
dbo.Tbl_Business ON dbo.T003_Trade_Reporting.Business = dbo.Tbl_Business.Business LEFT OUTER JOIN
dbo.Tbl_SalesOrganization ON dbo.T003_Trade_Reporting.SalesOrganization = dbo.Tbl_SalesOrganization.SalesOrganization LEFT OUTER JOIN
dbo.Tbl_CL3 ON dbo.T003_Trade_Reporting.CL3 = dbo.Tbl_CL3.CL3 LEFT OUTER JOIN
dbo.Tbl_CL5 ON dbo.T003_Trade_Reporting.CL5 = dbo.Tbl_CL5.CL5 LEFT OUTER JOIN
dbo.Tbl_Activity ON dbo.T003_Trade_Reporting.TradeActivity = dbo.Tbl_Activity.Activity LEFT OUTER JOIN
dbo.Tbl_CL4 ON dbo.T003_Trade_Reporting.CL4 = dbo.Tbl_CL4.CL4 LEFT OUTER JOIN
dbo.Tbl_CalendarYearMonth ON dbo.T003_Trade_Reporting.CalendarYearMonth = dbo.Tbl_CalendarYearMonth.CalendarYearMonth
GROUP BY CONVERT(varchar(10), dbo.T003_Trade_Reporting.ImportDate, 121), dbo.Tbl_Business.BusinessDesc, dbo.T003_Trade_Reporting.SalesOrganization,
dbo.Tbl_SalesOrganization.SalesOrganizationDesc, dbo.Tbl_Category.CategoryDesc, dbo.Tbl_SubCategory.SubCategoryDesc,
dbo.Tbl_DistributionChannel.DistributionChannelDesc, dbo.Tbl_CL3.CL3Desc, dbo.Tbl_CL4.CL4Desc, dbo.Tbl_CL5.CL5Desc,
dbo.Tbl_CalendarYearMonth.CalendarYearMonthDesc, dbo.T003_Trade_Reporting.TradeActivity, dbo.Tbl_Activity.ActivityDesc,
dbo.T003_Trade_Reporting.MKTPLUserStatus, dbo.T003_Trade_Reporting.TradeActivityFrom, dbo.T003_Trade_Reporting.TradeActivityTo,
dbo.T003_Trade_Reporting.Type, dbo.T003_Trade_Reporting.CustomerChannel3
HAVING (dbo.Tbl_Business.BusinessDesc = 'GBL2 BEVERAGE') AND (COALESCE (SUM(dbo.T003_Trade_Reporting.BBDEverydaySalesNPS), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TAROldActualstodate), 0) <> 0) AND (COALESCE (SUM(dbo.T003_Trade_Reporting.TDSProductListing), 0) <> 0)
AND (COALESCE (SUM(dbo.T003_Trade_Reporting.TDSProductDelisting), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPlacementInStoreDisplays), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPlacementShelfSpace), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPlacementSpaceOther), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPromotionPriceOffPromotions), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPromotionCouponActivity), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToConsumer), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToTrade), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPromotionOtherPromotion), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPriceDefferredPriceChange), 0) <> 0) AND (COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPriceEDLP), 0)
<> 0) AND (COALESCE (SUM(dbo.T003_Trade_Reporting.TDSSTIVolumeValueIncentives), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TDSSTIOtherPerformanceIncentives), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TDSNPTEAdHocRelationship), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TDSNPTEOnGoingRelationship), 0) <> 0) AND (COALESCE (SUM(dbo.T003_Trade_Reporting.TDSTDS), 0) <> 0)
AND (COALESCE (SUM(dbo.T003_Trade_Reporting.TDSProduct), 0) <> 0) AND (COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPlacement), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPromotion), 0) <> 0) AND (COALESCE (SUM(dbo.T003_Trade_Reporting.TDSPrice), 0) <> 0) AND
(COALESCE (SUM(dbo.T003_Trade_Reporting.TDSSpecificTargetIncentives), 0) <> 0) AND (COALESCE (SUM(dbo.T003_Trade_Reporting.TDSNPTE), 0) <> 0)
AND (COALESCE (SUM(dbo.T003_Trade_Reporting.TAEPromotionNPS), 0) <> 0) AND (COALESCE (SUM(dbo.T003_Trade_Reporting.TAENetIncrementalNPS), 0)
<> 0) AND (COALESCE (SUM(dbo.T003_Trade_Reporting.TAETotalSpend), 0) <> 0) AND (COALESCE (SUM(dbo.T003_Trade_Reporting.TAEFixedSpend), 0) <> 0)
AND (COALESCE (SUM(dbo.T003_Trade_Reporting.TAEVariableSpend), 0) <> 0)

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-07-10 : 10:44:33

Right I'm happy hat might be working. Thanks again

mrphantuan
Starting Member

5 Posts

Posted - 2011-07-11 : 22:37:56

quote:
Originally posted by PanzerAttack

Sorry I was trying to keep it small and readable. I have no WHERE should I put where I have the HAVING?

SELECT CONVERT(varchar(10), dbo.T003_Trade_Reporting.ImportDate, 121) AS [Import Date], dbo.Tbl_Business.BusinessDesc,
dbo.T003_Trade_Reporting.SalesOrganization, dbo.Tbl_SalesOrganization.SalesOrganizationDesc, dbo.Tbl_Category.CategoryDesc,
dbo.Tbl_SubCategory.SubCategoryDesc, dbo.Tbl_DistributionChannel.DistributionChannelDesc, dbo.Tbl_CL3.CL3Desc, dbo.Tbl_CL4.CL4Desc,
dbo.Tbl_CL5.CL5Desc, dbo.Tbl_CalendarYearMonth.CalendarYearMonthDesc, dbo.T003_Trade_Reporting.TradeActivity, dbo.Tbl_Activity.ActivityDesc,
dbo.T003_Trade_Reporting.MKTPLUserStatus, dbo.T003_Trade_Reporting.TradeActivityFrom, dbo.T003_Trade_Reporting.TradeActivityTo,
dbo.T003_Trade_Reporting.Type, dbo.T003_Trade_Reporting.CustomerChannel3, SUM(dbo.T003_Trade_Reporting.BBDEverydaySalesNPS)
AS [Everyday Sales NPS], SUM(dbo.T003_Trade_Reporting.TAROldActualstodate) AS [TAR Actuals to date],
SUM(dbo.T003_Trade_Reporting.TDSProductListing) AS [Product Listing], SUM(dbo.T003_Trade_Reporting.TDSProductDelisting) AS [Product Delisting],
SUM(dbo.T003_Trade_Reporting.TDSPlacementInStoreDisplays) AS [Placement InStore Displays],
SUM(dbo.T003_Trade_Reporting.TDSPlacementShelfSpace) AS [Placement Shelf Space], SUM(dbo.T003_Trade_Reporting.TDSPlacementSpaceOther)
AS [Placement Space Other], SUM(dbo.T003_Trade_Reporting.TDSPromotionPriceOffPromotions) AS [Promotion Price Off Promotions],
SUM(dbo.T003_Trade_Reporting.TDSPromotionCouponActivity) AS [Promotion Coupon Activity],
SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToConsumer) AS [Promotion Free Goods To Consumer],
SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToTrade) AS [Promotion Free Goods To Trade],
SUM(dbo.T003_Trade_Reporting.TDSPromotionOtherPromotion) AS [Promotion Other Promotion],
SUM(dbo.T003_Trade_Reporting.TDSPriceDefferredPriceChange) AS [Price Defferred Price Change], SUM(dbo.T003_Trade_Reporting.TDSPriceEDLP)
AS [Price EDLP], SUM(dbo.T003_Trade_Reporting.TDSSTIVolumeValueIncentives) AS [Volume Value Incentives],
SUM(dbo.T003_Trade_Reporting.TDSSTIOtherPerformanceIncentives) AS [Other Performance Incentives],
SUM(dbo.T003_Trade_Reporting.TDSNPTEAdHocRelationship) AS [NPTE AdHoc Relationship],
SUM(dbo.T003_Trade_Reporting.TDSNPTEOnGoingRelationship) AS [NPTE OnGoing Relationship], SUM(dbo.T003_Trade_Reporting.TDSTDS) AS TDS,
SUM(dbo.T003_Trade_Reporting.TDSProduct) AS Product, SUM(dbo.T003_Trade_Reporting.TDSPlacement) AS Placement,
SUM(dbo.T003_Trade_Reporting.TDSPromotion) AS Promotion, SUM(dbo.T003_Trade_Reporting.TDSPrice) AS Price,
SUM(dbo.T003_Trade_Reporting.TDSSpecificTargetIncentives) AS [Specific Target Incentives], SUM(dbo.T003_Trade_Reporting.TDSNPTE) AS NPTE,
SUM(dbo.T003_Trade_Reporting.TAEPromotionNPS) AS [Promotion NPS], SUM(dbo.T003_Trade_Reporting.TAENetIncrementalNPS) AS [Net Incremental NPS],
SUM(dbo.T003_Trade_Reporting.TAETotalSpend) AS [Total Spend], SUM(dbo.T003_Trade_Reporting.TAEFixedSpend) AS [Fixed Spend],
SUM(dbo.T003_Trade_Reporting.TAEVariableSpend) AS [Variable Spend]
FROM dbo.T003_Trade_Reporting LEFT OUTER JOIN
dbo.Tbl_Category ON dbo.T003_Trade_Reporting.Category = dbo.Tbl_Category.Category LEFT OUTER JOIN
dbo.Tbl_DistributionChannel ON dbo.T003_Trade_Reporting.DistributionChannel = dbo.Tbl_DistributionChannel.DistributionChannel LEFT OUTER JOIN
dbo.Tbl_SubCategory ON dbo.T003_Trade_Reporting.SubCategory = dbo.Tbl_SubCategory.SubCategory LEFT OUTER JOIN
dbo.Tbl_Business ON dbo.T003_Trade_Reporting.Business = dbo.Tbl_Business.Business LEFT OUTER JOIN
dbo.Tbl_SalesOrganization ON dbo.T003_Trade_Reporting.SalesOrganization = dbo.Tbl_SalesOrganization.SalesOrganization LEFT OUTER JOIN
dbo.Tbl_CL3 ON dbo.T003_Trade_Reporting.CL3 = dbo.Tbl_CL3.CL3 LEFT OUTER JOIN
dbo.Tbl_CL5 ON dbo.T003_Trade_Reporting.CL5 = dbo.Tbl_CL5.CL5 LEFT OUTER JOIN
dbo.Tbl_Activity ON dbo.T003_Trade_Reporting.TradeActivity = dbo.Tbl_Activity.Activity LEFT OUTER JOIN
dbo.Tbl_CL4 ON dbo.T003_Trade_Reporting.CL4 = dbo.Tbl_CL4.CL4 LEFT OUTER JOIN
dbo.Tbl_CalendarYearMonth ON dbo.T003_Trade_Reporting.CalendarYearMonth = dbo.Tbl_CalendarYearMonth.CalendarYearMonth
GROUP BY CONVERT(varchar(10), dbo.T003_Trade_Reporting.ImportDate, 121), dbo.Tbl_Business.BusinessDesc, dbo.T003_Trade_Reporting.SalesOrganization,
dbo.Tbl_SalesOrganization.SalesOrganizationDesc, dbo.Tbl_Category.CategoryDesc, dbo.Tbl_SubCategory.SubCategoryDesc,
dbo.Tbl_DistributionChannel.DistributionChannelDesc, dbo.Tbl_CL3.CL3Desc, dbo.Tbl_CL4.CL4Desc, dbo.Tbl_CL5.CL5Desc,
dbo.Tbl_CalendarYearMonth.CalendarYearMonthDesc, dbo.T003_Trade_Reporting.TradeActivity, dbo.Tbl_Activity.ActivityDesc,
dbo.T003_Trade_Reporting.MKTPLUserStatus, dbo.T003_Trade_Reporting.TradeActivityFrom, dbo.T003_Trade_Reporting.TradeActivityTo,
dbo.T003_Trade_Reporting.Type, dbo.T003_Trade_Reporting.CustomerChannel3
HAVING (dbo.Tbl_Business.BusinessDesc = 'GBL2 BEVERAGE') AND (SUM(dbo.T003_Trade_Reporting.BBDEverydaySalesNPS) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TAROldActualstodate) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSProductListing) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSProductDelisting) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPlacementInStoreDisplays) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSPlacementShelfSpace) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPlacementSpaceOther) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSPromotionPriceOffPromotions) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPromotionCouponActivity) <> 0) AND du hoc singapore
(SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToConsumer) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPromotionFreeGoodsToTrade) <> 0)
AND (SUM(dbo.T003_Trade_Reporting.TDSPromotionOtherPromotion) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPriceDefferredPriceChange) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSPriceEDLP) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSSTIVolumeValueIncentives) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSSTIOtherPerformanceIncentives) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSNPTEAdHocRelationship) <> 0) AND
du hoc (SUM(dbo.T003_Trade_Reporting.TDSNPTEOnGoingRelationship) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSTDS) <> 0) AND noi that fami
(SUM(dbo.T003_Trade_Reporting.TDSProduct) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPlacement) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSPromotion) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSPrice) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TDSSpecificTargetIncentives) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TDSNPTE) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TAEPromotionNPS) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TAENetIncrementalNPS) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TAETotalSpend) <> 0) AND (SUM(dbo.T003_Trade_Reporting.TAEFixedSpend) <> 0) AND
(SUM(dbo.T003_Trade_Reporting.TAEVariableSpend) <> 0)

Right I'm happy hat might be working

mrphantuan phan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources