Solved

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

Posted on 2013-01-11
14
245 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
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.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SHOWPLAN permission denied in database 'AdventureWorks'. 13 99
Managing Columnstore Indexes 2 28
Help Required 3 92
MS SQL + Insert Into Table - If Doesnt Exist 9 32
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

785 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