May 23, 2012 at 5:03 am
I have the following scripts which gets me all the data from a table. But I want to shows months such as December and January which currently dont have any data against them but want to show them in a report.
SELECT statecodename [Status], count(statecodename) [Count],
CASE WHEN tpl_renewalmonthname IS NULL THEN 'Unknown' ELSE tpl_renewalmonthname END as [Month],
CASE WHEN tpl_renewalmonth IS NULL THEN '13' ELSE tpl_renewalmonth END as [Monthnumber]
FROM
CRMV2_MSCRM.dbo.FilteredOpportunity
GROUP BY statecodename,tpl_renewalmonthname,tpl_renewalmonth
ORDER BY 4 asc
This give me the following results:
Status Count Month Monthnumber
Open 1 February 2
Open 7 March 3
Won 6 March 3
Won 7 April 4
Open 6 April 4
Open 12 May 5
Won 5 May 5
Won 3 June 6
Lost 1 June 6
Open 10 June 6
Open 8 July 7
Won 2 July 7
Won 5 August 8
Open 5 August 8
Lost 1 August 8
Open 4 September 9
Won 1 September 9
Won 1 October 10
Open 2 October 10
Open 1 November 11
Won 3 Unknown 13
Lost 1 Unknown 13
May 23, 2012 at 5:15 am
Sachin 80451 (5/23/2012)
I have the following scripts which gets me all the data from a table. But I want to shows months such as December and January which currently dont have any data against them but want to show them in a report.SELECT statecodename [Status], count(statecodename) [Count],
CASE WHEN tpl_renewalmonthname IS NULL THEN 'Unknown' ELSE tpl_renewalmonthname END as [Month],
CASE WHEN tpl_renewalmonth IS NULL THEN '13' ELSE tpl_renewalmonth END as [Monthnumber]
FROM
CRMV2_MSCRM.dbo.FilteredOpportunity
GROUP BY statecodename,tpl_renewalmonthname,tpl_renewalmonth
ORDER BY 4 asc
This give me the following results:
Status Count Month Monthnumber
Open 1 February 2
Open 7 March 3
Won 6 March 3
Won 7 April 4
Open 6 April 4
Open 12 May 5
Won 5 May 5
Won 3 June 6
Lost 1 June 6
Open 10 June 6
Open 8 July 7
Won 2 July 7
Won 5 August 8
Open 5 August 8
Lost 1 August 8
Open 4 September 9
Won 1 September 9
Won 1 October 10
Open 2 October 10
Open 1 November 11
Won 3 Unknown 13
Lost 1 Unknown 13
join it with a CTE that lists the months of the year
with x (mon,monid) as
(select DATENAME(month,1) as month,1 as monid
UNION ALL
select DATENAME(month,X.monid+31) as month,X.monid+31 as monid from x
where monid<=311
)
select x.mon from X option (maxrecursion 12);
MVDBA
May 23, 2012 at 5:27 am
Sorry never done CTE? how would I join this onto my script?
May 23, 2012 at 5:39 am
i simplified the code just to match the CTE i wrote for you, but it would look a little like this
with x (mon,monid) as
(select DATENAME(month,1) as month,1 as monid
UNION ALL
select DATENAME(month,X.monid+31) as month,X.monid+31 as monid from x
where monid<=311
)
SELECT statecodename [Status], count(statecodename) [Count],
coalesce,tpl_renewalmonthname,X.mon)as [Month]
FROM
X left outer join
CRMV2_MSCRM.dbo.FilteredOpportunity y on X.mon=y.tpl_renewalmonthname
GROUP BY statecodename,coalesce,tpl_renewalmonthname,X.mon)
option (maxrecursion 12);
apologies if there are any errors, i'm coding this without a SQL server in front of me
MVDBA
May 23, 2012 at 7:15 am
thank you that works. How can I get the month to be in order like it should be jan-->Dec?
May 23, 2012 at 7:20 am
alter the CTE to be as follows
with x (mon,monid,monthord) as
(select DATENAME(month,1) as month,1 as monid,1 as monthord
UNION ALL
select DATENAME(month,X.monid+31) as month,X.monid+31 as monid,x.monthord+1 as monthord from x
where monid<=311
)
now you have an additional column available in your query
x.monthord , which should be values 1-12
you can then say ORDER by x.monthord
MVDBA
May 24, 2012 at 8:27 am
How can I bring through the Nulls that exist in the original script. I have a couple of rows which dont have a month. But still want to show these in my results as 'Unknown'?
May 25, 2012 at 7:37 am
Hello Sachin,
I think the easiest way to go through this problem is creating a month table and left-joining it with your query, maybe something like (I have'nt checked it)
WITH MonthList (MonthId) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION
SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13)
SELECT [Status], [Count], [Month], [MonthId]
FROM MonthList
LEFT JOIN (
SELECT statecodename [Status], count(statecodename) [Count],
CASE WHEN tpl_renewalmonthname IS NULL THEN 'Unknown' ELSE tpl_renewalmonthname END as [Month],
CASE WHEN tpl_renewalmonth IS NULL THEN '13' ELSE tpl_renewalmonth END as [Monthnumber]
FROM
CRMV2_MSCRM.dbo.FilteredOpportunity
GROUP BY statecodename,tpl_renewalmonthname,tpl_renewalmonth
) AS OriginalQuery ON MonthList.MonthId = OriginalQuery.Monthnumber
ORDER BY 4 asc
Hope it helps,
Francesc
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply