SQL Join w/ Group By

[edmana]

I have a query:

SELECT O.Range, COUNT(*) AS NumOfOpportunities, CAST (SUM(O.ExpectedAmount) AS MONEY) AS ExpectedAmount
FROM (
SELECT CASE
	WHEN DATEDIFF(D, EstimatedCloseDate, GETDATE()) BETWEEN 0 AND 30 THEN '0-30 Days'
	WHEN DATEDIFF(D, EstimatedCloseDate, GETDATE()) BETWEEN 31 AND 60 THEN '31-60 Days'
	WHEN DATEDIFF(D, EstimatedCloseDate, GETDATE()) BETWEEN 61 AND 90 THEN '61-90 Days'
	WHEN DATEDIFF(D, EstimatedCloseDate, GETDATE()) > 91 THEN 'Over 90 Days'
	ELSE 'No Expected Close Date'
END AS Range, ExpectedAmount FROM tblOpportunities) O 
GROUP BY O.Range

Which only returns values that fit one of the above groups. If there is no data, the group doesn't show. I then modified the query to include a temp table:

CREATE TABLE #TT_TOD_OPPTEMP ( Range Char(50))
INSERT #TT_TOD_OPPTEMP
VALUES ('No Expected Close Date'), ('0-30 Days'), ('31-60 Days'), ('61-90 Days'), ('Over 90 Days')

SELECT O.Range, COUNT(*) AS NumOfOpportunities, CAST (SUM(O.ExpectedAmount) AS MONEY) AS ExpectedAmount
FROM (
SELECT CASE
	WHEN DATEDIFF(D, EstimatedCloseDate, GETDATE()) BETWEEN 0 AND 30 THEN '0-30 Days'
	WHEN DATEDIFF(D, EstimatedCloseDate, GETDATE()) BETWEEN 31 AND 60 THEN '31-60 Days'
	WHEN DATEDIFF(D, EstimatedCloseDate, GETDATE()) BETWEEN 61 AND 90 THEN '61-90 Days'
	WHEN DATEDIFF(D, EstimatedCloseDate, GETDATE()) > 91 THEN 'Over 90 Days'
	ELSE 'No Expected Close Date'
END AS Range, ExpectedAmount FROM tblOpportunities) O 
GROUP BY O.Range

DROP TABLE #TT_TOD_OPPTEMP

I am having trouble creating a proper join to show all 5 groups and the associated data.

Can anyone help?

Thanks!
Ed

 

[fredericofonseca]

possibly (untested)

CREATE TABLE #TT_TOD_OPPTEMP ( Range Char(50))
INSERT #TT_TOD_OPPTEMP
VALUES ('No Expected Close Date'), ('0-30 Days'), ('31-60 Days'), ('61-90 Days'), ('Over 90 Days')

SELECT base.Range, COUNT(o.range) AS NumOfOpportunities, CAST (SUM(O.ExpectedAmount) AS MONEY) AS ExpectedAmount
FROM #TT_TOD_OPPTEMP base
left outer join 
    (SELECT CASE
	    WHEN DATEDIFF(D, EstimatedCloseDate, GETDATE()) BETWEEN 0 AND 30 THEN '0-30 Days'
	    WHEN DATEDIFF(D, EstimatedCloseDate, GETDATE()) BETWEEN 31 AND 60 THEN '31-60 Days'
	    WHEN DATEDIFF(D, EstimatedCloseDate, GETDATE()) BETWEEN 61 AND 90 THEN '61-90 Days'
	    WHEN DATEDIFF(D, EstimatedCloseDate, GETDATE()) > 91 THEN 'Over 90 Days'
	    ELSE 'No Expected Close Date'
           END AS Range
         , ExpectedAmount
     FROM tblOpportunities
    ) O 
on o.range = base.range
GROUP BY base.Range

DROP TABLE #TT_TOD_OPPTEMP

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[edmana]

Frederico,

That seems to work. Thanks so much! I was struggling where to put the left outer join.

Ed