Learn how to a build a cloud-first strategyRegister Now

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

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

0
ITMcmcpa
Asked:
ITMcmcpa
  • 3
  • 2
  • 2
1 Solution
 
SharathData EngineerCommented:
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

0
 
ITMcmcpaAuthor 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?
0
 
SharathData EngineerCommented:
Add a WHERE clause.
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') 
         AND ((WIP.Wdate BETWEEN CONVERT(DATETIME,'2010-01-01 00:00:00',102) AND CONVERT(DATETIME,'2010-04-30 00:00:00',102)) 
               OR (WIP.Wdate BETWEEN CONVERT(DATETIME,'2011-01-01 00:00:00',102) AND CONVERT(DATETIME,'2011-04-30 00:00:00',102))) 
GROUP BY Employee.Emplname, 
         WIP.Wbillable, 
         Clients.Cltname 
  HAVING (WIP.Wbillable = 1) 
ORDER BY Employee.Emplname, 
         Clients.Cltname

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ITMcmcpaAuthor Commented:
Thanks.  I will validate and post back.
0
 
SharathData EngineerCommented:
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'
0
 
GRayLCommented:
I would create two aliases of WIP - WIPCurr and WIPLast and refer the appropriate two Fields in the SELECT clause to the appropriate alias.
0
 
GRayLCommented:
Let me know if you need help with that.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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