Month Difference Without additional table

Question

I am new to SQL query world and got stuck into one requirement.

In my Query i have toDate and fromdate input parameter, based on business logic it will return result like below.

Result:-

Month
Dec-16
Dec-16
Dec-16
Feb-17
Feb-17
Mar-17
Mar-17

now query should need to return the data for each month , if we dont have data for perticular month(in image which is Jan) then it should insert data and return data for that month too, in image we can see for Jan we dont have any data.

Possible duplicate of [Include missing months in Group By query](https://stackoverflow.com/questions/11479918/include-missing-months-in-group-by-query) — ahmed abdelqader, Jun 18 '17 at 11:33
I don't see any image in your post, I also notice some lack of details. Can you five more detail about the business logic used to determine the month from 'toDate' and 'FromDate'? Where is the "data for a particular month stored"? How is the new data calculated? — Sergio Prats, Jun 18 '17 at 11:55

score 1 · Answer 1 · answered Jun 18 '17 at 12:03

You can use a calendar or dates table for this sort of thing.

Without a calendar table, you can generate an adhoc set of months using a common table expression with just this:

declare @fromdate date = '20161201';
declare @todate   date = '20170301';
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, Months as (
  select top (datediff(month, @fromdate, @todate)+1) 
    [Month]=convert(date,dateadd(month,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo
   order by [Month]
)
/* your query here: */
select 
    d.[Month]
  , sum_col = sum(t.col)
from Months 
  left join tbl t
    on d.[Month] = t.[Month]
group by d.[Month]

Number and Calendar table reference:

score 0 · Answer 2 · answered Jun 19 '17 at 07:43

Solved Query:-

Declare @customDate DATETIME 
declare @datafound integer
set @customDate = @fromDate

    WHILE  @customDate < @toDate
    BEGIN
        select @datafound = count(1) from @temp where datepart(month, MonthDate) = datepart(month,  @customDate)
        if @datafound = 0
        select Format(@customDate,'MMM-yy') as Month

     SET @customDate = DATEADD(month, 1,@customDate)


END;

Month Difference Without additional table

2 Answers2