T-SQL Select Get All Months Within a Range of Years

t-sql select get all Months within a range of years

declare @date1 datetime, 
    @date2 datetime, 
    @date  datetime, 
    @month integer, 
    @nm_bulan varchar(20) 

create table #month_tmp 
    ( bulan integer null, keterangan varchar(20) null ) 

select @date1 = '2000-01-01', 
       @date2 = '2000-12-31' 

select @month = month(@date1) 

while (@month < 13) 
Begin 
    IF @month = 1 
    Begin 
       SELECT @date  = CAST( CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,0,@date1))-1),DATEADD(mm,0,@date1)),111) + ' 00:00:00' as DATETIME ) 
    End
    ELSE
    Begin
       SELECT @date  = CAST( CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,@month -1,@date1))-1),DATEADD(mm,@month -1,@date1)),111) + ' 00:00:00' as DATETIME ) 
    End
    select @nm_bulan = DATENAME(MM, @date)

    insert into #month_tmp
    select @month as nilai, @nm_bulan as nama 

    select @month = @month + 1
End 

select * from #month_tmp 
drop table #month_tmp 
go

Return All Months & Years Between Date Range - SQL

Thank your for your suggestions.

I managed to get this working using another method.

Declare @DateFrom datetime, @DateTo Datetime
Set @DateFrom = '2012-01-01'
set @DateTo = '2013-12-31'

select 
YearMonthTbl.YearMonth,
orders.vad_variant_code,
orders.qty

From 
(SELECT  Convert(CHAR(4),DATEADD(MONTH, x.number, @DateFrom),120) + '/' + Convert(CHAR(2),DATEADD(MONTH, x.number, @DateFrom),110) As YearMonth
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'        
AND     x.number <= DATEDIFF(MONTH, @DateFrom, @DateTo)) YearMonthTbl

left join 
    (select variant_Detail.vad_variant_code, 
    sum(order_line_item.oli_qty_required) as 'Qty', 
    Convert(CHAR(4),order_header.oh_datetime,120) + '/' + Convert(CHAR(2),order_header.oh_datetime,110) As 'YearMonth'
    FROM order_line_item 
    join variant_detail on variant_detail.vad_id = order_line_item.oli_vad_id
    join order_header on order_header.oh_id = order_line_item.oli_oh_id
    Where 
    (order_header.oh_datetime between @DateFrom and @DateTo)
    GROUP BY variant_Detail.vad_variant_code,
    Convert(CHAR(4),order_header.oh_datetime,120) + '/' + Convert(CHAR(2),order_header.oh_datetime,110)
    ) as Orders on Orders.YearMonth = YearMonthTbl.YearMonth

How to get a list of months and year between two dates in SQL Server

If I understand what you're trying to accomplish, a recursive CTE might help. Here's a quick example of what you can do. The CTE will expand out into a list of dates, which you can then use as the base for your query.

The contents of the TargetData CTE may need to be adjusted, as I don't have a complete picture of your data structure.

DECLARE @startDate DATE = '1/1/2015';
DECLARE @endDate DATE = '7/31/2016';

-- Recursive CTE to generate a list of months within the date range:
WITH Months AS (
    SELECT CONVERT(DATE, DATEADD(D, -(DAY(@startDate)) + 1, @startDate)) [MonthDate]

    UNION ALL

    SELECT DATEADD(M, 1, MonthDate)
    FROM Months
    WHERE MonthDate <= DATEADD(M, -1, @endDate)
),
TargetData AS (
    -- This is a slightly modified version of the original query:
    select
        d.id_base as case_id, 
        c.C_LAST_ACTION AS Docketed,
        c.C_CASE_TYPE AS caseType,
        ct.C_NAME As caseName, 
        ct.C_DESCRIPTION AS caseNameDescription,
        case when d.c_mod_decision_id is not null then '' else  DATENAME(mm, d.c_issue_date) + DATENAME(yyyy, d.c_issue_date)  end as display,
        -- Return the "MonthDate" so that it can be left joined to the Months table:
        DATEADD(D, -(DAY(d.c_issue_date)) + 1, d.c_issue_date) [MonthDate]
    from t_case_decision d JOIN T_CASE_INPUT c on c.id = d.id_base JOIN T_CASE_TYPE ct on C_CASE_TYPE = ct.id 
    where cast(d.c_issue_date AS date) BETWEEN @startDate AND @endDate
)
SELECT
    m.MonthDate,
    DATENAME(mm, m.MonthDate) + DATENAME(yyyy, m.MonthDate),
    td.*
FROM Months m
    LEFT JOIN TargetData td ON td.MonthDate = m.MonthDate;

SQL select date range by month and year

I'd convert those literals to dates using str_to_date:

SELECT MONTH(date1)
FROM   data
WHERE  date1 BETWEEN STR_TO_DATE('2017-05-22', '%Y-%m-%d') AND STR_TO_DATE('2018-05-23', '%Y-%m-%d')

Note that between is inclusive on the first argument and exclusive on the second, so I bumped the day to the 23rd.

Show all months in date range even when no results are present in select

For not very big result tables using a table variable as a "base table" much easier:

--add these lines before your select
declare @d datetime set @d=@date1
declare @t table(count int, month varchar(12), year int)
--fill the table with 0-month-year values
while @d < @date2
begin
    insert into @t values(0, datename(mm, @d), datepart(yy, @d))
    set @d = dateadd(mm, 1, @d)
end

then add your results to the table variable:

insert into @t 
select count(distinct w.wkid)
    ,DATENAME(MONTH, r.date)
    ,year(r.date)
from Workshop w
    left join Reservation r on r.wkid=w.wkid
    left join Registration reg on reg.wkid=w.wkid
where r.date between @date1 and @date2
group by DatePart(Month, r.date) ,DateName(Month, r.date), year(r.date)

and finally get required result:

select sum(count) as count, month, year from @t
    group by year, month
    order by year desc, month desc 

Aggregate query from relatively little table variable is much more cheap then one more LEFT JOIN. If you have a lot of periods, consider using of #temporary table.

Sorry, I haven't MSSqlServer installed right now to check my code, but the idea is quite simple.

tsql: How to retrieve the last date of each month between given date range

You can use CTE for getting all last days of the month within the defined range

Declare @Start datetime
Declare @End datetime

Select @Start = '20130808'
Select @End = '20131111'
;With CTE as
(
Select @Start  as Date,Case When DatePart(mm,@Start)<>DatePart(mm,@Start+1) then 1 else 0 end as [Last]
UNION ALL
Select Date+1,Case When DatePart(mm,Date+1)<>DatePart(mm,Date+2) then 1 else 0 end from CTE
Where Date<@End
)

Select * from CTE
where [Last]=1   OPTION ( MAXRECURSION 0 )

Get all year and months from date range in sql with or without data

Finally I got the Solution:

SELECT Year(SG_PROCESS_DATE) AS yearname ,DATENAME(MONTH, DateAdd(MONTH, MONTH(SG_PROCESS_DATE), 0) - 1) AS monthname ,SUM(SG_WORKTIME) AS workhour FROM innovator.[SG_ATTENDANCE] WHERE ( SG_PROCESS_DATE BETWEEN '2013-01-01' AND '2014-03-01') GROUP BY YEAR(SG_PROCESS_DATE),MONTH(SG_PROCESS_DATE)

UNION

 SELECT DATENAME(YEAR, DATEADD(MONTH , x.number , '2013-01-01')) as yearname, DATENAME(MONTH, DATEADD(MONTH, x.number, '2013-01-01')) AS monthname, 0 as workhour  FROM    master.dbo.spt_values x WHERE   x.type = 'P' AND     x.number <= DATEDIFF(MONTH, '2013-01-01', '2014-03-01')

EXCEPT

SELECT Year(SG_PROCESS_DATE) AS yearname ,DATENAME(MONTH, DateAdd(MONTH, MONTH(SG_PROCESS_DATE), 0) - 1) AS monthname ,0 AS workhour FROM innovator.[SG_ATTENDANCE] WHERE ( SG_PROCESS_DATE BETWEEN '2013-01-01' AND '2014-03-01') GROUP BY YEAR(SG_PROCESS_DATE),MONTH(SG_PROCESS_DATE)

---

Related Topics