Thread: SQL Query Help

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Lahore
    Posts
    4
    Rep Power
    0

    Question SQL Query Help


    tblAssignments

    ***_id

    ***_user_id

    ***_cty_id

    ***_crd_start

    ***_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

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



    Please Help,

    Haseeb
    Attached Files
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    anyone wishing to help should first see this solution posted on, um, another site
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Lahore
    Posts
    4
    Rep Power
    0
    but that solution is oracle speacific. can anyone know how to do this using sql server?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    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

    good luck
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Lahore
    Posts
    4
    Rep Power
    0
    i have tried but no luck. the query doesnt retun anything.
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Lahore
    Posts
    4
    Rep Power
    0
    the query return nothin.can we use procedure for this???

IMN logo majestic logo threadwatch logo seochat tools logo