Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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

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
HSI_guelph
Asked:
HSI_guelph
  • 6
  • 5
  • 2
1 Solution
 
Jared_SCommented:
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
 
LIONKINGCommented:
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
 
HSI_guelphAuthor Commented:
@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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
HSI_guelphAuthor Commented:
@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
 
LIONKINGCommented:
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
 
HSI_guelphAuthor Commented:
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
 
LIONKINGCommented:
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
 
LIONKINGCommented:
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
 
Jared_SCommented:
Too true. (Points for LK)
0
 
HSI_guelphAuthor Commented:
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
 
HSI_guelphAuthor Commented:
@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
 
LIONKINGCommented:
@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
 
HSI_guelphAuthor Commented:
Thank you so much for your help!!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now