Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2013
    Posts
    2

    Unanswered: how to structure my CurrBal query

    Hi All

    Greetings from South Africa

    I am very new too writing queries and would appreciate some help structuring a query to give me the CurrBal per CardNo.

    I have table that looks like this:

    CardNo DepDate CurrBal DepAmnt TransNo
    1 2013-06-04 11:50 AM 79 0 6
    2 2013-08-05 15:34 PM 52 100 41
    2 2013-08-05 14:11 PM -48 0 40
    3 2013-07-09 13:52 PM 49 0 12
    3 2013-07-22 13:51 PM 11 0 14
    1 2013-06-12 10:46 AM 63 0 7
    3 2013-07-15 14:04 PM 33 0 13
    2 2013-08-06 15:05 PM 39 0 42
    2 2013-08-07 13:38 PM 30 0 43

    I am looking to order this table by CardNo and then TransNo but i only want the query to display the record with the highest TransNo for each CardNo. In other words discard the records with the lower TransNo for each CardNo.

    My desired result should hopefully look something like this:

    CardNo DepDate CurrBal DepAmnt TransNo
    1 2013-06-12 10:46 AM 63 0 7
    2 2013-08-07 13:38 PM 30 0 43
    3 2013-07-22 13:51 PM 11 0 14

    I am using SQL 2012 Express but would also like this query to work in SQL 2005.

    Please would you guys be so kind as to tell me how too write this query.

    Thanks
    Stephen

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    This should do the trick in 2005 through 2014.

    Code:
    declare @t1 table
    (
       CardNo int,
       DepDate datetime,
       CurrBal decimal(7,2),
       DepAmt  decimal(7,2),
       TransNo int
    )   
    
    insert @t1 (CardNo, DepDate, CurrBal, DepAmt, TransNo) values
      (1, '2013-06-04 11:50 AM', 79, 0, 6)
     ,(2, '2013-08-05 15:34 PM', 52, 100, 41)
     ,(2, '2013-08-05 14:11 PM', -48, 0, 40)
     ,(3, '2013-07-09 13:52 PM', 49, 0, 12)
     ,(3, '2013-07-22 13:51 PM', 11, 0, 14)
     ,(1, '2013-06-12 10:46 AM', 63, 0, 7)
     ,(3, '2013-07-15 14:04 PM', 33, 0, 13)
     ,(2, '2013-08-06 15:05 PM', 39, 0, 42)
     ,(2, '2013-08-07 13:38 PM', 30, 0, 43)
     
     
     ;with cte as
     (
       select CardNo, DepDate, currbal, DepAmt, TransNo,
              ROW_NUMBER() over (Partition by CardNo Order By TransNo DESC) RowNum
         from @t1
    )
    select CardNo, DepDate, currbal, DepAmt, TransNo
      from cte
      where cte.RowNum = 1
    Which gives me:

    Code:
    1	2013-06-12 10:46:00.000	63.00	0.00	7
    2	2013-08-07 13:38:00.000	30.00	0.00	43
    3	2013-07-22 13:51:00.000	11.00	0.00	14

  3. #3
    Join Date
    Aug 2013
    Posts
    2
    Got a solution.
    Thanks so much for your help



    Quote Originally Posted by LinksUp View Post
    This should do the trick in 2005 through 2014.

    Code:
    declare @t1 table
    (
       CardNo int,
       DepDate datetime,
       CurrBal decimal(7,2),
       DepAmt  decimal(7,2),
       TransNo int
    )   
    
    insert @t1 (CardNo, DepDate, CurrBal, DepAmt, TransNo) values
      (1, '2013-06-04 11:50 AM', 79, 0, 6)
     ,(2, '2013-08-05 15:34 PM', 52, 100, 41)
     ,(2, '2013-08-05 14:11 PM', -48, 0, 40)
     ,(3, '2013-07-09 13:52 PM', 49, 0, 12)
     ,(3, '2013-07-22 13:51 PM', 11, 0, 14)
     ,(1, '2013-06-12 10:46 AM', 63, 0, 7)
     ,(3, '2013-07-15 14:04 PM', 33, 0, 13)
     ,(2, '2013-08-06 15:05 PM', 39, 0, 42)
     ,(2, '2013-08-07 13:38 PM', 30, 0, 43)
     
     
     ;with cte as
     (
       select CardNo, DepDate, currbal, DepAmt, TransNo,
              ROW_NUMBER() over (Partition by CardNo Order By TransNo DESC) RowNum
         from @t1
    )
    select CardNo, DepDate, currbal, DepAmt, TransNo
      from cte
      where cte.RowNum = 1
    Which gives me:

    Code:
    1	2013-06-12 10:46:00.000	63.00	0.00	7
    2	2013-08-07 13:38:00.000	30.00	0.00	43
    3	2013-07-22 13:51:00.000	11.00	0.00	14

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I never could get into the CTE to replace:

    Code:
    select CardNo, max(DepDate), currbal, DepAmt, TransNo
      from my_table
    group by CardNo, currbal, DepAmt, TransNo

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Oops, should have been max on TransNo, got carried away

  6. #6
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by dav1mo View Post
    I never could get into the CTE to replace:

    Code:
    select CardNo, max(DepDate), currbal, DepAmt, TransNo
      from my_table
    group by CardNo, currbal, DepAmt, TransNo
    Even with the Max(TransNo), this query does not return the desired output. If your query was:

    Code:
    select CardNo, MAX(TransNo)
      from my_table
    group by CardNo
    you would be closer. But when you add the other columns to the select and the group by, you make each row unique making the query return all rows.

    The cte makes these kind of queries very easy. Well worth your time to learn about!

Posting Permissions

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