asked on

Run two queries side by side for comparison.

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

Sharath S

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

ITMcmcpa

ASKER

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?

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ITMcmcpa

ASKER

Thanks. I will validate and post back.

Sharath S

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'

GRayL

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

GRayL

Let me know if you need help with that.