SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count by Months
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/20/2013 :  10:31:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 11/20/2013 :  10:37:38  Show Profile  Reply with Quote
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.
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/20/2013 :  11:58:31  Show Profile  Reply with Quote

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

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 11/20/2013 :  12:15:17  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/20/2013 :  13:03:52  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000