SQL Select

Hello,
can you please ,
Is there any way I can run the below select and get the results as in the sample attached.

Select AccountNumber,AccountCode,Name,
(Select TransactionDate from AROpenTransactions WHERE AROpenTransactions.AccountNumber = Clients.AccountNumber) AS [TransactionDate],
(Select SalesAmount from AROpenTransactions WHERE AROpenTransactions.AccountNumber = Clients.AccountNumber) AS [SalesAmount]
from Clients where PrimarySalesPersonUserID = 165

Also, When I run the select, I get error
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any help is appreciated.
Thank you
Sample.xlsx
W.E.BAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
For verificaion, the code below currently just prints the generated code.
When you're ready to run the code, uncomment the EXEC() at the end.


DECLARE @sql varchar(max)

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

SELECT
    c.AccountNumber, aot.TransactionDate, SUM(aot.SalesAmount) AS SalesAmount
INTO #Results
FROM #Clients c
INNER JOIN #AROpenTransactions aot ON
    aot.AccountNumber = c.AccountNumber
WHERE
    c.PrimarySalesPersonUserID = 165 AND
    aot.TransactionDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 13, 0)
GROUP BY
    c.AccountNumber, aot.TransactionDate
   
--SELECT * FROM #Results

SELECT @sql = (
    SELECT
        CAST(',MAX(CASE WHEN TransactionDate = ''' AS varchar(max)) +
            CONVERT(varchar(8), TransactionDate, 112) + ''' THEN SalesAmount END) ' +
            ' AS [' + CONVERT(varchar(10), TransactionDate, 101) + ']'
    FROM (
        SELECT DISTINCT TransactionDate
        FROM #Results
    ) AS derived
    ORDER BY TransactionDate
    FOR XML PATH('')
)

SET @sql = 'SELECT r.AccountNumber, c.AccountCode, c.Name
' + @sql + '
FROM #Results r
INNER JOIN #Clients c ON
    c.AccountNumber = r.AccountNumber
GROUP BY r.AccountNumber, c.AccountCode, c.Name
ORDER BY AccountNumber
'

SELECT @sql
--EXEC(@sql)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Like the error says the subqueries are returning more than one row. You need to link more columns to assure that will return a single row. Also you can join the tables in the main query, like this_
Select Clients.AccountNumber,Clients.AccountCode,Clients.Name,AROpenTransactions.TransactionDate ,
AROpenTransactions.SalesAmount 
 from Clients 
inner join AROpenTransactions on AROpenTransactions.AccountNumber = Clients.AccountNumber
where PrimarySalesPersonUserID = 165

Open in new window

0
 
W.E.BAuthor Commented:
Hi Victor,

Appreciate the help,
it's only returning Clients that has Transactions,
I'm trying to get all clients for PrimarySalesPersonUserID = 165
and list the transactions info. (Regardless if they have Transactions or not)

thanks
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Scott PletcherSenior DBACommented:
What range of transaction dates do you want to see?  That info is needed to determine how many columns will be in the output, if I understand the format correctly.
0
 
W.E.BAuthor Commented:
last 13 months from getdate()
0
 
Scott PletcherSenior DBACommented:
Wow, that would be a lot of columns.

Do you want to see every trans date or just, say, a summary of one per month or one per week?
0
 
W.E.BAuthor Commented:
There will be only 2 per month (maximum) per client.
0
 
W.E.BAuthor Commented:
most transaction dates will be, 15th, end of the month. (couple exceptions)
0
 
Scott PletcherSenior DBACommented:
But, since you're listing every client, there were have to date columns for every client.  If the clients have different days per month, then you will end up with far more than just two days per month.
0
 
W.E.BAuthor Commented:
I just checked the database,
they all have 2 dates,
so, I should end up with 26 columns of dates.
0
 
Scott PletcherSenior DBACommented:
OK, great, your sample data seemed to imply more than that.
0
 
W.E.BAuthor Commented:
I'm getting error
Msg 208, Level 16, State 0, Line 6
Invalid object name '#Clients'.
0
 
Scott PletcherSenior DBACommented:
Sorry, I created temp table variations of your tables with a few test rows of my own.

For your tests, please use your actual tables, i.e., remove the # from the table names.
0
 
W.E.BAuthor Commented:
HI Scott,
not sure how this will work,

if Ii run the code,I'm only getting 2 rows, and one date.

AccountNumber      AccountCode         Name                             02/15/2015
11377                         T11377      aaaaaaaa                       1448.64
11379                        T11379               bbbbb                           1444.45

thanks,
0
 
Scott PletcherSenior DBACommented:
I noticed the dates in the spreadsheet were for 2010.  If you need dates further back than the 18 months you stated, change the code accordingly.
0
 
W.E.BAuthor Commented:
Thank you very much.
I was using  the wrong PrimarySalesPersonUserID
0
 
W.E.BAuthor Commented:
Thank you.
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.

All Courses

From novice to tech pro — start learning today.