Solved

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

Posted on 2013-01-11
14
240 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
  • 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 500 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now