SQL View Issue

[Sphexi]

I'm trying to create a report showing a break down of sums, specific to dates. The problem is that it's basically two different SELECTS, as the first is based on the date the item was created (then sums based on cases), the second is based on the date closed.

Trying to combine them directly obviously results in the closed column showing a sum of items closed ON the created date (where the two fields in the row were identical), rather than what I want, which is anything where the distinct date appears in the closed field (no matter the created).

I'm thinking I can do this somehow with a view, but I'm not too sure.

--QUERY1, pulls sum values for each date found in the createddate field
SELECT CONVERT(VARCHAR(10),oppo_createddate,120) AS 'Created Date', 
SUM(1) AS 'Opps In',  
            SUM(CASE WHEN Oppo_Type LIKE '%C3%' THEN 1 ELSE 0 END )MA,  
            SUM(CASE WHEN Oppo_Type LIKE '%HV%' THEN 1 ELSE 0 END )GW, 
            SUM(CASE WHEN Oppo_Type = 'FD_WL' THEN 1 ELSE 0 END )FDWL, 
            SUM(CASE WHEN Oppo_Type = 'DD' THEN 1 ELSE 0 END )DD, 
            SUM(CASE WHEN (Oppo_Type NOT LIKE '%C3%' AND Oppo_Type NOT LIKE '%HV%' AND Oppo_Type <> 'DD' AND Oppo_Type <> 'FD_WL') THEN 1 ELSE 0 END )OTHER 
FROM      dbo.Opportunity  
LEFT JOIN dbo.users ON oppo_assigneduserid = users.user_userid 
WHERE  ( Oppo_Deleted <> '1' 
              OR Oppo_Deleted IS NULL 
            ) 
      AND (Oppo_Status = 'complete' or Oppo_Status = 'in_progress') 
GROUP BY CONVERT(VARCHAR(10),oppo_createddate,120) 
ORDER BY CONVERT(VARCHAR(10),oppo_createddate,120) desc 

--QUERY2, finds a sum value of each date in the closed field
SELECT CONVERT(VARCHAR(10),Oppo_Closed,120) AS 'Closed Date', 
SUM(1) AS 'Opps Closed'            
FROM      dbo.Opportunity  
LEFT JOIN dbo.users ON oppo_assigneduserid = users.user_userid 
WHERE  ( Oppo_Deleted <> '1' 
              OR Oppo_Deleted IS NULL 
            ) 
      AND (Oppo_Status = 'complete' or Oppo_Status = 'in_progress') 
GROUP BY CONVERT(VARCHAR(10),Oppo_Closed,120)
ORDER BY CONVERT(VARCHAR(10),Oppo_Closed,120) desc

Running these two queries results in:

Created Date Opps In MA GW FDWL DD OTHER
2011-07-28 18 6 1 3 7 1
2011-07-27 17 6 2 0 6 3
2011-07-26 9 3 5 0 0 1
2011-07-25 4 1 2 0 1 0
2011-07-24 3 0 0 0 3 0
2011-07-23 6 3 2 0 0 1

Closed Date Opps Closed
2011-07-28 15
2011-07-27 13
2011-07-26 9
2011-07-25 9
2011-07-22 9


We don't close items every day, but tend to create them every day. I'd like the closed column to appear with the rest of the data, so on the 28th it'd show 15 for example, but if the date listed (23rd) has none closed then it'd show 0.

Any ideas on which direction I should be taking this?

 

[KIAman]

Use the 2nd query as a left outer join on the first query and group the entire query by the date.

Here is what you have in simplified example...

select a, b, c from table
 
select a, b from table

Here is what you should do

select Z.a, Z.b, Z.c, Y.b
from table Z
left outer join (select a, b from table) Y on Z.a = Y.a
group by Z.a

You can fill in the typing and it should give you what you're looking for.

 

[Sphexi]

I actually came up with that on Friday, but I found that it only finds the "closed" entries if a corresponding "opened" entry exists. As in, if the date isn't found in the first query, then it isn't going to be listed in the second.

As I don't get paid enough to mess around with this stuff for too long, I took the lazy path and simply created a new table, imported the data, and setup a scheduled vbs task to run daily to update it with the previous day's data. This gives me what my boss wants, and I can move on with my life (and real job).

Thanks for the feedback though