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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.