Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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