Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need to grab multiple values from one table as one field in a subquery

Posted on 2013-01-11
14
Medium Priority
?
250 Views
Last Modified: 2013-01-14
I have a query that returns the name of a Project, the last invoice date, the number of days past due and the project manager for projects that have not been invoiced in the last 45 days.  This is it and it works well
SELECT     ClientID, Cltname, Cltnum, CltEng, Status, LastInvoiceDate, CurrentDate, NumberOfDays, CBMnum
FROM         (SELECT     a.ClientID, c.Cltname, c.Cltnum, c.CltEng, c.Engstatus AS Status, MAX(a.InvoiceDate) AS LastInvoiceDate, GETDATE() AS CurrentDate, DATEDIFF(day, 
                                              MAX(a.InvoiceDate), GETDATE()) AS NumberOfDays, c.CBMnum
                       FROM          ARC_Invoices AS a LEFT OUTER JOIN
                                              Clients AS c ON a.ClientID = c.ID
                       WHERE      (c.Engstatus = 0)
                       GROUP BY a.ClientID, c.Engstatus, c.Cltname, c.Cltnum, c.CltEng, c.CBMnum) AS a_1
WHERE     (NumberOfDays > 45)
ORDER BY ClientID, LastInvoiceDate DESC

Open in new window


Now we have a table with comments and I'd like to include the comments in the above query but there could be more than one comment for each ClientID.  Is there an easy way to do this?  The comments would be tied to the ClientID so I thought I could do another SELECT statement within my SELECT statement before the FROM clause but I'm all messed up by everything I've found online and all.

table relations
If anyone can help it would be greatly appreciated!!!!
0
Comment
Question by:HSI_guelph
[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
  • 6
  • 5
  • 2
14 Comments
 
LVL 12

Expert Comment

by:Jared_S
ID: 38768574
This is written to pull back the first 10 comments for each client.

SELECT     ClientID, Cltname, Cltnum, CltEng, Status, LastInvoiceDate, CurrentDate, NumberOfDays, CBMnum,
a_3.[1], a_3.[2], a_3.[3], a_3.[4], a_3.[5], a_3.[6], a_3.[7], a_3.[8], a_3.[9], a_3.[10]
FROM         

(SELECT     a.ClientID, c.Cltname, c.Cltnum, c.CltEng, c.Engstatus AS Status, 
MAX(a.InvoiceDate) AS LastInvoiceDate, GETDATE() AS CurrentDate, 
DATEDIFF(day, MAX(a.InvoiceDate), GETDATE()) AS NumberOfDays, c.CBMnum

FROM          ARC_Invoices AS a LEFT OUTER JOIN
Clients AS c ON a.ClientID = c.ID
WHERE      (c.Engstatus = 0)

GROUP BY a.ClientID, c.Engstatus, c.Cltname, c.Cltnum, c.CltEng, c.CBMnum) AS a_1

LEFT OUTER JOIN 
(
select * from (
select row_count() over (partition by clientid order by commentdate) as pvt, clientid, comments
from comments) p 
PIVOT(max(comments) for pvt in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) as a_2
) as a_3 

on a.ClientID = c.ID

WHERE     (NumberOfDays > 45)
ORDER BY ClientID, LastInvoiceDate DESC

Open in new window

0
 
LVL 13

Accepted Solution

by:
LIONKING earned 2000 total points
ID: 38768629
Give this a try, it will get you all the comments separated by a dot and a space.

SELECT     ClientID, Cltname, Cltnum, CltEng, Status, LastInvoiceDate, CurrentDate, NumberOfDays, CBMnum,
(SELECT SUBSTRING(c.comments+'. ', 1, LEN(c.comments)+2) FROM Comments c WHERE c.ClientId = a_1.ClientID FOR XML PATH('')) COMMENTS,
FROM         (SELECT     a.ClientID, c.Cltname, c.Cltnum, c.CltEng, c.Engstatus AS Status, MAX(a.InvoiceDate) AS LastInvoiceDate, GETDATE() AS CurrentDate, DATEDIFF(day, 
                                              MAX(a.InvoiceDate), GETDATE()) AS NumberOfDays, c.CBMnum
                       FROM          ARC_Invoices AS a LEFT OUTER JOIN
                                              Clients AS c ON a.ClientID = c.ID
                       WHERE      (c.Engstatus = 0)
                       GROUP BY a.ClientID, c.Engstatus, c.Cltname, c.Cltnum, c.CltEng, c.CBMnum) AS a_1
WHERE     (NumberOfDays > 45)
ORDER BY ClientID, LastInvoiceDate DESC

Open in new window

0
 

Author Comment

by:HSI_guelph
ID: 38768721
@Lionking - I get an error 'Error in WHERE clause near 'FROM'.
Unable to parse query text.'

@Jared_S - I get an error 'The OVER SQL construct or statement is not supported.'  

I'm trying to do this in the Query Designer window of SSRS and I get a lot of errors of code that isn't supported.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:HSI_guelph
ID: 38768734
@Lionking - I found the error.  It was an extra , after Comments.  My Comments output is '<Expr1>Type here. </Expr1><Expr1>Testing comments. </Expr1>'  Is there a way to remove the tags?
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38768738
This will not run in SSRS because both queries are built to run in SQL.
The easiest thing you can do is create a view/stored procedure with any of these queries and build your report from there.
0
 

Author Comment

by:HSI_guelph
ID: 38768776
I've requested that this question be closed as follows:

Accepted answer: 0 points for HSI_guelph's comment #a38768734

for the following reason:

I used the expression to replace the <Expr1> with an empty space.  I wish we had stored procedures available but I'm having to do without for now.  This works great, thank you so much!
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38768767
I think you can also bring in all your data (master + detail), create a group on the master level, create an expression that concatenates your comments, hide your detail level and show the result of that expression in your header/footer (where the master data is)...
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38768777
I guess it would be fair to assign at least "some" points for the effort...
I think some ideas that were given contributed to the solution.
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38768788
Too true. (Points for LK)
0
 

Author Comment

by:HSI_guelph
ID: 38774422
Oh crap!  I'm sorry I meant to accept the solutions!!  Could you please allocate points to Lionking for his solution?  I don't want to accept myself at all!
0
 

Author Comment

by:HSI_guelph
ID: 38774430
@Lionking - I'm so sorry!  I think I just grabbed the last comment not realizing it was mine!  I'll send a request that all the points get awarded to you.  You helped me out on this and I don't want you to think I'm not grateful because I am!
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38774445
@HSI_guelph: No worries, I knew there had been some mistake throughout the process.
It's just that whenever you object the closing of a question, you must specify your reason. That's why you see that message.

No hard feelings :)
0
 

Author Closing Comment

by:HSI_guelph
ID: 38774452
Thank you so much for your 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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
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.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

722 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