June 11, 2012 at 4:24 am
I have the following script, does anyone have clue where I have gone wrong? I currently only have data for 'Overdue' classification but want to return all the other classifications in the result set ('Today', 'Tomorrow', 'Next 6 days'). I have created a temporary table with all the classification's and left outer joined with my main query but this is still not working?
DECLARE @TomorrowsDate DateTime
DECLARE @Today DateTime
DECLARE @FROM DATETIME
DECLARE @To DATETIME
SET @TomorrowsDate = DATEADD (Day, 1, CONVERT (VARCHAR, GETDATE(), 101))
SET @Today = GETDATE()
SET @From = dateadd(dd, datediff(dd, 0, getdate()) + 1, 0)
SET @To = dateadd(dd, datediff(dd, 0, getdate()) + 7, 0)
SELECT classification, count
INTO #A1
FROM
(select 'Overdue' as classification, 0 as count
UNION ALL
SELECT 'Today' as classification, 0 as count
UNION ALL
SELECT 'Tomorrow' as classificiation, 0 as count
UNION ALL
SELECT 'Next 6 Days' as classification, 0 as count) A
SELECT A.Classification,A.o,
SUM(A.count) Count
FROM
(select 'Overdue' as classification, '1' as o, Count(scheduledstart) as count
from CRMV2_MSCRM.dbo.FilteredActivityPointer
where statecode IN (0,3)
AND scheduledstart < @Today -- Overdue
GROUP BY scheduledstart
UNION ALL
select 'Today' as classification,'2' as o,
Count(scheduledstart) as count
from CRMV2_MSCRM.dbo.FilteredActivityPointer
where statecode IN (0,3)
AND scheduledstart > GETDATE() AND DAY(scheduledstart) = Day(GETDATE())
GROUP BY scheduledstart
UNION ALL
select 'Tomorrow' as classification,'3' as o,
Count(scheduledstart) as count
from CRMV2_MSCRM.dbo.FilteredActivityPointer
where statecode IN (0,3)
AND scheduledstart = @TomorrowsDate
GROUP BY scheduledstart
UNION ALL
select 'Next 6 Days' as classification,'4' as o,
Count(scheduledstart) as count
from CRMV2_MSCRM.dbo.FilteredActivityPointer
where statecode IN (0,3)
AND scheduledstart > @From AND scheduledstart < @To
GROUP BY scheduledstart)
A
LEFT OUTER JOIN #A1 on
#A1.classification = A.Classification
GROUP BY A.Classification,A.o
DROP TABLE #A1
June 11, 2012 at 4:31 am
Your join is the wrong way around. Left join means all row from LEFT table, but the table that you want all rows from is on the right.
FROM #A1
LEFT OUTER JOIN (subquery here) A ON #A1.classification = A.Classification
GROUP BY A.Classification,A.o
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply