INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Hide zero value

Hide zero value

(OP)
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.

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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close