Post reply

Date three months ago (sort of...)

  • January 13, 2006 at 11:05 am

    #89230

    Hi,

    Because our company's 'months' always need to end on a Friday and start on a Monday, the dates that we use for reporting aren't the first and last day of the month (1st and 31st), but the nearest monday and friday (eg 28th Jan 06 to 31st March 06).  I need to create a report which relies upon me establishing the accounting month three months ago.  So, for example, we are currently in January 06 (or 06-01 as per the 'code' column) and I need to pass in today's date, and get back the code three months ago i.e. 05-10.

    Can anyone suggest a way of doing this other than horrible nasty looping\cursor methods???

    ID     CODE    STARTDATE                  ENDDATE
--    ------   -------------              ------------------
61    05-08    2005-07-30 00:00:00.000    2005-09-02 00:00:00.000
62    05-09    2005-09-03 00:00:00.000    2005-09-30 00:00:00.000
63    05-10    2005-10-01 00:00:00.000    2005-10-28 00:00:00.000
64    05-11    2005-10-29 00:00:00.000    2005-12-02 00:00:00.000
65    05-12    2005-12-03 00:00:00.000    2005-12-30 00:00:00.000
66    06-01    2005-12-31 00:00:00.000    2006-01-27 00:00:00.000
67    06-02    2006-01-28 00:00:00.000    2006-02-24 00:00:00.000
68    06-03    2006-02-25 00:00:00.000    2006-03-31 00:00:00.000
69    06-04    2006-04-01 00:00:00.000    2006-04-28 00:00:00.000
70    06-05    2006-04-29 00:00:00.000    2006-06-02 00:00:00.000

    Many thanks,

    Martin

  • January 13, 2006 at 11:20 am

    #614706

    Ever heard of Y2K ? Why not use a code in the YYYY-MM format to avoid ambiguity ? Or use a real date type instead of char/varchar.

    Select Replace( '20' + Code + '01', '-', '') As ISODate

    From YourTable

    That converts your 'Code' to a real date in ISO format. Pull the ISODate for the current period, DateAdd() -3 months from the ISODate, search for the resulting ISODate 3 months earlier.

    Is it too late to change the table design, or at least tag on an extra column that gives you a 'real' date to work with. If you are always going to be doing math on the 'Code' column, you need to design it to hold something other than char/varchar.

     

  • January 16, 2006 at 2:33 am

    #614852

    Thanks for the reply, PW,

    Oh, how I wish it was possible to change the format of the code column!  Sadly, it's been there for about 3 years now, and there are many reports\functions\sp's which rely on it, so it has to stay I'm afraid.

    The problem I can see with your solution, is that if I use a DateAdd() function to knock off 3 months, that will not necessarily give me the row 3 periods back.  For example, if I ran that function on 26th February 06 (accounting period 06-03), dateadd(mm,-3,'20060226') will give 26th November 05 as you would expect of course.  However this date actually falls in the period four periods back (05-11). 

    The catch is that the accounting dates are not the first and last CALENDAR dates of each month.

    Any more suggestions very welcome.

    Thanks again,

    Martin

  • January 16, 2006 at 5:55 am

    #614867

    Could you state more precisely how StartDate and EndDate are calculated? StartDate is always a Saturday, not a Monday...

    It seems to be enough to describe how one of the dates are found, as the other one can be found by looking at the previous or next month...

     

  • January 16, 2006 at 6:15 am

    #614869

    Hi Jesper

    The dates are pre defined by our accounts department; there is no actual formula.  You are right about start dates always being a Saturday, my Monday to Friday analogy is in fact inaccurate.  The data for start dates and end dates is entered manually.

    Our company took the decision to always end months at the end of a week instead of using calendar months which could end midweek as we are have a large sales force, and it is easier to motivate them if there is always a deadline of 17:30 on a Friday.

    Hope that makes more sence,

    Martin

  • January 16, 2006 at 6:26 am

    #614871

    Oh, I thought this problem was about calculating all rows of the table you were referencing above

    I assume (now) that you have a table containing all these dates. Then you could try the following:

    select substring(cast(datepart(yy, dateadd(m, -3, '20' + code + '-01')) as varchar), 3, 2) + '-'

    + cast(datepart(mm, dateadd(m, -3, '20' + code + '-01')) as varchar)

    from yourtable where startdate <= getdate() and getdate() <= enddate

  • January 16, 2006 at 6:31 am

    #614872

    Correction: Replace getdate() above by

    dateadd(d, datediff(d, '1900', getdate()), '1900')

    to get rid of hours, minutes, seconds and milliseconds...

  • January 16, 2006 at 7:05 am

    #614878

    If the dates are predefined and there is REALLY no formula then one of your options is to create a new table with

    MonthCode - this is where your 05-10 will go

    StartDate - the accounting month start date

    EndDate - the accounting month end date

    and then just link with this table to get the proper MonthCode for whatever specific date you want. Bacause you want the MonthCodes always for a date 3 months in the past you can adjust your SatrtDate and EndDate to reflect this.

    If you have a formula then please spell it out so one can figure out the code to help you.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • January 16, 2006 at 7:06 am

    #614880

    Sadly, I think that this solution will fall down for the same reason as PW's above...
     

         

    The problem I can see with your solution, is that if I use a DateAdd() function to knock off 3 months, that will not necessarily give me the row 3 periods back.  For example, if I ran that function on 26th February 06 (accounting period 06-03), dateadd(mm,-3,'20060226') will give 26th November 05 as you would expect of course.  However this date actually falls in the period four periods back (05-11). 

    I am starting to resign myself to the fact that I will need to use a temp table\table variable with an identity column so that I can bring back the third row back by subtracting 3 from the current month's row.

  • January 16, 2006 at 7:53 am

    #614888

    Thanks for your replies.  I've decided that there isn't a way to do this using a single select statement, so I have created the following solution which will pull the accounting dates table into a table variable including an identity column.  The function will then subtract 3 from the id to return the row three up.

    create function udf_GefOffsetPeriod (@datein datetime)
    returns char(5)
    as
BEGIN
 
 declare @returnval char(5)
 
 --This will give me consecutive id's for each row
 declare @accMonth table (id int identity(1,1), code char(5), date_from datetime, date_to datetime)
 insert into @accMonth select code, date_from, date_to from bapsdata..accounting_months  
 
 select @returnval = code from @accMonth where id = (
     select id from @accMonth where @datein between date_from and date_to
&nbsp - 3
 
 return @returnval
    END

    If anybody can come up with a more efficient solution, I'd be very happy to hear it 

    Thanks again,

    Martin

  • January 17, 2006 at 1:53 am

    #615063

    Please explain in more detail why my method fails - the code below returns "05-12" as requested...

     

    declare @Dates table(Code varchar(10), StartDate datetime, EndDate datetime)

    insert @Dates select '05-10', '2005-10-01 00:00:00.000', '2005-10-28 00:00:00.000'

    insert @Dates select '05-11', '2005-10-29 00:00:00.000', '2005-12-02 00:00:00.000'

    insert @Dates select '05-12', '2005-12-03 00:00:00.000', '2005-12-30 00:00:00.000'

    insert @Dates select '06-01', '2005-12-31 00:00:00.000', '2006-01-27 00:00:00.000'

    insert @Dates select '06-02', '2006-01-28 00:00:00.000', '2006-02-24 00:00:00.000'

    insert @Dates select '06-03', '2006-02-25 00:00:00.000', '2006-03-31 00:00:00.000'

    declare @Date datetime

    select @Date = '2006-02-26'

    select substring(cast(datepart(yy, dateadd(m, -3, '20' + code + '-01')) as varchar), 3, 2) + '-'

    + cast(datepart(mm, dateadd(m, -3, '20' + code + '-01')) as varchar)

    from @Dates where startdate <= dateadd(d, datediff(d, '1900', @Date), '1900')

    and dateadd(d, datediff(d, '1900', @Date), '1900') <= enddate

     

  • January 17, 2006 at 7:32 am

    #615151

    When I tried this example, I got 05-10.  I got the same result regardless of the value I placed in @Date.  The same thing happened when I tried this from your original post.

    However, it occured to me that there could be a datetime format problem, as I am in the UK and use British English format, so I put "set dateformat ymd" at the top, and now your query works perfectly.

    many thanks for your help, Jesper 

  • January 18, 2006 at 6:58 am

    #615462

    How about

    SELECT b.CODE

    FROM

    a

    INNER JOIN

    b ON b.[ID] = a.[ID]-3

    WHERE @datein BETWEEN a.STARTDATE AND a.ENDDATE

    or have I got this completely wrong

    Far away is close at hand in the images of elsewhere.
    Anon.

  • January 18, 2006 at 7:09 am

    #615469

    Despite the description of the table in the initial post, I don't think there is an identity column (or a sequential ID) on the table. Quote:

    I am starting to resign myself to the fact that I will need to use a temp table\table variable with an identity column so that I can bring back the third row back by subtracting 3 from the current month's row.
     
    But I am not sure... In my opinion, your solution is definitely the simplest...
     

  • January 18, 2006 at 7:53 am

    #615489

    How about this then

    SELECT MIN(CODE) AS

    FROM (SELECT TOP 3 CODE

    FROM

    WHERE ENDDATE < @datein

    ORDER BY CODE DESC) x

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply