Date Split - Sql help
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Date Split - Sql help

  1. #1
    Join Date
    Nov 2002
    Posts
    11

    Post Date Split - Sql help

    hi Sql Gurus,
    This is challenging sql for me , i hope you could solve this.

    I have a table with following info
    SDate EDate Value
    10/10/2002 10/15/2002 15
    10/12/2002 10/14/2002 28
    10/20/2002 10/30/2002 30

    Sql should return like this
    SDate EDate Value
    10/10/2002 10/11/2002 15
    10/12/2002 10/14/2002 28
    10/15/2002 10/15/2002 15
    10/20/2002 10/30/2002 30

    The SQL is to split the records if the date range is overlapped. If it's fully overlapped sql should return one record for the overlapping.

    Any help/suggestion is appreciated.
    [Cursor/dynamic sql also okay for me]

    Thanks
    Senthil

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Okay, you may need to provide some more info here. First of all, this is an extremely wierd request. You may want to seriously look at your table design here. Regardless, you need to define some rules. Since rows 1 and 2 both cover the same days, why does row 2 win out?

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Nov 2002
    Posts
    11
    Chris,
    The application has to accept overlapping date. While viewing the data, the screen has to split the date & show in the above format.
    Actaully i've one more table that has data for each days.

    ie in the first table i store
    SDate EDate Value
    10/10/2002 10/15/2002 15

    in the second table i store
    date value
    10/10/2002 15
    10/11/2002 15
    ...
    10/15/2002 15

    If the date is overlapped in the first table, the corresponding record in the second table will be replaced with the newly overlapped records.

    Hope to get a solution.

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Okay, I'm still confused. The second table sounds like a results table, but then why did you ask for a result in a different format (in your first query)? If it isn't a results table, then what is it?

    Secondly, I don't believe you answered my question. If 2 date ranges overlap in the first table, what is the rule for which range wins for which dates? What if there are 3 overlapping ranges?

    Please provide as much detail as possible.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Nov 2002
    Posts
    11
    You are correct chris. The second table is result table & first table is main table. I give you an example to make this clear.

    @10 am User creates an entry for date range 10/10/2002 to 10/15/2002 with qty 15. After 1 hour or next day, he thinks few dates should contain high qty so creates one more record for date range 10/12/02 to 10/14/2002 with qty 28.

    ie last entry always wins

    After he saves the record through gui, he will expect to see in the below format

    10/10/2002 10/11/2002 15
    10/12/2002 10/14/2002 28
    10/15/2002 10/15/2002 15

    As per spec, once user added a record, he cannot update.
    If i get a solution for this, i would solve my performance issue. Please help me..........

    Have a nice day

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Okay, now define 'last record'. You provided no sequenced column or date column to obviously define what 'last record' meant.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  7. #7
    Join Date
    Nov 2002
    Posts
    11
    For good html reply please click this link
    http://www26.brinkster.com/kec9498/MainResult.htm

    Each records in main & result table has RowID ie Sequence & main table has Submission ID which identifies the unique submission.

    Similarly each record has last user id & last time stamp ie who created & when it's created.

    ie @10AM records in Main & result tables are

    Main Table
    ID SDate EDate qty SubmissionID lastuserid lasttimestamp
    10 10/10/02 10/15/2002 15 3 xx xx-xx-xxxx
    11 10/20/02 10/30/2002 30 3 xx xx-xx-xxxx

    User has added 2 records in main table, the submission ID is 3[which is unique for each submission]

    Result Table
    ID MainTableID Date qty lastuserid lasttimestamp
    1 10 10/10/02 15 xx xx-xx-xxxx
    2 10 10/11/02 15 xx xx-xx-xxxx
    3 10 10/12/02 15 xx xx-xx-xxxx
    4 10 10/13/02 15 xx xx-xx-xxxx
    5 10 10/14/02 15 xx xx-xx-xxxx
    6 10 10/15/02 15 xx xx-xx-xxxx
    6 11 10/20/02 30 xx xx-xx-xxxx
    7 11 10/21/02 30 xx xx-xx-xxxx
    --
    --
    10 11 10/30/02 30 xx xx-xx-xxxx

    After 1hr or next day user adds one more record which overlaps with the existing record with the date range 10/12 to 10/14 qty 28.
    Now the Main & Result table data look like,

    Main Table
    ID SDate EDate qty SubmissionID lastuserid lasttimestamp
    10 10/10/02 10/15/2002 15 3 xx xx-xx-xxxx
    11 10/20/02 10/30/2002 30 3 xx xx-xx-xxxx
    12 10/12/02 10/14/2002 28 4 xx xx-xx-xxxx

    User has added 1 record in main table, the submission ID is 4

    Result Table
    ID MainTableID Date qty lastuserid lasttimestamp
    1 10 10/10/02 15 xx xx-xx-xxxx
    2 10 10/11/02 15 xx xx-xx-xxxx
    20 12 10/12/02 28 xx xx-xx-xxxx
    21 12 10/13/02 28 xx xx-xx-xxxx
    22 12 10/14/02 28 xx xx-xx-xxxx
    6 10 10/15/02 15 xx xx-xx-xxxx
    6 11 10/20/02 30 xx xx-xx-xxxx
    7 11 10/21/02 30 xx xx-xx-xxxx
    --
    --
    10 11 10/30/02 30 xx xx-xx-xxxx

    I've not missed anything in table schema.

    In GUI, i've to display in this format..
    10/10/2002 10/11/2002 15
    10/12/2002 10/14/2002 28
    10/15/2002 10/15/2002 15

    Last submission always wins....

    Thanks,
    Senthil
    Last edited by taskchat; 11-12-2002 at 12:33 PM.

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Okay, we'll go with the easiest solution first.

    If you store (in another table), the last submission that was applied to the results table, then this becomes much easier. Another useful component would be a DateDimension_T table. I almost always make one of these. It holds a record for every date (going bacwards and forwards a decade or so usually). There are many advantages to such a table, including being able to easily identify working days, holidays, which days belong to which quarters, etc. In this case, the primary advantage is the ability to convert ranges into rows. So, assuming we have the PrevAppliedSubmissionID somewhere that we can load into a variable (I usually have a GlobalValues_T table for such things), we would do:
    Code:
    UPDATE
       Results_T   R
    SET
       (
       MainTableID    ,
       Date           ,
       QTY            ,
       LastUserID     ,
       LastTimestamp
       )   =
       (
       SELECT
          MainTableID    ,
          SQL_Date       ,
          QTY            ,
          LastUserID     ,
          LastTimestamp
       FROM
          (
          SELECT
             MT.MainTableID    ,
             DD.SQL_Date       ,
             MT.QTY            ,
             MT.LastUserID     ,
             MT.LastTimestamp  ,
             ROW_NUMBER()
                OVER (
                   PARTITION BY
                      DD.DateDimension_ID
                   ORDER BY
                      MT.LastTimestamp DESC
                   )
                AS RN
          FROM
             MainTable_T       MT   ,
             DateDimension_T   DD
          WHERE
             MT.Submission_ID   >       :Submission_ID   AND
             DD.SQL_DT         BETWEEN  MT.SDate
                               AND      MT.EDate
          )
       WHERE
          RN   =   1
       )
    WHERE
       MT.Submission_ID   >   :Submission_ID
    
    
    INSERT INTO
       Results_T   R
    SELECT
       MainTableID    ,
       QTY            ,
       LastUserID     ,
       LastTimestamp
    FROM
       (
       SELECT
          MainTableID    ,
          QTY            ,
          LastUserID     ,
          LastTimestamp  ,
          ROW_NUMBER()
             OVER (
                PARTITION BY
                   DD.DateDimension_ID
                ORDER BY
                   MT.LastTimestamp DESC
                )
             AS RN
       FROM
          MainTable_T       MT   ,
          DateDimension_T   DD
       WHERE
          MT.Submission_ID   >       :Submission_ID   AND
          DD.SQL_DT         BETWEEN  MT.SDate
                            AND      MT.EDate
       )   T
    WHERE
       RN   =   1   AND
       NOT EXISTS   (
          SELECT
             1
          FROM
             Results_T   R2
          WHERE
             R2.Date   =   T.Date
          )
    So now we have the results table populated. Oh, and don't forget to update the LastSubmissionID value within the same transaction.

    Now, we need to convert these values back into ranges for the output. Give me a minute and I'll craft that for ya.


    - Chris
    Last edited by chrisrlong; 11-12-2002 at 02:19 PM.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by chrisrlong
    Okay, we'll go with the easiest solution first.

    If you store (in another table), the last submission that was applied to the results table, then this becomes much easier. Another useful component would be a DateDimension_T table. I almost always make one of these. It holds a record for every date (going bacwards and forwards a decade or so usually). There are many advantages to such a table, including being able to easily identify working days, holidays, which days belong to which quarters, etc. In this case, the primary advantage is the ability to convert ranges into rows. So, assuming we have the PrevAppliedSubmissionID somewhere that we can load into a variable (I usually have a GlobalValues_T table for such things), we would do:
    Code:
    UPDATE
    	Results_T	R
    SET
    	(
    	MainTableID		,
    	Date				,
    	QTY				,
    	LastUserID		,
    	LastTimestamp
    	)	=
    	(
    	SELECT
    		MainTableID		,
    		SQL_Date			,
    		QTY				,
    		LastUserID		,
    		LastTimestamp
    	FROM
    		(
    		SELECT
    			MT.MainTableID		,
    			DD.SQL_Date			,
    			MT.QTY				,
    			MT.LastUserID		,
    			MT.LastTimestamp	,
    			ROW_NUMBER()
    				OVER (
    					PARTITION BY
    						DD.DateDimension_ID
    					ORDER BY
    						MT.LastTimestamp DESC
    					)
    				AS RN
    		FROM
    			MainTable_T			MT	,
    			DateDimension_T	DD
    		WHERE
    			MT.Submission_ID	>			:Submission_ID	AND
    			DD.SQL_DT			BETWEEN	MT.SDate
    									AND		MT.EDate
    		)
    	WHERE
    		RN	=	1
    	)
    WHERE
    	MT.Submission_ID	>			:Submission_ID
    
    
    INSERT INTO
    	Results_T	R
    SELECT
    	MainTableID		,
    	QTY				,
    	LastUserID		,
    	LastTimestamp
    FROM
    	(
    	SELECT
    		MainTableID		,
    		QTY				,
    		LastUserID		,
    		LastTimestamp	,
    		ROW_NUMBER()
    			OVER (
    				PARTITION BY
    					DD.DateDimension_ID
    				ORDER BY
    					MT.LastTimestamp DESC
    				)
    			AS RN
    	FROM
    		MainTable_T			MT	,
    		DateDimension_T	DD
    	WHERE
    		MT.Submission_ID	>			:Submission_ID	AND
    		DD.SQL_DT			BETWEEN	MT.SDate
    								AND		MT.EDate
    	)	T
    WHERE
    	RN	=	1	AND
    	NOT EXISTS	(
    		SELECT
    			1
    		FROM
    			Results_T	R2
    		WHERE
    			R2.Date	=	T.Date
    		)
    So now we have the results table populated. Oh, and don't forget to update the LastSubmissionID value within the same transaction.

    Now, we need to convert these values back into ranges for the output. Give me a minute and I'll craft that for ya.


    - Chris
    Geez, That's the EASY solution?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by marist89
    Geez, That's the EASY solution?
    Uh, yeah. Why?

    See, the hardest solution would be to produce the resultset directly from the MainTable in a single SQL. With the existence of the Result table, we get to use 3 statements. With the existence of a stored LastSubmissionID value, the first 2 statements become much faster.

    See....easy. Right?

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

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


Click Here to Expand Forum to Full Width