Professional Web Applications Themes

Counting 1st Record only - Microsoft SQL / MS SQL Server

Hello, I am trying to write a query which will identify only those members who have used either one or more services in a given year. 1 MemberID can have 1 or more UnitID's. This means that if I group the report by UnitID's, I will see multiple MemberID's. I just want to see 1 MemberID based on utilization (that being >=1) based on first use in a given calander year. However, that member should be repeated the next year if he/she has used the service 1 or more times. I am just trying to count my members using services ...

  1. #1

    Default Counting 1st Record only

    Hello,

    I am trying to write a query which will identify only
    those members who have used either one or more services in
    a given year. 1 MemberID can have 1 or more UnitID's. This
    means that if I group the report by UnitID's, I will see
    multiple MemberID's. I just want to see 1 MemberID based
    on utilization (that being >=1) based on first use in a
    given calander year.

    However, that member should be repeated the next year if
    he/she has used the service 1 or more times. I am just
    trying to count my members using services irrespective of
    the number of times they have used it.

    Here is a data-set I am CURRENTLY generating:

    Yr Mth MemberID MemType UnitID Type
    ==============================================
    2003 April 7044 AS C Renew
    2003 April 7044 AS L Change
    2003 June 7044 AS C Change
    2003 June 7044 AS L Renew
    2004 May 7044 AS C Change
    2003 April 5000 NA CL Current
    2003 April 5000 NA WSR New
    2003 June 5000 NA WSR Change
    2004 May 5000 NA CL Change
    2004 May 5000 NA WSR Renew


    Here is the results set I want to see:

    Yr Mth MemberID MemType UnitID Type
    ==============================================
    2003 April 7044 AS C Renew
    2004 May 7044 AS C Change
    2003 April 5000 NA CL Current
    2004 May 5000 NA WSR Renew

    Any idea on how would I acieve this result set. I am on
    SQL Query yzer ver.8.00.194

    Thanks so much.

    Joshi

    J. Joshi Guest

  2. #2

    Default Re: Counting 1st Record only

    Here is a generalized t-SQL method for such requirements,

    SELECT *
    FROM tbl
    WHERE keycol = (SELECT TOP 1 keycol
    FROM tbl t1
    WHERE t1.dupcol = tbl.dupcol
    ORDER BY t1.keycol DESC)

    Here the keycol is the column/set of columns which can uniquely identify a
    row within the group & dupcol is the column which is duplicated across the
    rows.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

Similar Threads

  1. How can i retrieve record ONLY From 300 - 400 in amillion Record Table?
    By keonglah in forum Coldfusion Database Access
    Replies: 6
    Last Post: June 13th, 02:53 AM
  2. Find record w/ conditions met in the same child record
    By Thomas Berg in forum FileMaker
    Replies: 2
    Last Post: October 21st, 05:01 AM
  3. Replies: 1
    Last Post: October 3rd, 04:20 PM
  4. Stop adding record in subform after record count = 1
    By Charlie in forum Microsoft Access
    Replies: 0
    Last Post: July 31st, 10:42 AM
  5. Adding a new record when the record source is a query.
    By Andy in forum Microsoft Access
    Replies: 0
    Last Post: July 29th, 01:41 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139