?
Solved

Concatenate SUM with Varchar

Posted on 2013-01-22
5
Medium Priority
?
584 Views
Last Modified: 2013-01-22
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
Comment
Question by:skull52
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38805599
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
 

Author Comment

by:skull52
ID: 38805616
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
 

Author Comment

by:skull52
ID: 38805629
got it just needed another ) VARCHAR(20))
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38805631
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
 

Author Comment

by:skull52
ID: 38805653
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question