Tsql: How to Retrieve the Last Date of Each Month Between Given Date Range

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 dates between two dates in SQL Server

My first suggestion would be use your calendar table, if you don't have one, then create one. They are very useful. Your query is then as simple as:

DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106';

SELECT  Date
FROM    dbo.Calendar
WHERE   Date >= @MinDate
AND     Date < @MaxDate;

If you don't want to, or can't create a calendar table you can still do this on the fly without a recursive CTE:

DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106';

SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;

For further reading on this see:

• Generate a set or sequence without loops – part 1
• Generate a set or sequence without loops – part 2
• Generate a set or sequence without loops – part 3

With regard to then using this sequence of dates in a cursor, I would really recommend you find another way. There is usually a set based alternative that will perform much better.

So with your data:

  date   | it_cd | qty 
24-04-14 |  i-1  | 10 
26-04-14 |  i-1  | 20

To get the quantity on 28-04-2014 (which I gather is your requirement), you don't actually need any of the above, you can simply use:

SELECT  TOP 1 date, it_cd, qty 
FROM    T
WHERE   it_cd = 'i-1'
AND     Date <= '20140428'
ORDER BY Date DESC;

If you don't want it for a particular item:

SELECT  date, it_cd, qty 
FROM    (   SELECT  date, 
                    it_cd, 
                    qty, 
                    RowNumber = ROW_NUMBER() OVER(PARTITION BY ic_id 
                                                    ORDER BY date DESC)
            FROM    T
            WHERE   Date  <= '20140428'
        ) T
WHERE   RowNumber = 1;

SQL query to select dates between two dates

you should put those two dates between single quotes like..

select Date, TotalAllowance from Calculation where EmployeeId = 1
             and Date between '2011/02/25' and '2011/02/27'

or can use

select Date, TotalAllowance from Calculation where EmployeeId = 1
             and Date >= '2011/02/25' and Date <= '2011/02/27'

keep in mind that the first date is inclusive, but the second is exclusive, as it effectively is '2011/02/27 00:00:00'

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

Months between two dates

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SELECT   @StartDate = '20110501'        
        ,@EndDate   = '20110801';

SELECT  DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'        
AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);

Results:

MonthName
------------------------------
May
June
July
August

(4 row(s) affected)

SQL - Search table for last day of each month in date range and output data?

You can use a recursive cte to get your dates and join your snapshot table to the cte

DECLARE @StartDate DATETIME = '2016-01-01',
    @EndDate DATETIME = '2016-03-03';

WITH DateCTE AS 
(
    SELECT    EOMONTH(@StartDate) snapshot_date
    UNION ALL
    SELECT    EOMONTH(DATEADD(MONTH,1,snapshot_date))
    FROM      DateCTE
    WHERE     EOMONTH(DATEADD(MONTH,1,snapshot_date)) <= EOMONTH(@EndDate)
)
SELECT  Project_Group AS Project_Name,
        trs.snapshot_date,
        COUNT(ticket_status) AS Open_Tickets
FROM    Ticket_Report_SnapShot trs
        INNER JOIN DateCTE cte ON trs.snapshot_date = cte.snapshot_date
WHERE   ticket_status != 'closed'
        AND ticket_status != 'cancelled'
GROUP BY Project_Group,
        trs.snapshot_date 

You can use this if you are still using SQL Server 2008

DECLARE @StartDate DATETIME = '2016-01-01',
        @EndDate DATETIME = '2016-03-03';

WITH DateCTE AS 
(
    SELECT    DATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,@StartDate) + 1,0)) snapshot_date
    UNION ALL
    SELECT    DATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,DATEADD(MONTH,1,snapshot_date)) + 1,0))
    FROM      DateCTE
    WHERE     DATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,DATEADD(MONTH,1,snapshot_date)) + 1,0)) <= DATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,@EndDate) + 1,0))
)
SELECT  Project_Group AS Project_Name,
        trs.snapshot_date,
        COUNT(ticket_status) AS Open_Tickets
FROM    Ticket_Report_SnapShot trs
        INNER JOIN DateCTE cte ON trs.snapshot_date = cte.snapshot_date
WHERE   ticket_status != 'closed'
        AND ticket_status != 'cancelled'
GROUP BY Project_Group,
        trs.snapshot_date 

---

Related Topics