quotes in SQL Statement

I have a select statement in a SP in an SQL 2008 database that contains the following:

SUM(case when b.TrxType = 'R' AND b.LbsTransferred =  0 then b.NoOfCoils else 0 end) As Received

If I have this outside the stored procedure, it works fine...but inside the procedure it states that there is incorrect syntax near 'R'.
If I change the 'R' to ' 'R' ' the error goes away , but the procedure does not return the value of R.

Any ideas?
ITMikeKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
are you using dynamic SQL?
0
ITMikeKAuthor Commented:
Full statement:
 (
        @chkInventory int, @txtCustomerNumber varchar(MAX), @strGrade varchar(max), @strRodSize varchar(max), @strSecondaryRodSize varchar(MAX), @strHeat varchar(max)
)
as
 
BEGIN
 
     
        SET NOCOUNT ON;
 
        DECLARE      @return_value int,
            @strAllocateSQL varchar(max)
   SET @strHeat = NULLIF(@strHeat,'')
       
     

        set @strAllocateSQL = 'SELECT x.* ,(Received-Shipped-Transferred) AS OnHand
                  ,(Received-Allocated) AS Available
                  ,case when Received>0 then cast(Weight/ case when (Received-Shipped-Transferred) = 0 then NULL else (Received-Shipped-Transferred) end as int) else 0 end AS AvgWt
                  FROM (SELECT b.HeatID,b.Customer AS Customer,a.GradeType,a.RodSize,a.HeatNumber,a.Grade,a.VendorNumber,a.Consignment,a.Vendor,a.CustomerNumber,a.CustomerPO, a.PremiumRod,
                  SUM(case when b.TrxType = 'R' AND b.LbsTransferred =  0 then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = 'S'  AND b.TransferToCustomer = 0 then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = 'A' then b.NoOfCoils else 0 end) As Allocated
                   ,SUM(case when b.TrxType = 'R'  AND b.LbsTransferred > 0 then b.NoOfCoils else 0 end) As TransferredIn
                  ,SUM(case when b.TrxType = 'S' AND b.TransferToCustomer > 0 then b.CoilsShipped else 0 end) As TransferredOut
                  ,SUM(case when b.TrxType = 'S' AND b.TransferToCustomer > 0 then b.CoilsShipped else 0 end) As Transferred
                  ,SUM(b.LbsRecd-b.LBSShipped+b.LbsTransferred) As Weight
                  ,MIN(case when b.TrxType = 'R' then b.TrxDate end) As EarlyDate
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID
                              
                         
                   WHERE 1 = 1 '
 
                 if( @chkInventory = 0 )  
                  begin
                   
                     set @strAllocateSQL = @strAllocateSQL + ' AND (b.TransferFromCustomer IN (70,79,74,81,97) OR b.CustomerNumber  IN (70,79,74,81,97) OR b.CustomerNumber =  cast( ' + @txtCustomerNumber + 'as int) ) ' -- cast( ' + @txtCustomerNumber + 'as int) '

                    if (@strGrade != '')

                      begin

                       set @strAllocateSQL = @strAllocateSQL + ' AND a.Grade = ''' + @strGrade + ''' '
                           end        
                     
                     

                     if( @strRodSize != '')
                        begin
                          SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
                        end
         
                       
                   end
 
           
                  set @strAllocateSQL =@strAllocateSQL + ' GROUP BY b.HeatID,b.Customer,a.RodSize,a.HeatNumber,a.Grade,a.VendorNumber,a.Consignment,a.GradeType,a.Vendor,a.CustomerNumber, a.CustomerPO, a.PremiumRod) AS x WHERE (Received-Allocated)  > 0'
 
   
 
 
 
    EXEC(@strAllocateSQL )
    PRINT @strAllocateSQL
 
END
0
Anthony PerkinsCommented:
As alluded to previously (no points please) since you are using dynamic sql you have to escape any single quotes.  This is true in a Stored Procedure as well as SQL Script.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ITMikeKAuthor Commented:
If I change
SUM(case when b.TrxType = 'R' AND b.LbsTransferred =  0 then b.NoOfCoils else 0 end) As Received,

to
SUM(case when b.TrxType =' 'R'' AND b.LbsTransferred =  0 then b.NoOfCoils else 0 end) As Received,

the R never sums
0
Anthony PerkinsCommented:
Incidentally, there is no need to use Dynamic SQL here.  There are three reasons for that:
1. Typically bad performance.
2. Lousy security.  Aside from the risk of SQL Injection you will have to GRANT SELECT permissions to the user that runs this Stored Procedure.  This is a bad idea.
3. Nightmare to maintain.
0
Anthony PerkinsCommented:
This is how you would do it without using Dynamic SQL (and yes you could combine those two queries into one, however I would not recommend it):
CREATE PROCEDURE usp_YourStoredProedure
    @chkInventory int,
    @txtCustomerNumber varchar(MAX),	-- Don't use varchar(MAX) where it is not appropriate:
    @strGrade varchar(MAX),		-- Are you really going to pass in up to 2 billion characters?
    @strRodSize varchar(MAX),
    @strSecondaryRodSize varchar(MAX),	-- not used
    @strHeat varchar(MAX)		-- not used

AS 

SET NOCOUNT ON; 

--DECLARE @return_value int,		-- not used
--    @strAllocateSQL varchar(max)
--SET @strHeat = NULLIF(@strHeat, '')

IF @chkInventory = 0 
    SELECT  x.*,	-- Don't use *, columns should be explicitly named
            x.Received - x.Shipped - x.Transferred AS OnHand,
            x.Received - x.Allocated AS Available,
            CASE WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
                                                                 ELSE x.Received - x.Shipped - x.Transferred
                                                            END AS int)
                 ELSE 0
            END AS AvgWt
    FROM    (SELECT b.HeatID,
                    b.Customer AS Customer,
                    a.GradeType,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred = 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS Received,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer = 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS Shipped,
                    SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
                             ELSE 0
                        END) AS Allocated,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred > 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS TransferredIn,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS TransferredOut,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS Transferred,
                    SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight,
                    MIN(CASE WHEN b.TrxType = 'R' THEN b.TrxDate
                        END) AS EarlyDate
             FROM   tblHeat AS a
                    LEFT JOIN tblHeatTransaction AS b ON a.ID = b.HeatID
             WHERE  (b.TransferFromCustomer IN (70, 79, 74, 81, 97)
                     OR b.CustomerNumber IN (70, 79, 74, 81, 97)
                     OR b.CustomerNumber = CAST(@txtCustomerNumber AS int)
                    )
                    AND (@strGrade = ''
                         OR a.Grade = @strGrade
                        )
                    AND (@strRodSize = ''
                         OR a.RodSize = @strRodSize
                        )
             GROUP BY b.HeatID,
                    b.Customer,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.GradeType,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod
             HAVING Received - Allocated > 0
            ) AS x

ELSE 
    SELECT  x.*,	-- Don't use *, columns should be explicitly named
            x.Received - x.Shipped - x.Transferred AS OnHand,
            x.Received - x.Allocated AS Available,
            CASE WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
                                                                 ELSE x.Received - x.Shipped - x.Transferred
                                                            END AS int)
                 ELSE 0
            END AS AvgWt
    FROM    (SELECT b.HeatID,
                    b.Customer AS Customer,
                    a.GradeType,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred = 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS Received,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer = 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS Shipped,
                    SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
                             ELSE 0
                        END) AS Allocated,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred > 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS TransferredIn,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS TransferredOut,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS Transferred,
                    SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight,
                    MIN(CASE WHEN b.TrxType = 'R' THEN b.TrxDate
                        END) AS EarlyDate
             FROM   tblHeat AS a
                    LEFT JOIN tblHeatTransaction AS b ON a.ID = b.HeatID
             GROUP BY b.HeatID,
                    b.Customer,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.GradeType,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod
             HAVING Received - Allocated > 0
            ) AS x

Open in new window

0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
As acperkins said, if you can avoid using dynamic SQL, don't use it.  That said, dynamic SQL being outside the context of upper level caller procedure, you can get variables back using sp_executesql stored proc.  For more info:

http://technet.microsoft.com/en-us/library/ms188001.aspx
0
ITMikeKAuthor Commented:
Just tried to run your example (thanks BTW!), but I'm getting an "Invalid Column names" in the HAVING clause.  Any thoughts?
0
Anthony PerkinsCommented:
You are right, you cannot use aliases in the HAVING clause.  

Instead of:
HAVING Received - Allocated > 0
It should be:
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred = 0 THEN b.NoOfCoils
                             ELSE 0
                        END) -
                    SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
                             ELSE 0
                        END) > 0

<edit>
Which you may be able to simplify like this:
SUM(
      CASE
             WHEN b.TrxType = 'R' AND b.LbsTransferred = 0 THEN b.NoOfCoils
             WHEN b.TrxType = 'A' THEN -b.NoOfCoils
             ELSE 0
         END) > 0
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ITMikeKAuthor Commented:
Initial testing are giving expected results.  I really appreciate the quick response and explanation.  Super job!
0
ITMikeKAuthor Commented:
I have the following SP I am using.
All my "S" records come back as "0"
USE [OrderEntry_prodSQL]
GO

/****** Object:  StoredProcedure [dbo].[usp_YourStoredProedure2]    Script Date: 10/16/2013 18:04:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_YourStoredProedure2]
    @chkInventory int,
    @txtCustomerNumber varchar(10),	
    @strGrade varchar(10),		
    @strRodSize varchar(8)

AS 

SET NOCOUNT ON; 


IF @chkInventory = 0 
    SELECT  x.*,
            x.Received - x.Shipped - x.Transferred AS OnHand,
            x.Received - x.Allocated AS Available,
            CASE WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
                                                                 ELSE x.Received - x.Shipped - x.Transferred
                                                            END AS int)
                 ELSE 0
            END AS AvgWt
    FROM    (SELECT b.HeatID,
                    b.Customer AS Customer,
                    a.GradeType,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred = 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS Received,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer = 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS Shipped,
                    SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
                             ELSE 0
                        END) AS Allocated,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred > 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS TransferredIn,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS TransferredOut,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.TransferToCustomer > 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS Transferred,
                    SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight,
                    MIN(CASE WHEN b.TrxType = 'R' THEN b.TrxDate
                        END) AS EarlyDate
             FROM   tblHeat AS a
                    LEFT JOIN tblHeatTransaction AS b ON a.ID = b.HeatID
             WHERE  (b.TransferFromCustomer IN (70, 79, 74, 81, 97,10)
                     OR b.CustomerNumber IN (70, 79, 74, 81, 97,10)
              
                    )
                    AND (@strGrade = ''
                         OR a.Grade = '1022'
                        )
                    AND (@strRodSize = ''
                         OR a.RodSize = '1/4'
                        )
             GROUP BY b.HeatID,
                    b.Customer,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.GradeType,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod
             HAVING SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred = 0 THEN b.NoOfCoils
                             ELSE 0
                        END) -
                    SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
                             ELSE 0
                        END) > 0
            ) AS x

ELSE 
    SELECT  x.*,	-- Don't use *, columns should be explicitly named
            x.Received - x.Shipped - x.Transferred AS OnHand,
            x.Received - x.Allocated AS Available,
            CASE WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
                                                                 ELSE x.Received - x.Shipped - x.Transferred
                                                            END AS int)
                 ELSE 0
            END AS AvgWt
    FROM    (SELECT b.HeatID,
                    b.Customer AS Customer,
                    a.GradeType,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred = 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS Received,
                    SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer = 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS Shipped,
                    SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
                             ELSE 0
                        END) AS Allocated,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred > 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS TransferredIn,
                  SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS TransferredOut,
                    SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.TransferToCustomer > 0 THEN b.NoOfCoils
                             ELSE 0
                        END) AS Transferred,
                    SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight,
                    MIN(CASE WHEN b.TrxType = 'R' THEN b.TrxDate
                        END) AS EarlyDate
             FROM   tblHeat AS a
                    LEFT JOIN tblHeatTransaction AS b ON a.ID = b.HeatID
             GROUP BY b.HeatID,
                    b.Customer,
                    a.RodSize,
                    a.HeatNumber,
                    a.Grade,
                    a.VendorNumber,
                    a.Consignment,
                    a.GradeType,
                    a.Vendor,
                    a.CustomerNumber,
                    a.CustomerPO,
                    a.PremiumRod
             HAVING SUM(CASE WHEN b.TrxType = 'R'
                                  AND b.LbsTransferred = 0 THEN b.NoOfCoils
                             ELSE 0
                        END) -
                    SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
                             ELSE 0
                        END) > 0
            ) AS x
GO

Open in new window

When I isolate
      the routine:
      
      
 select a.HeatNumber,b.RodSize,b.Customer ,a.HeatNumber,a.Consignment,b.Grade,
 
 SUM(CASE WHEN b.TrxType = 'S'
                                  AND b.TransferToCustomer = 0 THEN b.CoilsShipped
                             ELSE 0
                        END) AS Shipped
                                    FROM   tblHeatTransaction AS b
                     JOIN tblHeat AS a ON a.ID = b.HeatID
                                    WHERE (b.TransferFromCustomer IN (70, 79, 74, 81, 97,10)
                     OR b.CustomerNumber IN (70, 79, 74, 81, 97,10)) and a.RodSize = '1/4' AND a.Grade = '1022'
                                    GROUP BY
                                    b.HeatNumber,
                                    a.HeatNumber,
                    b.Customer,
                    b.RodSize,
                   
                    b.Grade,
                              a.Consignment
            ( I reveresed tblHeat and tblHeatTransaction) in this example, I get values in the shipped column.  If
I put it all together, I get zip.
0
PortletPaulfreelancerCommented:
I don't see much point in the left join (perhaps there is, just that I'm not seeing it) as all case expressions are based on values in tblHeatTransaction. So either you just move to an inner join and/or reverse the tables (I did both below).

If the left join really is required, then your outer query should probably use ISNULL() and/or you may have to reconsider all the case expressions to cater for nulls.

Also:
You could remove the IF/ELSE test on @chkInventory by joining that condition into the where clause and hence reduce the amount of code being tested.
I have also moved the case expressions around so tests on b.TrxType are in A,R,S sequence (not a necessary thing I just found it easier to read)

CREATE PROCEDURE usp_YourStoredProedure @chkInventory int,
@txtCustomerNumber varchar(MAX), -- Don't use varchar(MAX) where it is not appropriate:
@strGrade varchar(MAX), -- Are you really going to pass in up to 2 billion characters?
@strRodSize varchar(MAX),
@strSecondaryRodSize varchar(MAX), -- not used
@strHeat varchar(MAX) -- not used

AS

        SET NOCOUNT ON;

        --DECLARE @return_value int,		-- not used
        --    @strAllocateSQL varchar(max)
        --SET @strHeat = NULLIF(@strHeat, '')

        SELECT
                x.HeatID
              , x.Customer
              , x.GradeType
              , x.RodSize
              , x.HeatNumber
              , x.Grade
              , x.VendorNumber
              , x.Consignment
              , x.Vendor
              , x.CustomerNumber
              , x.CustomerPO
              , x.PremiumRod
              , x.Allocated         --<< if left joining use ISNULL(x.Allocated) as Allocated
              , x.Received
              , x.TransferredIn
              , x.Transferred
              , x.EarlyDate
              , x.Shipped
              , x.TransferredOut
              , x.Weight
              , x.Received - x.Shipped - x.Transferred AS OnHand
              , x.Received - x.Allocated               AS Available
              , CASE
                        WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE
                                WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
                                ELSE x.Received - x.Shipped - x.Transferred
                                END AS int)
                        ELSE 0
                        END                            AS AvgWt
        FROM (
                SELECT
                        b.HeatID
                      , b.Customer
                      , a.GradeType
                      , a.RodSize
                      , a.HeatNumber
                      , a.Grade
                      , a.VendorNumber
                      , a.Consignment
                      , a.Vendor
                      , a.CustomerNumber
                      , a.CustomerPO
                      , a.PremiumRod
                      , SUM(CASE
                                WHEN b.TrxType = 'A' THEN b.NoOfCoils
                                ELSE 0
                                END)                                     AS Allocated
                      , SUM(CASE
                                WHEN b.TrxType = 'R'
                                AND b.LbsTransferred = 0 THEN b.NoOfCoils
                                ELSE 0
                                END)                                     AS Received
                      , SUM(CASE
                                WHEN b.TrxType = 'R'
                                AND b.LbsTransferred > 0 THEN b.NoOfCoils
                                ELSE 0
                                END)                                     AS TransferredIn
                      , SUM(CASE
                                WHEN b.TrxType = 'R'
                                AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                                ELSE 0
                                END)                                     AS Transferred
                      , MIN(CASE
                                WHEN b.TrxType = 'R' THEN b.TrxDate
                                END)                                     AS EarlyDate
                      , SUM(CASE
                                WHEN b.TrxType = 'S'
                                AND b.TransferToCustomer = 0 THEN b.CoilsShipped
                                ELSE 0
                                END)                                     AS Shipped
                      , SUM(CASE
                                WHEN b.TrxType = 'S'
                                AND b.TransferToCustomer > 0 THEN b.CoilsShipped
                                ELSE 0
                                END)                                     AS TransferredOut
                      , SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight
                FROM tblHeatTransaction AS b
                INNER JOIN tblHeat AS a ON a.ID = b.HeatID
                WHERE ( @chkInventory = 0
                        AND     (
                                   b.TransferFromCustomer IN (70, 79, 74, 81, 97)
                                OR b.CustomerNumber IN (70, 79, 74, 81, 97)
                                OR b.CustomerNumber = CAST(@txtCustomerNumber AS int)
                                )
                        AND (@strGrade = '' OR a.Grade = @strGrade)
                        AND (@strRodSize = '' OR a.RodSize = @strRodSize)
                      )
                      OR
                      ( @chkInventory <> 0 OR @chkInventory IS NULL )
                GROUP BY
                         b.HeatID
                       , b.Customer
                       , a.RodSize
                       , a.HeatNumber
                       , a.Grade
                       , a.VendorNumber
                       , a.Consignment
                       , a.GradeType
                       , a.Vendor
                       , a.CustomerNumber
                       , a.CustomerPO
                       , a.PremiumRod
                HAVING SUM(CASE
                                WHEN b.TrxType = 'R'
                                AND b.LbsTransferred = 0 THEN b.NoOfCoils
                                ELSE 0
                                END) 
                       - SUM(CASE
                                WHEN b.TrxType = 'A' THEN b.NoOfCoils
                                ELSE 0
                                END)
                       > 0
        ) AS x
GO

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.