• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

Concatenate SUM with Varchar

I would like to concatenate [Total Qty] = SUM(SOP30300.QUANTITY ) OVER (PARTITION BY SOP30300.ITEMNMBR ) with [Residing Company] =  'Name'  to display the company associated with the Total Qty, any help would be greatly appreciated.

DECLARE @STARTDTE as Varchar(50);
DECLARE @ENDDTE as Varchar(50);
SET @STARTDTE = '01/15/2013'; -- START DATE
SET @ENDDTE = '01/18/2013'; -- END DATE
SELECT
[Residing Company] =  'Name',
[Vendor Name] = PM00200.VENDNAME,
[Vendor Item Number] = IV00103.VNDITNUM,
[Item Number] = SOP30300.ITEMNMBR, 
[Description] = SOP30300.ITEMDESC  , 
[Quantity Sold] = SOP30300.QUANTITY  , 
[Total Qty] = SUM(SOP30300.QUANTITY ) OVER (PARTITION BY SOP30300.ITEMNMBR ), 
[Disposition Date] =  CONVERT(varchar, SOP30200.DOCDATE, 101) ,
[Customer Name] = SOP30200.CUSTNAME  , 
[FFL Number] = SSG00100.FFLNUM   , 
[Address] = SOP30200.ADDRESS1  , 
[City] =SOP30200.City , 
[State] =SOP30200.STATE  , 
[Zipcode] =SOP30200.ZIPCODE  , 
[Country] =SOP30200.COUNTRY  , 
[Ship To Name] =SOP30200.ShipToName   , 
[Standard Cost] =IV00101.STNDCOST  
--SOP30200.DOCDATE
FROM   ATI.dbo.SOP30200 WITH (nolock)  INNER JOIN
                   ATI.dbo.SOP30300 WITH (nolock)ON SOP30200.SOPTYPE = SOP30300.SOPTYPE AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE INNER JOIN
                   ATI.dbo.SSG00100 WITH (nolock) ON SOP30200.CUSTNMBR = SSG00100.CUSTNMBR LEFT OUTER JOIN
                   ATI.dbo.IV00101 WITH (nolock) ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
                   ATI.dbo.IV00102 WITH (nolock)ON IV00101.ITEMNMBR = IV00102.ITEMNMBR INNER JOIN
                   ATI.dbo.IV00103 WITH (nolock)ON IV00101.ITEMNMBR = IV00103.ITEMNMBR AND IV00102.PRIMVNDR = IV00103.VENDORID INNER JOIN
                   ATI.dbo.PM00200 WITH (nolock)ON IV00103.VENDORID = PM00200.VENDORID
WHERE     (SOP30200.SOPTYPE = 3) AND (IV00101.ITMCLSCD = 'AMMO')AND (SOP30200.DOCDATE BETWEEN CONVERT(VARCHAR, @STARTDTE, 102) AND CONVERT(VARCHAR, @ENDDTE, 102))
AND (IV00102.LOCNCODE <>'')
GROUP BY SOP30300.ITEMNMBR, SOP30300.ITEMDESC, SOP30300.QUANTITY, CONVERT(varchar, SOP30200.DOCDATE, 101), SOP30200.CUSTNAME, SSG00100.FFLNUM, 
                      SOP30200.ADDRESS1, SOP30200.CITY, SOP30200.STATE, SOP30200.ZIPCODE, SOP30200.COUNTRY, SOP30200.ShipToName, IV00102.LOCNCODE, 
                      IV00101.STNDCOST,SOP30200.DOCDATE,SSG00100.ADRSCODE,PM00200.VENDNAME,IV00103.VNDITNUM
HAVING      (SOP30300.QUANTITY > 0)AND (SSG00100.ADRSCODE = 'PRIMARY')

ORDER BY [Item Number], [Disposition Date]

Open in new window

0
skull52
Asked:
skull52
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean something like this expression?
CAST( SUM(SOP30300.QUANTITY ) OVER (PARTITION BY SOP30300.ITEMNMBR ) AS VARCHAR(20) + '  - ' +  [Residing Company]  

Open in new window

0
 
skull52Author Commented:
thanks Angel, I tried it but got  Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '+'.  


[Total Qty] = CAST( SUM(SOP30300.QUANTITY ) OVER (PARTITION BY SOP30300.ITEMNMBR ) AS VARCHAR(20) + '  - ' +  [Residing Company],
0
 
skull52Author Commented:
got it just needed another ) VARCHAR(20))
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, I missed a closing ):
 
[Total Qty] = CAST( SUM(SOP30300.QUANTITY ) OVER (PARTITION BY SOP30300.ITEMNMBR ) AS VARCHAR(20)) + '  - ' +  [Residing Company], 

Open in new window

0
 
skull52Author Commented:
Hey no problem, that is just what I needed, I was sure I needed to cast the sum but I couldn't get the concatenation right, as always you have been a great help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now