Solved

Concatenate SUM with Varchar

Posted on 2013-01-22
5
583 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 500 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

717 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