SQL Query Help

hasibiqbal · #1 (**permalink**) 01-22-04, 05:43

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

andrewst · #2 (**permalink**) 01-22-04, 05:54

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

r937 · #3 (**permalink**) 01-22-04, 07:02

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...

andrewst · #4 (**permalink**) 01-22-04, 07:38

"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.

r937 · #5 (**permalink**) 01-22-04, 07:53

yes, coalesce

nice query; bookmarked

DUAL is oracle-specific too, but at least i know what it does

andrewst · #6 (**permalink**) 01-22-04, 08:02

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)?

r937 · #7 (**permalink**) 01-22-04, 08:39

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

andrewst · #8 (**permalink**) 01-22-04, 08:50

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...

r937 · #9 (**permalink**) 01-22-04, 08:58

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

hasibiqbal · #10 (**permalink**) 01-22-04, 17:11

Quote:

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

r123456 · #11 (**permalink**) 01-22-04, 17:28

SQL Server: ISNULL(expression, value)

If expression is null then value else expression.

Internet Services

Web Development

Digital Marketing

Consumer Tech