Results 1 to 11 of 11

Thread: SQL Query Help

  1. #1
    Join Date
    Jan 2004
    Location
    Pakistan
    Posts
    2

    Question Unanswered: SQL Query Help

    tblAssignments
    ass_id
    ass_user_id
    ass_cty_id
    ass_crd_start
    ass_crd_end (Ending Number)

    tblCards
    crd_id
    crd_cty_id
    crd_start_num
    crd_end_num
    crd_desc

    tblCardTypes
    cty_id
    cty_name
    cty_desc

    state of tblCardTypes
    cty_id cty_name cty_desc
    1 Gold some desc

    state of tblCards
    crd_id crd_cty_id crd_start_num crd_end_num crd_desc
    1 1 1 10000 some desc

    state of tblAssignments
    ass_id ass_user_id ass_cty_id ass_cd_start ass_cd_end
    1 1 1 1 100
    2 2 1 200 250
    3 1 1 150 175
    4 2 1 5000 7000

    some information about the table structure
    tblCardTypes stores the card category information. TblCards hold the information about the cards. There are 9999 cards in the category Gold. TblAssignments contains the information about the users who are assigned ranges from cards. As you can see the user with id 1 is assigned cards from 1-100,150-175 From Gold Category. Now user with id 2 is assigned card range from 200-250,5000-7000. now I want t show the breakthrough(the card ranges that are now assigned yet) in this case cards from 101-149,176-199,251-4999,7001-10000

    can anyone help me with the query.

    HTH,
    Haseeb
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL Query Help

    Edit: I wrote this Oracle-specific solution before I realised this was the SQL forum. It won't work for other DBMSs as written.

    This will need a little work to fit your situation, but hopefully gives you an idea:
    Code:
      1  select nvl((lag(end_val) over (order by start_val)),0)+1 gap_start,
      2         start_val-1 gap_end
      3  from (select start_val, end_val from ranges
      4        UNION ALL
      5        select 10001, 0 from dual)
      6* where start_val > 1
    SQL> /
    
     GAP_START    GAP_END
    ---------- ----------
             1        149
           176        199
           251       4999
          7001      10000
    Last edited by andrewst; 01-22-04 at 07:01.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hopefully gives me an idea?

    yeah, if i had any clue as to what "lag" and "over" do

    i've used sql for over 15 years and i couldn't begin to guess how they work



    i'm sorry i don't have an sql solution for this either

    not without a bevy of NOT EXISTS subqueries, i'm guessing

    hmmm, where's my celko book...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    "lag" and "over" are cool Oracle-specific features, but I'm sure standard SQL has or will have something similar for "analytic functions". Anyway, here is a less Oracle-specific solution:

    1 select prev.end_val+1, curr.start_val-1
    2 from
    3 (select start_val, end_val from ranges
    4 UNION ALL
    5 select 10001, 0 from dual
    6 ) curr,
    7 (select start_val, end_val from ranges
    8 UNION ALL
    9 select 0, 0 from dual
    10 ) prev
    11 where prev.end_val = (select nvl(max(p.end_val),0)
    12 from ranges p
    13 where p.end_val < curr.start_val
    14 )
    15 and prev.end_val+1 <= curr.start_val-1
    16 order by 1;

    PREV.END_VAL+1 CURR.START_VAL-1
    -------------- ----------------
    101 149
    176 199
    251 4999
    7001 10000

    That Oracle-specifi NVL in line 11 is called something like COALESCE in standard SQL I think? It means: If first parameter is not null, return it, else return the second parameter.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, coalesce

    nice query; bookmarked

    DUAL is oracle-specific too, but at least i know what it does
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oh yes, I forgot about DUAL. How would I survive without DUAL? Is there a generic alternative (other than creating a table called DUAL and inserting 1 row)?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, there isn't, not consistently across all RDBMSs

    what i often do is
    Code:
    select 'somethingelse'
      from integers
     where i=1
    but that's because i typically always define my integers table wherever i go, because it's so incredibly useful for all sorts of queries --
    Code:
    create table integers (i integer); 
    insert into integers (i) values (0); 
    insert into integers (i) values (1); 
    insert into integers (i) values (2); 
    insert into integers (i) values (3); 
    insert into integers (i) values (4); 
    insert into integers (i) values (5); 
    insert into integers (i) values (6); 
    insert into integers (i) values (7); 
    insert into integers (i) values (8); 
    insert into integers (i) values (9);
    alternatively, sometimes an application will have various loose pieces of necessary data which i will store in a "one of a kind" table --
    Code:
    create table ooak
    ( appversion    varchar(5)   
    , exchangerate  numeric(7,4) 
    , archivebefore date
    );
    and of course then i can use this just like DUAL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Shouldn't standard SQL have a solution to this? Perhaps something like:

    SELECT a,b,c FROM VALUES(1 AS a, 'Hello' AS b, 99 AS c);

    Just off the top of my head...

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think a recent version of standard sql (don't ask me, i dunno which version, sql 2003?) allows
    Code:
    select 1 AS a, 'Hello' AS b, 99 AS c
    i.e. without specifying a table

    you can do this now in sql server
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2004
    Location
    Pakistan
    Posts
    2
    Originally posted by andrewst
    "lag" and "over" are cool Oracle-specific features, but I'm sure standard SQL has or will have something similar for "analytic functions". Anyway, here is a less Oracle-specific solution:

    1 select prev.end_val+1, curr.start_val-1
    2 from
    3 (select start_val, end_val from ranges
    4 UNION ALL
    5 select 10001, 0 from dual
    6 ) curr,
    7 (select start_val, end_val from ranges
    8 UNION ALL
    9 select 0, 0 from dual
    10 ) prev
    11 where prev.end_val = (select nvl(max(p.end_val),0)
    12 from ranges p
    13 where p.end_val < curr.start_val
    14 )
    15 and prev.end_val+1 <= curr.start_val-1
    16 order by 1;

    PREV.END_VAL+1 CURR.START_VAL-1
    -------------- ----------------
    101 149
    176 199
    251 4999
    7001 10000

    That Oracle-specifi NVL in line 11 is called something like COALESCE in standard SQL I think? It means: If first parameter is not null, return it, else return the second parameter.
    you can say that i dont know anything about sql. all this is over my head. i am using sql server. i have tried searching for replacement for nvl but no avail. can you please help me in this regard. i the card also depends on the cty_id. you can download the attachment to better view the table stucture.
    Please Help

  11. #11
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    SQL Server: ISNULL(expression, value)

    If expression is null then value else expression.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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