July 4, 2012 at 8:06 am
I have the following script, which was returning all the months, regardless of whether there was data against them, but when I add the the where in the query it only returns the months against the user. But I still want to return all the months?
declare @User as varchar (100)
select @User = fullname
from dbo.FilteredSystemUser where systemuserid = CRMV2_MSCRM.dbo.fn_FindUserGuid()
;
With x (mon) as
(
SELECT 'January'
UNION ALL
SELECT 'February'
UNION ALL
SELECT 'April'
UNION ALL
SELECT 'August'
UNION ALL
SELECT 'December'
UNION ALL
SELECT 'July'
UNION ALL
SELECT 'June'
UNION ALL
SELECT 'March'
UNION ALL
SELECT 'May'
UNION ALL
SELECT 'November'
UNION ALL
SELECT 'October'
UNION ALL
SELECT 'September'
UNION ALL
SELECT 'Unknown'
)
--select @User = fullname
--from dbo.FilteredSystemUser where systemuserid = CRMV2_MSCRM.dbo.fn_FindUserGuid()
SELECT o.statecodename [Status],
count(o.statecodename) [Count],
coalesce(o.tpl_renewalmonthname,X.mon)as [Month],
CASE WHEN coalesce(o.tpl_renewalmonthname,X.mon) = 'January' THEN 1
WHEN coalesce(o.tpl_renewalmonthname,X.mon) = 'February' THEN 2
WHEN coalesce(o.tpl_renewalmonthname,X.mon) = 'March' THEN 3
WHEN coalesce(o.tpl_renewalmonthname,X.mon) = 'April' THEN 4
WHEN coalesce(o.tpl_renewalmonthname,X.mon) = 'May' THEN 5
WHEN coalesce(o.tpl_renewalmonthname,X.mon) = 'June' THEN 6
WHEN coalesce(o.tpl_renewalmonthname,X.mon) = 'July' THEN 7
WHEN coalesce(o.tpl_renewalmonthname,X.mon) = 'August' THEN 8
WHEN coalesce(o.tpl_renewalmonthname,X.mon) = 'September' THEN 9
WHEN coalesce(o.tpl_renewalmonthname,X.mon) = 'October' THEN 10
WHEN coalesce(o.tpl_renewalmonthname,X.mon) = 'November' THEN 11
WHEN coalesce(o.tpl_renewalmonthname,X.mon) = 'December' THEN 12
WHEN coalesce(o.tpl_renewalmonthname,X.mon) = 'Unknown' THEN 13 ELSE tpl_renewalmonth END as [Monthnumber]
FROM X LEFT OUTER JOIN
CRMV2_MSCRM.dbo.FilteredOpportunity o
on X.mon=o.tpl_renewalmonthname
AND YEAR(o.tpl_renewaldate) = YEAR(GETDATE())
INNER JOIN dbo.FilteredTpl_accountexec a
on o.tpl_accountexecidname = a.tpl_staffmemberidname
INNER JOIN dbo.FilteredBusinessUnit b
on a.tpl_businessunitid = b.businessunitid
INNER JOIN dbo.FilteredSystemUser su
on su.businessunitid = b.businessunitid
WHERE @User = fullname
GROUP BY o.statecodename,
o.tpl_renewalmonth,
coalesce(o.tpl_renewalmonthname,X.mon)
ORDER BY 4
option (maxrecursion 12);
July 5, 2012 at 2:40 am
You haven't really given enough information to answer your question.
Here's a random guess: -
WITH t1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)), -- 2 Rows
t2(N) AS (SELECT 1 FROM t1 x, t1 y), -- 4 Rows (2*2)
t3(N) AS (SELECT 1 FROM t2 x, t2 y), -- 16 Rows (4*4)
tally(mon) AS (SELECT TOP 12 DATENAME(MONTH,DATEADD(M,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),'1900-12-01'))
FROM t3
UNION ALL SELECT 'Unknown')
SELECT o.statecodename [Status], COUNT(o.statecodename) [Count],
COALESCE(o.tpl_renewalmonthname,x.mon) AS [Month],
CASE WHEN coalesce(o.tpl_renewalmonthname,x.mon) = 'January' THEN 1
WHEN coalesce(o.tpl_renewalmonthname,x.mon) = 'February' THEN 2
WHEN coalesce(o.tpl_renewalmonthname,x.mon) = 'March' THEN 3
WHEN coalesce(o.tpl_renewalmonthname,x.mon) = 'April' THEN 4
WHEN coalesce(o.tpl_renewalmonthname,x.mon) = 'May' THEN 5
WHEN coalesce(o.tpl_renewalmonthname,x.mon) = 'June' THEN 6
WHEN coalesce(o.tpl_renewalmonthname,x.mon) = 'July' THEN 7
WHEN coalesce(o.tpl_renewalmonthname,x.mon) = 'August' THEN 8
WHEN coalesce(o.tpl_renewalmonthname,x.mon) = 'September' THEN 9
WHEN coalesce(o.tpl_renewalmonthname,x.mon) = 'October' THEN 10
WHEN coalesce(o.tpl_renewalmonthname,x.mon) = 'November' THEN 11
WHEN COALESCE(o.tpl_renewalmonthname,x.mon) = 'December' THEN 12
WHEN COALESCE(o.tpl_renewalmonthname,x.mon) = 'Unknown' THEN 13 ELSE tpl_renewalmonth END as [Monthnumber]
FROM tally x
LEFT OUTER JOIN (SELECT tpl_renewalmonthname, statecodename
FROM CRMV2_MSCRM.dbo.FilteredOpportunity
WHERE fullname = @User) o ON x.mon=o.tpl_renewalmonthname AND YEAR(o.tpl_renewaldate) = YEAR(GETDATE())
INNER JOIN dbo.FilteredTpl_accountexec a ON o.tpl_accountexecidname = a.tpl_staffmemberidname
INNER JOIN dbo.FilteredBusinessUnit b ON a.tpl_businessunitid = b.businessunitid
INNER JOIN dbo.FilteredSystemUser su ON su.businessunitid = b.businessunitid
GROUP BY o.statecodename, o.tpl_renewalmonth, COALESCE(o.tpl_renewalmonthname,X.mon)
ORDER BY 4;
If that doesn't help, please read this article[/url] about how to provide readily consumable sample data and DDL scripts. Without this information, it'd be like walking up to a mechanic and telling him your car doesn't work without showing him the car or explaining what you mean by "doesn't work". He might get lucky, but it's more likely that he won't be able to help 😉
July 5, 2012 at 10:26 pm
To me, it looks like there are 2 issues that are effectively meaning the "LEFT OUTER JOIN" is really and "INNER JOIN".
1. The syntax used for the joins to dbo.FilteredTpl_accountexec, dbo.FilteredBusinessUnit and dbo.FilteredSystemUser are all INNER JOINs. I think that this will cause SQL to interpret the OUTER JOIN as an INNER JOIN
2. the condition "@User = fullname" will cause SQL to interpret the OUTER JOIN as an INNER JOIN
Try the changing the JOINS and the WHERE condition to the following
FROM X
LEFT OUTER JOIN CRMV2_MSCRM.dbo.FilteredOpportunity o
INNER JOIN dbo.FilteredTpl_accountexec a
on o.tpl_accountexecidname = a.tpl_staffmemberidname
INNER JOIN dbo.FilteredBusinessUnit b
on a.tpl_businessunitid = b.businessunitid
INNER JOIN dbo.FilteredSystemUser su
on su.businessunitid = b.businessunitid
on X.mon=o.tpl_renewalmonthname
AND YEAR(o.tpl_renewaldate) = YEAR(GETDATE())
AND @User = fullname
NOTE: is no WHERE condition any more.
The rest of the query (before the FROM and after the WHERE remain the same)
I think this will fix your problem
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply