Count by Months - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count by Months

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-11-20 : 10:31:41

Hi All
I need some further help

Aim - > Count how many id falls within the appropriate months
Currently the “CreatedDate” Column is in the following format “2013-02-26T10:59:26.000Z”

My query is ;
SELECT
[Id],
[CreatedDate]
FROM [FDMS].[Dan].[Stg_SF_Opportunities]

For eg
Id CreatedDate
1 2013-02-04T10:59:26.000Z
2 2013-02-10T10:59:26.000Z
3 2013-02-21T10:59:26.000Z
4 2013-02-26T10:59:26.000Z
4 2013-03-01T10:59:26.000Z

Desired results
Month Count
Jan 0
Feb 4
March 1
April 0
Etc etc

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-20 : 10:37:38

You need a numbers table or something similar. Or, you can create one in cte like shown below and use it:

;WITH N(n) AS 
( SELECT 0 UNION ALL SELECT n+1 FROM N WHERE n < 11)
SELECT
	DATENAME(month,DATEADD(mm,DATEDIFF(mm,0,'20130101')+n,0)) [Month],
	COUNT(*) AS [Count]
FROM 
	N
	LEFT JOIN  [FDMS].[Dan].[Stg_SF_Opportunities]
		ON DATEADD(mm,DATEDIFF(mm,0,'20130101')+n,0)
			= DATEADD(mm,DATEDIFF(mm,0,[CreatedDate]),0)
ORDER BY 		
	[Month]

There may be some opportunities for increased efficiency in the join conditions, especially if you have indexes on created date column.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-11-20 : 11:58:31

James K

I want to adapt your query so i can incorporate some additional logic,

select
[CreatedDate],
[Channel Indicator]
from #build
where [Channel Indicator] = 'Field'

I want to incorporate [Channel Indicator] = 'Field' into your query,

Would the query be ;

;WITH N(n) AS
(SELECT 0 UNION ALL SELECT n+1 FROM N WHERE n < 11)
SELECT
DATENAME(month,DATEADD(mm,DATEDIFF(mm,0,'20130101')+n,0)) [Month],
COUNT(*) AS [Starting Pipeline Count]
FROM N LEFT JOIN #build
ON DATEADD(mm,DATEDIFF(mm,0,'20130101')+n,0) = DATEADD(mm,DATEDIFF(mm,0,[CreatedDate]),0)
where [Channel Indicator] = 'Field'
group by n
--ORDER BY [Month]asc

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-20 : 12:15:17

quote:
Originally posted by masond

James K

I want to adapt your query so i can incorporate some additional logic,

select
[CreatedDate],
[Channel Indicator]
from #build
where [Channel Indicator] = 'Field'

I want to incorporate [Channel Indicator] = 'Field' into your query,

Would the query be ;

;WITH N(n) AS
(SELECT 0 UNION ALL SELECT n+1 FROM N WHERE n < 11)
SELECT
DATENAME(month,DATEADD(mm,DATEDIFF(mm,0,'20130101')+n,0)) [Month],
COUNT(*) AS [Starting Pipeline Count]
FROM N LEFT JOIN #build
ON DATEADD(mm,DATEDIFF(mm,0,'20130101')+n,0) = DATEADD(mm,DATEDIFF(mm,0,[CreatedDate]),0)
where [Channel Indicator] = 'Field'
group by n
--ORDER BY [Month]asc

In query where you have a LEFT JOIN, and you use any column that is in the right table in a WHERE clause, that effectively turns it into an inner join. In your case, what you probably will need to do is to move the where clause to the join condition like this:

FROM N LEFT JOIN  #build
ON DATEADD(mm,DATEDIFF(mm,0,'20130101')+n,0) = DATEADD(mm,DATEDIFF(mm,0,[CreatedDate]),0)
 AND  [Channel Indicator] = 'Field'
group by n

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-20 : 13:03:52

see this for the detailed explanation

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources