Get Current Year Of The 12 Month With Month Wise Count Data In Sql Server?

I am creating a query to get the 12 month of name and I also want month wise count for the UserLogin. I want to show month wise report in bar chart. So I need to create a query in

Solution 1:

Can you try something like this. Live Demo

DECLARE@StartDate SMALLDATETIME, @EndDate SMALLDATETIME;

SELECT@StartDate='20170101', @EndDate='20171231';

;WITH d(d) AS 
(
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
  FROM ( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) +1) 
    n =ROW_NUMBER() OVER (ORDERBY [object_id]) -1FROM sys.all_objects ORDERBY [object_id] ) AS n
)
SELECT 
  [Month]    = DATENAME(MONTH, d.d),   
  OrderCount =COUNT(o.UserId) 
FROM d LEFTOUTERJOIN Table1 AS o
  ON o.InsertDateTime >= d.d
  AND o.InsertDateTime < DATEADD(MONTH, 1, d.d)
GROUPBY d.d
ORDERBY d.d;

Solution 2:

After some modification in your code

SELECT  FORMAT(dateadd(MM, T.i, getdate()),'MMMM') AS DateColumn,casewhen TotalCount isnullthen0else TotalCount endas TotalCount
    FROM (VALUES (-11),(-10),(-9),(-8),(-7),(-6), (-5), (-4), (-3), (-2), (-1), (0)) AS T(i)

    OUTER APPLY
    (
       SELECTdistinctCount(datepart(MM,InsertDateTime)) AS TotalCount
           FROM user1 
           WHERE Datediff(mm,InsertDateTime, dateadd(month, T.i, getdate())) =0and IsLogin  =1
    ) uf
    orderby DATEPART(MM,convert(datetime,FORMAT(dateadd(MM, T.i, getdate()),'MMMM') +'01 2017',110))

Output -

DateColumn  TotalCount
January     0
February    0
March       0
April       0
May         0
June        3
July        2
August      2
September   0
October     0
November    0
December    0

Solution 3:

Please modify your query something like below:

SELECT*FROM (
SELECT FORMAT(dateadd(MM, T.i, getdate()), 'MMMM') AS DateColumn --, uf.TotalCountFROM (
    VALUES (-11)
        ,(-10)
        ,(-9)
        ,(-8)
        ,(-7)
        ,(-6)
        ,(-5)
        ,(-4)
        ,(-3)
        ,(-2)
        ,(-1)
        ,(0)
    ) AS T(i)
) A
LEFTJOIN (
SELECT datename(month, calcdate) datecol
    ,count(*) #trxns
FROM EmployeeMTDResults
WHERE calcdate BETWEEN'2017-03-01'AND getdate()
GROUPBY datename(month, calcdate)
) b ON (b.datecol = a.DateColumn)

Solution 4:

Another Approach:

with cte as(select0as n
unionallselect n+1from cte
where n<11)

select DateColumn,isnull(cnt,0) 'TotalCount'from (
select DATENAME(MONTH,dateadd(mm,n,'2000-01-01'))DateColumn from cte)x
leftjoin 
(
select DATENAME(MONTH,InsertDateTime)DateColumn1 ,count (DATENAME(MONTH,InsertDateTime)) cnt fromUser t
where InsertDateTime>='2017-06-04 16:12:46.713'and InsertDateTime<=getdate()
groupby DATENAME(MONTH,InsertDateTime)
  ) y on x.DateColumn=DateColumn1