Hide zero value

[CarrieRo]

In the following code, I can't figure out how to hide 0 values. The created fields of 'Base Charge' and 'Misc Charges' in some instances are all 0. The four rate descriptions, Base Rate, Fuel Surcharge, Pieces and Weight you will see repeated for each job #. Some of these are 0 on the base charge and 0 on the Misc Charges. I need to hide these rows, example would be Job# 315878, "pieces" both base & misc are -0-. Also Job #316810 both Pieces and weight are both -0-.

The output looks like this:

CustID Job # Rate Description Base Charge Misc Charges
23006 315840 Base Rate $173.90 $0.00
23006 315878 Base Rate $89.25 $0.00
23006 315878 Fuel Surcharge Freight $0.00 $22.31
23006 315878 Pieces $0.00 $0.00
23006 315884 Base Rate $46.35 $0.00
23006 315884 Fuel Surcharge Freight $0.00 $11.59
23006 315884 Pieces $0.00 $0.00
23006 316810 Base Rate $46.35 $0.00
23006 316810 Fuel Surcharge Freight $0.00 $11.59
23006 316810 Pieces $0.00 $0.00
23006 316810 Weight $0.00 $0.00

SQL Code:

DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME

SET @BEGINDATE = '{{{Select a BEGIN date}}}'
SET @ENDDATE = '{{{Select an END date}}}'

SELECT
CONVERT(VARCHAR(10), OS.PickedUp, 1) AS 'Ship Date'
,CONVERT(VARCHAR(10), OS.Delivered, 1) AS 'Deliver Date'
,C.CustID
,OS.OrderID as 'Job #'
,OS.InvoiceNumber as 'Inv #'
,OS.Invoiced as 'Inv Date'
,O.Caller as 'Caller'
,O.OriginName as 'Shipper'
,OriginAddress as 'Pickup Address'
,OriginCity as 'Pickup City'
,OriginState as 'Pickup State'
,OriginZip as 'Pickup Zip'
,O.ReadyTimeFrom as 'Ready Date'
,I.Description as 'Rate Description'
,OT.Description as 'Order Type Description'
,Z.Ratezone3 as 'Pickup Zone #'
,O.DestName as 'Consignee'
,DestAddress as 'Deliver Address'
,DestCity as 'Deliver City'
,DestState as 'Deliver State'
,DestZip as 'Deliver Zip'
,ZZ.RateZone3 as 'Deliver Zone #'
,O.Reference1 as 'RC Code'
,O.Weight
,O.Distance as 'Mileage'
,SUM(CASE WHEN OI.ItemID ='0' THEN OI.Price ELSE 0 END) AS 'Base Charge'
,'' as 'Mod Code 1'
,'' as 'Charge 1'
,'' as 'Multi2'
,'' as 'Mod Code 2'
,'' as 'Charge 2'
,'' as 'Multi2'
,'' as 'Mod Code 3'
,'' as 'Charge 3'
,'' as 'Multi3'
,SUM(CASE WHEN OI.ItemID not in (0) THEN OI.Price ELSE 0 END ) AS 'Misc Charges'
,'' as 'Comments'
,OrderType as 'Order Type Number'


from tblOrder O WITH (NOLOCK)
inner join tblOrderStatus OS WITH (NOLOCK)on OS.OrderID =O.OrderID
inner join tblCustomer C WITH (NOLOCK) on C.CustID = O.CustID
inner join tblOrderItems OI WITH (NOLOCK) on OI.OrderID = O.OrderID
Inner join tblItems I WITH (NOLOCK) ON I.ID = OI.ItemID
inner join tblZones Z WITH (NOLOCK) on Z.Zip = O.OriginZip
inner join tblZones ZZ WITH (NOLOCK) on ZZ.Zip = O.DestZip
inner join tblOrderTypes OT WITH (NOLOCK) on OT.OrderTypeID = O.Ordertype


WHERE
OS.Delivered >= @BeginDate
AND OS.Delivered <= DATEADD(d,1,@EndDate)
AND
C.CustID =<<Enter Customer ID#>>

group by
C.CustID
,OS.PickedUp
,OS.Delivered
,OS.OrderID
,OS.InvoiceNumber
,OS.Invoiced
,O.Caller
,OriginAddress
,OriginCity
,OriginState
,OriginZip
,ReadyTimeFrom
,OS.Pod
,O.DestAddress
,O.DestCity
,O.DestState
,O.DestZip
,O.Weight
,O.Pieces
,O.Distance
,O.OriginName
,O.DestName
,O.Reference1
,O.OrderType
,I.Description
,OT.Description
,Z.RateZone3
,ZZ.RateZone3

ORDER BY OS.OrderID

I am very much SQL "challenged" as I like to call it. I am trying to modify existing code, have successfully so far other than hiding zero values.


Also I just realized I originally posted this in the wrong thread, my apologies, am reposting here.
Thanks you guys have always come thru for me in helping me find a solution, been using your site for about 15 years or so.

 

[fredericofonseca]

before your order by add

having SUM(CASE WHEN OI.ItemID ='0' THEN OI.Price ELSE 0 END) <> 0
and SUM(CASE WHEN OI.ItemID not in (0) THEN OI.Price ELSE 0 END ) <> 0

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[fredericofonseca]

and as another note - with (nolock) should be avoided as hell and only used where it has been determined it is both safe and the only way to do it.

see

http://blogs.msdn.com/b/davidlean/a...lock-hint-other-poor-ideas.aspx?wa=wsignin1.0

for info on it.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[CarrieRo]

Frederico, this did not work. Now no records are being returned. I just need the rows that randomly will be blank in both the Base charge and Misc charge columns to not show up. If either one is populated it should show up. & thx for the NOLOCK info.

 

[fredericofonseca]

sorry my error - and you should have been able to figure it out.

change the AND to an OR

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[CarrieRo]

I told you I am sql challenged, I will remember this in the future. Appreciate the help.