January 22nd, 2004, 05:12 AM
SQL Query Help
***_crd_end (Ending Number)
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
***_id ***_user_id ***_cty_id ***_cd_start ***_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.
January 22nd, 2004, 09:21 AM
anyone wishing to help should first see this solution posted on, um, another site
January 22nd, 2004, 04:15 PM
but that solution is oracle speacific. can anyone know how to do this using sql server?
January 22nd, 2004, 04:40 PM
it's a really tough problem, the gaps
i would recommend you take tony's solution and substitute your own column/table names and try it
instead of SELECT x,y FROM DUAL, in sql server all you have to say is SELECT x,y
also, just replace NVL with COALESCE
January 22nd, 2004, 04:54 PM
i have tried but no luck. the query doesnt retun anything.
January 22nd, 2004, 04:58 PM
the query return nothin.can we use procedure for this???