August 14, 2012 at 9:37 am
I have the following which worked before i added the function at the top. Which returns the data for the current user. But regardless of this I still want everything returned in the CTE (x) table.
declare @User as varchar (100)
select @User = owneridname
from CRMV2_MSCRM.dbo.FilteredOpportunity where ownerid = 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 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())
WHERE @User = o.owneridname
GROUP BY o.statecodename,
o.tpl_renewalmonth,
coalesce(o.tpl_renewalmonthname,X.mon)
ORDER BY 4
option (maxrecursion 12);
August 14, 2012 at 9:52 am
What exactly is the problem?
August 14, 2012 at 10:01 am
Your WHERE clause forces the equivalent of an inner join. Move the @User = o.owneridname into your join criteria.
August 14, 2012 at 10:05 am
I'm not sure if it will solve the problem without having DDL and sample data to test on.
My suggestion is to move the condition from the WHERE clause to the JOIN clause.
FROM X
LEFT OUTER
JOIN CRMV2_MSCRM.dbo.FilteredOpportunity o
ON X.mon=o.tpl_renewalmonthname
AND YEAR(o.tpl_renewaldate) = YEAR(GETDATE())
AND o.owneridname = @User
PS. An improvement on performance would be to assign the GETDATE() to a variable instead of using the function directly in the query. Of course, the best part is to use a range of dates so you won't need functions on your conditions (allowing the proper use of indexes if available).
August 14, 2012 at 10:11 am
Luis Cazares (8/14/2012)
I'm not sure if it will solve the problem without having DDL and sample data to test on.My suggestion is to move the condition from the WHERE clause to the JOIN clause.
FROM X
LEFT OUTER
JOIN CRMV2_MSCRM.dbo.FilteredOpportunity o
ON X.mon=o.tpl_renewalmonthname
AND YEAR(o.tpl_renewaldate) = YEAR(GETDATE())
AND o.owneridname = @User
PS. An improvement on performance would be to assign the GETDATE() to a variable instead of using the function directly in the query. Of course, the best part is to use a range of dates so you won't need functions on your conditions (allowing the proper use of indexes if available).
Basically, what is being suggested regarding the YEAR function is this, and I agree:
FROM X
LEFT OUTER
JOIN CRMV2_MSCRM.dbo.FilteredOpportunity o
ON X.mon=o.tpl_renewalmonthname
AND o.tpl_renewaldate >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
AND o.tpl_renewaldate < DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0)
AND o.owneridname = @User
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply