Professional Web Applications Themes

help with SELECT statement - ASP

"Aaron" <abroadwayameritrust.com> wrote in message news:05a601c365df$b31a8d40$a401280aphx.gbl... > I have the the below select statement, which is only part > of what I need. > > Set rsAE = oConn.Execute("Select count(DISTINCT > GEN.lo_rep, sum(loan_am) as cnt) as total_records from > Gen.DBF GEN, USERINFO U where (((action_date>= {" & > (start_date2) & "} and action_date < {" & (end_date) > & "}) or (app_date>= {" & (start_date2) & "} and app_date > < {" & (end_date) & "}) and (branch_type = [B]))) " ) > > **I need to order by the cnt, but I need cnt to be the > sum ...

  1. #1

    Default Re: help with SELECT statement

    "Aaron" <abroadwayameritrust.com> wrote in message
    news:05a601c365df$b31a8d40$a401280aphx.gbl...
    > I have the the below select statement, which is only part
    > of what I need.
    >
    > Set rsAE = oConn.Execute("Select count(DISTINCT
    > GEN.lo_rep, sum(loan_am) as cnt) as total_records from
    > Gen.DBF GEN, USERINFO U where (((action_date>= {" &
    > (start_date2) & "} and action_date < {" & (end_date)
    > & "}) or (app_date>= {" & (start_date2) & "} and app_date
    > < {" & (end_date) & "}) and (branch_type = [B]))) " )
    >
    > **I need to order by the cnt, but I need cnt to be the
    > sum of the loan_amt only where action_date>= {" &
    > (start_date2) & "} and action_date < {" & (end_date)
    > & "})
    >
    > I do not know how to do this. Please help!!!!! Thanks,
    "SUM(ABS(action_date>= {" & start_date2 & "} and action_date < {" &
    end_date & "})*loan_amt) AS cnt"

    Notes:
    1. Don't use ODBC. Use OLEDB.
    2. Use a stored procedure/parameterized query
    3. When posting database related questions, please specify which
    database and version you are using and the structure of the tables in
    question.

    HTH
    -Chris


    Chris Hohmann Guest

  2. #2

    Default Re: help with SELECT statement

    Sorry,

    Thanks for the help, but I cannot do a stored prod b/c I
    am accessing foxpro tables.

    Any onther ideas?
    Thanks


    >-----Original Message-----
    >"Aaron" <abroadwayameritrust.com> wrote in message
    >news:05a601c365df$b31a8d40$a401280aphx.gbl...
    >> I have the the below select statement, which is only
    part
    >> of what I need.
    >>
    >> Set rsAE = oConn.Execute("Select count(DISTINCT
    >> GEN.lo_rep, sum(loan_am) as cnt) as total_records from
    >> Gen.DBF GEN, USERINFO U where (((action_date>= {" &
    >> (start_date2) & "} and action_date < {" & (end_date)
    >> & "}) or (app_date>= {" & (start_date2) & "} and
    app_date
    >> < {" & (end_date) & "}) and (branch_type = [B]))) " )
    >>
    >> **I need to order by the cnt, but I need cnt to be the
    >> sum of the loan_amt only where action_date>= {" &
    >> (start_date2) & "} and action_date < {" & (end_date)
    >> & "})
    >>
    >> I do not know how to do this. Please help!!!!! Thanks,
    >
    >"SUM(ABS(action_date>= {" & start_date2 & "} and
    action_date < {" &
    >end_date & "})*loan_amt) AS cnt"
    >
    >Notes:
    >1. Don't use ODBC. Use OLEDB.
    >2. Use a stored procedure/parameterized query
    >3. When posting database related questions, please
    specify which
    >database and version you are using and the structure of
    the tables in
    >question.
    >
    >HTH
    >-Chris
    >
    >
    >.
    >
    Aaron Guest

  3. #3

    Default Re: help with SELECT statement

    "Aaron" <abroadwayameritrust.com> wrote in message
    news:0d5101c36641$ab0673a0$a101280aphx.gbl...
    > Sorry,
    >
    > Thanks for the help, but I cannot do a stored prod b/c I
    > am accessing foxpro tables.
    >
    > Any onther ideas?
    > Thanks
    The notes were just afterthoughts. Did you try the following expression
    for cnt?

    "SUM(ABS(action_date>= {" & start_date2 & "} and > action_date < {" &
    end_date & "})*loan_amt) AS cnt"

    Using this expression instead of your existing expression for cnt should
    give you the values you're looking for.

    HTH-
    Chris


    Chris Hohmann Guest

Similar Threads

  1. select statement woes
    By megalith in forum Coldfusion Database Access
    Replies: 8
    Last Post: March 27th, 09:07 PM
  2. sql select case statement
    By Anderson11983 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 9th, 01:50 PM
  3. Enter Select Statement?
    By Hung Kuen Kung Fu in forum Dreamweaver AppDev
    Replies: 0
    Last Post: March 22nd, 09:30 PM
  4. SP with Select statement
    By Gerald in forum ASP Database
    Replies: 3
    Last Post: November 27th, 03:38 PM
  5. SELECT statement
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 23
    Last Post: August 18th, 01:12 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