Thread: Lookup?? in sql

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0

    Lookup?? in sql


    Hi all,

    Im new to this forum and quite new to sql. I use(d) sql mainly to pul data from a database and use this data in excel to manipulate. I did some summarising in sql when the dataset got too large...

    Anyhow, Im struggling with the following since my excel seems to crash when i try to solve this with an excel formula after pulling the data by some simple sql.

    Basically I have a column with 8 or 9 digit card numbers, a column with a creation date, and a column with a expiry data.

    In a 4th column I want to see how many months a card was valid.

    The problem is that some cards are created, then replaced by a new card and expired. All first cards start with 01xxxxxx(x), and second cards the the same number but start with 02xxxxxx(x) (possibly also thirds, fouth etc. with 03, 04 etc).

    In excel i had the formula that does the trick.
    Code:
    =IF(E6<D6;"";IF(E6>NOW();ROUND(DAYS360(D6;NOW();TRUE)/30;0);ROUND(DAYS360(VLOOKUP("01"&MID(B6;3;10);$B:$D;3;0);E6;TRUE)/30;0)))
    column E = expiry date
    Column d = creation date
    column b = cardnumbers
    unfortunately my excel crashes when applied to the whole table.

    The sql code i use to pull the data
    Code:
    SELECT 
    "BASCVATTRIBUTE"."BCVAB_NAME", "WRKCVBALANCE"."WCVBL_CVNUMBER", "WRKCVBALANCE"."WCVBL_LIVESTATE", "WRKCVBALANCE"."WCVBL_DTCREATED", "WRKCVBALANCEDETAIL"."WCVBD_EXPIRYDATE"
     
    FROM   
    ("TICKETDOLPHIN"."WRKCVBALANCEDETAIL" "WRKCVBALANCEDETAIL" INNER JOIN "TICKETDOLPHIN"."WRKCVBALANCE" "WRKCVBALANCE" ON "WRKCVBALANCEDETAIL"."WCVBD_WCVBL_ID"="WRKCVBALANCE"."WCVBL_ID") INNER JOIN "TICKETDOLPHIN"."BASCVATTRIBUTE" "BASCVATTRIBUTE" ON "WRKCVBALANCE"."WCVBL_BCVAB_ID"="BASCVATTRIBUTE"."BCVAB_ID"
     WHERE 
     "WRKCVBALANCE"."WCVBL_LIVESTATE"=0 AND ("BASCVATTRIBUTE"."BCVAB_NAME" LIKE 'c1' 
    OR "BASCVATTRIBUTE"."BCVAB_NAME" LIKE 'c2' 
    OR "BASCVATTRIBUTE"."BCVAB_NAME" LIKE 'c3' OR "BASCVATTRIBUTE"."BCVAB_NAME" LIKE 'c4')
    Now im looking to do the trick in slq when possible.

    Hope anyone can help to put me in the right direction.
    Any help is much appreciated!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    What output you expected
    if you have
    card 123456789 from 1/1/2000 thru 12/31/2004
    card 223456789 from 1/1/2005 thru 12/31/2010
    card 323456789 from 1/1/2011 thru 12/31/2014

    according to your rules all 3 cards is replacement for the same card, what output you expect?
    card 323456789 remaining 20 month? or
    card 123459789 remaining 20 month?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    I expect the highest cardnumber, 323456789, to come up with start 1/1/2010 expirydate 12/31/2014. So now - 1/1/2010 as number of months.

    Thanks for your interest in the topic!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    one more question how you define card number varchar or int?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    really it is question about leading 0 in card number... because it is may be 8 or 9 digits
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    I assume its an integer. How would i be certain?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    do you leading 0?
    like 01xxxxxx(x)
    or it is
    1xxxxxx(x)
    how you know
    number 112345678
    is record 1 for card 12345678
    or number 11 for card 2345678?
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    Ok

    First two digits are always the number of card thats where issued. So when the cardnumber starts with 11 its the eleventh card issued.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    So first 2 characters if entry number for card and the rest is card number? right?
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    Yes
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    And you on SQL server. I'm asking because you sql not looks like from SQL server
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    The query is microsoft query ODBC
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    you are in excel and just run odbc connection to get data from SQL server?
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    Yes

    Or access when the output it too large
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    so, if you have table with card data named cards
    your query should be
    something like
    Code:
    select c.cardNumber, 
    		c.creationDate, 
    		c.ExpiryDate,
    		DATEDIFF(MONTH, GETDATE(), ExpiryDate) as monthLeft 
    from #cards c
    join (select max(LEFT(cardNumber,2)) as entryNumber,
    		SUBSTRING(cardNumber,3,7) as cardNumber,  
    		max(LEFT(cardNumber,2)) + SUBSTRING(cardNumber,3,7) as fullNumber
    		from #cards
    		group by SUBSTRING(cardNumber,3,7)) cl
    on c.cardNumber = cl.fullNumber
    for data like below
    012345678 2000-01-01 2000-12-13
    012345678 2000-01-01 2004-12-31
    013345678 2000-01-01 2004-12-31
    013345678 2013-01-01 2015-12-31
    022345678 2005-01-01 2010-12-31
    023345678 2005-01-01 2010-12-31
    032345678 2011-01-01 2014-12-31
    033345678 2011-01-01 2012-12-31

    it is produce output
    cardNumber creationDate ExpiryDate monthLeft
    032345678 2011-01-01 2014-12-31 20
    033345678 2011-01-01 2012-12-31 -4

IMN logo majestic logo threadwatch logo seochat tools logo