We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Run two queries side by side for comparison.

ITMcmcpa
ITMcmcpa asked
on
Medium Priority
231 Views
Last Modified: 2012-05-11
I need to see two sets of information side by side for comparison.

In this case, it is billable time for a specific period.  Let's say 1/1/2011 through 4/30/2011.  I want to show a comparison from last year from 1/1/2010 through 4/30/2011.  In this scenario, there may be more or fewier clients to lay side by side during each period.

Example layout:

Employee Name    Client Name       Period1Hours   Period1Fees   Period2Hours   Period2Fees
-------------------        ---------------         ----------------     ----------------     ----------------     ----------------
Bradley                     ABC                     77.45                 16548.75          54.25                 12453.33
Bradley                     CAR                     0                         0.00                   25.80                 5455.25
Bradley                     ESS                     290.75               12000.00           0                        0.00
Smith                        DFH                     245                    96540.35           23                      4200.35
Smith                        EXX                      0                         0.00                   15                       3288.85

The query that I have created mimics the original report out of our system and shows only one period.  I just need to lay the next period beside it and show zeros where no time exists in Period 1 and does in Period 2.  And vice versa.

Here is the query for one side.

 
SELECT     Employee.Emplname as EmployeeName, Clients.Cltname as ClientName, SUM(WIP.Whours) AS Period1Hours, SUM(WIP.wfee) AS Period2Fees
FROM         Clients INNER JOIN
                      WIP ON Clients.ID = WIP.WCltID INNER JOIN
                      Employee ON Clients.CRPnum = Employee.Empnum
WHERE     (WIP.Wdate BETWEEN CONVERT(DATETIME, '2011-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-04-30 00:00:00', 102)) AND (WIP.wIndicator = 'W' or WIP.wIndicator='H')
GROUP BY Employee.Emplname, WIP.Wbillable, Clients.Cltname
HAVING      (WIP.Wbillable = 1) 
ORDER BY Employee.Emplname, Clients.Cltname

Open in new window

Comment
Watch Question

SharathData Engineer
CERTIFIED EXPERT

Commented:
try this.
SELECT Employee.Emplname AS EmployeeName, 
         Clients.Cltname   AS ClientName, 
         SUM(CASE 
               WHEN WIP.Wdate BETWEEN CONVERT(DATETIME,'2010-01-01 00:00:00',102) AND CONVERT(DATETIME,'2010-04-30 00:00:00',102) THEN WIP.Whours 
               ELSE 0 
             END) AS Period1Hours, 
         SUM(CASE 
               WHEN WIP.Wdate BETWEEN CONVERT(DATETIME,'2010-01-01 00:00:00',102) AND CONVERT(DATETIME,'2010-04-30 00:00:00',102) THEN WIP.wfee 
               ELSE 0 
             END) AS Period1Fees, 
         SUM(CASE 
               WHEN WIP.Wdate BETWEEN CONVERT(DATETIME,'2011-01-01 00:00:00',102) AND CONVERT(DATETIME,'2011-04-30 00:00:00',102) THEN WIP.Whours 
               ELSE 0 
             END) AS Period2Hours, 
         SUM(CASE 
               WHEN WIP.Wdate BETWEEN CONVERT(DATETIME,'2011-01-01 00:00:00',102) AND CONVERT(DATETIME,'2011-04-30 00:00:00',102) THEN WIP.wfee 
               ELSE 0 
             END) AS Period2Fees 
    FROM Clients 
         INNER JOIN WIP 
           ON Clients.ID = WIP.WCltID 
         INNER JOIN Employee 
           ON Clients.CRPnum = Employee.Empnum 
   WHERE WIP.wIndicator IN ('W','H') 
GROUP BY Employee.Emplname, 
         WIP.Wbillable, 
         Clients.Cltname 
  HAVING (WIP.Wbillable = 1) 
ORDER BY Employee.Emplname, 
         Clients.Cltname

Open in new window

Author

Commented:
At first glance it seems good.  Is there a way to omit the client all together if there are zeros in Hours and Fees both periods?
Data Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks.  I will validate and post back.
SharathData Engineer
CERTIFIED EXPERT

Commented:
Also, you need not to convert '2010-01-01 00:00:00' to datetime. you can directlry compare like this.
WIP.Wdate BETWEEN '2010-01-01 00:00:00' and '2010-04-30 00:00:00'

Commented:
I would create two aliases of WIP - WIPCurr and WIPLast and refer the appropriate two Fields in the SELECT clause to the appropriate alias.

Commented:
Let me know if you need help with that.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.