locked
Number of days between a date and the its month end RRS feed

  • Question


  • Dear all,

    I have a column which stores a set of dates. I want to tell how many days left of a date till it’s month end. It should be noted that month ends are taken from the date series, not a calendar month end.

     

    Something like below, thanks

     

    DateTD        Days left

    2009-01-05       14

    2009-01-06       13

    2009-01-07       12

    2009-01-08       11

    2009-01-09       10

    2009-01-12       9

    2009-01-13       8

    2009-01-14       7

    2009-01-15       6

    2009-01-16       5

    2009-01-19       4

    2009-01-20       3

    2009-01-21       2

    2009-01-22       1

    2009-01-23       0

    2009-02-02       /

    2009-02-03       /

    Wednesday, September 23, 2015 9:24 AM

Answers

  • use tempdb
    GO
    create table T (DateTD date)
    GO
    insert T (DateTD)
    values
    ('2009-01-05'),
    ('2009-01-06'),
    ('2009-01-07'),
    ('2009-01-08'),
    ('2009-01-09'),
    ('2009-01-12'),
    ('2009-01-13'),
    ('2009-01-14'),
    ('2009-01-15'),
    ('2009-01-16'),
    ('2009-01-19'),
    ('2009-01-20'),
    ('2009-01-21'),
    ('2009-01-22'),
    ('2009-01-23'),
    ('2009-02-02'),
    ('2009-02-03')
    GO
    select *,row_number()over(partition by year(datetd),month(datetd) order by datetd desc)-1 DaysRemaining from t order by datetd

    • Proposed as answer by Jason A Long Wednesday, September 23, 2015 1:59 PM
    • Marked as answer by Bobojin_Z Thursday, September 24, 2015 1:50 AM
    Wednesday, September 23, 2015 1:38 PM
  • This is just a simple "running count" problem. As long as you're using SQL Server 2012 or higher, the following should do the trick for you...

    IF OBJECT_ID('tempdb..#temp') IS NOT NULL 
    DROP TABLE #temp;
    
    CREATE TABLE #temp (
    	Dates DATE 
    	);
    
    INSERT #temp (Dates)
    values
    ('2009-01-05'),
    ('2009-01-06'),
    ('2009-01-07'),
    ('2009-01-08'),
    ('2009-01-09'),
    ('2009-01-12'),
    ('2009-01-13'),
    ('2009-01-14'),
    ('2009-01-15'),
    ('2009-01-16'),
    ('2009-01-19'),
    ('2009-01-20'),
    ('2009-01-21'),
    ('2009-01-22'),
    ('2009-01-23'),
    ('2009-02-02'),
    ('2009-02-03');
    
    SELECT 
    	t.Dates,
    	MONTH(t.Dates) AS MunthNum,
    	COUNT(*) OVER (PARTITION BY YEAR(t.Dates), MONTH(t.Dates) ORDER BY t.Dates DESC) -1 AS DaysLeft
    FROM 
    	#temp t
    ORDER BY 
    	t.Dates

    HTH,

    Jason


    Jason Long



    • Edited by Jason A Long Wednesday, September 23, 2015 2:01 PM Posted before I was Pavan's solution
    • Marked as answer by Bobojin_Z Thursday, September 24, 2015 3:05 AM
    Wednesday, September 23, 2015 1:58 PM
  • What version of SQL Server are you using?

    We have several windows functions improvements since version 2012 that could be helpful to solve your question.

    CREATE TABLE #T (
    DateTD date NOT NULL PRIMARY KEY
    );
    
    INSERT #T
    	(DateTD)
    VALUES
    	('2009-01-05'),
    	('2009-01-06'),
    	('2009-01-07'),
    	('2009-01-08'),
    	('2009-01-09'),
    	('2009-01-12'),
    	('2009-01-13'),
    	('2009-01-14'),
    	('2009-01-15'),
    	('2009-01-16'),
    	('2009-01-19'),
    	('2009-01-20'),
    	('2009-01-21'),
    	('2009-01-22'),
    	('2009-01-23'),
    	('2009-02-02'),
    	('2009-02-03');
    
    SELECT
    	DateTD,
    	COUNT(*) OVER(
    	PARTITION BY YEAR(DateTD), MONTH(DateTD) 
    	ORDER BY DateTD
    	ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING 
    	) AS days_left
    FROM
    	#T
    ORDER BY
    	DateTD;
    GO
    DROP TABLE #T;
    GO



    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas




    • Edited by HunchbackMVP Wednesday, September 23, 2015 3:31 PM
    • Marked as answer by Bobojin_Z Thursday, September 24, 2015 3:05 AM
    Wednesday, September 23, 2015 3:00 PM
  • CREATE TABLE test (DateTD date )
    
    INSERT test	(DateTD) VALUES
    	('2009-01-05'),
    	('2009-01-06'),
    	('2009-01-07'),
    	('2009-01-08'),
    	('2009-01-09'),
    	('2009-01-12'),
    	('2009-01-13'),
    	('2009-01-14'),
    	('2009-01-15'),
    	('2009-01-16'),
    	('2009-01-19'),
    	('2009-01-20'),
    	('2009-01-21'),
    	('2009-01-22'),
    	('2009-01-23'),
    	('2009-02-02'),
    	('2009-02-03');
    
    
    Select DateTD  
    , Datediff(day,DateTD, max(DateTD) Over(Partition by Month(DateTD), Year(DateTD) )) - datediff(week, DateTD,max(DateTD) Over(Partition by Month(DateTD), Year(DateTD) )) *2  as [Days left] 
     
    From test
    
    drop table test

    • Marked as answer by Bobojin_Z Thursday, September 24, 2015 3:05 AM
    Wednesday, September 23, 2015 3:28 PM

All replies

  • How your date series are identified? Is it 23rd of every month?

    Amit Govil | Email

    "Weeks of coding can save you hours of planning"

    Wednesday, September 23, 2015 9:36 AM
  • Good day,

    1. This is simple math on dates. There is no need to use another table which will leads to the use of JOIN (expensive operation).

    2. I am not sure what your data in the question since there is nothing between the "day left" and the real value.

    3. There is no reason and in most cases it will be mistake to have the seconf column in the table since this calculate value from the first column. If you want to use it then you should use calculate column and not to insert duplicate value. duplicate balues leads to mistakes!

    4. "month ends are taken from the date series"

    why?!? you machine already know all the information regarding month end. Your operation system has built-in a calendar :-)

    Let's go to your question's answer :-)
    You can calculate the days to the first day in the next month, which will give you the days let in this month. Please check if this solution fits you:

    use tempdb
    GO
    
    ------------------------------ DDL
    -- This is something that YOU should have post instead of text
    
    create table T (DateTD date)
    GO
    
    ------------------------------ DML
    -- same as above! please next time you should post the DDL+DML for us :-)
    insert T (DateTD)
    values
    ('2009-01-05'),
    ('2009-01-06'),
    ('2009-01-07'),
    ('2009-01-08'),
    ('2009-01-09'),
    ('2009-01-12'),
    ('2009-01-13'),
    ('2009-01-14'),
    ('2009-01-15'),
    ('2009-01-16'),
    ('2009-01-19'),
    ('2009-01-20'),
    ('2009-01-21'),
    ('2009-01-22'),
    ('2009-01-23'),
    ('2009-02-02'),
    ('2009-02-03')
    GO
    
    ------------------------------ solution
    -- I added 2 more columns in the solution just to make it clear
    -- you can see that I got the FirstDayOfMonth 
    -- Next I got the FirstDayOfNextMonth 
    -- and than I got the number of days left in this month
    select 
    	DateTD, 
    	FirstDayOfThisMonth = DATEADD(DAY, 1-DATEPART(DAY,DateTD), DateTD), 
    	FirstDayOfNextMonth = DateADD(MONTH, 1, DATEADD(DAY, 1-DATEPART(DAY,DateTD), DateTD)), 
    	Days2TheEndOfMonth = DATEDIFF(DAY,DateTD,DateADD(MONTH, 1, DATEADD(DAY, 1-DATEPART(DAY,DateTD), DateTD))) - 1
    from T
    GO
    
    There are other solutions as well, but I think this way is more clear that we have done simple math on dates


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]



    • Edited by pituachMVP Wednesday, September 23, 2015 11:05 AM
    Wednesday, September 23, 2015 11:00 AM
  • Written a couple of articles on this, some slight modification may be required.

    https://www.claytabase.co.uk/Articles/SQL-Server/SQL-Server-function-calculating-the-working-days-between-two-dates-excluding-holidays 


    Gavin Clayton Claytabase Ltd www.claytabase.co.uk

    Wednesday, September 23, 2015 1:22 PM
  • What is your date series? the sample data above 2009-01-19 4 seem not correct. 2009-01-16 5 should be 2009-01-18 5?

    A Fan of SSIS, SSRS and SSAS

    Wednesday, September 23, 2015 1:35 PM
  • use tempdb
    GO
    create table T (DateTD date)
    GO
    insert T (DateTD)
    values
    ('2009-01-05'),
    ('2009-01-06'),
    ('2009-01-07'),
    ('2009-01-08'),
    ('2009-01-09'),
    ('2009-01-12'),
    ('2009-01-13'),
    ('2009-01-14'),
    ('2009-01-15'),
    ('2009-01-16'),
    ('2009-01-19'),
    ('2009-01-20'),
    ('2009-01-21'),
    ('2009-01-22'),
    ('2009-01-23'),
    ('2009-02-02'),
    ('2009-02-03')
    GO
    select *,row_number()over(partition by year(datetd),month(datetd) order by datetd desc)-1 DaysRemaining from t order by datetd

    • Proposed as answer by Jason A Long Wednesday, September 23, 2015 1:59 PM
    • Marked as answer by Bobojin_Z Thursday, September 24, 2015 1:50 AM
    Wednesday, September 23, 2015 1:38 PM
  • This is just a simple "running count" problem. As long as you're using SQL Server 2012 or higher, the following should do the trick for you...

    IF OBJECT_ID('tempdb..#temp') IS NOT NULL 
    DROP TABLE #temp;
    
    CREATE TABLE #temp (
    	Dates DATE 
    	);
    
    INSERT #temp (Dates)
    values
    ('2009-01-05'),
    ('2009-01-06'),
    ('2009-01-07'),
    ('2009-01-08'),
    ('2009-01-09'),
    ('2009-01-12'),
    ('2009-01-13'),
    ('2009-01-14'),
    ('2009-01-15'),
    ('2009-01-16'),
    ('2009-01-19'),
    ('2009-01-20'),
    ('2009-01-21'),
    ('2009-01-22'),
    ('2009-01-23'),
    ('2009-02-02'),
    ('2009-02-03');
    
    SELECT 
    	t.Dates,
    	MONTH(t.Dates) AS MunthNum,
    	COUNT(*) OVER (PARTITION BY YEAR(t.Dates), MONTH(t.Dates) ORDER BY t.Dates DESC) -1 AS DaysLeft
    FROM 
    	#temp t
    ORDER BY 
    	t.Dates

    HTH,

    Jason


    Jason Long



    • Edited by Jason A Long Wednesday, September 23, 2015 2:01 PM Posted before I was Pavan's solution
    • Marked as answer by Bobojin_Z Thursday, September 24, 2015 3:05 AM
    Wednesday, September 23, 2015 1:58 PM
  • I'd recommend using a calendar table. You can read about them here

    Using a table like the one in that article, and assuming your End of Month date is the last working (non-holiday/non-weekend) day, you can do something like:

    DECLARE @myDates TABLE (date DATE)
    INSERT INTO @myDates (date) VALUES
    ('2013-01-05'),
    ('2013-01-06'),
    ('2013-01-07'),
    ('2013-01-08'),
    ('2013-01-09'),
    ('2013-01-12'),
    ('2013-01-13'),
    ('2013-01-14'),
    ('2013-01-15'),
    ('2013-01-16'),
    ('2013-01-19'),
    ('2013-01-20'),
    ('2013-01-21'),
    ('2013-01-22'),
    ('2013-01-23'),
    ('2013-02-02'),
    ('2013-02-03')
    
    ;WITH lastWorkingDay AS (
    SELECT year, month, MAX(today) AS maxWorkingDay
      FROM calendar
     WHERE isHoliday = 0 
       AND weekDay NOT IN (1,7)
     GROUP BY year, month
    )
    
    SELECT m.date, DATEDIFF(DAY,m.date,l.maxWorkingDay) AS daysTillEOM
      FROM @myDates m
        INNER JOIN calendar c
    	  ON m.date = c.today
        INNER JOIN lastWorkingDay l
    	  ON c.year = l.year
    	  AND c.month = l.month
    


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, September 23, 2015 2:12 PM
  • What version of SQL Server are you using?

    We have several windows functions improvements since version 2012 that could be helpful to solve your question.

    CREATE TABLE #T (
    DateTD date NOT NULL PRIMARY KEY
    );
    
    INSERT #T
    	(DateTD)
    VALUES
    	('2009-01-05'),
    	('2009-01-06'),
    	('2009-01-07'),
    	('2009-01-08'),
    	('2009-01-09'),
    	('2009-01-12'),
    	('2009-01-13'),
    	('2009-01-14'),
    	('2009-01-15'),
    	('2009-01-16'),
    	('2009-01-19'),
    	('2009-01-20'),
    	('2009-01-21'),
    	('2009-01-22'),
    	('2009-01-23'),
    	('2009-02-02'),
    	('2009-02-03');
    
    SELECT
    	DateTD,
    	COUNT(*) OVER(
    	PARTITION BY YEAR(DateTD), MONTH(DateTD) 
    	ORDER BY DateTD
    	ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING 
    	) AS days_left
    FROM
    	#T
    ORDER BY
    	DateTD;
    GO
    DROP TABLE #T;
    GO



    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas




    • Edited by HunchbackMVP Wednesday, September 23, 2015 3:31 PM
    • Marked as answer by Bobojin_Z Thursday, September 24, 2015 3:05 AM
    Wednesday, September 23, 2015 3:00 PM
  • CREATE TABLE test (DateTD date )
    
    INSERT test	(DateTD) VALUES
    	('2009-01-05'),
    	('2009-01-06'),
    	('2009-01-07'),
    	('2009-01-08'),
    	('2009-01-09'),
    	('2009-01-12'),
    	('2009-01-13'),
    	('2009-01-14'),
    	('2009-01-15'),
    	('2009-01-16'),
    	('2009-01-19'),
    	('2009-01-20'),
    	('2009-01-21'),
    	('2009-01-22'),
    	('2009-01-23'),
    	('2009-02-02'),
    	('2009-02-03');
    
    
    Select DateTD  
    , Datediff(day,DateTD, max(DateTD) Over(Partition by Month(DateTD), Year(DateTD) )) - datediff(week, DateTD,max(DateTD) Over(Partition by Month(DateTD), Year(DateTD) )) *2  as [Days left] 
     
    From test
    
    drop table test

    • Marked as answer by Bobojin_Z Thursday, September 24, 2015 3:05 AM
    Wednesday, September 23, 2015 3:28 PM
  • Hi AGovil, thanks for your reply, the month ends in data were given in data files, there is no pattern I can use in this case.


    • Edited by Bobojin_Z Thursday, September 24, 2015 2:19 AM
    Thursday, September 24, 2015 12:37 AM
  • Hi Guoxiong, The date series is trade days, neither weekdays nor calendar days, it is an input.



    • Edited by Bobojin_Z Thursday, September 24, 2015 1:25 AM
    Thursday, September 24, 2015 12:47 AM
  • Hi Ariely, Thank you for your answers and suggestions.

    The date series is an example of trading days, which is different from calendar days. The purpose is to tell the number of days between a trade day and the last trade of the month. The last trade days are embedded in the date series. I should make the questions clearer.

    Your code can be used after repalcing 'FirstDayOfThisMonth' or 'FirstDayOfNextMonth' as 'LastTradeDayOfThisMonth'.

    Thank you once again.


    • Edited by Bobojin_Z Thursday, September 24, 2015 2:19 AM
    Thursday, September 24, 2015 1:13 AM
  • Hi Patrick Hust, Thanks for your reply.

    I am not sure why the calendar table does not work on my machine, I got error message 'Msg 208, Level 16, State 1, Line 21 Invalid object name 'calendar''. Should I create the calendar table before I use it, or it is a built-in table?

    Thanks.

    Thursday, September 24, 2015 1:45 AM
  • Thank you Hunchback, I am using 2012. Your code works and your solution uses different functions from others. I like to mark yours as answer. However, I need to mark the first solution which does the job as the answer, hopefully it is fine. Thank you for your help.



    • Edited by Bobojin_Z Thursday, September 24, 2015 2:18 AM
    Thursday, September 24, 2015 1:49 AM
  • Hi Jingyang, Thanks for your reply. I have marked the first solution which does the job as the answer, hopefully it is fine. Thank you for your help.

    Thursday, September 24, 2015 1:50 AM
  • You should create that table first.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, September 24, 2015 2:00 AM
  • Hi Jason, Thanks for your reply. I am not sure which of Pavan's solution or yours came first.

    It seems that yours came in earlier but Pavan's post was placed before yours. Next time I'll mark yours.

    Thank you for your help.

    Thursday, September 24, 2015 2:16 AM
  • You should create that table first.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Sorry which Post are you referring to?
    Thursday, September 24, 2015 2:26 AM
  • invalid object name 'calendar''. Should I create the calendar table before I use it, or it is a built-in table?

    Thanks.

    -----------------

    This is what I've been answering, that you need to create that table first.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, September 24, 2015 3:02 AM
  • You can mark as many answers in one thread as you'd like. There is no rule of a single answer per thread.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, September 24, 2015 3:03 AM
  • You can mark as many answers in one thread as you'd like. There is no rule of a single answer per thread.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    OK I got it, Thank you.

    • Edited by Bobojin_Z Thursday, September 24, 2015 3:09 AM
    Thursday, September 24, 2015 3:05 AM
  • Hi Patrick Hust, Thanks for your reply.

    I am not sure why the calendar table does not work on my machine, I got error message 'Msg 208, Level 16, State 1, Line 21 Invalid object name 'calendar''. Should I create the calendar table before I use it, or it is a built-in table?

    Thanks.

    You'd need to create a calendar table. It's not something that ships with SQL Server. The article I pointed you to (here) has a walk through on how to make one.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Thursday, September 24, 2015 2:31 PM
  • Thank you Hunchback, I am using 2012. Your code works and your solution uses different functions from others. I like to mark yours as answer. However, I need to mark the first solution which does the job as the answer, hopefully it is fine. Thank you for your help.



    You can make ANY posts which are acceptable answers to your question. By marking an answer you're essentially saying "this solved my problem". This is really helpful for other people when they find your thread in a search engine. It's also nice for those giving your their time an effort to get some points :)

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Thursday, September 24, 2015 2:33 PM
  • You are most welcome Bobojin_Z :-)

    * I appreciate that you remembered to thanks people that tried to help you, even if it did not give you the direct answer, or if they misunderstood you. Unfortunately, Most people in the forum for get that this is volunteer support and forgets this small act of manners [+1]. 

    I glad to see that you got an answer that fit to your needs 
    Have a great day,



    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Saturday, September 26, 2015 5:50 AM
  • You are most welcome Bobojin_Z :-)

    * I appreciate that you remembered to thanks people that tried to help you, even if it did not give you the direct answer, or if they misunderstood you. Unfortunately, Most people in the forum for get that this is volunteer support and forgets this small act of manners [+1]. 

    I glad to see that you got an answer that fit to your needs 
    Have a great day,



    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]


    Well said, Ronen. I agree 100%.

    Jason Long

    Saturday, September 26, 2015 11:32 AM