Need help with SQL Query - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with SQL Query

Author

Topic

sp22403
Starting Member

3 Posts

Posted - 2011-08-01 : 11:01:50

I need help in writing a SQL query to retrieve the data in the following format.

Col1 Col2
XXX 06-22-2011
XXX 07-18-2011
YYY 07-06-2011
YYY 07-18-2011

NOTE: The result returns the unique user count per month and returns all the other months in the result set even if there is no data.

Query Result
2011 Jan 0
2011 Feb 0
...
2011 Jun 1
2011 Jul 2
2011 Aug 0
...
2011 Nov 0
2011 Dec 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-01 : 12:51:28

[code]
;With Month_CTE(mnth)
AS
(SELECT 1
UNION ALL
SELECT mnth + 1
FROM Month_CTE
WHERE mnth+1<=12
)

SELECT t.Yr,t.mnth,COALESCE(f.DisCnt,0) AS DistCnt
FROM
(
SELECT t1.Yr,c.mnth
FROM (SELECT DISTINCT YEAR(Col2) AS Yr FROM Table) t1
CROSS JOIN Month_CTE c
)t
LEFT JOIN (SELECT YEAR(Col2) AS Yr,MONTH(Col2) AS mnth,COUNT(DISTINCT Col1) AS DisCnt
FROM Table
GROUP BY YEAR(Col2),MONTH(Col2))f
ON f.Yr = t.Yr
AND f.mnth = t.mnth
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sp22403
Starting Member

3 Posts

Posted - 2011-08-01 : 14:56:03

Thanks for the quick reply. That's exactly what I needed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 00:59:59

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources