Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Concatenate SUM with Varchar

Posted on 2013-01-22
5
Medium Priority
?
585 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

618 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