Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Splitting dates

  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: Splitting dates

    I have a table that contains agrrements and contracts with dates. Now I need to calculate some things and I'd like the rows to only have one month per row.

    I have rows like:

    Agreement, Start, End
    ID001, 2004-01-01, 2004-04-30


    If I could get these single rows that contains 4 months into a temptable like this:

    Agreement, Start, End
    ID001, 2004-01-01, 2004-01-31
    ID001, 2004-02-01, 2004-02-29
    ID001, 2004-03-01, 2004-03-31
    ID001, 2004-04-01, 2004-04-30

    It would simplify my calculations very much. How to do this with a query or sp?

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    SELECT Agreement, DATEPART(MM,Start) AS Month
    FROM Agreement
    GROUP BY DATEPART(MM,Start). The hard part here will be if you have a weird month. Otherwise, you can just use the above statement.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    derrick, i think what was wanted was to generate 4 rows of output for the single row that has a date range of january - april

    oneleg_theone, see this article for an idea --
    Selecting all months even if they're missing
    (site registration may be required, but it's free)

    join the integers table to your rows with
    Code:
    ... on i between month(start) and month(end)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2003
    Posts
    123
    Ok, I've looked into the article, but I think I'm too tired right now to get it.

    It appears that I have to create a table which contains integers, but how many? Does that depend on how many months there can be between Start and End?

    A table which only contains 0,1,2,3,4,5,6,7,8,9,10,11,12 ?? For one year..

    Seems like a nice solution though, now if I could only understand the logic behind it. :-)

    I'll look into this again tomorrow, now it's time to go home...

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this was a bit trickier than it looked

    the integers table approach still works, but generating feb 29 required a good rethink

    okay, so here it is

    however many months your agreements can span from start to finish is how many integers you need

    for example, for an agreement from 2002-12-01 to 2005-01-31, you want to generate 26 rows, so you need 26 integers

    Code:
    create table Agreements 
    ( Agreement char(1)
    , Startdate datetime
    , Enddate datetime
    )
    insert into Agreements values ('a','2002-12-01','2005-01-31')
    insert into Agreements values ('b','2004-02-01','2005-01-31')
    
    create table integers (i integer)
    insert into integers
    select 0 union all
    select 1 union all
    select 2 union all
    select 3 union all
    select 4 union all
    select 5 union all
    select 6 union all
    select 7 union all
    select 8 union all
    select 9 union all
    select 10 union all
    select 11 union all
    select 12 union all
    select 13 union all
    select 14 union all
    select 15 union all
    select 16 union all
    select 17 union all
    select 18 union all
    select 19 union all
    select 20 union all
    select 21 union all
    select 22 union all
    select 23 union all
    select 24 union all
    select 25 union all
    select 26 union all
    select 27 union all
    select 28
    you can load as many integers into this table as necessary to cover the largest span of months

    (i usually use only 0 through 9 and cross join them together three times to get 0 through 999, but let's keep it simple, and use just one integers table)

    Code:
    select Agreement
         , convert(char(10),dateadd(m, i, Startdate),120)   as Startdate
         , convert(char(10),dateadd(d, -1
                             , dateadd(m, i+1, Startdate)
                                         )          ,120)   as Enddate
      from integers
    cross 
      join Agreements  
     where i <= datediff(month,Startdate,Enddate)  
    order by 1,2
    
    a 2002-12-01 2002-12-31
    a 2003-01-01 2003-01-31
    a 2003-02-01 2003-02-28
    a 2003-03-01 2003-03-31
    a 2003-04-01 2003-04-30
    a 2003-05-01 2003-05-31
    a 2003-06-01 2003-06-30
    a 2003-07-01 2003-07-31
    a 2003-08-01 2003-08-31
    a 2003-09-01 2003-09-30
    a 2003-10-01 2003-10-31
    a 2003-11-01 2003-11-30
    a 2003-12-01 2003-12-31
    a 2004-01-01 2004-01-31
    a 2004-02-01 2004-02-29
    a 2004-03-01 2004-03-31
    a 2004-04-01 2004-04-30
    a 2004-05-01 2004-05-31
    a 2004-06-01 2004-06-30
    a 2004-07-01 2004-07-31
    a 2004-08-01 2004-08-31
    a 2004-09-01 2004-09-30
    a 2004-10-01 2004-10-31
    a 2004-11-01 2004-11-30
    a 2004-12-01 2004-12-31
    a 2005-01-01 2005-01-31
    b 2004-02-01 2004-02-29
    b 2004-03-01 2004-03-31
    b 2004-04-01 2004-04-30
    b 2004-05-01 2004-05-31
    b 2004-06-01 2004-06-30
    b 2004-07-01 2004-07-31
    b 2004-08-01 2004-08-31
    b 2004-09-01 2004-09-30
    b 2004-10-01 2004-10-31
    b 2004-11-01 2004-11-30
    b 2004-12-01 2004-12-31
    b 2005-01-01 2005-01-31
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2003
    Posts
    123
    Perfect!

    Now I can drop two out of three temptables in my sp, AND a problem is solved. :-)

    I bet this solution is usable in other situations as well, thanx again.

  7. #7
    Join Date
    Jul 2003
    Posts
    123
    A problem has occurred.. agreements can be started and ended at ANY date, not just at the beginning or the end of the month...

    I tried splitting the dates day by day but that method generates far to many rows...

    How do I handle this?

    Example:

    Start : 2004-01-15
    End : 2004-03-31

    With the code I have it generates:
    2004-01-15 - 2004-02-14
    2004-02-15 - 2004-03-14
    2004-03-15 - 2004-04-14

    I want it to generate:
    2004-01-15 - 2004-01-31
    2004-02-01 - 2004-02-29
    2004-03-01 - 2004-03-31

    How can I do this? Is it possible in one query or do I need several? This is done in a sp so multiple queries are no problem.
    -------------------------

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a pure "off the cuff" shot, but something like:
    Code:
    CREATE PROCEDURE p200407070726
       @pdBegin DATETIME
    ,  @pdEnd DATETIME
    AS
    
    CREATE TABLE #ranges (
       rbegin DATETIME NOT NULL
    ,  rend DATETIME NOT NULL
       )
    
    SET @dBegin = @pdBegin
    SET @dEnd = DateAdd(ms, -3, Convert(CHAR(8), DateAdd(month, 1, @dBegin), 121) + '01')
    
    WHILE @dBegin < @pdEnd
       BEGIN
          INSERT INTO #ranges (rbegin, rend) @dWork, CASE WHEN @dEnd < @pdEnd THEN @dEnd ELSE @pdEnd END
          SET @dBegin = DateAdd(month, 1, @dEnd)
          SET @dEnd = DateAdd(ms, -3, Convert(CHAR(8), DateAdd(month, 1, @dBegin), 121) + '01')
       END
    
    SELECT Agreement
    ,  CASE WHEN r.rBegn < a.start THEN a.start ELSE r.begin END
    ,  CASE WHEN r.rEnd < a.end THEN r.rEnd ELSE a.end END
       FROM Agreement AS a
       JOIN #ranges AS r
          ON (r.rBegin <= a.end
          AND a.start <= r.rEnd)
    
    RETURN
    -PatP

  9. #9
    Join Date
    Jul 2003
    Posts
    123
    Generates errors when I check syntax. I'm not so experienced with variables in sp, but I figured out that @dBegin, @dEnd needed to be declared.

    But whats @dWork for?

    And how will I use this sp if I have a table with 10000 rows with agreements?

    Question questions... :-)

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Let me get to somewhere I can set up a test case, and run it on a screen larger than 160 by 160. Maybe when I can see/test what I'm doing, I'll get it right on the next try!

    -PatP

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, now that I can see the whole thing, I didn't do too bad for composing on the fly, scribbling on a somewhat bloated postage stamp! Let's try again, using:
    Code:
    CREATE TABLE tAgreement (
       Agreement		VARCHAR(20)		NOT NULL
    ,  start		DATETIME		NOT NULL
    ,  [end]		DATETIME		NOT NULL
       )
    
    INSERT INTO tAgreement VALUES ('ID001', '2004-04-01', '2004-04-30')
    INSERT INTO tAgreement VALUES ('PP001', '2004-01-01', '2004-12-31')
    GO
    
    DROP PROCEDURE p200407070726
    GO
    CREATE PROCEDURE p200407070726
       @pdBegin DATETIME
    ,  @pdEnd DATETIME
    AS
    
    CREATE TABLE #ranges (
       rbegin DATETIME NOT NULL
    ,  rend DATETIME NOT NULL
       )
    
    DECLARE
       @dBegin	DATETIME
    ,  @dEnd	DATETIME
    
    SET @dBegin = @pdBegin
    SET @dEnd = DateAdd(day, -1, Convert(CHAR(8), DateAdd(month, 1, @dBegin), 121) + '01')
    
    WHILE @dBegin < @pdEnd
       BEGIN
          INSERT INTO #ranges (rbegin, rend)
             SELECT @dBegin, CASE WHEN @dEnd < @pdEnd
                THEN @dEnd ELSE @pdEnd END
    
          SET @dBegin = Convert(CHAR(8), DateAdd(month, 1, @dBegin), 121) + '01'
          SET @dEnd = DateAdd(day, -1, Convert(CHAR(8)
    ,        DateAdd(month, 1, @dBegin), 121) + '01')
       END
    
    SELECT Agreement
    ,  CASE WHEN r.rBegin < a.start THEN a.start ELSE r.rBegin END
    ,  CASE WHEN r.rEnd < a.[end] THEN r.rEnd ELSE a.[end] END
       FROM tAgreement AS a
       JOIN #ranges AS r
          ON (r.rBegin <= a.[end]
          AND a.start <= r.rEnd)
    
    RETURN
    GO
    
    EXECUTE p200407070726 '2004-02-01', '2004-09-30'
    Note that this builds test data, the procedure, and executes it.

    -PatP

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    Let me get to somewhere I can set up a test case, and run it on a screen larger than 160 by 160. Maybe when I can see/test what I'm doing, I'll get it right on the next try!

    -PatP

    Pat...are you drunk?


    What's @dWork for?

    Code:
    USE Northwind
    GO
    
    CREATE PROCEDURE p200407070726
       @pdBegin DATETIME
    ,  @pdEnd DATETIME
    AS
    
    DECLARE    @dBegin DATETIME
    	,  @dEnd DATETIME
    
    
    CREATE TABLE #ranges (
       rbegin DATETIME NOT NULL
    ,  rend DATETIME NOT NULL
       )
    
    SET @dBegin = @pdBegin
    SET @dEnd = DateAdd(ms, -3, Convert(CHAR(8), DateAdd(month, 1, @dBegin), 121) + '01')
    
    WHILE @dBegin < @pdEnd
       BEGIN
          INSERT INTO #ranges (rbegin, rend) SELECT @dWork, CASE WHEN @dEnd < @pdEnd THEN @dEnd ELSE @pdEnd END
          SELECT 	  @dBegin = DateAdd(month, 1, @dEnd)
          		, @dEnd = DateAdd(ms, -3, Convert(CHAR(8), DateAdd(month, 1, @dBegin), 121) + '01')
       END
    
    SELECT Agreement
    ,  CASE WHEN r.rBegn < a.start THEN a.start ELSE r.begin END
    ,  CASE WHEN r.rEnd < a.end THEN r.rEnd ELSE a.end END
       FROM Agreement AS a
       JOIN #ranges AS r
          ON (r.rBegin <= a.end
          AND a.start <= r.rEnd)
    
    RETURN
    GO
    
    EXEC p200407070726 '1/1/2004', '6/30/2004'
    GO
    
    DROP PROC p200407070726
    GO
    Got rid of the rest of the syntax errors, except for that....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    Pat...are you drunk?
    No, not drunk at all... That was a side effect of writing code on a 160 by 160 screen, with a machine where I couldn't test at all. If you check my revised post, it works way more gooder yet even!

    -PatP

  14. #14
    Join Date
    Jul 2003
    Posts
    123
    And again I must ask:
    "And how will I use this sp if I have a table with 10000 rows with agreements?"

    I don't understand how this sp works... first I tried it with an empty tAgreement table, didn't work. Then I tried it with the values that you had there (what are they for?) And got a faulty result.

    I tried EXEC p200407070726 '2004-01-16', '2004-05-25'

    and got the dates

    2004-01-16 - 2004-01-31
    2004-02-01 - 2004-02-29
    2004-03-01 - 2004-03-31
    2004-04-01 - 2004-04-30
    2004-04-01 - 2004-04-30
    2004-05-01 - 2004-05-25

    TWO rows with 2004-04-01 - 2004-04-30...

    And I still don't get it how I will be able to use this proc on my table.

    I have one table with agreements (about 10000 rows) and want to generate another table where these rows are split month by month.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What's faulty about reporting two different agreements in April? I thought that made sense. What do you want to see once you have 10,000 agreements? I'm really confused now!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •