- Feb 22, 2005
- 7,280
- 0
- 0
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.
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?
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.
Code:
--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?
Last edited: