Solved

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

Posted on 2013-01-11
14
242 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating Alerts in sql sever 2 25
SQL Server Error Log - logging period 1 33
Update in Sql 7 30
Solution for warm standby SQL server 20 38
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 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

28 Experts available now in Live!

Get 1:1 Help Now