Solved

Need help with a complicated query for my report

Posted on 2014-01-17
5
318 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 34

Assisted Solution

by:James0628
James0628 earned 250 total points
Comment Utility
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 65

Accepted Solution

by:
Jim Horn earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SSO with SSRS 2014 / 2016 2 164
SQL Reporting 8 79
SSRS Expression 2 30
SSRS Pie Chart Data Points Inside AND Outside 4 44
Introduction As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Serve…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

15 Experts available now in Live!

Get 1:1 Help Now