Results 1 to 4 of 4

Thread: Help with Query

  1. Feb 2nd, 2007, 01:47 PM #1
    brucevde
    brucevde is offline

    Thread Starter
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Help with Query

    This query has me stumped. The DB is Oracle but I cannot use stored procedures, temp tables etc only sql statements.

    I need to return the first Start Date and last Expiry date from a Range of records if any one of the records within the range meets the following criteria (user will supply a From and To date period).

    Location Like '%S' And
    (Start Date is within the period OR
    Expiry date is within the period OR
    Start Date is < From date and Expiry Date is > To date)

    For example, user enters Aug 1 2004 to Aug 15 2004
    History Id 902112 satisfies the criteria so the range of records would be history id's 897708 - 926964. The range is based on the Location, ie go back from 902112 until a Location is NOT like '%S' and of course go forward until a Location is NOT like '%S'. So the query would return

    Master Id = 518362, Start Date = 2004-07-20 12:05:00, Expiry Date = 2004-10-14 19:07:00

    If the user enters Jul 20 2004 to Jul 21 2004, 3 records match the criteria - 897112, 897708, 897889. However 897708, 897889 are in the same range so the query would return 2 records.

    Master Id = 518362, Start Date = 2004-05-27 00:07:00, Expiry Date = 2004-07-20 11:46:00 (range would be 876306 to 897112)
    Master Id = 518362, Start Date = 2004-07-20 12:05:00, Expiry Date = 2004-10-14 19:07:00 (range would be 897708 to 926964)

    Code:
    MastId	HistId	Loc	Start Date		Expiry Date
518362	870355	A	2004-05-12 09:22:00	2004-05-12 12:16:00
518362	870571	AS	2004-05-12 12:16:00	2004-05-14 14:44:00
518362	871773	AS	2004-05-14 14:44:00	2004-05-19 16:55:00
518362	873742	AS	2004-05-19 16:55:00	2004-05-20 13:00:00
518362	874379	AS	2004-05-20 13:00:00	2004-05-21 11:30:00
518362	874931	A	2004-05-21 11:30:00	2004-05-23 14:04:00
518362	874977	AS	2004-05-23 14:04:00	2004-05-27 00:06:00
518362	876304	L	2004-05-27 00:06:00	2004-05-27 00:07:00
518362	876306	AS	2004-05-27 00:07:00	2004-07-08 10:53:00
518362	892807	AS	2004-07-08 10:53:00	2004-07-13 09:08:00
518362	894863	AS	2004-07-13 09:08:00	2004-07-17 23:58:00
518362	897112	AS	2004-07-17 23:58:00	2004-07-20 11:46:00
518362	897698	A	2004-07-20 11:46:00	2004-07-20 12:03:00
518362	897706	A	2004-07-20 12:03:00	2004-07-20 12:05:00
518362	897708	XS	2004-07-20 12:05:00	2004-07-20 14:03:29
518362	897889	XS	2004-07-20 14:03:28	2004-07-22 21:38:00
518362	899357	YS	2004-07-22 21:38:00	2004-07-22 21:40:00
518362	899358	YS	2004-07-22 21:40:00	2004-07-22 21:52:00
518362	899360	XS	2004-07-22 21:52:00	2004-07-24 10:11:00
518362	899729	XS	2004-07-24 10:11:00	2004-07-25 16:17:00
518362	899781	XS	2004-07-25 16:17:00	2004-07-25 16:40:00
518362	900064	XS	2004-07-25 16:40:00	2004-07-31 10:28:00
518362	902112	XS	2004-07-31 10:28:00	2004-08-18 14:50:00
518362	907449	ZS	2004-08-18 14:50:00	2004-08-19 23:12:00
518362	908088	ZS	2004-08-19 23:12:00	2004-10-11 23:22:00
518362	926964	ZS	2004-10-11 23:22:00	2004-10-14 19:07:00
518362	928149	A	2004-10-14 19:07:00	2004-11-10 22:30:00
518362	939535	AS	2004-11-10 22:30:00	2004-11-11 11:01:26
    I just need ideas. I have tried queries with subqueries with subqueries but always find cases where information is not being returned.
    Last edited by brucevde; Feb 2nd, 2007 at 01:57 PM.
    Reply With Quote Reply With Quote
  2. Feb 2nd, 2007, 04:01 PM #2
    szlamany
    szlamany is offline
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Help with Query

    This seems to do it:

    VB Code:
    1. Declare @Tbl Table (MastId int, HistId int, Loc varchar(2), StartDate datetime, ExpiryDate datetime)

    2.  
    3. Set NoCount On

    4.  
    5. Insert into @Tbl values (518362,870355,'A','2004-05-12 09:22:00','2004-05-12 12:16:00')

    6. Insert into @Tbl values (518362,870571,'AS','2004-05-12 12:16:00','2004-05-14 14:44:00')

    7. Insert into @Tbl values (518362,871773,'AS','2004-05-14 14:44:00','2004-05-19 16:55:00')

    8. Insert into @Tbl values (518362,873742,'AS','2004-05-19 16:55:00','2004-05-20 13:00:00')

    9. Insert into @Tbl values (518362,874379,'AS','2004-05-20 13:00:00','2004-05-21 11:30:00')

    10. Insert into @Tbl values (518362,874931,'A','2004-05-21 11:30:00','2004-05-23 14:04:00')

    11. Insert into @Tbl values (518362,874977,'AS','2004-05-23 14:04:00','2004-05-27 00:06:00')

    12. Insert into @Tbl values (518362,876304,'L','2004-05-27 00:06:00','2004-05-27 00:07:00')

    13. Insert into @Tbl values (518362,876306,'AS','2004-05-27 00:07:00','2004-07-08 10:53:00')

    14. Insert into @Tbl values (518362,892807,'AS','2004-07-08 10:53:00','2004-07-13 09:08:00')

    15. Insert into @Tbl values (518362,894863,'AS','2004-07-13 09:08:00','2004-07-17 23:58:00')

    16. Insert into @Tbl values (518362,897112,'AS','2004-07-17 23:58:00','2004-07-20 11:46:00')

    17. Insert into @Tbl values (518362,897698,'A','2004-07-20 11:46:00','2004-07-20 12:03:00')

    18. Insert into @Tbl values (518362,897706,'A','2004-07-20 12:03:00','2004-07-20 12:05:00')

    19. Insert into @Tbl values (518362,897708,'XS','2004-07-20 12:05:00','2004-07-20 14:03:29')

    20. Insert into @Tbl values (518362,897889,'XS','2004-07-20 14:03:28','2004-07-22 21:38:00')

    21. Insert into @Tbl values (518362,899357,'YS','2004-07-22 21:38:00','2004-07-22 21:40:00')

    22. Insert into @Tbl values (518362,899358,'YS','2004-07-22 21:40:00','2004-07-22 21:52:00')

    23. Insert into @Tbl values (518362,899360,'XS','2004-07-22 21:52:00','2004-07-24 10:11:00')

    24. Insert into @Tbl values (518362,899729,'XS','2004-07-24 10:11:00','2004-07-25 16:17:00')

    25. Insert into @Tbl values (518362,899781,'XS','2004-07-25 16:17:00','2004-07-25 16:40:00')

    26. Insert into @Tbl values (518362,900064,'XS','2004-07-25 16:40:00','2004-07-31 10:28:00')

    27. Insert into @Tbl values (518362,902112,'XS','2004-07-31 10:28:00','2004-08-18 14:50:00')

    28. Insert into @Tbl values (518362,907449,'ZS','2004-08-18 14:50:00','2004-08-19 23:12:00')

    29. Insert into @Tbl values (518362,908088,'ZS','2004-08-19 23:12:00','2004-10-11 23:22:00')

    30. Insert into @Tbl values (518362,926964,'ZS','2004-10-11 23:22:00','2004-10-14 19:07:00')

    31. Insert into @Tbl values (518362,928149,'A','2004-10-14 19:07:00','2004-11-10 22:30:00')

    32. Insert into @Tbl values (518362,939535,'AS','2004-11-10 22:30:00','2004-11-11 11:01:26')

    33.  
    34. Select MastId,StartDate From @Tbl Where Loc like '%S' and (StartDate between '2004-08-01' and '2004-08-15'

    35.                         or ExpiryDate between '2004-08-01' and '2004-08-15'

    36.                         or (StartDate<'2004-08-01' and ExpiryDate>'2004-08-15'))

    37. -- Get our record that we will pivot on

    38.  
    39. Select Max(StartDate) From @Tbl T1 Where T1.StartDate<(Select StartDate From @Tbl Where Loc like '%S' and (StartDate between '2004-08-01' and '2004-08-15'

    40.                                 or ExpiryDate between '2004-08-01' and '2004-08-15'

    41.                                 or (StartDate<'2004-08-01' and ExpiryDate>'2004-08-15')))

    42.                         and T1.Loc not like '%S'

    43. -- and the bottom boundary (use that pivot query as the sub-query)

    44.  
    45. Select Min(StartDate) From @Tbl T1 Where T1.StartDate>(Select StartDate From @Tbl Where Loc like '%S' and (StartDate between '2004-08-01' and '2004-08-15'

    46.                                 or ExpiryDate between '2004-08-01' and '2004-08-15'

    47.                                 or (StartDate<'2004-08-01' and ExpiryDate>'2004-08-15')))

    48.                         and T1.Loc not like '%S'

    49. -- and the top boundary (use that pivot query as the sub-query)

    50.  
    51. Select * From @Tbl T2 Where Loc like '%S'

    52.                 and StartDate>(Select Max(StartDate) From @Tbl T1 Where T1.StartDate<(Select StartDate From @Tbl

    53.                                 Where Loc like '%S' and (StartDate between '2004-08-01' and '2004-08-15'

    54.                                 or ExpiryDate between '2004-08-01' and '2004-08-15'

    55.                                 or (StartDate<'2004-08-01' and ExpiryDate>'2004-08-15')))

    56.                         and T1.Loc not like '%S')

    57.                 and StartDate<(Select Min(StartDate) From @Tbl T1 Where T1.StartDate>(Select StartDate From @Tbl

    58.                                 Where Loc like '%S' and (StartDate between '2004-08-01' and '2004-08-15'

    59.                                 or ExpiryDate between '2004-08-01' and '2004-08-15'

    60.                                 or (StartDate<'2004-08-01' and ExpiryDate>'2004-08-15')))

    61.                         and T1.Loc not like '%S')

    62. -- now the rows between our boundaries (use the bottom and top boundary queries)
    returns...

    VB Code:
    1. MastId      StartDate                                              

    2. ----------- ------------------------------------------------------ 

    3. 518362      2004-07-31 10:28:00.000

    4.  
    5.                                                        

    6. ------------------------------------------------------ 

    7. 2004-07-20 12:03:00.000

    8.  
    9.                                                        

    10. ------------------------------------------------------ 

    11. 2004-10-14 19:07:00.000

    12.  
    13. MastId      HistId      Loc  StartDate                                              ExpiryDate                                             

    14. ----------- ----------- ---- ------------------------------------------------------ ------------------------------------------------------ 

    15. 518362      897708      XS   2004-07-20 12:05:00.000                                2004-07-20 14:03:29.000

    16. 518362      897889      XS   2004-07-20 14:03:28.000                                2004-07-22 21:38:00.000

    17. 518362      899357      YS   2004-07-22 21:38:00.000                                2004-07-22 21:40:00.000

    18. 518362      899358      YS   2004-07-22 21:40:00.000                                2004-07-22 21:52:00.000

    19. 518362      899360      XS   2004-07-22 21:52:00.000                                2004-07-24 10:11:00.000

    20. 518362      899729      XS   2004-07-24 10:11:00.000                                2004-07-25 16:17:00.000

    21. 518362      899781      XS   2004-07-25 16:17:00.000                                2004-07-25 16:40:00.000

    22. 518362      900064      XS   2004-07-25 16:40:00.000                                2004-07-31 10:28:00.000

    23. 518362      902112      XS   2004-07-31 10:28:00.000                                2004-08-18 14:50:00.000

    24. 518362      907449      ZS   2004-08-18 14:50:00.000                                2004-08-19 23:12:00.000

    25. 518362      908088      ZS   2004-08-19 23:12:00.000                                2004-10-11 23:22:00.000

    26. 518362      926964      ZS   2004-10-11 23:22:00.000                                2004-10-14 19:07:00.000

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:     [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008
    Reply With Quote Reply With Quote
  3. Feb 2nd, 2007, 04:07 PM #3
    szlamany
    szlamany is offline
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Help with Query

    You will have to deal with the top and bottom boundary queries returning nothing - when there are no boundaries. In that case the boundary is DATEADD(dd,-1,{the min date in our %S group) and DATEADD(1,dd,{the max date in our %S group})

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:     [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008
    Reply With Quote Reply With Quote
  4. Feb 3rd, 2007, 11:50 AM #4
    brucevde
    brucevde is offline

    Thread Starter
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Help with Query

    Thanks szlamany. I did some test and unfortunately the query did not work. By changing the date range (Jul 12 - Aug 20), the subquery returns multiple records which causes an error. I added Max to the subquery but the dates in range 876306 to 897112 were not returned along with the other range.

    Oh well. Now it's time to enjoy the weekend.
    Last edited by brucevde; Feb 3rd, 2007 at 11:55 AM.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules


Click Here to Expand Forum to Full Width