Link to home
Start Free TrialLog in
Avatar of chriscboy
chriscboyFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Need help with SQL query to show table with all months of the year and associated results

I am trying to display a table of months in a year and against each month display the count of a visits carried out during that month. If there are no visits for a month then it should display 0 in the table.

Below is a query I have made so far.

DECLARE @Visit TABLE (visit_date DATETIME,visit_number VARCHAR(10))
INSERT INTO @Visit (visit_date,visit_number) VALUES
	('2015-01-01 00:00:00','10000/1'),
	('2015-01-02 00:00:00','10000/2'),
	('2015-01-02 00:00:00','10000/3'),
	('2015-01-03 00:00:00','10000/4'),
	('2015-02-02 00:00:00','10000/5'),
	('2015-02-03 00:00:00','10000/6')

SELECT *, DATENAME(MONTH,visit_date) AS MonthName FROM @Visit

DECLARE @Year VARCHAR(4)
SET @Year = '2015'

SELECT Months.Month,COUNT(visit_number) AS Count
FROM (
	SELECT 'January' as Month
    UNION
    select 'February' as Month
    UNION
    select 'March' as Month
    UNION
    select 'April' as Month
    UNION
    select 'May' as Month
    UNION
    select 'June' as Month
    UNION
    select 'July' as Month
    UNION
    select 'August' as Month
    UNION
    select 'September' as Month
    UNION
    select 'October' as Month
    UNION
    select 'November' as Month
    UNION
    select 'December' as Month
) AS Months
LEFT JOIN @Visit ON Months.Month = DATENAME(MONTH,visit_date)
WHERE visit_date  >= @Year + '-01-01 00:00:00' 
AND visit_date <= @Year + '-12-31 00:00:00'
GROUP BY Months.Month
ORDER BY Months.Month

Open in new window


My example displays the results, but is not showing the months where there are no visits carried out (March - December). I thought that the LEFT JOIN from my months query would show the months where no data is found, but it is not working as expected. Can someone help me fix my problem ?
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of chriscboy

ASKER

Thanks for your help!