?
Solved

Need help with a complicated query for my report

Posted on 2014-01-17
5
Medium Priority
?
331 Views
Last Modified: 2014-01-20
I'm trying to design a report and the deeper I get into the query the more skewed my results become so I was hoping someone could help me iron this out.

My report will show the $ for labour billed, labour unbilled, labour budget and how much is remaining in the labour budget as well as the project name and end date and grouped by Team Colour.  Unfortunately I need to pull this information from 6 different tables.

tables
WIP is used for submitted hours and expenses; the Indicator field is 'H' for billed, 'W' for non-billed and 'D' for deleted.  I also need to make sure the Date is between @StartDate and @EndDate, with parameters the user can change but they would be the start/end of our fiscal years.  Also I need to check the End Date against the parameters.  

I was hoping to have a query return all the information in one row per project.  

At one point I had it working, but then they wanted to add the start/end date parameters which is where my brain seemed to stop functioning.

SELECT     (SELECT DISTINCT EmpCustom.EmpCustValue
                       FROM          Clients INNER JOIN
                                              Employee ON Clients.CBMnum = Employee.Empnum INNER JOIN
                                              EmpCustom ON Employee.ID = EmpCustom.EmpCustEmpId
                       WHERE      (Clients.CBMnum = Clients_1.CBMnum)) AS TeamColour, CltBudget.CBudCltName AS Project, CltBudget.CBudfee AS LabourBudget, ISNULL
                          ((SELECT     SUM(Wfee) AS Expr1
                              FROM         WIP
                              WHERE     (Windicator = 'H') AND (CltBudget.CBudCltID = WCltID)), 0) AS Billed_Labour, ISNULL
                          ((SELECT     SUM(Wfee) AS Expr1
                              FROM         WIP AS WIP_1
                              WHERE     (Windicator = 'W') AND (CltBudget.CBudCltID = WCltID)), 0) AS Nonbilled_Labour,
                          (SELECT     CltCustValue
                            FROM          CltCustom
                            WHERE      (CltCustCltId = CltBudget.CBudCltID)) AS EndDate, Clients_1.Engstatus, CltBudget.CBudCltNum AS Client_Code, 
                      CltBudget.CBudEng AS Client_EngCode
FROM         CltBudget INNER JOIN
                      Clients AS Clients_1 ON CltBudget.CBudCltID = Clients_1.ID
WHERE     (CltBudget.CBudCodeCat = 'Bill') AND (Clients_1.Engstatus = 0)
ORDER BY TeamColour, Project

Open in new window


SELECT     (SELECT DISTINCT EmpCustom.EmpCustValue
                       FROM          Clients INNER JOIN
                                              Employee ON Clients.CBMnum = Employee.Empnum INNER JOIN
                                              EmpCustom ON Employee.ID = EmpCustom.EmpCustEmpId
                       WHERE      (Clients.CBMnum = Clients_1.CBMnum)) AS TeamColour, CltBudget.CBudCltName AS Project, CltBudget.CBudfee AS LabourBudget, ISNULL
                          ((SELECT     SUM(Wfee) AS Expr1
                              FROM         WIP
                              WHERE     (Windicator = 'H') AND (CltBudget.CBudCltID = WCltID)), 0) AS Billed_Labour, ISNULL
                          ((SELECT     SUM(Wfee) AS Expr1
                              FROM         WIP AS WIP_1
                              WHERE     (Windicator = 'W') AND (CltBudget.CBudCltID = WCltID)), 0) AS Nonbilled_Labour,
                          (SELECT     CltCustValue
                            FROM          CltCustom
                            WHERE      (CltCustCltId = CltBudget.CBudCltID)) AS EndDate, Clients_1.Engstatus, CltBudget.CBudCltNum AS Client_Code, 
                      CltBudget.CBudEng AS Client_EngCode
FROM         CltBudget INNER JOIN
                      Clients AS Clients_1 ON CltBudget.CBudCltID = Clients_1.ID
WHERE     (CltBudget.CBudCodeCat = 'Bill') AND (Clients_1.Engstatus = 0)
ORDER BY TeamColour, Project

Open in new window


Any help would be greatly appreciated!!
0
Comment
Question by:HSI_guelph
  • 3
5 Comments
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 750 total points
ID: 39790549
Unless I'm missing something, you posted two copies of the same query.  Was one of them supposed to be different?  Maybe the version with the date parameters that you were trying to add?

 As for your question, if the End Date that you referred to was for a project and you want to restrict the results to projects with an End Date that's within the parameters, then I think you may want to change the main query to be driven by CltCustom (presumably the table containing End Date), and then you'd Join from that to CltBudget, etc.  Either that, or add a Join with CltCustom to the main query.  Either way, then you would obviously check End Date in the main query Where.

  And if you simply want to exclude any WIP entries that are not within the date range specified by the parameters, then add a test to the Where's in the WIP sub-queries.  If there's more to it than that, like you don't want to include a project at all if all of the WIP entries are not within the specified date range, then that would be more complicated.

 Hope this helps.

 James
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 750 total points
ID: 39792308
It would greatly help if you can mock up for us some sample table data, then a sample output of what you are trying to pull off here.

Since we don't have access to your source of data, and who knows how clean it is, answering your question is going to involve a wompload of guessing.
0
 

Author Comment

by:HSI_guelph
ID: 39794182
Yeah the whole thing is kinda complicated.  I'll mock up something to add to the Visio diagram to help elaborate what I'm dealing with.
0
 

Author Closing Comment

by:HSI_guelph
ID: 39795340
Well after I posted my supervisor came in and said he wanted it now, lol.  So I scrapped the query and started out simple and built on it.  I ended up making separate datasets for pulling each field out (labour billed, non-billed, labour, remaining, etc) and then I copied that simple query and put it into my larger one as a nested select with a ID where clause.  This seemed to work and I was able to check how the new nested select affected my results as well as test the amount with the single result returned through a lookup of the single dataset.  Thanks for the replies!!
0
 

Author Comment

by:HSI_guelph
ID: 39795341
Still probably one of the ugliest queries I've written to date, lol.
SELECT     Clients.ID AS Project_ID, Clients.Cltnum AS Project_Code, Clients.CltEng AS Project_EngCode, Clients.Cltname AS Project, 
                      ISNULL(CAST(CltCustom.CltCustValue AS datetime), '1/1/1990 12:00:00 AM') AS EndDate, Clients.CBMnum AS ProjectManager, 
                      EmpCustom.EmpCustValue AS TeamColour,
                          (SELECT     ISNULL(SUM(Wfee), 0) AS LabourBilled
                            FROM          WIP
                            WHERE      (WCltID = Clients.ID) AND (Windicator = 'H') AND (WCodeCat = 'Bill') AND (Wdate BETWEEN CONVERT(DATETIME, @StartDate, 102) AND 
                                                   CONVERT(DATETIME, @EndDate, 102))) AS Billed_Labour, ISNULL
                          ((SELECT     TOP (100) PERCENT ISNULL(SUM(Wfee), 0) AS LabourPending
                              FROM         WIP AS WIP_1
                              WHERE     (WCltID = Clients.ID) AND (Windicator = 'W') AND (WCodeCat = 'Bill') AND (Wdate BETWEEN CONVERT(DATETIME, @StartDate, 102) AND 
                                                    CONVERT(DATETIME, @EndDate, 102))), 0) AS Labour_Pending,
                          (SELECT     ISNULL(SUM(CBudfee), 0) AS ProjectBudget
                            FROM          CltBudget
                            WHERE      (CBudCltID = Clients.ID)) AS Project_Budget,
                          (SELECT     ISNULL(SUM(Wfee), 0) AS LabourSpent
                            FROM          WIP AS WIP_2
                            WHERE      (WCltID = Clients.ID) AND (Windicator IN ('W', 'H')) AND (WCodeCat = 'Bill')) AS TotalLabourSpent
FROM         Clients INNER JOIN
                      Employee ON Clients.CBMnum = Employee.Empnum INNER JOIN
                      EmpCustom ON Employee.ID = EmpCustom.EmpCustEmpId LEFT OUTER JOIN
                      CltCustom ON Clients.ID = CltCustom.CltCustCltId
WHERE     (CONVERT(DATETIME, CltCustom.CltCustValue, 102) BETWEEN CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102))

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses
Course of the Month17 days, 7 hours left to enroll

864 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